Microsoft Query calling Oracle Pipelined Function with params

[CassidyHunt]

I created a pipelined function in Oracle that runs perfect and returns the data just as I need to have it setup to start running calculations in Excel. I use the Microsoft Query tool and it tells me it can't add my table. The SQL is very simple:

SELECT * FROM TABLE(ARBD(TO_DATE(P1,'DD-MON-YYYY')))

P1 being the parameter. It requires a date to process the data.

Any help would be greatly appreciated.

Cassidy

 

[SkipVought]

Hi, your SQL is syntaticly incorrect!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CassidyHunt]

I can swap the P1 for a ? and it still doesn't work in excel but works on SQLPlus. I just used P1 to illustrate parameter. Now if I don't use Microsoft Query and setup the ADO connection with a macro it works just fine. Trying to figure out why I can make it work one way and not the other.

 

[SkipVought]

If you edit your QueryTable in MS Query and open the HELP dialog, there are several articles regarding parameters. Have you consulted these?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CassidyHunt]

Yes I read those before I posted. Here is the original sql I wrote that I need to parameterize in Excel that MS Query is not capable of handling graphically which disallows the use of parameters according to the help.

select z.customer_id,
       z.invoice_id,
       z.invoice_date,
       z.native_amount,
       case when z.aged_date <= 30 then
            z.tp
       else
           0
       end "CURRENT - 30",
       case when z.aged_date between 31 and 60 then
            z.tp
       else
           0
       end "OVER 30",
       case when z.aged_date between 61 and 90 then
            z.tp
       else
           0
       end "OVER 60",
       case when z.aged_date between 91 and 120 then
            z.tp
       else
           0
       end "OVER 90",
       case when z.aged_date  >= 121 then
            z.tp
       else
           0
       end "OVER 120",
       z.tp as TOTAL_AMOUNT
  from (select a.customer_id,
               a.invoice_id,
               a.total_amount as native_amount,
               a.invoice_date,
               case
                 when a.zero_date <= '30-JUN-2012' then
                  a.total_amount - nvl(a.paid_amount, 0)
                 else
                  a.total_amount - nvl(d.checks_paid, 0)
               end as tp,
               round(to_date('30-JUN-2012','DD-MON-YYYY') - a.invoice_date,2) as aged_date
          from receivable a,
               (select c.invoice_id, sum(c.amount) as checks_paid
                  from cash_receipt b, cash_receipt_line c
                 where b.customer_id = c.customer_id
                   and b.check_id = c.check_id
                   and c.invoice_id is not null
                   and b.check_date <= '30-JUN-2012'
                 group by c.invoice_id) d
         where a.invoice_id = d.invoice_id(+)
           and a.invoice_date <= '30-JUN-2012'
           and a.recv_gl_acct_id = '1210'
           and (a.status <> 'X' or
               (a.status = 'X' and a.void_date > '30-JUN-2012'))
           and a.customer_id != '1000') z
 where z.tp <> 0
 order by z.customer_id, z.invoice_date, z.invoice_id

Since Oracle does not have a parameterized view like create or replace view test(abc date) as blah blah... I opted to use a pipeline function below:

create or replace type aged_receivable_by_date as object
 (CUSTOMER_ID  varchar2(50),
  INVOICE_ID  varchar2(50),
  INVOICE_DATE  date,
  NATIVE_AMOUNT  number(20,2),
  CURRENT_30  number(20,2),
  OVER_30  number(20,2),
  OVER_60  number(20,2),
  OVER_90  number(20,2),
  OVER_120  number(20,2),
  TOTAL_AMOUNT  number(20,2)
 );
/
create or replace type aged_receivable_by_date_table as table of aged_receivable_by_date;
/
create or replace function arbd(ad date) return aged_receivable_by_date_table pipelined
 as  
   Cursor c1(a_date date) is
   (select z.customer_id,
       z.invoice_id,
       z.invoice_date,
       z.native_amount,
       case when z.aged_date <= 30 then
            z.tp
       else
           0
       end CURRENT_30,
       case when z.aged_date between 31 and 60 then
            z.tp
       else
           0
       end OVER_30,
       case when z.aged_date between 61 and 90 then
            z.tp
       else
           0
       end OVER_60,
       case when z.aged_date between 91 and 120 then
            z.tp
       else
           0
       end OVER_90,
       case when z.aged_date  >= 121 then
            z.tp
       else
           0
       end OVER_120,
       z.tp as TOTAL_AMOUNT
  from (select a.customer_id,
               a.invoice_id,
               a.total_amount as native_amount,
               a.invoice_date,
               case
                 when a.zero_date <= a_date then
                  a.total_amount - nvl(a.paid_amount, 0)
                 else
                  a.total_amount - nvl(d.checks_paid, 0)
               end as tp,
               round(a_date - a.invoice_date,2) as aged_date
          from receivable a,
               (select c.invoice_id, sum(c.amount) as checks_paid
                  from cash_receipt b, cash_receipt_line c
                 where b.customer_id = c.customer_id
                   and b.check_id = c.check_id
                   and c.invoice_id is not null
                   and b.check_date <= a_date
                 group by c.invoice_id) d
         where a.invoice_id = d.invoice_id(+)
           and a.invoice_date <= a_date
           and a.recv_gl_acct_id = '1210'
           and (a.status <> 'X' or
               (a.status = 'X' and a.void_date > a_date))
           and a.customer_id != '1000') z
 where z.tp <> 0
 );
 l_rec c1%rowtype;
   begin
   open c1(ad);
   loop
       fetch c1 into l_rec;
       exit when (c1%notfound);
       pipe row(aged_receivable_by_date(l_rec.CUSTOMER_ID,
                                        l_rec.INVOICE_ID,
                                        l_rec.INVOICE_DATE,
                                        l_rec.NATIVE_AMOUNT,
                                        l_rec.CURRENT_30,
                                        l_rec.OVER_30,
                                        l_rec.OVER_60,
                                        l_rec.OVER_90,
                                        l_rec.OVER_120,
                                        l_rec.TOTAL_AMOUNT));
   end loop;
   close c1;
   return;
  end;
/

Hence the code before:

SELECT * FROM TABLE(ARBD(TO_DATE(?,'DD-MON-YYYY')))

Which put me right back into the table can't be displayed graphically so I can't use parameters. Right now I skipped the wizards and msquery and just edit the connection and command properties. That seems to allow me to put the SQL in directly and give them an avenue to change the date by editing the properties. This seems like it might cause issues down the road but I am willing to accept it if I can't get Excel to prompt for the date due to the graphical limitation of using parameters.

Hopefully this will help illustrate the issue.

Cassidy

 

[SkipVought]

Yes, an that is why I almost always use ADO for parameterized queries, unless I am doing local queries, in which case I'll simply substitute into the SQL in my VBA and then run the MS Query via VBA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}