•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 391,559 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,743 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser:
Views: 2222 | Replies: 5 | Solved
 |
•
•
Join Date: Aug 2007
Posts: 3
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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!
•
•
Join Date: Aug 2007
Posts: 1
Reputation:
Rep Power: 0
Solved Threads: 1
Newbie Poster
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.
•
•
Join Date: Aug 2007
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,164
Reputation:
Rep Power: 7
Solved Threads: 58
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.
•
•
Join Date: Aug 2007
Posts: 3
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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?
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,164
Reputation:
Rep Power: 7
Solved Threads: 58
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.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Database Design Marketplace
Linear Mode
- 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
