MS SQL for Crystal Report
SELECT
ACCT_UNIT
,DESCRIPTION
FROM GLNAMES
WHERE GLNAMES.LEVEL_DEPTH = 1
OR GLNAMES.LEVEL_DEPTH = 4
Returns data:
ACCT_UNIT DESCRIPTION
101 GENERAL FUND - CITY
1010100 FM-CHIEF FINANCIAL OFFICER
Is there a way to create a SQL which would have four columns that would have the data returned as:
Acct_Unit Description Acct_Unit Description
101 GENERAL FUND - CITY 1010100 FM-CHIEF FINANCIAL OFFICER
I tried a SQL SELECT within a SELECT and I did not return the correct descriptions.
ACCT_UNIT
,DESCRIPTION
FROM GLNAMES
WHERE GLNAMES.LEVEL_DEPTH = 1
OR GLNAMES.LEVEL_DEPTH = 4
Returns data:
ACCT_UNIT DESCRIPTION
101 GENERAL FUND - CITY
1010100 FM-CHIEF FINANCIAL OFFICER
Is there a way to create a SQL which would have four columns that would have the data returned as:
Acct_Unit Description Acct_Unit Description
101 GENERAL FUND - CITY 1010100 FM-CHIEF FINANCIAL OFFICER
I tried a SQL SELECT within a SELECT and I did not return the correct descriptions.
ASKER
I ran this query:
SELECT
ACCT_UNIT
,DESCRIPTION
,VAR_LEVEL_DISP
,PARENT_OBJ_ID
FROM GLNAMES
WHERE GLNAMES.LEVEL_DEPTH = 1
OR GLNAMES.LEVEL_DEPTH = 4
With the following results:
ACCT_UNIT DESCRIPTION VAR_LEVEL_DISP PARENT_OBJ_ID
101 GENERAL FUND - CITY 101 0
1010100 FM-CHIEF FINANCIAL OFFICER 101-015-100-0100 4,187
The Parent Obj ID is 0 fo all Acct Units that are 3 digit, such as, 101.
SELECT
ACCT_UNIT
,DESCRIPTION
,VAR_LEVEL_DISP
,PARENT_OBJ_ID
FROM GLNAMES
WHERE GLNAMES.LEVEL_DEPTH = 1
OR GLNAMES.LEVEL_DEPTH = 4
With the following results:
ACCT_UNIT DESCRIPTION VAR_LEVEL_DISP PARENT_OBJ_ID
101 GENERAL FUND - CITY 101 0
1010100 FM-CHIEF FINANCIAL OFFICER 101-015-100-0100 4,187
The Parent Obj ID is 0 fo all Acct Units that are 3 digit, such as, 101.
So would 3 digit account numbers always appear in the left column and the other accounts in the right?
ASKER
The hierarchy would be the 3-digit (or Fund) and the 7-digit (Acct Unit). There are 126 3-digit (Fund) and the 7-digit (Acct Unit) belong to the Fund based on the first 3 digits. The example above is that the FM-Chief Financial Office, 10101100, belongs to the General Fund City, 101.
See if this does what you're needing
SELECT
g1.ACCT_UNIT, g1.DESCRIPTION,
g2.ACCT_UNIT, g2.DESCRIPTION
FROM GLNAMES gq left outer join GLNAMES g2
on g1.acct = substring(g2.acct, 1,3)
WHERE len(g1.acct) = 3 and len(g2.acct) > 3 and
(GLNAMES.LEVEL_DEPTH = 1
OR GLNAMES.LEVEL_DEPTH = 4)
SELECT
g1.ACCT_UNIT, g1.DESCRIPTION,
g2.ACCT_UNIT, g2.DESCRIPTION
FROM GLNAMES gq left outer join GLNAMES g2
on g1.acct = substring(g2.acct, 1,3)
WHERE len(g1.acct) = 3 and len(g2.acct) > 3 and
(GLNAMES.LEVEL_DEPTH = 1
OR GLNAMES.LEVEL_DEPTH = 4)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
Isn't that the results of the query that was originally posted?
Hey garyjgs, looks like you marked my post by mistake (it was just a question). Did you meant to accept Jared_S's solution ?
Maybe the level_Depth of one of them is 1, and the other 4?