case statement help

[sonny1974]

i have oracle 9i and crystal xi

this formula works in crystal

if ({Command.RANK}= 3 and previous ({Command.RANK})=2 ) and ({Command.QTY} = {Command.QTY}
and {Command.TRAN_CODE} = '28') or
({Command.RANK}= 3 and previous({Command.RANK})=2 and {Command.TRAN_CODE} <> previous({Command.TRAN_CODE}))then 1

but it wont work in my command statement , when i do a case in sql
can you help me on the coding so it works in my command

 

[sonny1974]

would i do a lead ?or lag?
choose? decode?

 

[sonny1974]

or a prior connect by

 

[cmmrfrds]

If it is not too lenghty, can you show the SQL in your Command, since it would make it clearer what you want.

 

[sonny1974]

with qry1 as (SELECT A.PART,a.QTY, a.RCVR, a.TRAN_CODE,a.date_add,a.TIME_ADD,a.lot_nbr,
TO_DATE(to_char(a.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(a.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS') as date_time1,
COUNT(*) Rank
from int_table a INNER JOIN int_table B ON A.RCVR=B.RCVR and

tO_DATE(to_char(a.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(a.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS') >=
tO_DATE(to_char(b.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(b.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS')
where to_number (a.tran_code) in (11,28,23,26,22) and to_number (b.tran_code) in (11,28,23,26,22)
group by A.PART,a.QTY, a.RCVR, a.TRAN_CODE,a.date_add,a.TIME_ADD,a.lot_nbr,
TO_DATE(to_char(a.date_add,'MMDDYYYY')||TO_CHAR(FLOOR(a.TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS'))

select distinct
rcv1.rcvr,qry1.PART,qry1.QTY, qry1.RCVR, qry1.TRAN_CODE,qry1.date_time1,qry1.date_add,qry1.TIME_ADD,rcv1.vendor,rank,

case
when qry1.TRAN_CODE = '28' and (RANK = 2 or RANK = 3)
then qry1.QTY else 0 end approved,

case
when qry1.TRAN_CODE = '23' and (RANK = 2 or RANK = 3)
then qry1.QTY else 0 end denie



from qry1

left JOIN RCV_TABLE RCV ON qry1.RCVR = RCV.RCVR AND qry1.LOT_NBR = RCV.LOT_NBR
left join rcv_table rcv1 on qry1.rcvr = rcv1.rcvr
where rank < 4 and rcv1.rcvr is not null

 

[cmmrfrds]

It looks from the code that you are trying to create a rank by self joining the table. If this is the case it can be easier to read and more concise using the Rank() analytical function. Please explain in general terms what needs to be done.

 

[sonny1974]

ok,

well im ranking on my date and the ranking changes when a new rcvr number occurs.

so basically in my case statement i want it to tell me
if the tran code is 28 and rank 2 and 3 have the same qty give me a 1

or if the rank is 2 and 3 and the tran codes are different give me a 1

can i create something in sql , so i dont need to do formulas in crystal

 

[Dagon]

What exactly is the problem with your SQL ? Do you get an ORA- error when you try to run it ?

 

[sonny1974]

i dont know how to put this code into my sql

the below is from crystal reports, but rather do a smiliar code in sql so i dont have to do it in crystal, not sure how i would do it

if ({Command.RANK}= 3 and previous ({Command.RANK})=2 ) and ({Command.QTY} = {Command.QTY}
and {Command.TRAN_CODE} = '28') or
({Command.RANK}= 3 and previous({Command.RANK})=2 and {Command.TRAN_CODE} <> previous({Command.TRAN_CODE}))then 1

 

[cmmrfrds]

I am trying to determine the size of the window that you need to rank the data within. For example.
with qry1 as
(Select
part,
qty,
rcvr,
tran_code,
date_add,
time_add,
lot_nbr,
dense_rank() Over (partition by rcvr, trunc(date_add)
order by rcvr, trunc(date_add), qty) as rank
From int_table
)
Select * from qry1

The window size is determined by the partition clause and the rank is determined by the order by clause. You can take the above query and teak until you get the rank established like you want. When you get there it will be easy enough to add the rest of the code.