Solved

MS SQL for Crystal Report

Posted on 2012-09-13
548 Views
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.
0
Question by:garyjgs

LVL 12

Expert Comment

Is there a third column that might differentiate between these records?
Maybe the level_Depth of one of them is 1, and the other 4?
0

Author Comment

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.
0

LVL 12

Expert Comment

So would 3 digit account numbers always appear in the left column and the other accounts in the right?
0

Author Comment

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.
0

LVL 12

Expert Comment

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)
0

LVL 18

Accepted Solution

Is it possible to have data like :
ACCT_UNIT      DESCRIPTION
101                 GENERAL FUND - CITY
1010100        FM-CHIEF FINANCIAL OFFICER
1010101        something else
0

Author Closing Comment

Thank you.
0

LVL 12

Expert Comment

Isn't that the results of the query that was originally posted?
0

LVL 18

Expert Comment

Hey garyjgs, looks like you marked my post by mistake (it was just a question). Did you meant to accept Jared_S's solution ?
0

Featured Post

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.