Click here to Skip to main content
15,867,777 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello ALL,
I want to join two pivot table but getting error on below query.
Please, help me
Thanks

What I have tried:

SQL
With a1 as
(SELECT DISTINCT LEMNUM,
STATUSID,
CHANGEDATE
FROM BI_HZ_ETL.LEM_CNRLEMSTATUS 
WHERE STATUS NOT IN('DELETED','NEW'))
PIVOT (MAX (CHANGEDATE)
FOR STATUSID
IN (20 Submit,25 Rejected,40 WAPPR,50 APPR,55 POST,60 COMP)
) w), a2 as
(SELECT DISTINCT LEMNUM,
STATUSID,
CHANGEBy
FROM BI_HZ_ETL.LEM_CNRLEMSTATUS 
WHERE STATUS NOT IN('DELETED','NEW'))
PIVOT (MAX (trim(CHANGEby))
FOR STATUSID
IN (20 Submit,25 Rejected,40 WAPPR,50 APPR,55 POST,60 COMP)) w)
select LEMNUM, Statusid, changedate, changeby from a1 a11 
inner join a2 a22 on a22=a111
left join BI_HZ_ETL.LEM_FACTS lf on(lf.LEMNUM=W.LEMNUM)
        join BI_HZ_ETL.LEM_VENDOR V on(v.vendor_ID=lf.vendor)
Posted
Updated 3-May-22 12:02pm
v3
Comments
Richard MacCutchan 2-May-22 10:05am    
What error?
xoxo29401 2-May-22 10:26am    
getting below error message
Message=ORA-00928: missing SELECT keyword

It looks like you're having mismatched parenthesis and a 'naming issue' in pivoting. To isolate the problem, try running each cte separately. Also you seem to have an extra alias in the end of each cte

For example the first one could be something like
SQL
SELECT DISTINCT 
       LEMNUM,
       STATUSID,
       CHANGEDATE
FROM BI_HZ_ETL.LEM_CNRLEMSTATUS
WHERE STATUS NOT IN ('DELETED','NEW')
PIVOT (MAX (CHANGEDATE)
       FOR STATUSID
       IN (20 AS "Submit",25 AS "Rejected", 40 AS "WAPPR", 50 AS "APPR",55 AS "POST",60 AS "COMP"))


If that runs fine, do the same for second one etc.

--------------------
New example added
SQL
SELECT distinct 
       W.*, 
       v.vendor_name,
       lf.SITEID
FROM (SELECT DISTINCT 
             LEMNUM,
             STATUSID,
             CHANGEDATE,
             changeby
      FROM BI_HZ_ETL.LEM_CNRLEMSTATUS
      WHERE STATUS NOT IN('DELETED','NEW'))
      PIVOT (
         MAX(CHANGEDATE) AS MAXCHANGEDATE, MAX(CHANGEBY) AS MAXCHANGEBY
         FOR STATUSID IN (20 Submit, 40 WAPPR, 50 APPR, 55 POST, 60 COMP)
      )) W
...
 
Share this answer
 
v2
Comments
xoxo29401 2-May-22 11:31am    
Thanks Wendelius for your time and reply
When I do one pivot table. Below query is working but changedate and changeby are in one column and do not calculate max(changedate). How can i do in two join pivot.

SELECT distinct W.*, v.vendor_name,lf.SITEID
FROM (SELECT DISTINCT LEMNUM,
STATUSID,
CHANGEDATE,changeby
FROM BI_HZ_ETL.LEM_CNRLEMSTATUS
WHERE STATUS NOT IN('DELETED','NEW'))
PIVOT ((MAX (CHANGEDATE ||'//'||CHANGEBY))
FOR STATUSID
IN (20 Submit,
40 WAPPR,
50 APPR,
55 POST,
60 COMP)) W
left join BI_HZ_ETL.LEM_FACTS lf on(lf.LEMNUM=W.LEMNUM)
join BI_HZ_ETL.LEM_VENDOR V on(v.vendor_ID=lf.vendor)
Wendelius 2-May-22 22:59pm    
Not sure if I understand your question correctly but if you want to aggregate multiple columns you need to separate them. See the added example
Thanks Wendelius.
Its didn't work. But I solved other way this
 
Share this answer
 
Comments
CHill60 4-May-22 6:41am    
If you want to comment on a post then use the "Have a Question or Comment?" link next to it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900