problem in creating the view in formula column [message #264470] |
Mon, 03 September 2007 08:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #264484 is a reply to message #264470] |
Mon, 03 September 2007 09:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #264568 is a reply to message #264495] |
Mon, 03 September 2007 14:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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;
|
|
|