asked on

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

Mike McCracken

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

mlmcc

dqmq

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

DigDub

ASKER

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.

DigDub

ASKER

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?

Mike McCracken

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

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DigDub

ASKER

Good to know dgmg; I will give it a shot and let you know what happens.

DigDub

ASKER

Yay! The 1=1 join worked fine ... Thank you very much dgmg, that is an excellent little trick.