Issue quering Oracle DB from ColdFusion

[bamboo]

I'm trying to do a simple query in ColdFusion as follows but am not getting any results?? I can do this query directly in Oracle SQL Developer and the results are fine. However when I put the query in coldfusion it's not working? No error, no records returned? I also recreated these tables in SQL Server and used this same query in coldfusion and it works like a gem.

<cfquery datasource="test" name="qTest">
SELECT EmployeeID, FirstName, LastName
FROM EmployeeRefresh r JOIN EmployeeInfo i ON r.EmployeeID = i.Employee_ID
WHERE TRIM(r.building) <> TRIM(i.building)
<cfquery>

<cfoutput query="qTest">
#EmployeeID# - #FirstName#, #LastName#
</cfoutput>

Just some background, that you'll notice there is EmployeeID and Employee_ID. Unfortunately these are how the tables are setup that I inherited so I'm working with what I have. Basically the EmployeeRefresh table gets updated nightly from our employee data warehouse. Then I want to check and return all employees who have updated their building information.

 

[FALCONSEYE]

oracle is case sensitive and sql is not.
WHERE TRIM(UPPER(r.building)) <> TRIM(UPPER(i.building))

try this and see if it works...

http://www.fuzzysiberians.com

 

[bamboo]

Thanks much. I will try this. I also found it it was part user error. In Oracle SQL Developer it was not saving my update to the test record via my SQL statement thus the buildings actually were the same. I found I had to update the column manually, then click the 'commit' button to actually have the change save. Lesson learned.

 

[FALCONSEYE]

oh yes, the "commit"...

http://www.fuzzysiberians.com