Skip to Content

# Logic Miss

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

##### Add a comment
10|10000 characters needed characters exceeded

### 4 Answers

• Best Answer
Posted on May 17, 2015 at 02:14 PM

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

##### Add a comment
10|10000 characters needed characters exceeded
• AndakondaRamudu A AndakondaRamudu A

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]

• Posted on May 17, 2015 at 05:54 AM

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

##### Add a comment
10|10000 characters needed characters exceeded
• 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 ...

• Posted on May 17, 2015 at 06:32 AM

Hi,

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

Thanks.

##### Add a comment
10|10000 characters needed characters exceeded
• AndakondaRamudu A AndakondaRamudu A

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 ....

• Posted on May 18, 2015 at 12:46 PM

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

##### Add a comment
10|10000 characters needed characters exceeded

### Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.