OraFAQ Forum: Reports & Discoverer » Want to refer the column

Home » Developer & Programmer » Reports & Discoverer » Want to refer the column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Want to refer the column [message #258053]

Fri, 10 August 2007 00:13

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.

Report message to a moderator

Re: Want to refer the column [message #258068 is a reply to message #258053]

Fri, 10 August 2007 01:20

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: Want to refer the column [message #258070 is a reply to message #258053]

Fri, 10 August 2007 01:24

aditya_gangadharam
Messages: 43
Registered: February 2005
Location: Hyderabad

Member

dear littlefoot,

This is not happening with the query

regards,
aditya.

Report message to a moderator

Re: Want to refer the column [message #258130 is a reply to message #258070]

Fri, 10 August 2007 06:19

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Want to refer the column [message #258330 is a reply to message #258053]

Sat, 11 August 2007 01:20

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.

Report message to a moderator

Re: Want to refer the column [message #258341 is a reply to message #258330]

Sat, 11 August 2007 03:24

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Want to refer the column [message #258345 is a reply to message #258053]

Sat, 11 August 2007 03:56

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.

Report message to a moderator

Re: Want to refer the column [message #259284 is a reply to message #258345]

Tue, 14 August 2007 23:09

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 18:38:03 CST 2024