General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
SQL comparison positioning
First SQL:
SELECT Services.service, Ordr_svs.service_id, "2";
FROM chickbook!services INNER JOIN chickbook!ordr_svs ;
ON Services.service_id = Ordr_svs.service_id ;
WHERE Ordr_svs.sex = "2";
AND "0000001123" = ordr_svs.line_id INTO ARRAY aFemSvs
The above SQL reports back <6> records
Second SQL:
SELECT Services.service, Ordr_svs.service_id, "2";
FROM chickbook!services INNER JOIN chickbook!ordr_svs ;
ON Services.service_id = Ordr_svs.service_id ;
WHERE Ordr_svs.sex = "2";
AND ordr_svs.line_id = "0000001123" INTO ARRAY aFemSvs
Notice, the ONLY difference is comparing the Field to the Variable in the Second SQL.
Why is it that I can compare the .sex field to "2" but not
the .line_id field to "0000001123" ????????????
Field length of .sex is 1 variable length is 1
Field length of .line_id is 10 variable length is 10
I can reverse the first field comparisons with no problem, meaning I can put
where "2" = ordr_svs.sex .... or
where order_svs.sex = "2" with NO difference in results.,
but, if I switch the latter field comparison, it does affect output.
example:
If I put order_svs.line_id = "0000001123" I get <0> records
if I put "0000001123" = Ordr_svs.Line_id I get <6> records
Is there a trick here, a bug? Any answers?
obviously, I don't hard code variables, this example was what I had to write in order
to get results from my query which wasn't working. How would one know he or she has
to reverse the Field/variable comparisons????????
Thanks, this is a puzzler.
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only