[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Weird problem with SQL statement

Hi, I've got a rather complex query that gives me an error when trying to run from the cold fusion (MX, Oracle 9i) environment but if I simply cut and past the query from the error page into the SQL Worksheet Oracle returns the result set and does not give an error...what gives?


This is the error message, pasting the SQL below with no edits works fine in SQL worksheet...very strange... Oh and the WHERE 1 = 1 is because there can be dynamic AND clauses appended that act as filters when this page is viewed and putting 1 = 1 is a clean way for me to append dynamic AND clauses without lots of ugly logic..

[MERANT][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended
The Error Occurred in C:\Inetpub\wwwroot\FACQUAL_MX\2000a.cfm: line 64
 
62 :       </cfif>
63 :       ORDER BY b.PROJECT_NAME, a.SURVEY_ID
64 : </cfquery>
65 :


 

--------------------------------------------------------------------------------
 
SQL    SELECT b.*,a.*,g.SURVEY_TYPE_NAME, i.WORKORDER_NUMBER FROM EPM_PROJECTS_VALUE_V b LEFT OUTER JOIN FACQUAL_SURVEYS a ON a.PROJECT_ID = b.PROJECT_ID AND a.SURVEY_STATE <> 'CLOSED' AND a.SURVEY_CREATOR = 0 LEFT OUTER JOIN EPM_PROJECTS i ON b.PROJECT_ID = i.PROJECT_ID LEFT OUTER JOIN FACQUAL_SURVEY_TYPES g ON a.SURVEY_TYPE_ID = g.SURVEY_TYPE_ID INNER JOIN EPM_TEAM_MEMBERS_VALUE_V k ON b.PROJECT_ID = k.PROJECT_ID AND k.TEAM_MEMBER_ID = 0 WHERE 1 = 1 ORDER BY b.PROJECT_NAME, a.SURVEY_ID  
DATASOURCE   FACQUAL
VENDORERRORCODE   933
SQLSTATE   HY000
0
rdytmire
Asked:
rdytmire
  • 2
2 Solutions
 
kkhippleCommented:
could you include the everything...which means the CFQUERY tags also.. coz it seems like within the query you have <CFIF>  logic conditions...


0
 
pinaldaveCommented:
kkhipple is correct...also BY b.PROJECT_NAME, a.SURVEY_ID either of them could be null also.
There can be problem in your cfif before .
Regards,
---Pinal
0
 
rdytmireAuthor Commented:
thanks but I fixed this.  I reverted to an older working copy, put the changes back in manually and everything works fine.  Perhaps CF thought I had a control character somewhere...very strange...
0
 
pinaldaveCommented:
Hi rdytmire,
well glad it worked out... your title was correct "Weird" Smiles

Regards,
---Pinal
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now