Altered SQL Query Not Executing AS Oracle Query

Working with another Expert for SQL and he came up with code below to solve a problem.
I am trying to run this statement as a Oracle Statement (made minor adjustments for Oracle) then executing it in a Open Query over Oracle tables.
I have done this many times but am getting a error when trying to execute below code.           *** FROM keyword not found where expected ***
Anyone see what the issue might be ?  



DECLARE @sql VARCHAR(max)


QUERY  :

SET @sql =      ' SELECT P1.CUSTNO,P1.GROUP_ID,P1.PROBLEM_ID,L1.MODIFIED_ON,L1.DISPUTE_AMT,L1.SALESID,L1.CONTACT_ID,L1.RESOLVER,L1.PCODE,L1.STATUS,L1.SALESAREA,L1.CLOSEDATE,L1.PAYDATE
                      , (SELECT MIN(L2.MODIFIED_ON) FROM GPCOMP1.GPPROB P2, GPCOMP1.GPPROBLOG L2
                         WHERE P2.CUSTNO = L2.CUSTNO
                                     AND   P2.GROUP_ID = L2.GROUP_ID
                         AND   P2.SALESID = L2.SALESID
                         AND   P2.CONTACT_ID = L2.CONTACT_ID
                                     AND   P2.RESOLVER = L2.RESOLVER
                         AND   P2.PCODE = L2.PCODE
                         AND   P2.STATUS = L2.STATUS
                         AND   P2.SALESAREA = L2.SALESAREA
                                     AND   P2.CLOSEDATE = L2.CLOSEDATE
                                     AND   P2.PAYDATE = L2.PAYDATE                        
                      ) AS EarliestDate
                      , ROW_NUMBER() OVER (PARTITION BY P1.CUSTNO ORDER BY L1.MODIFIED_ON DESC) AS Row
      FROM GPCOMP1.GPPROB P1, GPCOMP1.GPPROBLOG L1
    Where P1.CUSTNO = ''P447680Y'' and P1.GROUP_ID = 1950188 and P1.GROUP_ID = L1.GROUP_ID '

   
EXECUTE QUERY  :

SET @sql = N'SELECT * from openquery ([GTPFR_LOUIS],    ''' + Replace(@sql, '''', '''''') + ''')'
      EXEC (@sql)  


ERROR IS :

OLE DB provider "OraOLEDB.Oracle" for linked server "GTPFR_LOUIS" returned message "ORA-00923: FROM keyword not found where expected".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query " SELECT P1.CUSTNO,P1.GROUP_ID,P1.PROBLEM_ID,L1.MODIFIED_ON,L1.DISPUTE_AMT,L1.SALESID,L1.CONTACT_ID,L1.RESOLVER,L1.PCODE,L1.STATUS,L1.SALESAREA,L1.CLOSEDATE,L1.PAYDATE
                      , (SELECT MIN(L2.MODIFIED_ON) FROM GPCOMP1.GPPROB P2, GPCOMP1.GPPROBLOG L2
                         WHERE P2.CUSTNO = L2.CUSTNO
                                     AND   P2.GROUP_ID = L2.GROUP_ID
                         AND   P2.SALESID = L2.SALESID
                         AND   P2.CONTACT_ID = L2.CONTACT_ID
                                     AND   P2.RESOLVER = L2.RESOLVER
                         AND   P2.PCODE = L2.PCODE
                         AND   P2.STATUS = L2.STATUS
                         AND   P2.SALESAREA = L2.SALESAREA
                                     AND   P2.CLOSEDATE = L2.CLOSEDATE
                                     AND   P2.PAYDATE = L2.PAYDATE                        
                      ) AS EarliestDate
                      , ROW_NUMBER() OVER (PARTITION BY P1.CUSTNO ORDER BY L1.MODIFIED_ON DESC) AS Row
      FROM GPCOMP1.GPPROB P1, GPCOMP1.GPPROBLOG L1
    Where P1.CUSTNO = 'P447680Y' and P1.GROUP_ID = 1950188 and P1.GROUP_ID = L1.GROUP_ID " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "GTPFR_LOUIS".
thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Row is a reserved word.

Try this:
SET @sql =      ' SELECT P1.CUSTNO,P1.GROUP_ID,P1.PROBLEM_ID,L1.MODIFIED_ON,L1.DISPUTE_AMT,L1.SALESID,L1.CONTACT_ID,L1.RESOLVER,L1.PCODE,L1.STATUS,L1.SALESAREA,L1.CLOSEDATE,L1.PAYDATE
                      , (SELECT MIN(L2.MODIFIED_ON) FROM GPCOMP1.GPPROB P2, GPCOMP1.GPPROBLOG L2
                         WHERE P2.CUSTNO = L2.CUSTNO
                                     AND   P2.GROUP_ID = L2.GROUP_ID
                         AND   P2.SALESID = L2.SALESID
                         AND   P2.CONTACT_ID = L2.CONTACT_ID
                                     AND   P2.RESOLVER = L2.RESOLVER
                         AND   P2.PCODE = L2.PCODE
                         AND   P2.STATUS = L2.STATUS
                         AND   P2.SALESAREA = L2.SALESAREA
                                     AND   P2.CLOSEDATE = L2.CLOSEDATE
                                     AND   P2.PAYDATE = L2.PAYDATE                        
                      ) AS EarliestDate
                      , ROW_NUMBER() OVER (PARTITION BY P1.CUSTNO ORDER BY L1.MODIFIED_ON DESC) AS MyRow
      FROM GPCOMP1.GPPROB P1, GPCOMP1.GPPROBLOG L1
    Where P1.CUSTNO = ''P447680Y'' and P1.GROUP_ID = 1950188 and P1.GROUP_ID = L1.GROUP_ID '

   

Open in new window


Best to try SQL in SQL Plus of SQL Developer first.  They are really good about pointing out the general area of the error:
 , ROW_NUMBER() OVER (PARTITION BY P1.CUSTNO ORDER BY L1.MODIFIED_ON DESC) AS Row
                                                                              *
ERROR at line 14:
ORA-00923: FROM keyword not found where expected

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Just a small note on language...

"SQL" does not belong to Microsoft or describe their database. SQL was originated by IBM engineers and is now used to describe a standardized language.

T-SQL (Transact SQL) is used by "Microsoft SQL Server" but even this SQL variant is also used by some Sybase products as well.

I only mention this because the question title is ambiguous; "Altered T-SQL Query Not Executing AS Oracle Query" would be more precise.
0
thayduckProgrammer AnalystAuthor Commented:
Thanks. That was the problem.

Now I just have to get it to do what I need it to do.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.