Home » SQL & PL/SQL » SQL & PL/SQL » Crystal report with sql
Crystal report with sql [message #196345] Thu, 05 October 2006 01:46 Go to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi,
I have to cnvert a crystal report into sql. I have to write a query that does the same thing as the report is doing.

I have written the below query :
SELECT s.item,i.descr,i.sku_stat,i.packed_for,
TO_NUMBER(dfu.sop_app),d.dfuloc,s.loc,
MAX(ROUND(TO_NUMBER(d.allocfactor),2)),
[B]COUNT(f.dmdunit)[/B]
  FROM stsc.item i,stsc.sku s,
       stsc.supplymethod sm,
       stsc.dfutosku d,stsc.dmdunit du,
       stsc.fcst f,stsc.dfu dfu 
 WHERE (i.item=s.item)
   AND ((s.item=sm.item) AND (s.loc=sm.loc))
   AND ((s.item=d.item (+)) AND (s.loc=d.skuloc (+)))
   AND (s.item=du.sku(+)) AND (du.dmdunit=f.dmdunit(+))
   AND (((d.dmdunit=dfu.dmdunit(+)) AND (d.dmdgroup=dfu.dmdgroup(+)))
   AND (d.dfuloc=dfu.loc(+)))
   AND (i.inv_class='F305' OR i.inv_class='L305' OR i.inv_class='L342')
   AND (s.loc='260' OR s.loc='261' OR s.loc='262')
 GROUP BY s.item,d.dfuloc,
       s.loc,f.dmdunit,i.descr,i.sku_stat,
       i.packed_for,dfu.sop_app
 ORDER BY s.item,s.loc;

--


There is a running total on f.dmdunit where it is evaluated Forn each row and Reset on Group # 3 where
1st group -> s.item
2nd Group -> d.dfuloc
3rd Group -> s.loc
If this was to be evaluated on change of group 3, I am getting the matching records form crystal report but since this is evaluated for each record my answer is differing only for the last column f.dmdunit.
Please help me if anyone is proficient in crystal report as well as in oracle. I have also tried inner query but couldn't get on it.

Thanks,
Sonali

[Updated on: Thu, 05 October 2006 01:48]

Report message to a moderator

Re: Crystal report with sql [message #196375 is a reply to message #196345] Thu, 05 October 2006 03:06 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Can anyone please help me on this.

Thanks,
Sonali
Re: Crystal report with sql [message #196418 is a reply to message #196375] Thu, 05 October 2006 04:47 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hi,

I find it very hard to comprehend what you want to accomplish, but let's give it a try.

First, I've rearranged your query a bit, I hope you don't mind:
SELECT s.item
      ,i.descr
      ,i.sku_stat
      ,i.packed_for
      ,to_number(dfu.sop_app) sop_app
      ,d.dfuloc
      ,s.loc
      ,MAX(round(to_number(d.allocfactor)
                ,2)) max_allocfactor
      ,COUNT(f.dmdunit) over(PARTITION BY s.item, d.dfulloc, s.loc ORDER BY s.item) count_dmdunit
FROM   item         i
      ,sku          s
      ,supplymethod sm
      ,dfutosku     d
      ,dmdunit      du
      ,fcst         f
      ,dfu          dfu
WHERE  i.item = s.item
       AND s.item = sm.item
       AND s.loc = sm.loc
       AND s.item = d.item(+)
       AND s.loc = d.skuloc(+)
       AND s.item = du.sku(+)
       AND du.dmdunit = f.dmdunit(+)
       AND d.dmdunit = dfu.dmdunit(+)
       AND d.dmdgroup = dfu.dmdgroup(+)
       AND d.dfuloc = dfu.loc(+)
       AND i.inv_class IN ('F305', 'L305', 'L342')
       AND s.loc IN ('260', '261', '262')
GROUP  BY s.item
         ,d.dfuloc
         ,s.loc
         ,f.dmdunit
         ,i.descr
         ,i.sku_stat
         ,i.packed_for
         ,dfu.sop_app
ORDER  BY s.item
         ,s.loc;


Some questions that I had:
Why do you count on f.dmdunit, since you can just as easily count on du.dmdunit?
What is it with all the outer joins? Are they required and why?


But the most important one: what do you want for a rolling total count group etc? Can you give a simplified data example to show us what you need?

Regards,
Sabine
What
Re: Crystal report with sql [message #196423 is a reply to message #196418] Thu, 05 October 2006 05:33 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi Sabine,
Thank you so much for looking into this.
I ran your query but the number of records is more in your case.
Also in the last column f.dmdunit, the answer is 1 or 0. While the crystal report output gives 2 in 14 cases.

As per your questions, I am converting a crystal report to sql and then exporting csv file where I have to match the data from crystal report to that of sql file.

So I have to take care of the columns displayed in CR and all the joins and other things are matching the crystal report condition.

My answer is coming wrong only at the last column and I am also getting the same number of records.

But as I said that the column f.dmdunit is working as a running total in CR where it is evaluated For each Row and reset on group # 3 .

Please let me know if I didn't made thing clear.

Thanks again,
Sonali

[Updated on: Thu, 05 October 2006 05:34]

Report message to a moderator

Re: Crystal report with sql [message #196435 is a reply to message #196423] Thu, 05 October 2006 06:06 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Ok, for the running total, you can use:

COUNT(f.dmdunit) over(PARTITION BY s.loc ORDER BY f.dmdunit) count_dmdunit


Regards,
Sabine
Previous Topic: script to bulk load csv file data to table when different but depends on csv data
Next Topic: Oracle select query
Goto Forum:
  


Current Time: Fri Dec 02 16:16:47 CST 2016

Total time taken to generate the page: 0.45795 seconds