Oracle Openquery - limit selection

I am trying to limit my selection from an oracle view from a selection in a local table.

in the code below i choose

  select * from openquery( OPS_ORACLE_IW, 'select
       sum(oss.SLS_QTY) as QTY
       ,sum(oss.SLS_AMT) as AMT
       from dcp0.V_SALE_ITEM oss
        oss.SLS_PROC_WRK_DT BETWEEN TO_DATE(''01/01/2008'', ''MM/DD/YYYY'') AND TO_DATE(''12/31/2011'' , ''MM/DD/YYYY'')
       ,oss.SLS_CUST_BUS_TYP_CD ')

as Sales
more ▼

asked Nov 04, 2010 at 01:44 PM in Default

Ken 2 gravatar image

Ken 2
13 2 2 3

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

1 answer: sort voted first

Here is one way to do it. It is based on actually feeding the values you retrieve from the SQL Server table into dynamic SQL, which you can then feed to your openquery and execute. I remember from way back when that Oracle used to have a limitation on the number of items in the IN() part of the predicate, the number of items was limited to 900. In any case, if your


returns less than 900 records then the solution below will work. Here is the final script:

declare @sql varchar(max);
declare @id_list varchar(max);

set @id_list = stuff(cast(
        ', ' + cast(EM_ITEM_NUM as varchar(30))
        from sqldb.dbo.T_PROGRAM_ITEM
        where PROGRAM_ID = 46
        for xml path(''), type
    ) as varchar(max)), 1, 2, '')

--select @id_list;

set @sql = '
select * from openquery( OPS_ORACLE_IW, ''select
    sum(oss.SLS_QTY) as QTY
    ,sum(oss.SLS_AMT) as AMT
    from dcp0.V_SALE_ITEM oss
    OSS.EM_ITEM_NUM IN(' + @id_list + ')
    and oss.SLS_PROC_WRK_DT BETWEEN TO_DATE(''''01/01/2008'''', ''''MM/DD/YYYY'''') 
    and TO_DATE(''''12/31/2011'''' , ''''MM/DD/YYYY'''')
    GROUP BY TO_CHAR(oss.INVC_DT, ''''YYYYMM''''),
        ,oss.SLS_CUST_BUS_TYP_CD '')

as Sales';

exec (@sql);
more ▼

answered Nov 04, 2010 at 02:30 PM

Oleg gravatar image

15.9k 2 4 24

Thank you - if EM_ITEM_NUM is fixed @ CHAR(7) can i amend everything to 7 characters or does unicode come into play with multiple vendors
Nov 04, 2010 at 02:36 PM Ken 2

@Ken 2 If your EM_ITEM_NUM is actually char(7) rather than the number then you can of course change varchar(30) cast to char(7), but in this case, you will also need to change the top script which populates the @id_list such that the list comes out not just as comma-delimited, but also with each item surrounded by a pair of single quotes on either side of it. Why 2 quotes on each side is because the dynamic part will "eat" one of the 2 and the list will come out correctly. In other words, the way it is done now, the @id_list will look like this

id1, id2, id3, id4

when it should look like

''id1'', ''id2'', ''id3'', ''id4''
Nov 04, 2010 at 02:43 PM Oleg

I know re: 2 quotes in dynamic sql - so far I'm @ 20 min with no results yet - is there any optimizing that can be done?

Can I use this Set @sql = 'select * into from openquery......
Nov 04, 2010 at 02:48 PM Ken 2

@Ken 2 Basically, if your EM_ITEM_NUM is character based then you will have to change the line in the answer currently reading

', ' + cast(EM_ITEM_NUM as varchar(30))

to instead read

', ' + '''''' + EM_ITEM_NUM + ''''''
in order to produce double-quoted list which will become single quoted when fed to exec.
Nov 04, 2010 at 02:50 PM Oleg
@Ken 2 Yes, you can, but try something simpler first, like for example take your original query and hardcode a single EM_ITEM_NUM there just to see what is the cause of the problem. Usually, feeding IN(some_delimited_list) to Oracle should not slow it down. On the other hand, you are probably talking to the Oracle view rather than a table and are querying at least 3 years worth of data, so if you have a chance to login directly to Oracle and run the same query, you might get some ideas about how to optimize it if it is possible. Sometimes, a small tweak such as changing the order of the conditions in the where clause can make a dramatic change in performance of Oracle queries. This is very unfortunate that Oracle's optimizer is still not smart enough to figure out the best order of all conditions in the where clause (like in SQL Server, which can easily change the order of AND conditions if it feels that it will speed up execution time).
Nov 04, 2010 at 02:53 PM Oleg
(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



Answers and Comments

SQL Server Central

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



asked: Nov 04, 2010 at 01:44 PM

Seen: 1929 times

Last Updated: Nov 04, 2010 at 01:59 PM