Wants to add 2 field in Query

[Former Member]

Hello Experts,

I wanted to add 2 fields in my existing query report i.e. Purchase Register

The 2 fields are GST Registration number and Mobile Number..

I tried to fetch the data from CRD1 but unortunately getting this error

"1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'cellolar'. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'cntctPrsn'. 3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared"

Please help me for this...

Regards

Malay

[jitin_chawla]

query please

[Former Member]

can you please share your mail id ??

[Johan_H]

Please post the query here.

[Former Member]

Please check query once...

[Former Member]

Sorry for the late reply...I was on vacation with my family.

Here is the query :

select T0.Docnum 'Invoice No.',T0.DocEntry AS 'DocEntry', T2.SeriesName,T0.U_NewDocDate 'New Document Date', T0.TaxDate as 'DocumentDate', T0.DocDate,

T0.cardcode as 'VendorCode',

T0.cardname as 'VendorName',T9.E_Mail 'Email Id',

T0.NumAtCard as 'BillNo',

T1.TaxId2 as 'VAT No.',

(T0.DocTotal+ISNULL(T8.WTAmnt,0))'Amount Before WTAX',ISNULL(T8.WTAmnt,0)'TDS',T0.Doctotal 'AP Total',

ISNULL(T6.Doctotal,0) 'APCR Total',ISNULL((ISNULL(T0.PaidToDate,0)-ISNULL(T6.DocTotal,0)),0)'Paid Amount',((T0.Doctotal-T0.PaidToDate))'Net Payable Amount',

T0.U_Pymnt_Date, cast(T0.U_Pymnt_Remarks as nvarchar(max)) 'Payment Remarks'

from OPCH T0

LEFT OUTER JOIN PCH12 T1 ON T0.DocEntry = T1.DocEntry

LEFT OUTER JOIN NNM1 T2 ON T0.Series = T2.SERIES

LEFT OUTER JOIN PCH4 T3 ON T3.DocEntry=T0.DocEntry

LEFT OUTER JOIN RPC1 T5 ON T5.BaseEntry = T0.DocEntry

LEFT OUTER JOIN ORPC T6 ON T6.DocEntry = T5.DocEntry

LEFT OUTER JOIN OFPR T7 ON T0.FinncPriod = T7.AbsEntry

LEFT OUTER JOIN PCH5 T8 ON T8.AbsEntry = T0.DocEntry

LEFT OUTER JOIN OCRD T9 ON T0.CardCode = T9.CardCode

where T0.TaxDate>=[%0] And T0.TaxDate<=[%1] And

T0.PIndicator<>'FY2013-14' And T0.DocTotal<>T0.PaidToDate AND T0.DocStatus<>'C'

GROUP BY T0.Docnum ,T2.SeriesName,T0.TaxDate,

T0.cardcode,

T0.cardname,

T0.NumAtCard,

T1.TaxId2,

T0.DocTotal, T0.VatSum, T0.TotalExpns,T0.RoundDif, T6.DocTotal, T6.VatSum, T6.TotalExpns,T0.RoundDif,T7.Indicator,T0.PaidToDate,

T0.TotalExpns,T0.DocTotal, t0.DocEntry, T6.RoundDif, T6.DocEntry, T8.WTAmnt, T9.E_Mail, T0.DocDate, T0.U_Pymnt_Date,

cast(T0.U_Pymnt_Remarks as nvarchar(max)), T0.U_NewDocDate

ORDER BY T0.TAXDATE

[Johan_H]

Hi,

In what table is the GST Registration number, and what is the field name please?

Regards,

Johan

[Former Member]

Hi Mr. Johan,

I just need a gst number in between my fetched data through query..

No matter where it comes..

in CRD1 we will get the desired data which I wants..

GSTRegnNo, GSTType and U_GSTINB this 3 field i need in my query..

Regards

Malay

[Johan_H · Accepted solution]

Hi,

Assuming that these fields are used with the billing address, please try this:

SELECT T0.Docnum 'Invoice No.',T0.DocEntry AS 'DocEntry', T2.SeriesName,
T0.U_NewDocDate 'New Document Date',
T0.TaxDate as 'DocumentDate',
T0.DocDate,
T0.cardcode as 'VendorCode',
T0.cardname as 'VendorName',
T9.E_Mail as 'Email Id',
T0.NumAtCard as 'BillNo',
T1.TaxId2 as 'VAT No.',
(T0.DocTotal+ISNULL(T8.WTAmnt,0)) as 'Amount Before WTAX',
ISNULL(T8.WTAmnt,0) as 'TDS',
T0.Doctotal as 'AP Total',
ISNULL(T6.Doctotal,0) as 'APCR Total',
ISNULL((ISNULL(T0.PaidToDate,0)-ISNULL(T6.DocTotal,0)),0) as 'Paid Amount',
((T0.Doctotal-T0.PaidToDate)) as 'Net Payable Amount',
T0.U_Pymnt_Date, 
cast(T0.U_Pymnt_Remarks as nvarchar(max)) as 'Payment Remarks',
T10.GSTRegnNo, T10.GSTType, T10.U_GSTINB

FROM OPCH T0
LEFT OUTER JOIN PCH12 T1 ON T0.DocEntry = T1.DocEntry
LEFT OUTER JOIN NNM1 T2 ON T0.Series = T2.SERIES
LEFT OUTER JOIN PCH4 T3 ON T3.DocEntry=T0.DocEntry
LEFT OUTER JOIN RPC1 T5 ON T5.BaseEntry = T0.DocEntry
LEFT OUTER JOIN ORPC T6 ON T6.DocEntry = T5.DocEntry
LEFT OUTER JOIN OFPR T7 ON T0.FinncPriod = T7.AbsEntry
LEFT OUTER JOIN PCH5 T8 ON T8.AbsEntry = T0.DocEntry
LEFT OUTER JOIN OCRD T9 ON T0.CardCode = T9.CardCode
LEFT OUTER JOIN CRD1 T10 ON T9.CardCode = T10.CardCode AND T10.AdresType = 'B'

WHERE T0.TaxDate >= [%0] And T0.TaxDate<= [%1]
And T0.PIndicator<>'FY2013-14' 
And T0.DocTotal<>T0.PaidToDate
and T0.DocStatus<>'C'

GROUP BY T0.Docnum ,T2.SeriesName,T0.TaxDate,
T0.cardcode,
T0.cardname,
T0.NumAtCard,
T1.TaxId2,
T0.DocTotal, T0.VatSum, T0.TotalExpns,T0.RoundDif, T6.DocTotal, T6.VatSum, T6.TotalExpns,T0.RoundDif,T7.Indicator,T0.PaidToDate,
T0.TotalExpns,T0.DocTotal, t0.DocEntry, T6.RoundDif, T6.DocEntry, T8.WTAmnt, T9.E_Mail, T0.DocDate, T0.U_Pymnt_Date,
cast(T0.U_Pymnt_Remarks as nvarchar(max)), T0.U_NewDocDate, T10.GSTRegnNo, T10.GSTType, T10.U_GSTINB

ORDER BY 5

Regards,

Johan

[Former Member · Accepted solution]

Thanks Mr. Johan...

Its working and I got the data too in the way i needed...

Regards

Malay

[gonzalogomez]

Only agregate T9.Cellular

this field "GST Registration number" .... ¿in which table or form you type it?