Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2006
    Posts
    8

    Unanswered: Using multiple queries in Crystal Reports??

    I had to develop a query for a Crystal report, and the report basically has 9 buckets for it's enrollment counts. There is a starting count in the 1st bucket, followed by 8 weeks worth of buckets (1 bucket per week). I developed a query that calculates all of the detail buckets, and another query to create the headings for each bucket (i.e., 'Jul 4 - Jul 11'). Can Crystal read multiple queries, 1 for the detail and 1 for the headings. If not, is there a way to combine the following two queries?

    --
    -- Script to create Headings
    --
    select TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 57),'MON DD') || ' Total' ,
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 56),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 50),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 49),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 43),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 42),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 36),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 35),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 29),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 28),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 22),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 21),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 15),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 14),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 8),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 7),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 1),'MON DD')
    FROM DUAL;

    --
    -- Script to produce data
    --

    select to_char(sum(DECODE(LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 57),TRUNC(CONNECT_DATE)),TRUNC(CONNECT_DATE),1,0)) ) "Jun 25 Total",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 56),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 50),TRUNC(CONNECT_DATE)),1,0))) "Jun 26 - Jul 02",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 49),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 43),TRUNC(CONNECT_DATE)),1,0))) "Jul 03 - Jul 09",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 42),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 36),TRUNC(CONNECT_DATE)),1,0))) "Jul 10 - Jul 16",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 35),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 29),TRUNC(CONNECT_DATE)),1,0))) "Jul 17 - Jul 23",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 28),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 22),TRUNC(CONNECT_DATE)),1,0))) "Jul 24 - Jul 30",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 21),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 15),TRUNC(CONNECT_DATE)),1,0))) "Jul 31 - Aug 6",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 14),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 8),TRUNC(CONNECT_DATE)),1,0))) "Aug 07 - Aug 13",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 7),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 1),TRUNC(CONNECT_DATE)),1,0))) "Aug 14 - Aug 20"
    FROM MENR;

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    Try it with the second query ONLY!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    While I agree with LKBrwn_DBA, you could do something like

    --
    -- Script to create Headings
    --
    select TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 57),'MON DD') || ' Total' ,
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 56),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 50),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 49),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 43),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 42),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 36),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 35),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 29),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 28),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 22),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 21),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 15),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 14),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 8),'MON DD'),
    TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 7),'MON DD') ||'-'||TO_CHAR((TO_DATE('21-AUG-2006','DD-MON-YYYY') - 1),'MON DD')
    FROM DUAL
    union all
    select to_char(sum(DECODE(LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 57),TRUNC(CONNECT_DATE)),TRUNC(CONNECT_DATE),1,0)) ) "Jun 25 Total",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 56),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 50),TRUNC(CONNECT_DATE)),1,0))) "Jun 26 - Jul 02",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 49),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 43),TRUNC(CONNECT_DATE)),1,0))) "Jul 03 - Jul 09",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 42),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 36),TRUNC(CONNECT_DATE)),1,0))) "Jul 10 - Jul 16",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 35),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 29),TRUNC(CONNECT_DATE)),1,0))) "Jul 17 - Jul 23",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 28),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 22),TRUNC(CONNECT_DATE)),1,0))) "Jul 24 - Jul 30",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 21),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 15),TRUNC(CONNECT_DATE)),1,0))) "Jul 31 - Aug 6",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 14),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 8),TRUNC(CONNECT_DATE)),1,0))) "Aug 07 - Aug 13",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 7),TRUNC(CONNECT_DATE)),LEAST((TRUNC(TO_DATE('21-AUG-2006','DD-MON-YYYY')) - 1),TRUNC(CONNECT_DATE)),1,0))) "Aug 14 - Aug 20"
    FROM MENR;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Yes, but if you notice, the second query already has column 'aliases'!

    ...And the first query is redundant (converting a constant to a date to a character???)

    If he needs the column headers formatted as the first query, then just set the aliases accordingly in the second query.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Thats why I agreed with you, I used this as an example for him on using the union all to join two selects.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs up



    Ok, I misunderstood the gist of your remark.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jul 2006
    Posts
    8

    My mistake...

    Quote Originally Posted by LKBrwn_DBA

    Yes, but if you notice, the second query already has column 'aliases'!

    ...And the first query is redundant (converting a constant to a date to a character???)

    If he needs the column headers formatted as the first query, then just set the aliases accordingly in the second query.

    I forgot to remove the headings from the 2nd script. These were hard coded and were there only to provide headings for the detail while I was verifying the results of the detail script. I really do need the variable headings that are provided by the 1st script. I will do the Union All join and see what happens.

    Thanks for your help.

  8. #8
    Join Date
    Jul 2006
    Posts
    8

    I tried the Union ALL to join the two queries, and got an error.

    I used the union all to join the two queries, and got the error:
    ORA-01789 - Query block has incorrect number of result columns

    This is what the query looks like now:

    select TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 57),'MON DD') || ' Total' ,
    TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 56),'MON DD') ||'-'||TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 50),'MON DD'),
    TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 49),'MON DD') ||'-'||TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 43),'MON DD'),
    TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 42),'MON DD') ||'-'||TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 36),'MON DD'),
    TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 35),'MON DD') ||'-'||TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 29),'MON DD'),
    TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 28),'MON DD') ||'-'||TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 22),'MON DD'),
    TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 21),'MON DD') ||'-'||TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 15),'MON DD'),
    TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 14),'MON DD') ||'-'||TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 8),'MON DD'),
    TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 7),'MON DD') ||'-'||TO_CHAR((TO_DATE('&startdate','DD-MON-YYYY') - 1),'MON DD')
    FROM DUAL
    UNION ALL
    select brc.description,
    to_char(sum(DECODE(LEAST((TRUNC(TO_DATE('&startdat e','DD-MON-YYYY')) - 57),TRUNC(bas.date_connected)),TRUNC(bas.date_conn ected),1,0))) "Jun 25 Total",
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('&start date','DD-MON-YYYY')) - 56),TRUNC(bas.date_connected)),LEAST((TRUNC(TO_DAT E('&startdate','DD-MON-YYYY')) - 50),TRUNC(bas.date_connected)),1,0))),
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('&start date','DD-MON-YYYY')) - 49),TRUNC(bas.date_connected)),LEAST((TRUNC(TO_DAT E('&startdate','DD-MON-YYYY')) - 43),TRUNC(bas.date_connected)),1,0))),
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('&start date','DD-MON-YYYY')) - 42),TRUNC(bas.date_connected)),LEAST((TRUNC(TO_DAT E('&startdate','DD-MON-YYYY')) - 36),TRUNC(bas.date_connected)),1,0))),
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('&start date','DD-MON-YYYY')) - 35),TRUNC(bas.date_connected)),LEAST((TRUNC(TO_DAT E('&startdate','DD-MON-YYYY')) - 29),TRUNC(bas.date_connected)),1,0))),
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('&start date','DD-MON-YYYY')) - 28),TRUNC(bas.date_connected)),LEAST((TRUNC(TO_DAT E('&startdate','DD-MON-YYYY')) - 22),TRUNC(bas.date_connected)),1,0))),
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('&start date','DD-MON-YYYY')) - 21),TRUNC(bas.date_connected)),LEAST((TRUNC(TO_DAT E('&startdate','DD-MON-YYYY')) - 15),TRUNC(bas.date_connected)),1,0))),
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('&start date','DD-MON-YYYY')) - 14),TRUNC(bas.date_connected)),LEAST((TRUNC(TO_DAT E('&startdate','DD-MON-YYYY')) - 8),TRUNC(bas.date_connected)),1,0))),
    to_char(sum(DECODE(GREATEST((TRUNC(TO_DATE('&start date','DD-MON-YYYY')) - 7),TRUNC(bas.date_connected)),LEAST((TRUNC(TO_DATE ('&startdate','DD-MON-YYYY')) - 1),TRUNC(bas.date_connected)),1,0)))
    from asc bas
    ,revenue_classes brc
    where bas.date_connected is not null
    and bas.co = brc.co
    and bas.revenue_class = brc.revenue_class
    group by brc.description;

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'd say it's the job of Crystal Reports to generate headings and other formatting for the report. There are lots of functions and a scripting language built in specifically for this purpose: to make reports look nice. The back-end database should only be used for what it's good at: delivering the data.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The columns returned by the seperate queries used in a UNION ALL, must be the same number of columns and the columns must be of the same type.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try posting this question in the Crystal Reports forum.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •