Hi All
I am trying to link a subquery to my main SQL query but amd getting error message re only one expression can be used without EXISTS and about Q2.CC can not be bound.
Can you please have a look at the subquery below and let me know where I am going wrong???
Any help greatly appriciated.
############
SELECT 'JE ' + RTRIM(c.TransId) AS DocNum, c.Line_ID, c.RefDate, c.Account, c.Debit, c.Credit, c.VatAmount, c.LineMemo AS RowDescription, c.TransType, d.Transcode, d.Ref2, d.Memo AS JournalRemarks, d.TaxDate, c.FinncPriod, f.Year, e.SubNum, e.Name, e.F_RefDate, e.T_RefDate, f.F_RefDate as F_YDate, f.T_RefDate as T_YDate, g.AcctName, g.ActType,g.AcctCode, g.FatherNum, g.Levels,g.GrpLine, g.GroupMask, g.AccntntCod,g.FrgnName, h.AcctName AS Father, OADM.CompnyName, OADM.MainCurncy, OADM.SysCurrncy, CC2.OcrCode, CC2.PrcName, (select c.TRansId, MAX(c.ocrCode2) as CC from JDT1 c GROUP BY c.TRansId) Q2
From JDT1 c
INNER JOIN OJDT d on c.TransId = d.TransId
INNER JOIN OFPR e on e.AbsEntry=c.FinncPriod
INnER JOIN OACP f on f.PeriodCat=e.Category
INNER JOIN OACT g on g.AcctCode=c.Account
INNER JOIN OACT h on h.AcctCode=g.FatherNum
LEFT OUTER JOIN
( SELECT ODIM.DimCode, DimDesc, OOCR.OcrCode, OOCR.OcrName, OPRC.PrcCode, OPRC.PrcName, OCR1.PrcAmount/OCR1.OcrTotal AS Dist, OPRC.CCTypeCode
FROM ODIM
INNER JOIN OOCR ON ODIM.DimCode=OOCR.DimCode
INNER JOIN OCR1 ON OOCR.OcrCode=OCR1.OcrCode
INNER JOIN OPRC ON OPRC.PrcCode = OCR1.PrcCode AND ODIM.DimCode = OPRC.DimCode
WHERE DimActive = 'Y' AND OOCR.Active='Y' AND OPRC.Active='Y') CC2 ON CC2.OcrCode = Q2.CC, OADM