0
Former Member
Jul 06, 2016 at 10:12 PM

# Need to add subquery in main query

91 Views

Hi All,

I need to add sub query in main query.

Main Query:

SELECT distinct T0.[DocNum], T0.[DocDate], T3.Series,T3.[DocNum], T3.[DocDate], T3.[CardName], T3.[Address], T3.Address2, T3.[DocTotal], T3.[RoundDif], T2.[ItemCode], T2.[Dscription], T2.[Quantity], T2.[DiscPrcnt], T2.[LineTotal], T2.[unitMsr], T2.[PriceBefDi], T3.[DocCur],

T3.numatcard,

(select Top 1 [TaxId1] from INV12 where docentry=t3.docentry),

(select Top 1 [TaxId11] from INV12 where docentry=t3.docentry),

T4.Batchnum,T4.Quantity, T6.[Name], T6.[Cellolar], T8.City, T9.[PymntGroup], T2.Price,T3.[TotalExpns],T3.[VatSum]

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join INV1 T2 on t2.baseentry=t1.docentry and t2.baseline=t1.linenum INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] left join IBT1 T4 on t4.itemcode=t2.itemcode and t4.whscode=t2.whscode and T4.BaseEntry = T2.DocEntry

inner join inv12 t5 on t5.docentry=t3.docentry

LEFT OUTER JOIN OCPR T6 ON T3.[CntctCode] = T6.[CntctCode]

left outer join OCRD t7 on t7.cardcode=t3.cardcode

left outer join CRD1 t8 on t8.cardcode=t7.cardcode

left outer JOIN OCTG T9 ON T3.[GroupNum] = T9.[GroupNum]

WHERE t4.bASEtYPE='13'

Sub Query:

(select case when inv1.uomentry=7 and inv.uomentry2=9 then IBT1.Quantity*160 else

case when inv1.uomentry=7 and inv.uomentry2=8 then IBT1.Quantity*80 else

0 end from INV1 left join IBT1 on IBT1.itemcode=INV1.itemcode and IBT1.whscode=INV1.whscode and IBT1.BaseEntry = INV1.DocEntry)

Can some one help me out.

Thanks & Regards,

Sai.