INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

subquery in From Clause

subquery in From Clause

(OP)
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

RE: subquery in From Clause



hi,

Does this query run in DB2?

CODE

select max(type_ID) from type where type_name ='test'
I'm not that familiar with DB2 sql functions.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: subquery in From Clause

(OP)
Yes it does.

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

Regards
G

RE: subquery in From Clause

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

RE: subquery in From Clause




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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: subquery in From Clause

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
 
 

RE: subquery in From Clause

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close