Home » Developer & Programmer » Reports & Discoverer » problem in creating the view in formula column
problem in creating the view in formula column [message #264470] Mon, 03 September 2007 08:19 Go to next message
geetika
Messages: 6
Registered: August 2007
Junior Member
hello
when i am creating views in formula column then its giving compile time error,would any1 please tell me why it is happening
code is:

create view abc as
select COUNT(sr.I_TRANSACTION_NO) into v_count, S.SHIPMENT_GID
from IE_SHIPMENTSTATUS ss, shipment s,IE_SHIPMENT_REFNUM sr
where ss.I_TRANSACTION_NO=sr.I_TRANSACTION_NO and sr.SHIPMENT_REFNUM_VALUE=s.shipment_gid and ss.STATUS_CODE_GID='SPL.ETA'
GROUP BY S.SHIPMENT_GID;

please help me,can we make views in formula column or not.
thanks
Re: problem in creating the view in formula column [message #264480 is a reply to message #264470] Mon, 03 September 2007 08:47 Go to previous messageGo to next message
lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France
Junior Member
Hi,

into v_count, S.SHIPMENT_GID ????? In a view???

Views can not do 'into' things.

Regards

Christian
Re: problem in creating the view in formula column [message #264484 is a reply to message #264470] Mon, 03 September 2007 09:03 Go to previous messageGo to next message
geetika
Messages: 6
Registered: August 2007
Junior Member
thanks,
but as per my requirement i need some value to be returned from the view,
actually i want to take the average of shipments having more than
one transactions over the total no of shipments of a country.
so i made the following query in the formula column



function CF_2Formula return Number is
v_count number(10);
v_num number(10);
v_den number(10);
v_avg number(10);
begin

create view abc as
select count(sr.I_TRANSACTION_NO) into v_count,S.SHIPMENT_GID
from IE_SHIPMENTSTATUS ss, shipment s,IE_SHIPMENT_REFNUM sr
where ss.I_TRANSACTION_NO=sr.I_TRANSACTION_NO and sr.SHIPMENT_REFNUM_VALUE=s.shipment_gid and ss.STATUS_CODE_GID='SPL.ETA'
GROUP BY S.SHIPMENT_GID;

SELECT count(s.shipment_gid) into v_num from abc
where abc.location_gid=cc
group by l.location_gid;

select count(s.shipment_gid) into v_den
from shipment s, location l
where l.location_gid=s.source_location_gid;

v_avg=(v_num/v_den);
return v_avg;

end;



would you please tell me some other alternate way to solve this situation

regards
Re: problem in creating the view in formula column [message #264493 is a reply to message #264484] Mon, 03 September 2007 09:43 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ouch! You can not create views on-the-fly in PL/SQL! (OK, you could using EXECUTE IMMEDIATE, but I've never tried to do that in Report Builder's Formula column).

Workaround? Don't use a CREATE VIEW, but INLINE VIEW. Something like this:
SELECT COUNT (s.shipment_gid)
INTO v_num
  FROM (SELECT   COUNT (sr.i_transaction_no) cnt, s.shipment_gid
            FROM ie_shipmentstatus ss, shipment s, ie_shipment_refnum sr
           WHERE ss.i_transaction_no = sr.i_transaction_no
             AND sr.shipment_refnum_value = s.shipment_gid
             AND ss.status_code_gid = 'SPL.ETA'
        GROUP BY s.shipment_gid
       );

Simplified:
SELECT count(*) INTO variable
FROM (YOUR_CREATE_VIEW_CODE_HERE);
Re: problem in creating the view in formula column [message #264495 is a reply to message #264470] Mon, 03 September 2007 09:56 Go to previous messageGo to next message
geetika
Messages: 6
Registered: August 2007
Junior Member
thanks
but when i am using count of s.shipment_gid before from clause,its giving invalid identifier,please help
regards
geetika
Re: problem in creating the view in formula column [message #264568 is a reply to message #264495] Mon, 03 September 2007 14:34 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you read OraFAQ Forum Guide? No? Well, you should. You'll see that Oracle version (database and, in your case, Developer Suite) would be useful information.

It works quite well for me (10g), both SQL and PL/SQL solution:
SQL> get p
  1  select x.dpt, count(x.eno) num_of_emps
  2  from (select d.dname dpt, e.empno eno
  3        from dept d, emp e
  4        where d.deptno = e.deptno
  5       ) x
  6* group by x.dpt
SQL> /

DPT            NUM_OF_EMPS
-------------- -----------
ACCOUNTING               3
OPERATIONS               5
RESEARCH                 5
SALES                    1

SQL> edit p

SQL>
SQL> set serveroutput on
SQL> get p
  1  declare
  2    l_dpt dept.dname%type;
  3    l_num_of_emps number;
  4  begin
  5    select x.dpt, count(x.eno) num_of_emps
  6    into l_dpt, l_num_of_emps
  7    from (select d.dname dpt, e.empno eno
  8          from dept d, emp e
  9          where d.deptno = e.deptno
 10            and d.deptno = 10
 11         ) x
 12    group by x.dpt;
 13    dbms_output.put_line(l_dpt || ': ' || l_num_of_emps);
 14* end;
 15  /
ACCOUNTING: 3

PL/SQL procedure successfully completed.

SQL>

How does your code look like? Can you simplify it to Scott's schema, just to make it work (and later we'll make it fancy)?
Re: problem in creating the view in formula column [message #264701 is a reply to message #264470] Tue, 04 September 2007 04:34 Go to previous messageGo to next message
geetika
Messages: 6
Registered: August 2007
Junior Member
thanks
your suggestion was helpful,but now i am facing new problem,
i am not able to group by the required fields.

a rough idea of the tables and the situation i am explaning below


table<shipment>
:shipment_gid
:source_location_gid
:location_gid

table<IE_shipmentstatus>
:status_code
:ITransaction_no

table<location>
:location_gid

table<IE_shipmentrefnum>
:shipment_gid
: ITransaction_no




Quote:

now i want count(ITranaction_no) 'cnt' on the basis of shipment_gid and futher counting the shipment_gid having
'cnt > 1' on the basis of location_gid.
for this i used the following query,but its giving errors while running the report.



SELECT COUNT(x.shipment_gid) INTO v_num
FROM location l,(SELECT COUNT(sr.i_transaction_no)cnt, s.shipment_gid
             FROM ie_shipmentstatus ss, shipment s, ie_shipment_refnum sr
             WHERE ss.i_transaction_no = sr.i_transaction_no
                   AND sr.shipment_refnum_value = s.shipment_gid
                   AND ss.status_code_gid ='SPL.ETA' 
                   

                   group by s.shipment_gid
             )x
where x.cnt>1
GROUP BY l.location_gid;



Quote:

if i use location inside the view,error come out for multiple grouping
please help
Thanks and Regards

Re: problem in creating the view in formula column [message #264710 is a reply to message #264701] Tue, 04 September 2007 05:03 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"SELECT something INTO variable FROM ..." expects one and only one value as a result. When you groupped the result set by 'location_gid', you got more than one result which returned an error.

What you could do is:
  • create a real view (while connected to the database via SQL*Plus, for example) and use this view in a query
  • modify query to rearrange tables (move 'location' into an inline view and include additional condition(s) into the WHERE clause. It would, probably, restrict returned record set to only one value.

Something like this (if applicable):
SELECT COUNT(x.shipment_gid) 
INTO v_num
FROM
    (SELECT COUNT(sr.i_transaction_no) cnt, s.shipment_gid, l.location_gid
     FROM ie_shipmentstatus ss, shipment s, ie_shipment_refnum sr,
           location l
     WHERE ss.i_transaction_no = sr.i_transaction_no
       AND sr.shipment_refnum_value = s.shipment_gid
       AND ss.status_code_gid ='SPL.ETA' 
       AND s.location_gid = l.location_gid
     GROUP BY s.shipment_gid, l.location_gid
    ) x
WHERE x.cnt > 1
  AND x.location_gid = :location_gid
  AND x.shipment_gid = :shipment_gid;
Previous Topic: Group per page
Next Topic: BI/XML Publisher - Stored Proc with REF CURSOR out parameter
Goto Forum:
  


Current Time: Thu Dec 08 10:28:43 CST 2016

Total time taken to generate the page: 0.10064 seconds