Link to home
Start Free TrialLog in
Avatar of meniyk
meniyk

asked on

troubleshooting the union sap sql query

Hi
need help on troubleshooting the sap sql syntax using union.
I have internal table where it call it_agent.


      SELECT name fldate bookid carrid connid class from sbook inner join stravelag
         on sbook~agencynum = stravelag~agencynum INTO CORRESPONDING FIELDS OF TABLE it_agent
          where sbook~customid  ='00000000'
        UNION
      SELECT name fldate bookid carrid connid class from sbook inner join stravelag
         on sbook~agencynum = stravelag~agencynum INTO CORRESPONDING FIELDS OF TABLE it_agent
           where sbook~customid = wa_scustom-ID order by name.

Thanks
Avatar of PortletPaul
PortletPaul
Flag of Australia image

try this:
SELECT name, fldate, bookid, carrid, connid, class
INTO CORRESPONDING FIELDS OF TABLE it_agent
FROM (
        SELECT name, fldate, bookid, carrid, connid, class
        FROM sbook 
        INNER JOIN stravelag on sbook~agencynum = stravelag~agencynum 
        WHERE sbook~customid  ='00000000' 

        UNION 

        SELECT name, fldate, bookid, carrid, connid, class 
        FROM sbook 
        WHERE sbook~customid = wa_scustom-ID 
    ) derived

Open in new window

nb: Im not familiar with:
"INTO CORRESPONDING FIELDS OF TABLE" but assume it would support a nested subquery.

also assume fields are separated by commas.
SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of meniyk
meniyk

ASKER

Hi Paul,
thank you for your reply. Just tried your code, it does not work.  
Unfortunately, it does not work in SAP ABAP.

here is the error msg i got
"select" is not defined in the ABAP Dictionary as table, projection view, or database view"

thanks
regards,
Is that error from your code or Paul's or both?

 James
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of meniyk

ASKER

Thank Paul  and James.
At last I manage to solve it my own by using another internal table to append the records instead append during the query stage...


Thank you.
Avatar of meniyk

ASKER

issue not solved.
for the encourage purposes, reward has been given