Need help with my SQL code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with my SQL code

Author

Topic

MN Grp
Starting Member

3 Posts

Posted - 2010-01-22 : 11:52:24

I am using microsoft query to run SQL against some DB2 tables. I keep getting a 'AQL0206N' error. My Query is:

SELECT all*

From Grp.table1 H
inner join (SELECT CTR_I, POL_CTR_I, max(CER_PRM_EFF_D) as cer_prm_eff_d
FROM GRP.Table2
WHERE CLN_CTR_RLT_TYP_C = '03'
GROUP BY CTR_I, POL_CTR_I) as I

on i.ctr_i = h.ctr_I
and i.pol_ctr_I = h.Pol_ctr_i
and h.CER_PRM_EFF_D = I.cer_prm_eff_d

The error message refers to I.cer_prm_eff_d is not valid.

Can some one help me with my code?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-01-22 : 12:01:35

change all * => * only

would there be a data type matching problem on the cer_prm_eff_d ?

what does
select * from the I
return

and can you do slect * from I
where i.cer_prm_eff_d = =varx

i.e. exclude the join statement as the source of your problem.

MN Grp
Starting Member

3 Posts

Posted - 2010-01-22 : 12:27:51

quote:
Originally posted by AndrewMurphy

change all * => * only

would there be a data type matching problem on the cer_prm_eff_d ?

what does
select * from the I
return

and can you do slect * from I
where i.cer_prm_eff_d = =varx

i.e. exclude the join statement as the source of your problem.

I get the same error with select * (instead of select all*). "SQL0206N 'I.CER_PRM_EFF_D' is not valid in the context where it is used.

There can not be a data type problem as table1 and table 2 are the same table. Doing a select * from 'I' would return a list of contract #'s (unique identifier) and the most recent date. By matching 'I' back to 'H' I get only the most recent(max eff_d) Contract row.

I'm new to SQL and microsoft query. I ask because I can run the exact same query in another tool.

MN Grp
Starting Member

3 Posts

Posted - 2010-01-22 : 14:20:12

I guess my question is why can't I use a sub query in the join?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-01-25 : 06:58:44

"I guess my question is why can't I use a sub query in the join? " you can.

My question re "select * from I-derived-table" is not to see what should come back from there...but to see what DOES come back. please advise - the contents might be unexpected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources