on 03-14-2009 7:46 AM
Hi experts...
I have been written some queries as below : -
My friend said need to include OJDT and JDT1 in order to diffrential "Credit". But i did it half way don't know how to separe it.....
CREATE PROCEDURE dbo.GL_Report
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- AR INV
SET NOCOUNT ON;
SELECT NNM1.BeginStr, ORIN.DocNum, ORIN.DocDate, ORIN.Series, ORIN.CardCode, ORIN.CardName, ORIN.Comments, RIN1.ItemCode,RIN1.AcctCode, RIN1.Dscription, RIN1.Quantity, ORIN.DocCur, ORIN.DocRate, RIN1.Price, RIN1.LineTotal AS Debit, 0 AS Credit ,RIN1.TotalFrgn, null,null,null,CONVERT(char(10),ORIN.DocType)
FROM ORIN INNER JOIN RIN1 ON ORIN.DocEntry = RIN1.DocEntry LEFT JOIN
NNM1 ON ORIN.Series = NNM1.Series
--WHERE RIN1.ItemCode NOT BETWEEN 'ZZ0001' AND 'ZZ9999'
UNION
SELECT NNM1.BeginStr, ORIN.DocNum, ORIN.DocDate, ORIN.Series, ORIN.CardCode, ORIN.CardName, ORIN.Comments, RIN1.ItemCode,RIN1.AcctCode, RIN1.Dscription, RIN1.Quantity, ORIN.DocCur, ORIN.DocRate, RIN1.Price,0 AS Debit,RIN1.LineTotal AS Credit, RIN1.TotalFrgn, null,null,null,CONVERT(char(10),ORIN.DocType)
FROM ORIN INNER JOIN RIN1 ON ORIN.DocEntry = RIN1.DocEntry LEFT JOIN
NNM1 ON ORIN.Series = NNM1.Series
-- JE
--UNION
--SELECT NNM1.BeginStr, OJDT.TransId ,OJDT.RefDate,OJDT.DocSeries,null, null,null,null,null,null,null,null,null,null,null,JDT1.Account,JDT1.Debit, JDT1.Credit,OJDT.Memo, --OJDT.Ref2,OJDT.Ref1
--FROM OJDT INNER JOIN JDT1 ON OJDT.TransId = JDT1.TransId LEFT JOIN
--NNM1 ON OJDT.Series = NNM1.Series
-- AR CM
UNION
SELECT NNM1.BeginStr, OINV.DocNum, OINV.DocDate, OINV.Series, OINV.CardCode, OINV.CardName, OINV.Comments, INV1.ItemCode,INV1.AcctCode, INV1.Dscription, INV1.Quantity, OINV.DocCur, OINV.DocRate, INV1.Price, INV1.LineTotal AS Debit, 0 AS Credit,INV1.TotalFrgn,null,null,null,CONVERT(char(10),OINV.DocType)
FROM OINV INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry LEFT JOIN
NNM1 ON OINV.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, OINV.DocNum, OINV.DocDate, OINV.Series, OINV.CardCode, OINV.CardName, OINV.Comments, INV1.ItemCode,INV1.AcctCode, INV1.Dscription, INV1.Quantity, OINV.DocCur, OINV.DocRate, INV1.Price, 0 AS Debit,INV1.LineTotal AS Credit,INV1.TotalFrgn,null,null,null,CONVERT(char(10),OINV.DocType)
FROM OINV INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry LEFT JOIN
NNM1 ON OINV.Series = NNM1.Series
-- AP INV
UNION
SELECT NNM1.BeginStr, OPCH.DocNum, OPCH.DocDate, OPCH.Series, OPCH.CardCode, OPCH.CardName,OPCH.Comments, PCH1.ItemCode,PCH1.AcctCode, PCH1.Dscription,PCH1.Quantity, OPCH.DocCur, OPCH.DocRate, PCH1.Price, PCH1.LineTotal AS Debit, 0 AS Credit,PCH1.TotalFrgn,null,null,null,CONVERT(char(10),OPCH.DocType)
FROM OPCH INNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry LEFT JOIN
NNM1 ON OPCH.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, OPCH.DocNum, OPCH.DocDate, OPCH.Series, OPCH.CardCode, OPCH.CardName,OPCH.Comments, PCH1.ItemCode,PCH1.AcctCode, PCH1.Dscription,PCH1.Quantity, OPCH.DocCur, OPCH.DocRate, PCH1.Price,0 AS Debit, PCH1.LineTotal AS Credit, PCH1.TotalFrgn,null,null,null,CONVERT(char(10),OPCH.DocType)
FROM OPCH INNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry LEFT JOIN
NNM1 ON OPCH.Series = NNM1.Series
-- AP CM
UNION
SELECT NNM1.BeginStr, ORPC.DocNum, ORPC.DocDate, ORPC.Series, ORPC.CardCode, ORPC.CardName,ORPC.Comments, RPC1.ItemCode,RPC1.AcctCode, RPC1.Dscription,RPC1.Quantity, ORPC.DocCur, ORPC.DocRate, RPC1.Price, RPC1.LineTotal AS Debit, 0 AS Credit,RPC1.TotalFrgn,null,null,null,CONVERT(char(10),ORPC.DocType)
FROM ORPC INNER JOIN RPC1 ON ORPC.DocEntry = RPC1.DocEntry LEFT JOIN
NNM1 ON ORPC.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, ORPC.DocNum, ORPC.DocDate, ORPC.Series, ORPC.CardCode, ORPC.CardName,ORPC.Comments, RPC1.ItemCode,RPC1.AcctCode, RPC1.Dscription,RPC1.Quantity, ORPC.DocCur, ORPC.DocRate, RPC1.Price,0 AS Debit, RPC1.LineTotal AS Credit, RPC1.TotalFrgn,null,null,null,CONVERT(char(10),ORPC.DocType)
FROM ORPC INNER JOIN RPC1 ON ORPC.DocEntry = RPC1.DocEntry LEFT JOIN
NNM1 ON ORPC.Series = NNM1.Series
-- GOOD RETURN
UNION
SELECT NNM1.BeginStr, ORDN.DocNum, ORDN.DocDate, ORDN.Series, ORDN.CardCode, ORDN.CardName,ORDN.Comments, RDN1.ItemCode,RDN1.AcctCode, RDN1.Dscription,RDN1.Quantity, ORDN.DocCur, ORDN.DocRate, RDN1.Price, RDN1.LineTotal AS Debit, 0 AS Credit,RDN1.TotalFrgn,null,null,null,CONVERT(char(10),ORDN.DocType)
FROM ORDN INNER JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry LEFT JOIN
NNM1 ON ORDN.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, ORDN.DocNum, ORDN.DocDate, ORDN.Series, ORDN.CardCode, ORDN.CardName,ORDN.Comments, RDN1.ItemCode,RDN1.AcctCode, RDN1.Dscription,RDN1.Quantity, ORDN.DocCur, ORDN.DocRate, RDN1.Price,0 AS Debit,RDN1.LineTotal AS Credit,RDN1.TotalFrgn,null,null,null,CONVERT(char(10),ORDN.DocType)
FROM ORDN INNER JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry LEFT JOIN
NNM1 ON ORDN.Series = NNM1.Series
-- GOOD RECEIPT PO
UNION
SELECT NNM1.BeginStr, OPDN.DocNum, OPDN.DocDate, OPDN.Series, OPDN.CardCode, OPDN.CardName,OPDN.Comments, PDN1.ItemCode,PDN1.AcctCode, PDN1.Dscription,PDN1.Quantity, OPDN.DocCur, OPDN.DocRate, PDN1.Price, PDN1.LineTotal AS Debit, 0 AS Credit,PDN1.TotalFrgn,null,null,null,CONVERT(char(10),OPDN.DocType)
FROM OPDN INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry LEFT JOIN
NNM1 ON OPDN.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, OPDN.DocNum, OPDN.DocDate, OPDN.Series, OPDN.CardCode, OPDN.CardName,OPDN.Comments, PDN1.ItemCode,PDN1.AcctCode, PDN1.Dscription,PDN1.Quantity, OPDN.DocCur, OPDN.DocRate, PDN1.Price, 0 AS Debit,PDN1.LineTotal AS Credit,PDN1.TotalFrgn,null,null,null,CONVERT(char(10),OPDN.DocType)
FROM OPDN INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry LEFT JOIN
NNM1 ON OPDN.Series = NNM1.Series
-- INCOMING (ACCOUNT)
UNION
SELECT NNM1.BeginStr, ORCT.DocNum, ORCT.DocDate, ORCT.Series, ORCT.CardCode, ORCT.CardName, ORCT.Comments, RCT4.Descrip, RCT4.AcctCode, RCT4.AcctName,null,
null, null, null, RCT4.SumApplied AS Debit, 0 AS Credit , null,null,null,null,CONVERT(char(10),ORCT.DocType)
FROM ORCT INNER JOIN RCT4 ON ORCT.DocNum = RCT4.DocNum LEFT JOIN
NNM1 ON ORCT.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, ORCT.DocNum, ORCT.DocDate, ORCT.Series, ORCT.CardCode, ORCT.CardName, ORCT.Comments, RCT4.Descrip, RCT4.AcctCode, RCT4.AcctName,null,
null, null, null,0 AS Debit, RCT4.SumApplied AS Credit, null,null,null,null,CONVERT(char(10),ORCT.DocType)
FROM ORCT INNER JOIN RCT4 ON ORCT.DocNum = RCT4.DocNum LEFT JOIN
NNM1 ON ORCT.Series = NNM1.Series
-- INCOMING (PAY TO)
UNION
SELECT NNM1.BeginStr, ORCT.DocNum, ORCT.DocDate, ORCT.Series, ORCT.CardCode, ORCT.CardName,ORCT.Comments,RCT1.BankCode,RCT1.CheckAct,null,null,null,null,null,RCT1.CheckSum, null,RCT1.CheckNum AS Debit, 0 AS Credit, null,null,CONVERT(char(10),ORCT.DocType)
FROM ORCT INNER JOIN RCT1 ON ORCT.DocNum = RCT1.DocNum LEFT JOIN
NNM1 ON ORCT.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, ORCT.DocNum, ORCT.DocDate, ORCT.Series, ORCT.CardCode, ORCT.CardName,ORCT.Comments,RCT1.BankCode,RCT1.CheckAct,null,null,null,null,null,RCT1.CheckSum, null,0 AS Debit,RCT1.CheckNum AS Credit, null,null,CONVERT(char(10),ORCT.DocType)
FROM ORCT INNER JOIN RCT1 ON ORCT.DocNum = RCT1.DocNum LEFT JOIN
NNM1 ON ORCT.Series = NNM1.Series
-- OUTGOING (ACCOUNT)
UNION
SELECT NNM1.BeginStr, OVPM.DocNum, OVPM.DocDate, OVPM.Series, OVPM.CardCode, OVPM.CardName, OVPM.Comments, VPM4.Descrip, VPM4.AcctCode, VPM4.AcctName,null,
null, null, null, VPM4.SumApplied AS Debit, 0 AS Credit, null,null,null,null,CONVERT(char(10),OVPM.DocType)
FROM OVPM INNER JOIN VPM4 ON OVPM.DocNum = VPM4.DocNum LEFT JOIN
NNM1 ON OVPM.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, OVPM.DocNum, OVPM.DocDate, OVPM.Series, OVPM.CardCode, OVPM.CardName, OVPM.Comments, VPM4.Descrip, VPM4.AcctCode, VPM4.AcctName,null,
null, null, null,0 AS Debit, VPM4.SumApplied AS Credit, null,null,null,null,CONVERT(char(10),OVPM.DocType)
FROM OVPM INNER JOIN VPM4 ON OVPM.DocNum = VPM4.DocNum LEFT JOIN
NNM1 ON OVPM.Series = NNM1.Series
-- OUTGOING (PAY TO)
UNION
SELECT NNM1.BeginStr, OVPM.DocNum, OVPM.DocDate, OVPM.Series, OVPM.CardCode, OVPM.CardName,OVPM.Comments,VPM1.BankCode,VPM1.CheckAct,null,null,null,null,null,VPM1.CheckSum, null,VPM1.CheckNum AS Debit, 0 AS Credit,null, null,CONVERT(char(10),OVPM.DocType)
FROM OVPM INNER JOIN VPM1 ON OVPM.DocNum = VPM1.DocNum LEFT JOIN
NNM1 ON OVPM.Series = NNM1.Series
UNION
SELECT NNM1.BeginStr, OVPM.DocNum, OVPM.DocDate, OVPM.Series, OVPM.CardCode, OVPM.CardName,OVPM.Comments,VPM1.BankCode,VPM1.CheckAct,null,null,null,null,null,VPM1.CheckSum, null,0 AS Debit,VPM1.CheckNum AS Credit, null, null,CONVERT(char(10),OVPM.DocType)
FROM OVPM INNER JOIN VPM1 ON OVPM.DocNum = VPM1.DocNum LEFT JOIN
NNM1 ON OVPM.Series = NNM1.Series
END
GO
Thanks
Regards,
Danny
Hi Danny,
Have you tried the SP when you just Union two or three tables? Even if it can be done, I believe it will use too much system resource so that the database performance will be affected too much. Beside, I could not understand your goal from your SQL codes. What is your friend really need?
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi
Rather populate temporary tables than doing all those UNIONs. You can then select from the temporar table.
regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.