Add subquery into PL/SQL command used in Crystal

Howdy Experts; I have a basic query that I am using to retrieve some values for a Crystal report (see attached), however I need to somehow change "sysdate" in the case statement to the following:

select max(aging_date) from hsp_bkt_aging_hx

It needs to just be a stand alone value, meaning there is no relationship between the tables in the main query and hsp_bkt_aging_hx.

This must be acceptable in Crystal as a command as well.  I'm hoping I'm not seeing something very obvious.  Any help would be appreciated.

Thanks,
Eddie
SELECT SUM(hsp_transactions.tx_amount) "90DREV_NONCAP",
  SUM(hsp_transactions.tx_amount) /(
CASE
WHEN((sysdate -1) -to_date('01-MAR-10') < 90) THEN(ROUND((sysdate -1) -to_date('01-MAR-10'),   0))
ELSE(90)
END) "90ADR_NONCAP"
FROM hsp_transactions
INNER JOIN hsp_account ON hsp_transactions.hsp_account_id = hsp_account.hsp_account_id 
LEFT JOIN zc_fin_class ON hsp_transactions.fin_class_c = zc_fin_class.fin_class_c
WHERE hsp_transactions.tx_type_ha_c = '1'
 AND hsp_transactions.tx_post_date >= TRUNC(
  (SELECT MAX(aging_date)
   FROM hsp_bkt_aging_hx) -90)
AND hsp_transactions.tx_post_date <=
  (SELECT MAX(aging_date)
   FROM hsp_bkt_aging_hx)
AND hsp_account.serv_area_id = 40
 AND hsp_account.loc_id = 3010
 AND zc_fin_class.title NOT LIKE '%CAP%'

Open in new window

DigDubAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
You could use a SQL Expression in Crystal.   Just use the SQL you entered for the expression

mlmcc
0
dqmqCommented:
Try attachec code
SELECT SUM(hsp_transactions.tx_amount) "90DREV_NONCAP",
  SUM(hsp_transactions.tx_amount) /(
CASE
WHEN((d.max_aging_date -1) -to_date('01-MAR-10') < 90) THEN(ROUND((d.max_aging_date -1) -to_date('01-MAR-10'),   0))
ELSE(90)
END) "90ADR_NONCAP"
FROM 
  (select max(aging_date) max_aging_date from hsp_bkt_aging_hx) d inner join
hsp_transactions
INNER JOIN hsp_account ON hsp_transactions.hsp_account_id = hsp_account.hsp_account_id 
LEFT JOIN zc_fin_class ON hsp_transactions.fin_class_c = zc_fin_class.fin_class_c
WHERE hsp_transactions.tx_type_ha_c = '1'
 AND hsp_transactions.tx_post_date >= TRUNC(
  (d.max_aging_date -90)
AND hsp_transactions.tx_post_date <=
  (d.max_aging_date)
AND hsp_account.serv_area_id = 40
 AND hsp_account.loc_id = 3010
 AND zc_fin_class.title NOT LIKE '%CAP%'

Open in new window

0
DigDubAuthor Commented:
Thanks dgmg; I see what you're doing, but I'm getting a missing keyword error on line 11.

So to declare a view, I don't have to have an explicit join in the actual query?  I can just create one with no "on x=x"?  I thought that was bad for some reason.

I will give it a shot to.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DigDubAuthor Commented:
mlmcc; I'm not seeing how, in CR, I can add this as a SQL expression into the SQL command.  Can you exlaborate a little bit?
0
mlmccCommented:
You don't add it into the command.

In the field explorer there should be a SQL Expression option
Right click it
Click NEW
Give it a name
Enter you SQL
Then use it just as any field in the report.

mlmcc
0
dqmqCommented:
my inner join syntax may be a little off: missing the "on x=x" part.   Off hand, I don't remember how to do an inner join without an "on condition".  A bit clunky, but, a dummy condition like this might work:  


...
  (select max(aging_date) max_aging_date from hsp_bkt_aging_hx) d inner join
hsp_transactions on 1=1
...

or you could revert to the older SQL with the join conditions are in the where clause
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DigDubAuthor Commented:
Good to know dgmg; I will give it a shot and let you know what happens.
0
DigDubAuthor Commented:
Yay!  The 1=1 join worked fine ... Thank you very much dgmg, that is an excellent little trick.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.