By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,883 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,883 IT Pros & Developers. It's quick & easy.

select-where anomaly

P: 25
I am apparently not qualified to drive this car. I am running Debian Linux 2.6.8-2-686, PHP4, and PostGreSQL. I have been working on this project for almost 15 years. Recently I converted my trade-secret database from C-ISAM to PGSQL using flat files, and it apparently worked.

My master file has only one field as its key - project number - up to 10 characters, alphanumeric. The detail files - tables - use that number as the primary key part, further classified by size, type etc (this is a construction estimating system).

I've got 3 test projects entered, and when I do "select * from tblproj" it retrieves all 3 and everything looks fine. If I do "select * from tblproj where proj=1999" it successfully finds that project. However, if I do "select * from tblproj where proj=test2" (or test3) I get :ERROR: Column test2 does not exist." (or test3).

If I try to select a project from one of the detail files, the same problem exists. I thought it might be a second-or-subsequent problem, indicating data corruption. However, it will find ALL detail records for project 1999, yet none for test2 or test3, giving the same error.

This happens both within the database, and if called from the browser.

Does anybody have any clues or suggestions? I would very much appreciate it, as this get-rich scheme has only been consuming time and resources for nigh on to 15 years!! - Rhys rj@borked.net
Nov 4 '06 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 534
From what you posted I suppose the proj column is defined as varchar.
So when you run query "... from tblproj where proj=1999", first the sql is successfully parsed because the assumption is made that you simply giving a numeric value, which later is implicitely converted into a char.
When your query has "... where proj=test2" it (correctly) errors-out, because there's no such column as test2.

Try to always include your varchar key in single quotes:

"select * from tblproj where proj = 'test2' "
"select * from tblproj where proj = '1999' "

(Spaces around the equal sign are optional, but good for clarity)
Nov 5 '06 #2

P: 25
From what you posted I suppose the proj column is defined as varchar.
So when you run query "... from tblproj where proj=1999", first the sql is successfully parsed because the assumption is made that you simply giving a numeric value, which later is implicitely converted into a char.
When your query has "... where proj=test2" it (correctly) errors-out, because there's no such column as test2.

Try to always include your varchar key in single quotes:

"select * from tblproj where proj = 'test2' "
"select * from tblproj where proj = '1999' "

(Spaces around the equal sign are optional, but good for clarity)
You Da Man!! I had tried double quote, to no avail. Single quote did the trick. I've got some experimentation upcoming before I post my next poser, relating to updating existing rows, display and input.

Thx again!! Can I buy you a beer?
Nov 5 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.