May 8, 2015 at 12:02 am
I get some error on the alias columns;
OUTER APPLY (
SELECT
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 1 THEN b1_ACCOUNTNUM END) AS b1_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 1 THEN b1_ACCOUNTNAME END) AS b1_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 2 THEN b1_ACCOUNTNUM END) AS b2_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 2 THEN b1_ACCOUNTNAME END) AS b2_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 3 THEN b1_ACCOUNTNUM END) AS b3_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 3 THEN b1_ACCOUNTNAME END) AS b3_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 4 THEN b1_ACCOUNTNUM END) AS b4_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 4 THEN b1_ACCOUNTNAME END) AS b4_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 5 THEN b1_ACCOUNTNUM END) AS b5_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 5 THEN b1_ACCOUNTNAME END) AS b5_ACCOUNTNAME
FROM HST_AX.LEDGERTABLE b
WHERE
b1_ACCOUNTNUM IN ( LEFT(a.ACCOUNTNUM, 1), LEFT(a.ACCOUNTNUM, 2), LEFT(a.ACCOUNTNUM, 3),
LEFT(a.ACCOUNTNUM, 4), LEFT(a.ACCOUNTNUM, 5) )
) AS outer_apply_1
look picture
May 8, 2015 at 6:53 am
This looks like Dynamics AX, right? It looks like you don't have enough conditions in your JOINs.
The first join, from LedgerAccountCategory to LedgerTable, is on DataAreaId only. This means, within each company, you're basically doing a cross join - probably not what you intended. I think you need to add AND a.AccountCategoryRef = g.AccountCategoryRef. That's based on the table Relations on LedgerTable; you should verify.
On each of the joins b through f, you should have AND a.DataAreaId = ?.DataAreaId. Even if you have only one company, you should still add this test; all of the indexes have DataAreaId first, before the keys you see in the AOT indexes.
The other thing is you have a lot of what are probably unnecessary CASTs. You CAST a.DataAreaId as NVARCHAR(4) three times. But LedgerTable.DataAreaId (all DataAreaIds, for that matter) is already NVARCHAR(4), so this CAST does nothing. Similarly, AccountNum is already defined as NVARCHAR(20), and Name is already NVARCHAR(60). The only CAST I see that you actually need is where you need to convert a.ACCOUNTPLTYPE from int to character so you can concatenate it with other character values. Removing the CASTs probably won't make any real performance difference, but it will definitely enhance readability.
May 8, 2015 at 7:52 am
I modify my SQL and this only 1 sec to run it 🙂 anyway tnx for comment 🙂
WITH ACCOUNT AS
(
SELECT
[DATAAREAID] + '_' + [ACCOUNTNUM] AS FACC_BKEY
,[ACCOUNTNUM]
,[ACCOUNTNAME]
,[ACCOUNTPLTYPE]
,[COMPANYGROUPACCOUNT]
,[ACCOUNTNAMEALIAS]
,[ACCOUNTCATEGORYREF]
,[DATAAREAID]
,[RECVERSION]
,[RECID]
FROM
HST_AX.[LEDGERTABLE]
)
, ACCOUNTINTERVAL AS
(
SELECT
[LEDGERTABLEINTERVAL].[ACCOUNTTABLEID]
,[LEDGERTABLEINTERVAL].[ACCOUNTRECID]
,[LEDGERTABLEINTERVAL].[FROMACCOUNT]
,[LEDGERTABLEINTERVAL].[TOACCOUNT]
,[LEDGERTABLEINTERVAL].[DATAAREAID]
,[LEDGERTABLEINTERVAL].[RECVERSION]
,[LEDGERTABLEINTERVAL].[RECID]
,LEDGERTABLE.[ACCOUNTNUM]
,LEDGERTABLE.[ACCOUNTNAME]
FROM
HST_AX.[LEDGERTABLEINTERVAL]
INNER JOIN HST_AX.[LEDGERTABLE]
ON [LEDGERTABLEINTERVAL].[ACCOUNTRECID] = LEDGERTABLE.RECID
AND [LEDGERTABLEINTERVAL].[DATAAREAID] = LEDGERTABLE.[DATAAREAID]
)
, ACCOUNTCATEGORY AS
(
SELECT
[ACCOUNTCATEGORYREF]
,[DATAAREAID]
,[ACCOUNTCATEGORY]
,[DESCRIPTION]
,[ACCOUNTTYPE]
FROM HST_AX.[LEDGERACCOUNTCATEGORY]
)
SELECT
ACCOUNT.FACC_BKEY AS FACC_BKEY
,ACCOUNT.ACCOUNTNUM AS FACC_CODE
,ACCOUNT.ACCOUNTNAME AS FACC_DESC
,ACCOUNTINTERVALL5.ACCOUNTNUMAS FACC_H1_L5_CODE
,ACCOUNTINTERVALL5.ACCOUNTNAMEAS FACC_H1_L5_DESC
,ACCOUNTINTERVALL4.ACCOUNTNUMAS FACC_H1_L4_CODE
,ACCOUNTINTERVALL4.ACCOUNTNAMEAS FACC_H1_L4_DESC
,ACCOUNTINTERVALL3.ACCOUNTNUMAS FACC_H1_L3_CODE
,ACCOUNTINTERVALL3.ACCOUNTNAMEAS FACC_H1_L3_DESC
,ACCOUNTINTERVALL2.ACCOUNTNUMAS FACC_H1_L2_CODE
,ACCOUNTINTERVALL2.ACCOUNTNAMEAS FACC_H1_L2_DESC
,ACCOUNTINTERVALL1.ACCOUNTNUMAS FACC_H1_L1_CODE
,ACCOUNTINTERVALL1.ACCOUNTNAMEAS FACC_H1_L1_DESC
,ACCOUNT.ACCOUNTPLTYPEAS FACC_TYPE_CODE
,CASE ACCOUNT.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
ENDAS FACC_TYPE_DESC
,ACCOUNTCATEGORY.ACCOUNTCATEGORY
,ACCOUNTCATEGORY.DESCRIPTION
FROM
ACCOUNT
LEFT OUTER JOIN ACCOUNTINTERVAL AS ACCOUNTINTERVALL1
ON ACCOUNT.DATAAREAID = ACCOUNTINTERVALL1.DATAAREAID
AND ACCOUNT.ACCOUNTNUM BETWEEN ACCOUNTINTERVALL1.FROMACCOUNT AND ACCOUNTINTERVALL1.TOACCOUNT
AND LEN(ACCOUNTINTERVALL1.ACCOUNTNUM) = 1
LEFT OUTER JOIN ACCOUNTINTERVAL AS ACCOUNTINTERVALL2
ON ACCOUNT.DATAAREAID = ACCOUNTINTERVALL2.DATAAREAID
AND ACCOUNT.ACCOUNTNUM BETWEEN ACCOUNTINTERVALL2.FROMACCOUNT AND ACCOUNTINTERVALL2.TOACCOUNT
AND LEN(ACCOUNTINTERVALL2.ACCOUNTNUM) = 2
LEFT OUTER JOIN ACCOUNTINTERVAL AS ACCOUNTINTERVALL3
ON ACCOUNT.DATAAREAID = ACCOUNTINTERVALL3.DATAAREAID
AND ACCOUNT.ACCOUNTNUM BETWEEN ACCOUNTINTERVALL3.FROMACCOUNT AND ACCOUNTINTERVALL3.TOACCOUNT
AND LEN(ACCOUNTINTERVALL3.ACCOUNTNUM) = 3
LEFT OUTER JOIN ACCOUNTINTERVAL AS ACCOUNTINTERVALL4
ON ACCOUNT.DATAAREAID = ACCOUNTINTERVALL4.DATAAREAID
AND ACCOUNT.ACCOUNTNUM BETWEEN ACCOUNTINTERVALL4.FROMACCOUNT AND ACCOUNTINTERVALL4.TOACCOUNT
AND LEN(ACCOUNTINTERVALL4.ACCOUNTNUM) = 4
LEFT OUTER JOIN ACCOUNTINTERVAL AS ACCOUNTINTERVALL5
ON ACCOUNT.DATAAREAID = ACCOUNTINTERVALL5.DATAAREAID
AND ACCOUNT.ACCOUNTNUM BETWEEN ACCOUNTINTERVALL5.FROMACCOUNT AND ACCOUNTINTERVALL5.TOACCOUNT
AND LEN(ACCOUNTINTERVALL4.ACCOUNTNUM) = 5
LEFT OUTER JOIN ACCOUNTCATEGORY
ON ACCOUNT.DATAAREAID = ACCOUNTCATEGORY.DATAAREAID
AND ACCOUNT.ACCOUNTCATEGORYREF = ACCOUNTCATEGORY.ACCOUNTCATEGORYREF
WHERE
ACCOUNT.ACCOUNTPLTYPE IN (3, 0)
ORDER BY
1
May 8, 2015 at 8:13 am
GG_BI_GG (5/8/2015)
I get some error on the alias columns;
OUTER APPLY (
SELECT
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 1 THEN b1_ACCOUNTNUM END) AS b1_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 1 THEN b1_ACCOUNTNAME END) AS b1_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 2 THEN b1_ACCOUNTNUM END) AS b2_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 2 THEN b1_ACCOUNTNAME END) AS b2_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 3 THEN b1_ACCOUNTNUM END) AS b3_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 3 THEN b1_ACCOUNTNAME END) AS b3_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 4 THEN b1_ACCOUNTNUM END) AS b4_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 4 THEN b1_ACCOUNTNAME END) AS b4_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 5 THEN b1_ACCOUNTNUM END) AS b5_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 5 THEN b1_ACCOUNTNAME END) AS b5_ACCOUNTNAME
FROM HST_AX.LEDGERTABLE b
WHERE
b1_ACCOUNTNUM IN ( LEFT(a.ACCOUNTNUM, 1), LEFT(a.ACCOUNTNUM, 2), LEFT(a.ACCOUNTNUM, 3),
LEFT(a.ACCOUNTNUM, 4), LEFT(a.ACCOUNTNUM, 5) )
) AS outer_apply_1
look picture
Sorry, global replaces of names a little bit off.
OUTER APPLY (
SELECT
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 1 THEN b.ACCOUNTNUM END) AS b1_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 1 THEN b.ACCOUNTNAME END) AS b1_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 2 THEN b.ACCOUNTNUM END) AS b2_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 2 THEN b.ACCOUNTNAME END) AS b2_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 3 THEN b.ACCOUNTNUM END) AS b3_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 3 THEN b.ACCOUNTNAME END) AS b3_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 4 THEN b.ACCOUNTNUM END) AS b4_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 4 THEN b.ACCOUNTNAME END) AS b4_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 5 THEN b.ACCOUNTNUM END) AS b5_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUNTNUM) = 5 THEN b.ACCOUNTNAME END) AS b5_ACCOUNTNAME
FROM HST_AX.LEDGERTABLE b
WHERE
b.ACCOUNTNUM IN ( LEFT(a.ACCOUNTNUM, 1), LEFT(a.ACCOUNTNUM, 2), LEFT(a.ACCOUNTNUM, 3),
LEFT(a.ACCOUNTNUM, 4), LEFT(a.ACCOUNTNUM, 5) )
) AS outer_apply_1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply