Logic Miss

[former_member188586]

hi

Experts

could help me in this query

The Bellow Querys are separately working but when i Combined it's not Executed...  

SELECT  distinct T3.[U_E360_Sln], T3.[U_E360_Bdn],(select (sum(a.[LineTotal])) FROM OPCH T2 INNER JOIN PCH1 a ON T2.DocEntry = a.DocEntry INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode WHERE  a.[U_E360_Sln]='[%0]' and a.[TargetType] !='19'  and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101' ) as TOTAL

FROM OPCH T2 INNER JOIN PCH1 T3 ON T2.DocEntry = T3.DocEntry INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode WHERE  T3.[U_E360_Sln]=[%0] and T3.[TargetType] !='19'  and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101'

Union

SELECT sum(T1.[U_Qty]) "Total Qty" FROM [dbo].[@E360_EXEMB]  T0 inner join  [dbo].[@E360_EXEMBC]  T1 on T0.[DocEntry]= T1.[DocEntry] WHERE T0.[U_PrjName]=[%1] and   T1.[U_Slno] =[%0] GROUP BY T1.[U_Qty]

Thanks for Advance Help

----AKR

[former_member184146 · Accepted solution]

Hi,

Try this

SELECT  distinct T3.[U_E360_Sln], T3.[U_E360_Bdn],(select (sum(a.[LineTotal]))

FROM OPCH T2 INNER JOIN PCH1 a ON T2.DocEntry = a.DocEntry INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode

WHERE  a.[U_E360_Sln]='[%0]' and a.[TargetType] !='19' 

and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101' ) as TOTAL

FROM OPCH T2 INNER JOIN PCH1 T3 ON T2.DocEntry = T3.DocEntry

INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode

WHERE  T3.[U_E360_Sln]=[%0] and T3.[TargetType] !='19'  and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101'

Union

SELECT '' as 'U_E360_Sln','' as 'U_E360_Bdn',sum(T1.[U_Qty]) as 'TOTAL'

FROM [dbo].[@E360_EXEMB]  T0 inner join  [dbo].[@E360_EXEMBC]  T1 on T0.[DocEntry]= T1.[DocEntry]

WHERE T0.[U_PrjName]=[%1] and   T1.[U_Slno] =[%0] GROUP BY T1.[U_Qty]

--Manish

[former_member188586 · Accepted solution]

hi

manish

the Query is not Executed ...

[former_member184146 · Accepted solution]

then you need to convert  '' blank fields in the second part  to the same data type the fields in frist part 1.

[former_member188586 · Accepted solution]

How to Do ??

[former_member188586 · Accepted solution]

Thanks to All and Special Thanks to @manish k

at Lost i Got The Solution with different ways

SELECT  T3.[U_E360_Sln], T3.[U_E360_Bdn],(sum (T3.[LineTotal])) as "SC Total Vlue" ,(Select (sum(b.[U_Qty])) from [dbo].[@E360_EXEMB]  a inner join  [dbo].[@E360_EXEMBC]  b on a.[DocEntry]= b.[DocEntry] where b.[U_Slno]='[%0]') As 'Execution Total Qty'

FROM OPCH T2 INNER JOIN PCH1 T3 ON T2.DocEntry = T3.DocEntry INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode WHERE  T3.[U_E360_Sln]=[%0] and T3.[TargetType] !='19'  and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101'

Group By T3.[U_E360_Sln], T3.[U_E360_Bdn]

[former_member188586]

Manish made like this (Excellent way )

SELECT  distinct T3.[U_E360_Sln], T3.[U_E360_Bdn],(select (sum(a.[LineTotal]))

FROM OPCH T2 INNER JOIN PCH1 a ON T2.DocEntry = a.DocEntry INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode

WHERE  a.[U_E360_Sln]='H1' and  T2.[U_E360_Prj] ='abc' and a.[TargetType] !='19'

and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101' ) as 'Total Value','0' as 'Total Qty'

FROM OPCH T2 INNER JOIN PCH1 T3 ON T2.DocEntry = T3.DocEntry

INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode

WHERE  T3.[U_E360_Sln]='H1' and T2.[U_E360_Prj] ='ABC' AND T3.[TargetType] !='19' 

and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101'

UNION

SELECT '' as 'U_E360_Sln','' as 'U_E360_Bdn','0' as 'Total Value',sum(T1.[U_Qty]) as 'Total Qty'

FROM [dbo].[@E360_EXEMB]  T0 inner join [dbo].[@E360_EXEMBC]  T1 on T0.[DocEntry]= T1.[DocEntry]

WHERE T0.[U_PrjName]='abc' and T1.[U_Slno] ='H1' GROUP BY T1.[U_Qty]

  Order BY T3.[U_E360_Sln] desc

[kothandaraman_nagarajan]

Hi,

For UNION function, both queries should have same number column. If not there, use   ' ' to get desired result.

Thanks.

[former_member188586]

hi Thanks for replay ,but i'not understand 2nd query result set should display as one separate column  on above Query ...please can you do it Sir ...

[former_member188586]

hi

i Taken like this then also not working  but separately working fine 

SELECT  distinct T3.[U_E360_Sln], T3.[U_E360_Bdn],(select (sum(a.[LineTotal]))

FROM OPCH T2 INNER JOIN PCH1 a ON T2.DocEntry = a.DocEntry INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode

WHERE  a.[U_E360_Sln]='[%0]' and  T2.[U_E360_Prj] ='[%1]' and a.[TargetType] !='19'

and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101' ) as TOTAL

FROM OPCH T2 INNER JOIN PCH1 T3 ON T2.DocEntry = T3.DocEntry

INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode

WHERE  T3.[U_E360_Sln]=[%0] and T2.[U_E360_Prj] =[%1] AND T3.[TargetType] !='19'  and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101'

Union

SELECT '' as 'U_E360_Sln','' as 'U_E360_Bdn',sum(T1.[U_Qty]) as 'TOTAL'

FROM [dbo].[@E360_EXEMB]  T0 inner join  [dbo].[@E360_EXEMBC]  T1 on T0.[DocEntry]= T1.[DocEntry]

WHERE T0.[U_PrjName]=[%1] and   T1.[U_Slno] ='[%0]' GROUP BY T1.[U_Qty]

...

[former_member188586]

hi

SELECT  distinct T3.[U_E360_Sln], T3.[U_E360_Bdn],(select (sum(a.[LineTotal]))

FROM OPCH T2 INNER JOIN PCH1 a ON T2.DocEntry = a.DocEntry INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode

WHERE  a.[U_E360_Sln]='[%0]' and  T2.[U_E360_Prj] ='[%1]' and a.[TargetType] !='19'

and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101' ) as TOTAL

FROM OPCH T2 INNER JOIN PCH1 T3 ON T2.DocEntry = T3.DocEntry

INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode

WHERE  T3.[U_E360_Sln]=[%0] and T2.[U_E360_Prj] =[%1] AND T3.[TargetType] !='19'  and T4.[GroupCode] <> '5' and T4.[GroupCode] <>'101'

Bellow Query Comes  to display  the above Query next column

SELECT '' as 'U_E360_Sln','' as 'U_E360_Bdn',sum(T1.[U_Qty]) as 'TOTAL'

FROM [dbo].[@E360_EXEMB]  T0 inner join  [dbo].[@E360_EXEMBC]  T1 on T0.[DocEntry]= T1.[DocEntry]

WHERE T0.[U_PrjName]=[%1] and   T1.[U_Slno] ='[%0]' GROUP BY T1.[U_Qty]

Please help me ....

[frank_wang6]

When you use UNION, it means the output columns including data type from the two queries have to be same.

But yours are not.

Frank

[former_member188586]

hi Thanks for replay ,but i'not understand 2nd query result set should display as one separate column  on above Query..........

..please can you do it Sir ...