Multiple Query

[former_member593234]

Hi,

SELECT T0.[DocEntry], t0.[docnum],T0.[DocDate],T0.[SlpCode], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], t4.ItmsGrpNam, T0.[DocTotal],
T2.[SlpName] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN OITM T3 ON T3.ItemCode = T1.ItemCode
INNER JOIN OITB T4 ON T4.ItmsGrpCod = T3.ItmsGrpCod
WHERE T0.[DocStatus] Like 'O' AND T0.[DocDate] BETWEEN [%0] AND [%1]
ORDER BY T2.[SlpCode]

This is the query for Open Sales Order, I want to add Open purchase order also same query,

How its Possible??

[former_member593234]

SELECT Distinct
T0.[DocEntry],
T0.[DocNum],
T5.[ItmsGrpNam],
T3.[FirmName] as [Mfg.Name],
T0.[Docdate],
Case when T0.Docstatus = 'O' then 'OPEN' End 'Docstatus',
T1.[Dscription] as [Description],
T1.U_Part_No as [PartNo] ,
T1.Price as [Unit Price],
T1.[Quantity],
T1.LineTotal as [Total Price],
T0.[CardCode] as [Vendor Code] ,
T0.[CardName] as [Vendor Name],
T6.CardFName as [Branch],
T9.[firstName] as [Owner name],
T4.[SlpName] as [SalesEmploee], T0.Comments as 'Remarks'
FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OHEM T9 ON T1.OwnerCode = T9.empID
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OMRC T3 ON T2.FirmCode = T3.FirmCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode INNER JOIN OITB T5 ON T5.[ItmsGrpCod] = T2.[ItmsGrpCod] INNER JOIN OCRD T6 ON T6.CardCode = T0.CardCode WHERE T0.Docdate >= '[%0]' and T0.Docdate <= '[%1]' and T0.Docstatus = 'O'

SELECT T5.[ItmsGrpNam],
T0.Docentry,
T0.[DocNum],
T0.docdate,
T1.U_Part_No as 'PartNo',
Case
when T0.DocType = 'I' then 'INVOICE'
when T0.DocType = 'S' then 'INVOICE'
End 'Doctype',
T1.[Itemcode],
T1.[Dscription],
T1.[U_INV_F_Text],
T6.FirmName as [Manufacturer],
T1.Price as 'SellingPrice',
T1.[Quantity],T1.Linetotal as 'TotalSellingprice',
(T1.GrssProfit) AS [GrossProfit],
(((Coalesce((T1.GrssProfit),0))/NullIf((T1.LineTotal),0))*100) as [GrosProfit%],
T0.[CardCode],
T0.[CardName],
T1.WhsCode as 'warehouse',
T7.CardFName AS 'Branch',
T4.[SlpName]

FROM OQUT T0 INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OMRC T3 ON T2.FirmCode = T3.FirmCode
INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode
INNER JOIN OITB T5 ON T5.[ItmsGrpCod] = T2.[ItmsGrpCod]
INNER JOIN OMRC T6 ON T2.FirmCode = T6.FirmCode
INNER JOIN OCRD T7 on T7.CardCode = T0.CardCode
WHERE
t0.docdate >= [%1] and t0.docdate <= [%2] and t0.docstatus = 'o'

How to make these two querys under one report????

[zal_parchem2]

Hello Athul:

You need to spend some time trying to learn something about SQL. Here are the steps you need to do:

1. Buy the book titled "Mastering SQL for SAP Business One" by Gordon Du.
2. Look up the function of "UNON ALL".
3. Set the two SQL you copied from somewhere to be what is required in the book. I don't know where you got these two SQL, but what you have above does not make any sense - the CASE is not even correct, different calculations from different SQL, different UDFs, different JOINs, etc., etc. I see about ten things wrong if anyone tries to combine the two SQL together.

You might think about putting an Excel spreadsheet as an example of what you are wanting, because what you have above does not lend itself to any kind of understanding as to what the outcome should be. It looks like a mess of data without any kind of logic.

Take a good look at this:

Tips and Tricks Wiki

Regards, Zal