Delivery Document Number need in Report

[Former Member]

Hi

i have created a query based on A/R Invoice in which i need to show Delivery Document No

SELECT T0.[CardCode] as 'Business Partner Code', T0.[CardName] as 'Business Partner',convert(varchar(10),

T0.[DocDate],105) as 'Invoice Date',

T0.[DocNum] as 'Invoice No', T0.[DocTotal] as 'Invoice Amount',

T0.[PaidtoDate] as 'Invoice Paid Amount', (T0.[docTotal] - T0.[PaidtoDate]) as 'Remaining Invoice Amount',

case T0.[DocStatus]

  when ('C') then 'Closed'

  when ('O') then 'Open'

  else 'Invalid Parameters' end AS 'Status'

FROM OINV T0

where CardCode between @CardCodeFrom and @CardCodeTo and DocDate between @DocFromDate and @DocToDate

END

Regards

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please try this,

SELECT T0.[CardCode] as 'Business Partner Code', T0.[CardName] as 'Business Partner',convert(varchar(10),

T0.[DocDate],105) as 'Invoice Date',

T0.[DocNum] as 'Invoice No', T0.[DocTotal] as 'Invoice Amount',

T0.[PaidtoDate] as 'Invoice Paid Amount', (T0.[docTotal] - T0.[PaidtoDate]) as 'Remaining Invoice Amount',

case T0.[DocStatus]

  when ('C') then 'Closed'

  when ('O') then 'Open'

  else 'Invalid Parameters' end AS 'Status', T1.BaseRef as DO

FROM OINV T0 INNER JOIN INV1 on T0.docentry = T1.Docentry

where CardCode between @CardCodeFrom and @CardCodeTo and DocDate between @DocFromDate and @DocToDate

END

Thanks

[Former Member · Accepted solution]

am getting an error

Msg 4104, Level 16, State 1, Line 12

The multi-part identifier "T1.Docentry" could not be bound.

Msg 4104, Level 16, State 1, Line 9

The multi-part identifier "T1.BaseRef" could not be bound.

[kothandaraman_nagarajan · Accepted solution]

sorry missed T1,

SELECT T0.[CardCode] as 'Business Partner Code', T0.[CardName] as 'Business Partner',convert(varchar(10),

T0.[DocDate],105) as 'Invoice Date',

T0.[DocNum] as 'Invoice No', T0.[DocTotal] as 'Invoice Amount',

T0.[PaidtoDate] as 'Invoice Paid Amount', (T0.[docTotal] - T0.[PaidtoDate]) as 'Remaining Invoice Amount',

case T0.[DocStatus]

  when ('C') then 'Closed'

  when ('O') then 'Open'

  else 'Invalid Parameters' end AS 'Status', T1.BaseRef as DO

FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

where CardCode between @CardCodeFrom and @CardCodeTo and DocDate between @DocFromDate and @DocToDate

END