Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

subquery in From Clause

Status
Not open for further replies.

goodmans

MIS
Apr 23, 2008
63
GB
Hi DB2 Gurus,

I have a situation where i have to use the subquery in the from clause. But db2 is failing to execute the query. Below query working fine in oracle but its failing in DB2 v9.

Dont know if its a known issue. please advise.

SELECT
dim.code, fact.amt
FROM
dim INNER JOIN fact ON
(dim.code=fact.dim_code and
fact.type_id = (select max(type_ID) from type where type_name ='test'))

Dont ask me to put it in the where clause. Since the query generation is not in my hand its being populated automatically by a tool we are using.

Regards
G
 


hi,

Does this query run in DB2?
Code:
select [b]max[/b](type_ID) from type where type_name ='test'
I'm not that familiar with DB2 sql functions.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Yes it does.

Its a simple query like select max(sal) from emp where ename like 'test%'

Regards
G
 
If i put the same subquery in the where clause it works.
 



Is your Type.Type_ID the SAME data type as Fact.Tupe_ID?

Also since the Fact.Type_ID criteria is NOT part of the inner join, might this not be better...
Code:
FROM
  dim INNER JOIN fact ON 
  (dim.code=fact.dim_code)

Where fact.type_id = (select max(type_ID) from type where type_name ='test')


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Code:
SELECT  dim.code, fact.amt
FROM  
dim 
INNER JOIN 
fact 
ON   dim.code=fact.dim_code 
inner join
(select max(type_ID) as maxtypeid from type where type_name ='test') temp
on
fact.type_id = temp.maxtypeid



Ties Blom

 
There should be data type mismatch. Please share the SQLCODE for abend.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top