Solved

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

Posted on 2011-02-28
10
408 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:anumoses
  • 5
  • 5
10 Comments
 
LVL 6

Accepted Solution

by:
venkotch earned 250 total points
ID: 34997908
This query will pull ALL records from DT table for question '1010'
Your travel history is OUTER joined and the inline view will have only history for the donors you want.
SELECT dc.country_id,
       dc.description,
       dt.question_id,
       hist.years,
       hist.amt_time_spent,
       CASE
         WHEN (hist.donor_id IS NULL)
         THEN 'No donors found'
         ELSE hist.donor_id
       END donor_eixts
  FROM ds_travel_countries dt,
       ds_countries dc,
       (SELECT ds.years,
               ds.amt_time_spent,
               ds.question_id,
               ds.country_id,
               ds.donor_id
          FROM ds_travel_history ds
         WHERE ds.donor_id IN
              (SELECT donor_id FROM temp_mobile_donors)
       ) hist
 WHERE dt.question_id = '1010'
   AND dc.country_id = dt.country_id
   AND dt.question_id = hist.question_id(+)
   AND dt.country_id = hist.country_id(+)

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 34999074
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
0
 
LVL 6

Expert Comment

by:venkotch
ID: 34999362
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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 35000090
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?
0
 
LVL 6

Expert Comment

by:venkotch
ID: 35000231
No.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
ID: 35000800
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?
0
 
LVL 6

Expert Comment

by:venkotch
ID: 35000830
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.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 35001279
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.
0
 
LVL 6

Expert Comment

by:venkotch
ID: 35001493
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

0
 
LVL 6

Author Comment

by:anumoses
ID: 35007007
Thanks. I am posting another question.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now