subquery in From Clause

[goodmans]

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

 

[SkipVought]

hi,

Does this query run in DB2?

select [b]max[/b](type_ID) from type where type_name ='test'

I'm not that familiar with DB2 sql functions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[goodmans]

Yes it does.

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

Regards
G

 

[goodmans]

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

 

[SkipVought]

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...

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[blom0344]

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

 

[delta403]

There should be data type mismatch. Please share the SQLCODE for abend.