SQL Query Analyzer Problem
Please support our Database Design advertiser: Programming Forums
Thread Solved
 |
SQL Query Analyzer Problem The program I use is housed in a SQL Database. What I am trying to do is term Members based on meeting 2 guidelines (PCP and Insurance). WHat I want to do is term a group of members that have the same PCP and Insurance plan. This data is broken up into 3 different databases - Pcphistory, Eligibilityhistory, and Benefitplans. I have been struggling, because I have never had to do this before. This is what I started...
When I run this Query in SA, I get the following error: Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.
This is running on SQL 2000... I am truly pulling my hair out, as there isn't anyone who uses the software that we do and the programmer bailed on us!
update eligibilityhistory set terminationdate='09/30/2007' from eligibilityhistory inner join benefitplans on eligibilityhistory.benefitplanid =benefitplans.benefitplanid where benefitplans.healthplanid='BCN' from eligibilityhistory inner join pcphistory on eligibilityhistory.memberid = pcphistory.memberid where pcphistory.providerid='1532' and terminationdate is null
When I run this Query in SA, I get the following error: Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.
This is running on SQL 2000... I am truly pulling my hair out, as there isn't anyone who uses the software that we do and the programmer bailed on us!
Try removing 'from eligibilityhistory' after 'BCN'. This is your main table so it is not necessary to restate what table the value is to be retreived from.
Depending on what app you are using to run your query, line numbers usually reflect the line after or before.
Depending on what app you are using to run your query, line numbers usually reflect the line after or before.
I did try and remove the 'from eligibilityhistory' and that didn't work. Then I get this error message: Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'inner'.
I am using SQL Query Analyzer for running queries. I examine my database structure with Enterprise Manager.
Incorrect syntax near the keyword 'inner'.
I am using SQL Query Analyzer for running queries. I examine my database structure with Enterprise Manager.
This is now syntactically correct, whether it does what you require I can only guess without DDL and expected results from you.
I recommend you run this first to see what is going to be updated before you actually run the update above.
update
eligibilityhistory
set
terminationdate='09/30/2007'
from
eligibilityhistory
inner join benefitplans
on eligibilityhistory.benefitplanid=benefitplans.benefitplanid
inner join pcphistory
on eligibilityhistory.memberid=pcphistory.memberid
where
benefitplans.healthplanid='BCN'
and pcphistory.providerid='1532'
and terminationdate is nullI recommend you run this first to see what is going to be updated before you actually run the update above.
Select
*
from
eligibilityhistory
inner join benefitplans
on eligibilityhistory.benefitplanid=benefitplans.benefitplanid
inner join pcphistory
on eligibilityhistory.memberid=pcphistory.memberid
where
benefitplans.healthplanid='BCN'
and pcphistory.providerid='1532'
and terminationdate is null Last edited by hollystyles : Aug 30th, 2007 at 8:18 am.
The syntax looks great. I am now down to getting one error:
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'terminationdate'.
I am not sure why it comes up Ambiguous, as the other queries I run (without the extra inner join) all work correctly. Any last ideas?
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'terminationdate'.
I am not sure why it comes up Ambiguous, as the other queries I run (without the extra inner join) all work correctly. Any last ideas?
This means terminationdate is a column name in more than one of the three tables in your SQL Query. You need to be Specific (not ambiguous) and prefix it with the table name and a period. Hint double click on the error message in the lower pane of Query Analyzer it will take you to the line that has the error.
You will need to do this in the 'where' clause. So if it's the terminationdate column in the pcphistory table that must be null put
You will need to do this in the 'where' clause. So if it's the terminationdate column in the pcphistory table that must be null put
pcphistory.terminationdate is null in the where clause. Last edited by hollystyles : Aug 30th, 2007 at 8:41 am.
|
Similar Threads
Other Threads in the Database Design Forum
- how to delete duplicate record in a table by using SQL query (MS SQL)
- sql query problem with MS Access and C# (C#)
- SQL query problem with WHERE clause (ASP)
- Accessing ADSI Query from ASP page (ASP)
- sql query updating problem (Visual Basic 4 / 5 / 6)
- Javascript array from sql query (JSP)
- Please help me out with MySQL query (MySQL)
- PHP/SQL query help (PHP)
- Retreiving variables from a sql query into a form (PHP)
Other Threads in the Database Design Forum
- Previous Thread: DB design for friends network
- Next Thread: Design help plz
•
•
•
•
Views: 3555 | Replies: 5 | Currently Viewing: 1 (0 members and 1 guests)
Linear Mode
