Home » Open Source » MySQL » OLE DB provider "OraOLEDB.Oracle" for linked server "GTPFR" returned message "ORA-00907: missing rig (SQL/Oracle)
OLE DB provider "OraOLEDB.Oracle" for linked server "GTPFR" returned message "ORA-00907: missing rig [message #669289] Thu, 12 April 2018 07:18 Go to next message
cpudad
Messages: 2
Registered: April 2018
Junior Member
Trying to execute below code in a open query over Oracle database but keep getting below error.
This inserted code was originally written for me to execute in a SQL statement over SQL database.
I have done this many times but first time I have tried using WITH inside the Oracle statement.
When I try and execute this statement when executing over Oracle DB it fails with:

OLE DB provider "OraOLEDB.Oracle" for linked server "GTPFR" returned message "ORA-00907: missing right parenthesis".
 (WITH groupdata AS 
( 
         SELECT   l1.created_on, 
                  l1.group_id, 
                  l1.salesid, 
                  l1.status, 
                  row_number() over (PARTITION BY l1.group_id ORDER BY l1.created_on DESC) AS grouprow 
         FROM     gpcomp1.gpproblog l1 
         WHERE    l1.paydate IS NULL 
         AND      l1.closedate IS NULL 
         AND      l1.group_id = 2062335 ) 
SELECT mx.group_id, 
       ( 
              SELECT created_on 
              FROM   groupdata chg 
              WHERE  chg.group_id = mx.group_id 
              AND    chg.grouprow = CHANGE.changerow - 1 ) AS earliestdate 
FROM   groupdata mx cross apply 
       ( 
              SELECT min(grouprow) 
              FROM   groupdata chg 
              WHERE  chg.group_id = mx.group_id 
              AND    ( 
                            chg.salesid <> mx.salesid 
                     OR     chg.status <> mx.status ) ) CHANGE(changerow) 
WHERE  mx.grouprow = 1) AS statusdateminmax, 
Re: OLE DB provider "OraOLEDB.Oracle" for linked server "GTPFR" returned message "ORA-00907: missing rig [message #669291 is a reply to message #669289] Thu, 12 April 2018 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
in which flavor of database (MYSQ, Oracle, SQL Server, etc) are you trying to invoke SQL above?

BTW, WHERE clause should not be terminated with comma as below.
>WHERE mx.grouprow = 1) AS statusdateminmax,


Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
Re: OLE DB provider "OraOLEDB.Oracle" for linked server "GTPFR" returned message "ORA-00907: missing rig [message #669300 is a reply to message #669291] Sat, 14 April 2018 08:39 Go to previous messageGo to next message
cpudad
Messages: 2
Registered: April 2018
Junior Member
Sorry, if you look at full Oracle expression you will see why the comma was there.
Re: OLE DB provider "OraOLEDB.Oracle" for linked server "GTPFR" returned message "ORA-00907: missing rig [message #669301 is a reply to message #669300] Sat, 14 April 2018 12:50 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT Substr(b.custno,1,15)           AS custno,
       Substr(b.custno,2,6)            AS custno6,
       b.amount                        AS amount,
       b.trantype                      AS trantype,
       Substr(h.team,1,10)             AS team,
       h.collname                      AS portfolioname,
       a.colltype                      AS custtype,
       a.company                       AS company,
       c.problem_id                    AS problemid,
       c.group_id                      AS groupid,
       Substr(c.ucontent01,1,255)      AS disputedesc,
       e.description                   AS reasoncategory,
       d.pdesc                         AS reason,
       g.group_name                    AS ownercategory,
       gpuser_2.fullname               AS owner1,
       c.salesarea                     AS salesarea,
       To_char(c.de_date,'YYYY/MM/DD') AS escalationdate,
       i.resname                       AS resolver,
       (with groupdata AS
       (
                SELECT   l1.created_on,
                         l1.group_id,
                         l1.salesid,
                         l1.status,
                         row_number() over (partition BY l1.group_id ORDER BY l1.created_on DESC) AS grouprow
                FROM     gpcomp1.gpproblog l1
                WHERE    l1.paydate IS NULL
                AND      l1.closedate IS NULL
                AND      l1.group_id = 2062335 )
SELECT mx.group_id,
       (
              SELECT created_on
              FROM   groupdata chg
              WHERE  chg.group_id = mx.group_id
              AND    chg.grouprow = CHANGE.changerow - 1 ) AS earliestdate
FROM   groupdata mx CROSS apply
       (
              SELECT min(grouprow)
              FROM   groupdata chg
              WHERE  chg.group_id = mx.group_id
              AND    (
                            chg.salesid <> mx.salesid
                     OR     chg.status <> mx.status ) ) CHANGE(changerow)
WHERE  mx.grouprow = 1)                         AS statusdateminmax,
       '0'                                      AS statusdatemin,
       '0'                                      AS statusdatedefault,
       m.description                            AS statusname,
       substr(c.ucontent02,1,30)                AS problemextended,
       substr(c.ucontent03,1,255)               AS nextstep,
       substr(c.ucontent04,1,255)               AS required1,
       b.flexdate3                              AS flexdate3,
       ' '                                      AS disputereasoncatcode,
       d.pcode                                  AS disputereasoncode,
       ' '                                      AS ownercategorycode,
       ' '                                      AS owner1code,
       c.dispute_amt                            AS disputeamt,
       gpcomp1.getgpnotes(c.rowid,c.problem_id) AS problemnote
FROM   gpcomp1.gpcust a,
       gpcomp1.gprecl b,
       gpcomp1.gpprob c,
       gpcomp1.gppcat d,
       gpcomp1.gpreacat e,
       gpcomp1.gpcoll h,
       gpcomp1.gpreslvr i,
       gpglobal.gpuserlic gpuser_1,
       gpglobal.gpuserlic f,
       gpcomp1.gpstatus m,
       gpglobal.gpgroup g,
       gpglobal.gpuserlic gpuser_2,
       gpglobal.gpusergroupmap p,
       gpglobal.gpuser_group_entitlement q
WHERE  b.custno = a.custno
AND    b.tran_id = c.open_invoice_tran_id
AND    c.pcode = d.pcode
AND    d.reascat = e.category
AND    a.collector = h.collcode
AND    c.resolver = i.rescode
AND    (
              c.modified_by = gpuser_1.userlic_id)
AND    (
              c.created_by = f.userlic_id)
AND    (
              NOT (
                     c.problem_id IS NULL))
AND    c.status = m.code
AND    (
              c.salesid = gpuser_2.named_user)
AND    (
              p.userlic_id = gpuser_2.userlic_id)
AND    g.group_id = p.group_id
AND    g.group_id = q.group_id
AND    q.entitlement IN ('DRS_USER.CAN_BE_PROB_OWNER',
                         'GP_COLLECTOR.CAN_BE_PROB_OWNER',
                         'GP_RESOLVER.CAN_BE_PROB_OWNER',
                         'CM_USER.CAN_BE_PROB_OWNER')
AND    a.inactive = 'N'
AND    c.closedate IS NULL

I don't think the problem is with WITH but with "APPLY() CHANGE()" which is not a valid syntax for an Oracle SQL.
What does it do in MySQL?

[Updated on: Sat, 14 April 2018 12:50]

Report message to a moderator

Previous Topic: how to retrive all COUNTRY from a table
Next Topic: MySQL Issue
Goto Forum:
  


Current Time: Sat Sep 19 08:25:46 CDT 2020