I have inherited code from a previous developer. Here is one of his SQL statements:

SELECT from_code id,description name
FROM lookups
WHERE type = 'HospitalSystems'
AND (
(
('-9,New Hospital,-9' = '-0')
AND from_code = from_code
)
OR
(
('-9,New Hospital,-9' != '-0')
AND
from_code in ('-9','New Hospital','-9')
)
)

my question is what the numbers represent (-9 and -0). I have
programmed with SQL for years, but am clueless on these
numbers.

Thanks
James

Without knowing anything about the database or the type of application it is for, I would guess that it's a status of the hospital? Putting things in single quotes just means that it takes the literal, so I couldn't see how the first and would ever be used. What types of results does this retreive?

:eek:

I dunno really. What "from_code" is ? Maybe that would put some light in it.

Matt

unlucky1
I get the same results if using:
SELECT from_code, description
FROM lookups
WHERE from_code = 'New Hospital'

I just had never seen numbers before and after the variable, and have no idea why the programmer wrote the query like this.

utsn
The "from_code" is a field name in the table, and "id" is the alias.
My confusion is what the numbers represent.

Here is the table fields:
type varchar(25) No Primary
from_code varchar(25) No Primary
to_code varchar(25) Yes None
description varchar(100) No None
order_by_sequence

I can make sense of everything but it just appears that "-9, NewHospital, -9" is just a literal, though I don't know the system and I would think the originator had a reason to put it in there. That's why commenting code is always good!!

Unless it's some wacky DB system..

('-9,New Hospital,-9' = '-0')

will always be false, it's no different than 'apples' = 'oranges'
as a result

(
('-9,New Hospital,-9' = '-0')
AND from_code = from_code
)

will always be false ( not also that from_code = from_code is pointless)

and...

('-9,New Hospital,-9' != '-0')

will always be true, leaving us with

from_code in ('-9','New Hospital','-9')

this one is the only clause in that whole bunch that's actually useful, if from_code is '-9' or 'New Hospital' (or, '-9', again, redundantly) then it wil lbe true.

Is this SQL actually hardcoded like that, or is it being generated from something (which could explain the pointless nature of most of it, if in other cases it's not so pointless)?

What database server is this being executed against?

mySQL 4.0.

What does that query actually look like in the code? As gogocode implied, I can't imagine any coder would be crazy enough to hardcode in so many obviously true and obviously false comparisons.

My guess is that '-9,New Hospital,-9' is generated within the program for comparison against from_code. The '-0' would be a special case value that the query forces true.