Skip to Content
avatar image
Former Member

Insert CASE function in this query ?

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@}'


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 25, 2017 at 09:52 AM
    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>
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 23, 2017 at 12:25 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • Jan 25, 2017 at 06:56 AM

    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

    Add comment
    10|10000 characters needed characters exceeded