Help Please - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help Please

Author

Topic

QJR
Starting Member

4 Posts

Posted - 2006-01-18 : 09:30:12

Long story short just got a huge project dumped onto me and i have never coded SQL. Got a couple of the queries done but got to this one and dont know what to do . any help would be greatly appreciated.

CODE
SELECT ProjectSub.PS_ID, ProjectSub.PS_Name, ProjectSub_Proposal.PSP_ProposalDate, ProjectSub.[PS_#Lots], ProjectSub.PS_SiteArea, ProjectSub.PS_SiteAreaUnits, ProjectSub_Work.PSWk_WOC_ID, ProjectSub_Work.PSWk_Cost, ProjectSub_ProposalDetails.PSP_TaskCode, ProjectSub_ProposalDetails.PSPD_Amount, ProjectSub_ProposalDetails.PSPD_Rate, [Forms]![Switchboard]![Surveying_Reports].[Form]![Type21] AS Expr1, ProjectSub.PS_SFD, ProjectSub.PS_SFA, ProjectSub.PS_MFRental, ProjectSub.PS_MFCondo, ProjectSub.PS_ResOther, ProjectSub.PS_Industrial, ProjectSub.PS_Retail, ProjectSub.PS_Office, ProjectSub.PS_CommOther

FROM ((ProjectSub INNER JOIN ProjectSub_Proposal ON ProjectSub.PS_ID = ProjectSub_Proposal.PSP_PS_ID) INNER JOIN ProjectSub_Work ON ProjectSub.PS_ID = ProjectSub_Work.PSWk_PS_ID) INNER JOIN ProjectSub_ProposalDetails ON ProjectSub_Proposal.PSP_ID = ProjectSub_ProposalDetails.PSPD_PSP_ID

WHERE (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="SFD") AND ((ProjectSub.PS_SFD)=Yes) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="SFA") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=Yes) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="MFRental") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=Yes) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="MFCondo") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=Yes) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="ResOther") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=Yes) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Industrial") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=Yes) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Retail") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=Yes) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Office") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=Yes) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="CommOther") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=Yes)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="All"))
ORDER BY ProjectSub.PS_ID, ProjectSub.PS_Name;

errors i get from query analyzer

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '!'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-01-18 : 15:13:20

This code comes from Access and can't just be placed in and tested in Query Analyzer.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-18 : 19:55:46

Wow ! Can you reformat the query in more readable form ? I don't think anybody can read the code in such manner.

>> Line 1: Incorrect syntax near '!'.
Replace ! with .

-----------------
'KH'

QJR
Starting Member

4 Posts

Posted - 2006-01-19 : 08:52:19

quote:
Originally posted by druer

This code comes from Access and can't just be placed in and tested in Query Analyzer.

that's what ive been doing with the other queries. i choose sql in access and then paste in to the analyzer and parse. Its worked for a good chunk of them.

QJR
Starting Member

4 Posts

Posted - 2006-01-19 : 08:53:00

quote:
Originally posted by khtan

Wow ! Can you reformat the query in more readable form ? I don't think anybody can read the code in such manner.

>> Line 1: Incorrect syntax near '!'.
Replace ! with .

-----------------
'KH'

Sorry i changed it i hope thats a little better.

szgldt
Starting Member

10 Posts

Posted - 2006-01-19 : 09:41:44

druer is correct. This query can not be parsed in Query Analyser because it comes from Access.

In the SELECT Clause you are attempting to reference a form that exists in Access but can not exist in SQL Server ([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21] ). Forms are also referenced in the WHERE clause.

Also in the WHERE clause you have statements with double quotes:

ProjectSub_ProposalDetails.PSP_TaskCode ="21"

The Query Analyser equivalent would be:

ProjectSub_ProposalDetails.PSP_TaskCode ='21'

Some of your more simple Access Queries may have worked in SQL Query Analyser but this query will not.

QJR
Starting Member

4 Posts

Posted - 2006-01-19 : 13:38:45

quote:
Originally posted by szgldt

druer is correct. This query can not be parsed in Query Analyser because it comes from Access.

In the SELECT Clause you are attempting to reference a form that exists in Access but can not exist in SQL Server ([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21] ). Forms are also referenced in the WHERE clause.

Also in the WHERE clause you have statements with double quotes:

ProjectSub_ProposalDetails.PSP_TaskCode ="21"

The Query Analyser equivalent would be:

ProjectSub_ProposalDetails.PSP_TaskCode ='21'

Some of your more simple Access Queries may have worked in SQL Query Analyser but this query will not.

ok, thanks. i understand now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources