Query Check it - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Check it

Author

Topic

Areeba.ali.88
Starting Member

7 Posts

Posted - 2010-08-25 : 06:07:32

Helllo Expert i have created a query it's result fine

select tmp1.form as [TRANS TYPE],tmp1.Docdate,tmp1.DocNum,tmp1.CardName,tmp1.TaxId11 as [TIN NO.],tmp1.IMPOREXP
,tmp1.Import as [EXPORT SALE],tmp1.INVTRANS as 'OutWard Transfer/Central Sale',tmp1.FormNo
,tmp1.Import+tmp1.INVTRANS 'TotalSale',tmp1.B4Tax as [LOCAL SALE],tmp1.TaxCode AS [TAX CODE],tmp1.VatSum as [OUTPUT TAX AMOUNT]
,tmp1.DocTotal 'TOTAL SALE INCLUDING TAX' ,tmp1.Location AS [RECEIVER LOCATION],tmp1.InvTotal as 'inv'
from (
SELECT 1 'Order1','Sales-N' 'Form', T0.Docdate,T0.DocNum ,T0.CardName,T2.TaxId11
,(case when t3.TfcId=1 then T0.DocTotal else 0 end)DocTotal
,(case when t3.TfcId=2 then T0.DocTotal else 0 end) 'INVTRANS'
,'' 'FormNo'
, (case when t3.TfcId=1 then T1.TaxCode else null end)TaxCode
,(case when t3.TfcId=1 then T0.VatSum else 0 end)VatSum,
(case when t3.TfcId=1 then (T0.Doctotal-T0.VatSum-t0.totalexpns) else 0 end) 'B4Tax'
,T2.IMPOREXP, 0 'Import' ,T5.Location,0 'InvTotal'
FROM [dbo].[OINV] T0
Left Outer Join INV1 T1 ON T0.DocEntry = T1.DocEntry
left outer join INV12 T2 on T0.DocEntry = T2.DocEntry
left outer join OSTC T3 on T1.TaxCode = T3.Code
left Outer join owhs T4 on T4.whsCode = T1.WhsCode
left OUTER Join OLCT T5 on T5.Code = T4.Location
where T1.LineNum = (select Top 1 linenum from INV1 where INV1.DocEntry = T0.DocEntry )
and T3.TfcId in('1','2') and T2.IMPOREXP <> 'Y'
and T0.DocType = 'I'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'
Union
SELECT 2 'Order1','Sales-Y' 'Form',T0.Docdate,T0.DocNum ,T0.CardName,T2.TaxId11 ,0 ,0,'','', 0,0 ,T2.IMPOREXP,T0.DocTotal 'Import'
,T5.Location,0 'InvTotal'
FROM [dbo].[OINV] T0
Left Outer Join INV1 T1 ON T0.DocEntry = T1.DocEntry
left outer join INV12 T2 on T0.DocEntry = T2.DocEntry
left outer join OSTC T3 on T1.TaxCode = T3.Code
left Outer join owhs T4 on T4.whsCode = T1.WhsCode
left OUTER Join OLCT T5 on T5.Code = T4.Location
where T1.LineNum = (select Top 1 linenum from INV1 where INV1.DocEntry = T0.DocEntry )
--and T3.TfcId = 1
and T2.IMPOREXP = 'Y'
and T0.DocType = 'I'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'

union
select tmp3.Order1,'INV' 'Form', tmp3.DocDate,tmp3.DocNum,tmp3.[CardName], tmp3.TaxId11,0,0,isnull(tmp3.FormNo,'') 'FormNo'
,'' 'TaxCode', 0 'VatSum', 0 'B4Tax', '' 'IMPOREXP' , 0 'Import', tmp3.fromloc,sum(tmp3.DocTotal)'InvTotal'
from
(SELECT 3 'Order1',T4.Location 'fromloc',T0.[DocNum], T0.[DocDate],T0.U_INV_Transferor, T0.[CardName] ,T6.Transcat,(t10.credit)DocTotal
,T0.U_TIN_NO, T9.TaxId11,T6.FormNo FROM OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry AND T1.LINENUM = 0 AND Substring(T0.Filler,1,3) <> Substring(T1.WhsCode,1,3)
INNER JOIN WTR12 T6 ON T0.DocEntry = T6.DocEntry
INNER JOIN OJDT T7 ON T0.TransId = T7.TransId and T0.objtype = T7.transtype
inner join jdt1 t10 on t7.transid=t10.transid
left outer join owhs T2 on T2.whsCode = T0.filler
left Outer join owhs T3 on T3.whsCode = T1.WhsCode
left OUTER Join OLCT T4 on T4.Code = T2.Location
left OUTER Join OLCT T5 on T5.Code = T3.Location
left outer join OCRD T8 on T0.CardCode = T8.CardCode
left outer join (select * from crd7 where crd7.addrtype = 'S') T9 on T8.CardCode = T9.CardCode and isnull(T9.Address,'') <> ''
where
T0.Jrnlmemo <> 'Cancelled' and
isnull(T0.Comments,'') not like 'Inventory transfer no. % has been cancelled'
--and T4.Location = 'MUMBAI'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'

) tmp3
group by tmp3.Order1, tmp3.DocDate,tmp3.DocNum,tmp3.U_INV_Transferor, tmp3.TaxId11,tmp3.[CardName], tmp3.U_TIN_NO,tmp3.FormNo, tmp3.fromloc

union

SELECT 4 'Order1','AR Credit-N' 'Form', T0.Docdate,T0.DocNum ,T0.CardName,T2.TaxId11
,(case when t3.TfcId=1 then -T0.DocTotal else 0 end)DocTotal
,0 'INVTRANS','' 'FormNo'
, (case when t3.TfcId=1 then T1.TaxCode else null end)TaxCode
,(case when t3.TfcId=1 then -T0.VatSum else 0 end)VatSum,
(case when t3.TfcId=1 then -(T0.Doctotal-T0.VatSum-t0.totalexpns) else 0 end) 'B4Tax'
,T2.IMPOREXP, 0 'Import' ,T5.Location,0 'InvTotal'
FROM [dbo].[ORIN] T0
Left Outer Join RIN1 T1 ON T0.DocEntry = T1.DocEntry
left outer join RIN12 T2 on T0.DocEntry = T2.DocEntry
left outer join OSTC T3 on T1.TaxCode = T3.Code
left Outer join owhs T4 on T4.whsCode = T1.WhsCode
left OUTER Join OLCT T5 on T5.Code = T4.Location
where T1.LineNum = (select Top 1 linenum from RIN1 where RIN1.DocEntry = T0.DocEntry )
and T0.DocType = 'I'
and T3.TfcId = 1 and T2.IMPOREXP <> 'Y'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'

union

SELECT 5 'Order1','AR Credit-Y' 'Form',T0.Docdate,T0.DocNum ,T0.CardName,T2.TaxId11 ,0 ,0,'','', 0,0 ,T2.IMPOREXP,-T0.DocTotal 'Import'
,T5.Location,0 'InvTotal'
FROM [dbo].[ORIN] T0
Left Outer Join RIN1 T1 ON T0.DocEntry = T1.DocEntry
left outer join RIN12 T2 on T0.DocEntry = T2.DocEntry
left outer join OSTC T3 on T1.TaxCode = T3.Code
left Outer join owhs T4 on T4.whsCode = T1.WhsCode
left OUTER Join OLCT T5 on T5.Code = T4.Location
where T1.LineNum = (select Top 1 linenum from RIN1 where RIN1.DocEntry = T0.DocEntry )
--and T3.TfcId = 1
and T2.IMPOREXP = 'Y'
and T0.DocType = 'I'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'

)tmp1
--where Tmp1.Location = 'SAHIBABAD'
order by docdate, order1

This query giving me this out put of coloum--

TRANS TYPE,Docdate,DocNum,CardName,TIN NO,IMPOREXP,EXPORT SALE,'OutWard Transfer/Central Sale,FormNo,TotalSale,[LOCAL SALE],
TAX CODE,OUTPUT TAX AMOUNT,TOTAL SALE INCLUDING TAX ,RECEIVER LOCATION,inv

i want to add two more coloum in this query so i am writing this query for adding two more coloum

select tmp1.form as [TRANS TYPE],tmp1.Docdate,tmp1.DocNum,tmp1.CardName,tmp1.TaxId11 as [TIN NO.],tmp1.IMPOREXP
,tmp1.Import as [EXPORT SALE],tmp1.INVTRANS as 'OutWard Transfer/Central Sale',tmp1.FormNo
,tmp1.Import+tmp1.INVTRANS 'TotalSale',tmp1.B4Tax as [LOCAL SALE],tmp1.TaxCode AS [TAX CODE],tmp1.VatSum as [OUTPUT TAX AMOUNT]
,tmp1.DocTotal 'TOTAL SALE INCLUDING TAX' ,tmp1.Location AS [RECEIVER LOCATION],tmp1.InvTotal as 'inv',tmp1.CTCode As [C Code],tmp1.CTSum As [C Sum]
from (
SELECT 1 'Order1','Sales-N' 'Form', T0.Docdate,T0.DocNum ,T0.CardName,T2.TaxId11
,(case when t3.TfcId=1 then T0.DocTotal else 0 end)DocTotal
,(case when t3.TfcId=2 then T0.DocTotal else 0 end) 'INVTRANS'
,'' 'FormNo'
, (case when t3.TfcId=1 then T1.TaxCode else null end)TaxCode
,(case when t3.TfcId=1 then T0.VatSum else 0 end)VatSum,
(case when t3.TfcId=2 then T1.TaxCode else null end)CTCODE
,(case when t3.TfcId=2 then T0.VatSum else 0 end)CTSum
,(case when t3.TfcId=1 then (T0.Doctotal-T0.VatSum-t0.totalexpns) else 0 end) 'B4Tax'
,T2.IMPOREXP, 0 'Import' ,T5.Location,0 'InvTotal'
FROM [dbo].[OINV] T0
Left Outer Join INV1 T1 ON T0.DocEntry = T1.DocEntry
left outer join INV12 T2 on T0.DocEntry = T2.DocEntry
left outer join OSTC T3 on T1.TaxCode = T3.Code
left Outer join owhs T4 on T4.whsCode = T1.WhsCode
left OUTER Join OLCT T5 on T5.Code = T4.Location
where T1.LineNum = (select Top 1 linenum from INV1 where INV1.DocEntry = T0.DocEntry )
and T3.TfcId in('1','2') and T2.IMPOREXP <> 'Y'
and T0.DocType = 'I'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'
Union
SELECT 2 'Order1','Sales-Y' 'Form',T0.Docdate,T0.DocNum ,T0.CardName,T2.TaxId11 ,0 ,0,'','', 0,0 ,T2.IMPOREXP,T0.DocTotal 'Import'
,T5.Location,0 'InvTotal',0 ,0
FROM [dbo].[OINV] T0
Left Outer Join INV1 T1 ON T0.DocEntry = T1.DocEntry
left outer join INV12 T2 on T0.DocEntry = T2.DocEntry
left outer join OSTC T3 on T1.TaxCode = T3.Code
left Outer join owhs T4 on T4.whsCode = T1.WhsCode
left OUTER Join OLCT T5 on T5.Code = T4.Location
where T1.LineNum = (select Top 1 linenum from INV1 where INV1.DocEntry = T0.DocEntry )
--and T3.TfcId = 1
and T2.IMPOREXP = 'Y'
and T0.DocType = 'I'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'

union
select tmp3.Order1,'INV' 'Form', tmp3.DocDate,tmp3.DocNum,tmp3.[CardName], tmp3.TaxId11,0,0,isnull(tmp3.FormNo,'') 'FormNo'
,'' 'TaxCode', 0 'VatSum', 0 'B4Tax', '' 'IMPOREXP' , 0 'Import', tmp3.fromloc,sum(tmp3.DocTotal)'InvTotal',0 ,0
from
(SELECT 3 'Order1',T4.Location 'fromloc',T0.[DocNum], T0.[DocDate],T0.U_INV_Transferor, T0.[CardName] ,T6.Transcat,(t10.credit)DocTotal
,T0.U_TIN_NO, T9.TaxId11,T6.FormNo FROM OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry AND T1.LINENUM = 0 AND Substring(T0.Filler,1,3) <> Substring(T1.WhsCode,1,3)
INNER JOIN WTR12 T6 ON T0.DocEntry = T6.DocEntry
INNER JOIN OJDT T7 ON T0.TransId = T7.TransId and T0.objtype = T7.transtype
inner join jdt1 t10 on t7.transid=t10.transid
left outer join owhs T2 on T2.whsCode = T0.filler
left Outer join owhs T3 on T3.whsCode = T1.WhsCode
left OUTER Join OLCT T4 on T4.Code = T2.Location
left OUTER Join OLCT T5 on T5.Code = T3.Location
left outer join OCRD T8 on T0.CardCode = T8.CardCode
left outer join (select * from crd7 where crd7.addrtype = 'S') T9 on T8.CardCode = T9.CardCode and isnull(T9.Address,'') <> ''
where
T0.Jrnlmemo <> 'Cancelled' and
isnull(T0.Comments,'') not like 'Inventory transfer no. % has been cancelled'
--and T4.Location = 'MUMBAI'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'

) tmp3
group by tmp3.Order1, tmp3.DocDate,tmp3.DocNum,tmp3.U_INV_Transferor, tmp3.TaxId11,tmp3.[CardName], tmp3.U_TIN_NO,tmp3.FormNo, tmp3.fromloc

union

SELECT 4 'Order1','AR Credit-N' 'Form', T0.Docdate,T0.DocNum ,T0.CardName,T2.TaxId11
,(case when t3.TfcId=1 then -T0.DocTotal else 0 end)DocTotal
,0 'INVTRANS','' 'FormNo'
, (case when t3.TfcId=1 then T1.TaxCode else null end)TaxCode
,(case when t3.TfcId=1 then -T0.VatSum else 0 end)VatSum,
(case when t3.TfcId=1 then -(T0.Doctotal-T0.VatSum-t0.totalexpns) else 0 end) 'B4Tax'
,T2.IMPOREXP, 0 'Import' ,T5.Location,0 'InvTotal',0,0
FROM [dbo].[ORIN] T0
Left Outer Join RIN1 T1 ON T0.DocEntry = T1.DocEntry
left outer join RIN12 T2 on T0.DocEntry = T2.DocEntry
left outer join OSTC T3 on T1.TaxCode = T3.Code
left Outer join owhs T4 on T4.whsCode = T1.WhsCode
left OUTER Join OLCT T5 on T5.Code = T4.Location
where T1.LineNum = (select Top 1 linenum from RIN1 where RIN1.DocEntry = T0.DocEntry )
and T0.DocType = 'I'
and T3.TfcId = 1 and T2.IMPOREXP <> 'Y'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'

union

SELECT 5 'Order1','AR Credit-Y' 'Form',T0.Docdate,T0.DocNum ,T0.CardName,T2.TaxId11 ,0 ,0,'','', 0,0 ,T2.IMPOREXP,-T0.DocTotal 'Import'
,T5.Location,0 'InvTotal',0,0
FROM [dbo].[ORIN] T0
Left Outer Join RIN1 T1 ON T0.DocEntry = T1.DocEntry
left outer join RIN12 T2 on T0.DocEntry = T2.DocEntry
left outer join OSTC T3 on T1.TaxCode = T3.Code
left Outer join owhs T4 on T4.whsCode = T1.WhsCode
left OUTER Join OLCT T5 on T5.Code = T4.Location
where T1.LineNum = (select Top 1 linenum from RIN1 where RIN1.DocEntry = T0.DocEntry )
--and T3.TfcId = 1
and T2.IMPOREXP = 'Y'
and T0.DocType = 'I'
--and t0.docdate>='[%0]' and t0.docdate<='[%1]'

)tmp1
--where Tmp1.Location = 'SAHIBABAD'
order by docdate, order1

But when i execute this query it give some error "Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric."
how can we resolved this problem

maevr
Posting Yak Master

169 Posts

Posted - 2010-08-25 : 07:14:31

It is very hard to see what columns you want to add including datatype and so on, please give more info about you want to achieve and how.

Have you checked the new fields for characters that cannot be converted.
If you are using a specific collation like finnsh_swedish, then 12,4 cannot be converted to float 12.4 without changing the comma to a dot(.)
If this is the case use replace(column name, ',', '.') as col for the column you want to convert

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:14:46

it would be much better if you can explain your problem giving some sample data and required output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Areeba.ali.88
Starting Member

7 Posts

Posted - 2010-08-26 : 02:57:05

quote:
Originally posted by visakh16

it would be much better if you can explain your problem giving some sample data and required output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks For Reply Both
mY PROBLEM HAS BEEN SOLVED.

Areeba

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:12:48

ok..If you think somebody else might benefit, you may post how you solved the problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources