Forum Stats

  • 3,853,602 Users
  • 2,264,244 Discussions
  • 7,905,405 Comments

Discussions

PL/SQL procedure for a crystal report

737735
737735 Member Posts: 3
edited Dec 1, 2009 5:28AM in SQL & PL/SQL
Dear All,

I am trying to use the following procedure as a datasource for my crystal report. The query works as I expected but the problem is I can't figure out how to fetch back the data from those dummy tables - IFS_PR_DUMMY_TAB and IFS_PR_DUMMY2_TAB

CREATE OR REPLACE procedure dummy9_IFS_FR2_Sales (cdate IN date)

as

acontract customer_order.contract%type;
ashowroom customer_order.district_code%type;
aorderno customer_order.order_no%type;
amount number(10);

bcontract customer_order.contract%type;
bshowroom customer_order.district_code%type;
borderno customer_order.order_no%type;
bamount number(10);

CURSOR c2 IS
select contract, district_code ,count(order_no),
SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no))
from CUSTOMER_ORDER
where order_no IN (select distinct order_no from customer_order_line where state IN ('Released') ) AND state IN ('Released') and to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')
and contract IN
*('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')*
group by contract,district_code, date_entered ;

CURSOR c2 IS
select contract, district_code ,count(order_no),
SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no))
from CUSTOMER_ORDER
where order_no IN (select distinct order_no from customer_order_line where state IN ('Reserved') ) AND state IN ('Reserved') and to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')
and contract IN
*('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')*
group by contract,district_code, date_entered ;

begin

--For Released Orders*

OPEN c1;

DELETE FROM IFS_PR_DUMMY_TAB;

loop
fetch c1 into acontract, ashowroom, aorderno, amount;
exit when c1%notfound;

Insert into IFS_PR_DUMMY_TAB
*(DCONTRACT ,DSHOWROOM ,DORDERNO,DAMOUNT) values (acontract,ashowroom,aorderno,amount);*

end loop;
close c1;

--For Reserved Orders*

OPEN c2;

DELETE FROM IFS_PR_DUMMY2_TAB;

loop

fetch c2 into bcontract, bshowroom, borderno, bamount;
exit when c2%notfound;

Insert into IFS_PR_DUMMY2_TAB
*(ECONTRACT ,ESHOWROOM ,EORDERNO,EAMOUNT) values (bcontract,bshowroom,borderno,bamount);*

end loop;
close c2;

end;


Please advise.

Regards, User
Tagged:

Answers

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    I don't know nothing about crystal report but isn't the following good enough as a datasource?
    select contract, 
           district_code ,
           count(order_no),
           SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no)),
           state
      from CUSTOMER_ORDER
     where order_no IN (select distinct order_no 
                          from customer_order_line 
                         where state IN ('Released','Reserved') 
                       ) 
       AND state IN ('Released','Reserved') 
       and to_char(date_entered,'MM/DD/YYYY') >= to_char(:cdate,'MM/DD/YYYY')
       and contract IN ('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
     group by contract,district_code, date_entered
    Regards

    Etbin
  • 737735
    737735 Member Posts: 3
    I used explicit cursors for different states because the format of the report is such that I need to differentiate.

    I just need to know how I could join the two tables, so there are no duplicate rows fetched on crystal report?
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    just guessing
    select nvl(rel_d.contract,res_d.contract) contract,
           nvl(rel_d.district_code,res_d.district_code) district_code,
           rel_d.the_count count_released,
           rel_d.total_price total_price_released,
           res_d.the_count count_reserved,
           res_d.total_price total_price_reserved
      from (select contract, 
                   district_code ,
                   count(order_no) the_count,
                   SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no)) total_price
              from CUSTOMER_ORDER
             where order_no IN (select distinct order_no 
                                  from customer_order_line 
                                 where state IN ('Released') 
                               ) 
               AND state IN ('Released') 
               and to_char(date_entered,'MM/DD/YYYY') >= to_char(:cdate,'MM/DD/YYYY')
               and contract IN ('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
             group by contract,district_code, date_entered
           ) rel_d
           full outer join
           (select contract, 
                   district_code ,
                   count(order_no) the_count,
                   SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no)) total_price
              from CUSTOMER_ORDER
             where order_no IN (select distinct order_no 
                                  from customer_order_line 
                                 where state IN ('Reserved') 
                               ) 
               AND state IN ('Reserved') 
               and to_char(date_entered,'MM/DD/YYYY') >= to_char(:cdate,'MM/DD/YYYY')
               and contract IN ('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
             group by contract,district_code, date_entered
           ) res_d
           on rel_d.contract = res_d.contract
          and rel_d.district_code = res_d.district_code
    Regards

    Etbin
  • 737735
    737735 Member Posts: 3
    I have tried using full outer join the way you suggested, but it throws me ORA-00918. I am on Oracle 9i and yes I used your query and got the same :(

    Please advise.
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,877 Red Diamond
    So...?

    The error is quite explicit and obvious as to what is wromg:
    00918, 00000, "column ambiguously defined"
    This means that the SQL refers to a column name that happens to be in more than one of the referenced objects (tables, views. etc). Thus the parser does not know which object's column value to use.

    For example, if column FOO exists in both tables TAB1 and TAB2, you need to explicitly reference the column using full scope. E.g.
    select tab1.foo from tab1, tab2 where tab1.id = tab2.id
    You can also alias objects, and use the alias as the explicit scope reference, e.g.
    select t1.foo from tab1 t1, tab2 t2 where t1.id = t2.id
    I suggest that you brush up on your SQL skills. This also explains why your PL/SQL code you posted above is horribly wrong for performance and scalability - as the correct approach is to solve the problem first using SQL and then applying PL/SQL where SQL cannot do the job (like process flow control).

    You will find the SQL Reference Guide, and Application Fundamental guides for the Oracle version you are using at [http://tahiti.oracle.com|http://tahiti.oracle.com].
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    no ORA-00918 for me - just the api call was commented out
    with
    CUSTOMER_ORDER as
    (select 'OEM' contract,2 district_code,3 order_no,4 price,'Released' state,sysdate date_entered from dual
    ),
    CUSTOMER_ORDER_LINE as
    (select 3 order_no,'Released' state from dual
    )
    select nvl(rel_d.contract,res_d.contract) contract,
           nvl(rel_d.district_code,res_d.district_code) district_code,
           rel_d.the_count count_released,
           rel_d.total_price total_price_released,
           res_d.the_count count_reserved,
           res_d.total_price total_price_reserved
      from (select contract, 
                   district_code ,
                   count(order_no) the_count,
    --               SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no)) total_price
                   SUM(price) total_price
              from CUSTOMER_ORDER
             where order_no IN (select distinct order_no 
                                  from customer_order_line 
                                 where state IN ('Released') 
                               ) 
               AND state IN ('Released') 
               and to_char(date_entered,'MM/DD/YYYY') >= to_char(:cdate,'MM/DD/YYYY')
               and contract IN ('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
             group by contract,district_code, date_entered
           ) rel_d
           full outer join
           (select contract, 
                   district_code ,
                   count(order_no) the_count,
    --               SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no)) total_price
                   SUM(price) total_price
              from CUSTOMER_ORDER
             where order_no IN (select distinct order_no 
                                  from customer_order_line 
                                 where state IN ('Reserved') 
                               ) 
               AND state IN ('Reserved') 
               and to_char(date_entered,'MM/DD/YYYY') >= to_char(:cdate,'MM/DD/YYYY')
               and contract IN ('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
             group by contract,district_code, date_entered
           ) res_d
           on rel_d.contract = res_d.contract
          and rel_d.district_code = res_d.district_code
    Regards

    Etbin
This discussion has been closed.