asked on

Designing an oracle report for multiple donors extension to previous asked question

Before I went on vacation you suggested this query

SELECT dc.country_id,
dc.description,
ds.years,
ds.amt_time_spent,
ds.donor_id,
ds.question_id,
(SELECT count(1)
FROM temp_mobile_donors temp
WHERE temp.donor_id = ds.donor_id)
AS registered_donor_found
FROM ds_travel_countries dt,
ds_countries dc,
ds_travel_history ds
WHERE dt.question_id = '1010'
AND dc.country_id = dt.country_id
AND ds.question_id(+) = dt.question_id
AND ds.country_id(+) = dt.country_id;
-----------------------
Now my question is when I am inserting into the temp table my query is

INSERT INTO temp_mobile_donors
select distinct d.donor_id
from blood_drives bd,
donations@pdon_new d,
donor_interdictions@pdon_new da
where bd.site_code = :site_code
and bd.drive_date between :mob_date1
and add_months( :mob_date2, v_udf1)
and bd.drive_id = substr(d.drive_id, 4,10)
and da.donor_id = d.donor_id
and da.inter_code = '0004'
and da.term_date is null;
---------------
DONOR_ID
DN00566342
DN20235147
---------------------- 2 donors for that site code -LA09
and dates 18-jan-2011 to 18-jan-2011
-----------------------
SELECT dc.country_id,
dc.description,
ds.years,
ds.amt_time_spent,
ds.donor_id,
ds.question_id,
(SELECT count(1)
FROM temp_mobile_donors temp
WHERE temp.donor_id = ds.donor_id)
AS registered_donor_found
FROM ds_travel_countries dt,
ds_countries dc,
ds_travel_history ds
WHERE dt.question_id = '1009'
and dc.country_id in (1067,1068,1069,1070)
AND dc.country_id = dt.country_id
AND ds.question_id(+) = dt.question_id
AND ds.country_id(+) = dt.country_id;

------Instead of 2 donors it gives me 72 donors.
------------
What you are saying is,
1) print info where donor_id in the temp table
2) print info where donor_id not in the temp table.

No
1)Print all the blocks with country names.
2)Print info for donors in the temp table.

-----------------attaching screen prints.
Two donors in the temp file. I want the report to look like third screen shot. No data but still countries get printed in the report.
one.JPG
two.JPG
three.JPG

ASKER CERTIFIED SOLUTION

venkotch

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

I further have a small question
There is a query where we have

None of the countries listed above(place an 'X' in the box.

The query is

select distinct ds.donor_id,
decode(ds.countries_listed, 'Y', 'X')
from ds_travel_history ds
where ds.question_id = '1010'
and ds.country_id = '1075'
and ds.donor_id(+) = :donor_id
---------------
This is below the previous query that you gave me. This particular query only used one table ie ds_travel_history. So for me to get this box as I am attaching the screen print....should I do the similar way that you have mentioned above?
four.JPG

venkotch

I don't quite understand your question. But if this is to mark F_36 with an "X" if all of the countries above have 'No donors found', then you can change this column to be something like

CASE
WHEN (hist.donor_id IS NULL)
THEN 0
ELSE 1
END donor_eixists

or

LEAST(1, NVL(hist.donor_id, 0))

Then on the report data model add a summary column by "donor_exists" Based on your report you can reset this by "report" or by other group.
Instead of framing F_36, just put an "X" (boilerplate) and add a formatting trigger on it checking the value of your summary column.

If value = 0 - then no data is listed for the countries and so, RETURN (TRUE) to display the boilerplate (i.e. the "X") - otherwise RETURN (FALSE)

No need for extra queries.

anumoses

ASKER

select distinct ds.donor_id,
decode(ds.countries_listed, 'Y', 'X'),
CASE
WHEN (hist.donor_id IS NULL)
THEN 0
ELSE 1
END donor_eixists
from ds_travel_history ds,
(SELECT distinct ds.donor_id
FROM ds_travel_history ds
WHERE ds.donor_id IN
(SELECT donor_id FROM temp_mobile_donors)
) hist
where ds.question_id = '1010'
and ds.country_id = '1075'
--------------
Will this work?

venkotch

No.

anumoses

ASKER

Actually If the donor served in military base during 1980-1990 and has not visited either Belgium,Germany,Netherlands but other than the above, then put an X mark in the box. In this case the database has 'Y' in countries_listed in the table
ds_travel_history. Is there a way to use not in - in the query?

venkotch

You can modify your CASE in the way you want. Put 0 for contries not to count. The rest I have already explained - with the summary column, etc. Again, I think you do not need another query for this logic.

anumoses

ASKER

I think you mistook my other question. Anyways I need to have another query because, it not what you think. If the donor has served in military between 1980-1990 and spent 6 months in countries not listed above, then mark the check box. I dont know if you have the answer for this.

venkotch

Then in your format trigger (the one on the "X" boilerplate) you can use a query to answer your question.

For example, if the "X" boilerplate is called B_1:

FUNCTION B_1FormatTrigger 
  RETURN BOOLEAN
IS
  v_served_80_90_and_6_months NUMBER;
BEGIN  
  SELECT count(1)
    INTO v_served_80_90_and_6_months
    FROM ds_travel_history ds,
   WHERE ds.donor_id IN
        (SELECT donor_id FROM temp_mobile_donors)
     AND ds.question_id = '1010'
     AND ds.country_id IN ('1075', other countries)
     AND <whatever else you need to determine the 6 months requrement>
     AND <whatever else you need for other requirements>;

  RETURN (v_served_80_90_and_6_months > 0);
END B_1FormatTrigger;

Open in new window

anumoses

ASKER

Thanks. I am posting another question.