2. ASPRunnerPro General questions
  3. New Version 5 Inner Join error
This topic is locked

New Version 5 Inner Join error

 		5/3/2007 10:26:24 AM
ASPRunnerPro General questions
S
 stealthwifi author

Hello,
I jsut started palying with the new version (love the built in templats saved me TONS of time with color changes and all) and working with the reports feature.
I am trying to make a report with a inner join in the SQL query and it keeps giving me an ambigious error and stating that the filed already exists. Well of course it allready exists it's the primary key. the statement i am using is this:
select * from Policy inner join Claims on Policy.PolicyID=Cliams.PolicyID
I have these exact same 2 tables set for master detail relationship in a seperate project and it works perfetly, is this a error with version 5 reports or am i totaly screwing up the way asp reads inner join statements?
Cheers,

Stealth-
Sergey Kornilov admin 5/3/2007

I need more info on this. Post exact error message and both tables structure here.
S
 		stealthwifi author 5/8/2007

I need more info on this. Post exact error message and both tables structure here.


There are 2 tables, one called Claims, the other Policy. Policy has PolicyID as the primary key and Claims has ClaimID as the primary and PolicyID as the foreign key. They are joined tables in SQL. My statement is SELECT FROM Claims inner join Policy on Claims.PolicyID=Policy.PolicyID

(I have tried multiple ways of formating the join statement, once it went through but then it never really joined the tables correctly after the build.)
The error i receive back is:
Cannot retreive columns information. Please modify SQL query and try again.

Error message:

[Microsfot][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'PolicyID'.
Also with diffrent foramting like instead of listing each name after the SELECT statement I get ther error:
Your SQL query contains the following duplicate field names:

PolicyID

All fields in the query must have unique names or aliases

Correct your SQL query or uncheck 'Deit SQL query manually' box.
Thanks for your help!

Stealth-
I use these exact same 2 tables with a master detail relationship in the same and previous projects and it works perfectly, not sure why this is not working.
Sergey Kornilov admin 5/8/2007

You need to fix SQL query providing unique names or aliases to all fields that exist in both tables.

I would recommend to avoid "select *" queries at all costs.

select Policy.*,

Claims.Field1 as ClaimsField1,

Claims.Field2 as ClaimsField2,

...

from Policy inner join Claims on Policy.PolicyID=Cliams.PolicyID
S
 		stealthwifi author 5/9/2007

You need to fix SQL query providing unique names or aliases to all fields that exist in both tables.

I would recommend to avoid "select *" queries at all costs.

select Policy.*,

Claims.Field1 as ClaimsField1,

Claims.Field2 as ClaimsField2,

...

from Policy inner join Claims on Policy.PolicyID=Cliams.PolicyID


Exelent thank you that worked perfectly!

I avoidided using the "Select *" and just typed in each one it made the join perfect.
Cheers-

Steatlh