holemania
asked on
SQL - Column doesn't exists error
Hello experts,
I have 2 databases for the same system, but since it's different versions one has additional columns and the other one doesn't. I'm using the same query to pull from both databases.
I am getting an error if I use the query with the extra column to pull from the database that doesn't have it. How could I set it to null if it doesn't have the extra column?
SELECT ID, NAME, DEF_ID FROM VENDOR
The column "DEF_ID" is a new field in the current database. It is returning an error.
I have 2 databases for the same system, but since it's different versions one has additional columns and the other one doesn't. I'm using the same query to pull from both databases.
I am getting an error if I use the query with the extra column to pull from the database that doesn't have it. How could I set it to null if it doesn't have the extra column?
SELECT ID, NAME, DEF_ID FROM VENDOR
The column "DEF_ID" is a new field in the current database. It is returning an error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The missing quote after vendor is the syntax issue.
The code you wrote won't work on a database where the vendor table doesn't contain a "DEF_ID" column, because SQL can't generate a plan for that query -- you have to remember that SQL has to pre-generate a plan for the query before the code runs and thus before the IF gets evaluated at run time. Try the code and you'll see what I mean.
The code you wrote won't work on a database where the vendor table doesn't contain a "DEF_ID" column, because SQL can't generate a plan for that query -- you have to remember that SQL has to pre-generate a plan for the query before the code runs and thus before the IF gets evaluated at run time. Try the code and you'll see what I mean.
Correct, the IF won't work, but the dynamic SQL alternative with the null in case the column doesn't exists will (http:#a39208480). And I think that's what the asker is looking for.
if col_length('vendor, 'DEF_ID') is null
SELECT ID, NAME, DEF_ID FROM VENDOR
else
SELECT ID, NAME, null as def_id FROM VENDOR