Home » Developer & Programmer » Reports & Discoverer » Want to refer the column
Want to refer the column [message #258053] Fri, 10 August 2007 00:13 Go to next message
aditya_gangadharam
Messages: 43
Registered: February 2005
Location: Hyderabad
Member

Hi all,

I want to refer the column in one query in data model as a parameter in the other query.

Regards,
aditya.
Re: Want to refer the column [message #258068 is a reply to message #258053] Fri, 10 August 2007 01:20 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Doesn't that mean that you should, actually, LINK those two queries by the same column?

If not, well, columns are referenced by colon sign in front of them:
WHERE empno = :q1_empno
Did you try something like that? How does it behave?
Re: Want to refer the column [message #258070 is a reply to message #258053] Fri, 10 August 2007 01:24 Go to previous messageGo to next message
aditya_gangadharam
Messages: 43
Registered: February 2005
Location: Hyderabad
Member

dear littlefoot,

This is not happening with the query

regards,
aditya.
Re: Want to refer the column [message #258130 is a reply to message #258070] Fri, 10 August 2007 06:19 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, but - what is not happening?

Establishing a data link between queries is trivial.

As of my second suggestion, I created a sample report based on Scott's schema - I used a column from the first query in WHERE clause of another query. Report compiles and executes. So, it IS possible.
REM query 1
select deptno q1_deptno, dname, loc 
from dept
where loc = 'CHICAGO'

REM query 2
select deptno, ename, job, sal
from emp
where deptno = :q1_deptno

Now, could you spend some more time and effort to explain what you, actually, want to accomplish? Total of two sentences in two posts isn't very descriptive.
Re: Want to refer the column [message #258330 is a reply to message #258053] Sat, 11 August 2007 01:20 Go to previous messageGo to next message
aditya_gangadharam
Messages: 43
Registered: February 2005
Location: Hyderabad
Member

Dear LittleFoot,

Dont get angry. I cant make a data link between two queries.

My doubt is that do my second query execute for each row of my first query.


regards,
aditya.
Re: Want to refer the column [message #258341 is a reply to message #258330] Sat, 11 August 2007 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I am not angry. It is the biometeorological situation that drives me crazy. Sorry.

If you can not link those queries, I'm afraid you can not make second query fetch data for every record returned in the first query. As you have seen, using a column as a parameter is possible, but it would make query_2 to return records which are in relation ONLY to the last query_1 returned record.

For example, if query_1 returned DEPTNOs 10 and 20, query_2 would return employees who work in DEPTNO = 20.

The next suggestion might be joining two queries into a single one (referring to my last example):
REM query_3 = query_1 + query_2

select e.deptno, e.ename, e.job, e.sal
from emp e, dept d
where e.deptno = d.deptno
  and d.loc = 'CHICAGO'

Currently, I can't suggest any other option.
Re: Want to refer the column [message #258345 is a reply to message #258053] Sat, 11 August 2007 03:56 Go to previous messageGo to next message
aditya_gangadharam
Messages: 43
Registered: February 2005
Location: Hyderabad
Member

Dear LittleFoot,

Thanks for the help and the thing is that i am not finding the solution to the report which has lot many queries which are linked in the sameway what i had earlier. so i heard from one of my friends that my problem may solve with the ref cursor query. but i or he also dont know how to use the ref cursor or how to add the developed package as attach library. can you please help me out in this.


i need a document how to create the program units in attached library and what has to be done.

regards,
aditya.
Re: Want to refer the column [message #259284 is a reply to message #258345] Tue, 14 August 2007 23:09 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Ref cursor is nothing but parameterized cursor. If you the concept of cursor then it will not take much time for you to understand the Ref cursor. You want to pass the parameter in the query itself.

Ashu
Previous Topic: LOV not appearing in parameter form
Next Topic: Create Oracle Report Server
Goto Forum:
  


Current Time: Mon Dec 05 19:18:07 CST 2016

Total time taken to generate the page: 0.04950 seconds