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

jrbledsoe001
jrbledsoe001 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
dqmq

Commented:
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

Commented:
On second thought, maybe t his is what you want
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_id = gls.object_id and gln.company = gls.company
and gls.posting_flag = 'S'   --I don't think you need this, but just to be save
where gln.posting_flag = 'P' 
and cast(gln.acct_unit as integer) = 10001

Open in new window

Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
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

Author

Commented:
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'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial