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

Assigned Tags

Related questions

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

    • 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

  • 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

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