x

OPENQUERY Error when accessing Oracle

I am getting an error OLE DB provider "MSDAORA" for linked server "" returned message "ORA-00907: missing right parenthesis

When I run this Query

  select * from openquery (OPS_ORACLE_IW, 'Select
                 SUM(SLS_QTY),
                 TO_CHAR(SLS_PROC_WRK_DT, ''''YYYYMM'''') AS SALE_DATE,
                 CNTRC_LEAD_TP_ID,
                 cust_acct_id, 
                 EM_ITEM_NUM
                     
                 from dcp0.V_SALE_ITEM
                 where rownum <=100
                 group by    
                 TO_CHAR(SLS_PROC_WRK_DT, ''''YYYYMM''''),
                 CNTRC_LEAD_TP_ID,
                 cust_acct_id, 
                 EM_ITEM_NUM') 

If I run it withough the date grouping it works fine...but I need it at the month/year level not the day

I am accessing oracle from SQL 2K5

more ▼

asked Feb 22, 2011 at 03:56 PM in Default

avatar image

siera_gld
1k 82 88 93

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It appears that if you want to run the query as written then you don't need to use 4 single quotes around your YYYYMM. You would need them only if the whole statement would be fed into the varchar variable first and then fed into exec so double escaping would be a must. Please try restating your statement like this:

 select * from openquery 
 (
     OPS_ORACLE_IW, 
     'Select 
         SUM(SLS_QTY), TO_CHAR(SLS_PROC_WRK_DT, ''YYYYMM'') 
         AS SALE_DATE, CNTRC_LEAD_TP_ID, cust_acct_id, EM_ITEM_NUM
         from dcp0.V_SALE_ITEM
         where rownum <=100
         group by    
             TO_CHAR(SLS_PROC_WRK_DT, ''YYYYMM''),
             CNTRC_LEAD_TP_ID,
             cust_acct_id, 
             EM_ITEM_NUM'
 );

Oleg

more ▼

answered Feb 22, 2011 at 04:14 PM

avatar image

Oleg
17.1k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x425
x33

asked: Feb 22, 2011 at 03:56 PM

Seen: 2103 times

Last Updated: Feb 22, 2011 at 10:25 PM

Copyright 2016 Redgate Software. Privacy Policy