Oracle Openquery - limit selection

Question

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
       ,TO_CHAR(oss.INVC_DT, ''YYYYMM'') AS SALE_DATE
       ,oss.EM_ITEM_NUM
       ,oss.SLS_CUST_BUS_TYP_CD
       from dcp0.V_SALE_ITEM oss
       WHERE 
       ** OSS.EM_ITEM_NUM  IN(SELECT EM_ITEM_NUM FROM sqldb.dbo.T_PROGRAM_ITEM WHERE PROGRAM_ID = 46)**
        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.EM_ITEM_NUM
       ,oss.SLS_CUST_BUS_TYP_CD ')

as Sales

Answer 1

0

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

SELECT EM_ITEM_NUM FROM sqldb.dbo.T_PROGRAM_ITEM WHERE PROGRAM_ID = 46

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(
    (
        select
        ', ' + 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
    ,TO_CHAR(oss.INVC_DT, ''''YYYYMM'''') AS SALE_DATE
    ,oss.EM_ITEM_NUM
    ,oss.SLS_CUST_BUS_TYP_CD
    from dcp0.V_SALE_ITEM oss
    where 
    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.EM_ITEM_NUM
        ,oss.SLS_CUST_BUS_TYP_CD '')

as Sales';

exec (@sql);

Oleg

answered Nov 04 '10 at 02:30 PM

Oleg
15.4k ● 1 ● 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 '10 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 '10 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 '10 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 '10 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 '10 at 02:53 PM Oleg

(comments are locked)

Oracle Openquery - limit selection

Follow this question

Related Questions