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?
 
dqmqConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.