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

siera_gld gravatar image

siera_gld
1k 77 82 84

(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

Oleg gravatar image

Oleg
15.9k 2 4 24

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x378
x26

asked: Feb 22, 2011 at 03:56 PM

Seen: 1634 times

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