Skip to Content
0

Insert If statement to My Creport Query For Stock Transfer

Jan 18, 2017 at 10:20 AM

60

avatar image

Hi All ,

Following is the code for my Stock Transfer(OWTR) Invoice Creport.

I need to put an if statement to the below code.I have a user defined field in Item master data (OITM) called 'OITM.U_STOCKPRICE' i need to manually enter a particular Item group's(OMRC.FIRMNAME ) price in that UDF and call that value to my crystal report. Remaining items price should call from the WTR1.stockprice (ie t0.StockPrice).That means the if statement should be like as follows

IF (OMRC.FIRMNAME = 'NOCIL LTD') THEN ( t0.StockPrice = OITM.U_STOCKPRICE) ELSE (t0.StockPrice = t0.StockPrice )

Stock Transfer(OWTR) Invoice Creport. :

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], 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 where t2.docentry='{?DocKey@}'


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

3 Answers

Dell Stinnett-Christy Jan 18, 2017 at 03:35 PM
0

Since this appears to be a filter that should go in the where clause, you'll use a couple or or statements. In SQL it would look something like this (the parentheses are required for this to work correctly...):

(
(OMRC.FIRMNAME = 'NOCIL LTD' and t0.StockPrice = OITM.U_STOCKPRICE)
or
OMRC.FIRMNAME <> 'NOCIL LTD'
)

-Dell

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

Hi ,

Can you share me the full code?Also its my a pleasure to invite you to my whatsapp group for sap support.

https://chat.whatsapp.com/FbNZK3T4hgHAPQWe3ukEw6

0
Janos Nagy
Jan 18, 2017 at 03:59 PM
0

Hello

You can use the select case when else SQL Statement, you may look at on the following link the usage:

https://msdn.microsoft.com/en-us/library/ms181765.aspx?f=255&MSPPError=-2147217396

it should look similar:

select case OMRC.FIRMNAME when 'NOCIL LTD' then OITM.U_STOCKPRICE else t0.StockPrice end

regards,

János

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

Janson ,

It will not work with Creport. I need to pull a single field to my layout design.If I follow your method i need to pull two values ie omrc.firmname and oitm.stockprice to the same field location. What i need is to create a variable say 'price' if the

OMRC.FIRMNAME = 'NOCIL LTD' then the value of 'price' should be 'OITM.U_STOCKPRICE' else the value of 'price' is WTR1.stockprice.I tried this using crystal report formulae but it won't work.It would be good if you give me the full code.

0
Dell Stinnett-Christy Jan 23, 2017 at 06:15 PM
0

I did a couple of things to your query:

1. Removed the "selects" for the from and to warehouses - using separate select statements in the select part of the query can significantly slow it down. The query had two sets of 8 selects off of the same table. Instead, I joined the table into the query twice using different aliases.

2. Added Janos' case statement to the select section of the query and tweaked it a bit so that you can get the data into a single field in the query. I also removed "t0.StockPrice" from the list of fields for the query.

3. Added the code I posted to the Where clause so that you can use it if you need to filter the data based on the original If statement.

I've commented where I've made changes to the code. You don't have to use both 2 and 3 above, just the one that meets your needs.

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.LineTotal,
  t2.DocTotal,t0.Dscription,t0.Quantity,t0.Quantity*t0.StockPrice[Value], 
  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], 

/*--------- 1.  Changed fields to remove Select -----------*/  
  fromT.whsname [wFrom whs], fromT.Street [wFrom street], fromT.StreetNo [wFrom streetno.], 
  fromT.Block [wFromblock], fromT.Building [wFrom building], fromT.ZipCode [wFromzipcode], 
  fromT.City [wFrom city], fromT.County [wFrom county], 
  toT.whsname [wTo whs], toT.Street [wTo street], toT.StreetNo [wTo streetno], 
  toT.Block [wTo block], toT.Building [wTo building], toT.ZipCode [wTozipcode], 
  toT.City [wTocity], toT.County [wTocounty],

/*--------- 2.  Case statement -------------------*/  
  case IsNull(OMRC.FIRMNAME, 'None') 
    when 'NOCIL LTD' then OITM.U_STOCKPRICE 
    else t0.StockPrice 
  end [Price]
  
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 

/*---------- 1. New joins -------------*/    
  left join owhs fromT
    on t2.filler = fromt.WhsCode
  left join owhs toT
    on t0.WhsCode = toT.WhsCode
where t2.docentry='{?DocKey@}'

/*---------- 3. Filter ---------------*/
  and (
    (OMRC.FIRMNAME = 'NOCIL LTD' and t0.StockPrice = OITM.U_STOCKPRICE)
    or
    OMRC.FIRMNAME <> 'NOCIL LTD'
    )

-Dell

,

Looking at your query, I rewrote it a bit to make it faster - using "select" statements in the list of fields will significantly slow down the query, so I did some joins with different aliases to get the data. I then did two things - you can choose which one is what you want.

1. I added Janos' case statement to get the data into a single field in the field list so that it brings back the data to the report.

2. I added my code to the where clause in case you want to filter the data based on your original If statement.

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.LineTotal,
  t2.DocTotal,t0.Dscription,t0.Quantity,t0.Quantity*t0.StockPrice[Value], 
  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], 

/*------------- Replaced Select statements ----------*/
  fromT.whsname [wFrom whs], fromT.Street [wFrom street], fromT.StreetNo [wFrom streetno], 
  fromT.Block [wFromblock], fromT.Building [wFrom building], fromT.ZipCode [wFromzipcode], 
  fromT.City [wFrom city], fromT.County [wFrom county], 
  toT.whsname [wTo whs], toT.Street [wTo street], toT.StreetNo [wTo streetno], 
  toT.Block [wTo block], toT.Building [wTo building], toT.ZipCode [wTozipcode], 
  toT.City [wTocity], toT.County [wTocounty],

/*------Case statement to add field --------------*/
  case IsNull(OMRC.FIRMNAME, 'None') 
    when 'NOCIL LTD' then OITM.U_STOCKPRICE 
    else t0.StockPrice 
  end [Price]

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 

/*--------2 new joins -------------*/
  left join owhs fromT
    on t2.filler = fromt.WhsCode
  left join owhs toT
    on t0.WhsCode = toT.WhsCode
where t2.docentry='{?DocKey@}'


/*---------- Added filter to Where clause -----------*/
  and (
    (OMRC.FIRMNAME = 'NOCIL LTD' and t0.StockPrice = OITM.U_STOCKPRICE)
    or
    OMRC.FIRMNAME <> 'NOCIL LTD'
    )

Pick which option you want and let us know if it works.

-Dell

Share
10 |10000 characters needed characters left characters exceeded