asked on 5/15/2011

SQL to list detail accounting unit as well as summary accounting unit

Hello,

I am writing a Crystal SQL Command report on an Oracle database for the Lawson ERP. The report has data from several tables but this section of code is not working. The table name is GLNAMES. The table contains a posting level account as well as a summary account; see sample data file. My code is not pulling in the summary account and summary account description.

Here is my code
select
gln.description,
gln.posting_flag,
(select gln2.acct_unit from lsfmigd.glnames gln, lsfmigd.glnames gln2
where gln2.posting_flag = 'S'
and gln.parent_obj_id = gln2.obj_id
and gln.company = gln2.company
and gln.acct_unit = gln2.acct_unit
and gln.company = gln.company) as Parent_Unit,
(select gln2.description from lsfmigd.glnames gln, lsfmigd.glnames gln2
where gln2.posting_flag = 'S'
and gln.parent_obj_id = gln2.obj_id
and gln.company = gln2.company
and gln.acct_unit = gln2.acct_unit
and gln.company = gln2.company) as Parent_Desc
from lsfmigd.glnames gln
where gln.posting_flag = 'P'
and cast(gln.acct_unit as integer) = 10001

I would like your help to modify my code so that the query pulls in accounting unit 100_NURSING_ADM and description Nursing Administration
glnames.csv

Crystal Reports Oracle Database DB Reporting Tools

Last Comment

jrbledsoe001

5/15/2011

dqmq

5/15/2011

I don't know the other ramificatations of this, but given the sample data you can change this part:

where gln.posting_flag = 'P'
and cast(gln.acct_unit as integer) = 10001

to:

Where gln.posting_flag in ('P','S')
and cast(gln.acct_unit as integer) in ('10001','100_NURSING_ADM')

or just leave off the last WHERE clause

ASKER CERTIFIED SOLUTION

dqmq

5/15/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Kurt Reinhardt

5/15/2011

One problem with your code is you have an alias for gln in both the outer query and the inner subqueries. As such, you're not really correlating the data from the outer query to the inner query. Here's what it looks like taking that out:

select 
  gln.description, 
  gln.posting_flag,
  (
  select distinct
    gln2.acct_unit
  from
    lsfmigd.glnames gln2 
  where
    gln2.posting_flag = 'S' 
    and gln.parent_obj_id = gln2.obj_id
    and gln.company = gln2.company
    and gln.acct_unit = gln2.acct_unit
    and gln.company = gln.company) as Parent_Unit,
  (
  select distinct
    gln2.description
  from
    lsfmigd.glnames gln2 
  where
    gln2.posting_flag = 'S' 
    and gln.parent_obj_id = gln2.obj_id
    and gln.company = gln2.company
    and gln.acct_unit = gln2.acct_unit
    and gln.company = gln2.company) as Parent_Desc
  from
    lsfmigd.glnames gln 
  where
    gln.posting_flag = 'P' 
    and cast(gln.acct_unit as integer) = 10001

Open in new window

Also, I've added distinct to the subqueries, since they can only return a single, distinct value if they're in the SELECT clause. Hopefully that's the case with the correlated filters, but if it's not, the distinct should take care of it.

~Kurt

jrbledsoe001

5/15/2011

ASKER

I'm a happy camper. I got this portion of code to run. i may have problems with the join syntax when I incorporate this sql snipet into the larger report SQL statement. I'm super happy with the results. THANK YOU!!!

select
gln.description,
gln.posting_flag,
gls.acct_unit as Parent_unit,
gls.description as Parent_Desc
from lsfmigd.glnames gln
inner join lsfmigd.glnames gls
on gln.parent_obj_id = gls.obj_id
and gln.company = gls.company
where gln.acct_unit ='10001'