Problem in query how to set the data in crystalreport according user entry ???

[Former Member]

Dear all,

Please give the suggestion  of problem in  sql query for the report

SELECT IBT1.ItemCode, WTR1.Dscription, WTR1.WhsCode,OBTN.DistNumber, OBTN.MnfDate, OBTN.ExpDate, WTR1.Quantity, OWTR.Filler

FROM      

  OBTN INNER JOIN

                      IBT1 ON OBTN.DistNumber = IBT1.BatchNum AND OBTN.ItemCode = IBT1.ItemCode INNER JOIN

                      OWTR ON IBT1.BaseNum = OWTR.DocNum AND IBT1.BaseType = OWTR.ObjType INNER JOIN

                      WTR1 ON IBT1.WhsCode = WTR1.WhsCode AND IBT1.ItemCode = WTR1.ItemCode AND OWTR.DocEntry = WTR1.DocEntry and IBT1.BaseLinNum=wtr1.LineNum

WHERE OBTN.U_Packing_Date_04 IS NOT NULL and IBT1.ItemCode like 'FIVF%'  and WTR1.WHSCODE = 'PACK_REJ'

and

(OWTR .DocDate >='20151101') and 

(OWTR.DocDate <='20151130') and  wtr1.Quantity  >=10000

   and Direction  = 0

  and isnull (U_Disp_Fg_Gq,'NA') <> 'No'

in the query there one problem is it catch  only  first entry of datawarehouse if there another entry done by user then it will not update in report it show only first entry.

Regards,

Varun Pandya

[former_member207653]

http://scn.sap.com/community/crystal-reports

Crystal Reports (CR) place is above in case you need assistance with the product.

It is not clear what you are asking for. Are you saying that that query should return two rows? Is that query generated by CR?
Or do you want to edit that query to include all rows inserted by the same user?

[Former Member]

Dear Mirco,

you see the above image there are one entry are fetched in a report instead of two entry show in a second image.i want total 24524 in crystal report.

Regards,

Varun Pandya

[former_member207653]

Difficult to answer without knowing the schema of that database.

I guess that the issue is in your query because you want to retrieve WTR1.Quantity but there is the following condition:

wrt1.Quantity >= 10000

In the first screenshot, only one row has a quantity >= 10000. Can you edit that query and try with

wtr1.Quantity >= 6000

[Former Member]

I want sum of both entry not a single entry.

[former_member207653]

http://dcx.sap.com/index.html#sa160/en/dbreference/sum-function.html

You can use the sum function