Skip to Content

Need help merging 2 queries

All,

First off I am not a DBA but have been able to (with help from the SAP Community) work through various queries. I am again turning to the community for help. I have 2 queries:

Revenue by state:

SELECT [Code] as CustomerCode, [CustName] as CustName,[T] as State, isnull([1],0) as Jan, isnull([2],0) as Feb, [3] as Mar, isnull([4],0) as Apr, isnull([5],0) as May, isnull([6],0) as june, isnull([7],0) as July, isnull([8],0) as Aug, isnull([9],0) as Sept, isnull([10],0) as Oct, isnull([11],0) as Nov, isnull([12],0) as Dec from (SELECT T0.[CardCode] as Code,T0.[CardName] as CustName,T1.[State1] as T, sum(T0.[DocTotal]) as Total, month(T0.[DocDate]) as month FROM OINV T0 inner join OCRD T1 on t0.cardcode = t1.cardcode WHERE year(T0.[DocDate]) = 2016 GROUP BY T0.[CardName],T0.[DocDate],T0.[CardCode],T1.[State1] )S Pivot (sum(S.total) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

And BP Listing (addresses):

SELECT T0.[CardCode], T1.[CardName], (case T1.[CardType] when 'S' then 'Vendor' when 'L' then 'Lead' when 'C' then 'Customer' END) as 'BP Type', ( case T0.[AdresType] when 'B' then 'Bill To' when 'S' then 'Ship To' END) as 'Address Type', T0.[Address], T0.[Street] as 'Addr1', T0.[Block] as 'Addr2', T0.[City] as 'City', T0.[State] as 'State', T0.[ZipCode] as 'Zip', T0.[Country] as 'Country',T1.[E_Mail] as 'Email' FROM CRD1 T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

I woul dlike to have a query that spits out the revenue and includes the Bill To address for the customers. I tried to merge it but keep getting an error with BOOLEAN something.

My try:

SELECT [Code] as CustomerCode, [CustName] as CustName,[T] as State, isnull([1],0) as Jan, isnull([2],0) as Feb, [3] as Mar, isnull([4],0) as Apr, isnull([5],0) as May, isnull([6],0) as june, isnull([7],0) as July, isnull([8],0) as Aug, isnull([9],0) as Sept, isnull([10],0) as Oct, isnull([11],0) as Nov, isnull([12],0) as Dec, T2.[Address], T2.[Street] as 'Addr1', T2.[Block] as 'Addr2', T2.[City] as 'City', T2.[State] as 'State', T2.[ZipCode] as 'Zip', T2.[Country] as 'Country', T1.[E_Mail] as 'Email' from (SELECT T0.[CardCode] as Code,T0.[CardName] as CustName,T1.[State1] as T, sum(T0.[DocTotal]) as Total, month(T0.[DocDate]) as month FROM OINV T0 inner join OCRD T1 on t0.cardcode = t1.cardcode INNER JOIN OCRD T1 ON CRD1 T2 ON T2.CardCode = T1.CardCode and T1.Cardname = T2.CardName WHERE year(T0.[DocDate]) = 2016 AND T2.[AdresType] = 'B' GROUP BY T0.[CardName],T0.[DocDate],T0.[CardCode],T1.[State1] )S Pivot (sum(S.total) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

If someone can point out what I did wrong I would appreciate it so I can learn.

THANK YOU,

Keith H

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 22, 2016 at 11:59 AM

    Hi Keith,

    It would seem that the problem is to be found in the FROM clause. I see two problems:

    FROM OINV T0 
         inner join OCRD T1 on t0.cardcode = t1.cardcode 
         INNER JOIN OCRD T1 ON CRD1 T2 ON T2.CardCode = T1.CardCode 
                                      and T1.Cardname = T2.CardName
    1. OCRD was (guessing accidentally) written in there twice:
    2. You tried to join CRD1 on a field that cannot be found in CRD1 (T2.CardName)

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded