Skip to Content
0

Insert CASE function in this query ?

Jan 23, 2017 at 10:51 AM

47

avatar image

Hi all ,

I need a little alteration in my query.

I need to creae a parameter called @myvalue this code and @myvalue should equal to OITM.U_stockprice for some item groups.(ie @myvalue = OITM.U_stockprice if OMRC.firmcode = 48 ) for other item groups @myvalue is wtr1.quantity itself.How can i do it using CASE funtion in the followijng query ?

select t0.LocCode,t1.Panno,t1.ceregno,t1.cerange,t1.CstNo,t1.CeDivision,t1.CeComRate, t3.SeriesName,t3.Series,
t1.tinno,t1.eccno,t1.LstVatNo,t2.DocNum,t2.Series,t2.DocDate,t1.Location,t2.Filler,t2.U_Destinat,t0.StockPrice,t0.LineTotal,t2.DocTotal,t0.Dscription,t0.Quantity,t0.Quantity*t0.StockPrice[Value],t6.U_StockTransfer,


t5.location [From Location],
T5.Building [FROM BUILDING],
t5.Street [from street],
t5.Block [from block],
t5.City [from city],
t5.ZipCode [from zip],
t5.TinNo [from tin],
t5.CstNo [from cst],


t1.location [To Location],
t1.Building [TO BUILDING],
t1.Street [TO street],
t1.Block [TO block],
t1.City [TO city],
t1.ZipCode [TO zip],
t1.TinNo [TO  tin],
t1.Cstno [To Cst],




(select a.whsname from owhs a where t2.filler=a.WhsCode) [wFrom whs],
(select a.Street from owhs a where t2.filler=a.WhsCode) [wFrom street],
(select a.StreetNo from owhs a where t2.filler=a.WhsCode) [wFrom streetno.],
(select a.Block from owhs a where t2.filler=a.WhsCode) [wFromblock],
(select a.Building from owhs a where t2.filler=a.WhsCode) [wFrom building],
(select a.ZipCode from owhs a where t2.filler=a.WhsCode) [wFromzipcode],
(select a.City from owhs a where t2.filler=a.WhsCode) [wFrom city],
(select a.County from owhs a where t2.filler=a.WhsCode) [wFrom county],


(select a.whsname from owhs a where t0.WhsCode=a.WhsCode) [wTo whs],
(select a.Street from owhs a where t0.WhsCode=a.WhsCode) [wTo street],
(select a.StreetNo from owhs a where t0.WhsCode=a.WhsCode) [wTo streetno],
(select a.Block from owhs a where t0.WhsCode=a.WhsCode) [wTo block],
(select a.Building from owhs a where t0.WhsCode=a.WhsCode) [wTo building],
(select a.ZipCode from owhs a where t0.WhsCode=a.WhsCode) [wTozipcode],
(select a.City from owhs a where t0.WhsCode=a.WhsCode) [wTocity],
(select a.County from owhs a where t0.WhsCode=a.WhsCode) [wTocounty]




from WTR1 t0 




left join OLCT t1 on t0.LocCode=t1.Code
left join OWTR t2 on t0.DocEntry=t2.DocEntry
inner join NNM1 t3 on t2.Series = t3.Series
inner join OWHS t4 on t2.Filler = t4.WhsCode 
left join OLCT t5 ON t4.Location =  T5.Code
inner join OITM t6 on t0.ItemCode = t6.ItemCode
inner join OMRC t7 on t6.FirmCode = t7.FirmCode






where t2.docentry='{?DocKey@}'


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Johan Hakkesteegt Jan 25, 2017 at 09:52 AM
0
declare @myvalue as float 
SELECT @myvalue = CASE
                   WHEN t7.firmcode = 48 THEN t6.U_stockprice
                   ELSE wtr1.quantity
                  END
from WTR1 t0 
left join OLCT t1 on t0.LocCode=t1.Code
left join OWTR t2 on t0.DocEntry=t2.DocEntry
inner join NNM1 t3 on t2.Series = t3.Series
inner join OWHS t4 on t2.Filler = t4.WhsCode 
left join OLCT t5 ON t4.Location =  T5.Code
inner join OITM t6 on t0.ItemCode = t6.ItemCode
inner join OMRC t7 on t6.FirmCode = t7.FirmCode
where t2.docentry='{?DocKey@}'
<br>
Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Jan 23, 2017 at 12:25 PM
0

Hi,

Are you sure you need the @myvalue parameter?

Anywhere that you use this price you can just repeat the same CASE statement:

CASE
 WHEN t7.firmcode = 48 THEN t6.U_stockprice
 ELSE wtr1.quantity
END

Regards,

Johan

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Johan,

Yes I need the out as @myvalue parameter ( which I can use in a Crystal report easily.)ie the condition should be like as follows :

IF t7.firmcode = 48

THEN @myvalue = t6.U_stockprice

ELSE @myvalue = wtr1.quantity



so that i can drag @myvalue as a single field in crystal report.

0
Johan Hakkesteegt Jan 25, 2017 at 06:56 AM
0

Okay, the syntax is pretty much the same:

SELECT @myvalue = CASE
                   WHEN t7.firmcode = 48 THEN t6.U_stockprice
                   ELSE wtr1.quantity
                  END
FROM /* etc, etc */
 

Regards,

Johan

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Johan ,

Please give full code im not into SQL.

0