asked on

Oracle query

I have a query

select SUM(CASE
WHEN da.attrib_value IN ( '04', '05' ) THEN 1
ELSE 0
END )
FROM (blood_drives bd
join donations_don d ON 'DRV'||to_char(bd.drive_id) = d.drive_id
JOIN sites s on s.site_code = bd.site_code)
left outer join donation_attributes_don da
ON ( d.transaction_id = da.transaction_id
AND da.attrib_value IN ( '04', '05' )
AND da.attrib_code = 'VENI' )
WHERE bd.drive_date between :start_date and :end_date
AND d.unit_id IS NOT NULL
and area_rep_no = :area_rep_no;

I need to pass this value into a variable called v_count . I tried using into v_count after SUM(CASE
WHEN da.attrib_value IN ( '04', '05' ) THEN 1
ELSE 0
END ) but I am getting an error. Help appreciated. I know it might be a simple and known error that I am getting confused.

anumoses

ASKER

I am using this query in a report and the error I get I have attached as a screen print.

awking00

Remove the parentheses. The query thinks you are selecting from a subquery.

anumoses

ASKER

before the blood_drives table?

awking00

Remove the parentheses before blood_drives bd, after bd.site_code, before d.transaction_id, and after 'VENI'

anumoses

ASKER

select SUM(CASE
WHEN da.attrib_value IN ( '04', '05' ) THEN 1
ELSE 0
END ) into v_count2
FROM blood_drives bd
join donations_don d ON 'DRV'||to_char(bd.drive_id) = d.drive_id
JOIN sites s on s.site_code = bd.site_code
left outer join donation_attributes_don da
ON d.transaction_id = da.transaction_id
AND da.attrib_value IN ( '04', '05' )
AND da.attrib_code = 'VENI'
WHERE bd.drive_date between :start_date and :end_date
AND d.unit_id IS NOT NULL
and area_rep_no = :area_rep_no;

still get an error

anumoses

ASKER

I modified the query

select SUM(CASE
WHEN da.attrib_value IN ( '04', '05' ) THEN 1
ELSE 0
END ) into v_count2
from blood_drives bd,
donations_don d,
sites s,
donation_attributes_don da
where 'DRV'||to_char(bd.drive_id) = d.drive_id
and s.site_code = bd.site_code
and d.transaction_id = da.transaction_id
AND da.attrib_value IN ( '04', '05' )
AND da.attrib_code = 'VENI'
and bd.drive_date between :start_date and :end_date
AND d.unit_id IS NOT NULL
and area_rep_no = :area_rep_no;

Just need to know if this translates to the old query and that I would get the same result?

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

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

Start Free Trial

anumoses

ASKER

thanks