Solved

SQL - Column doesn't exists error

Posted on 2013-05-30
5
537 Views
Last Modified: 2013-06-03
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.
0
Comment
Question by:holemania
  • 3
  • 2
5 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 39207896
try

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
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 400 total points
ID: 39208358
Even without the minor syntax problem, the above code won't work.

Assuming permissions aren't an issue, you can use dynamic SQL:

DECLARE @sql varchar(8000)

SET @sql = 'SELECT ID, NAME'
IF COL_LENGTH('vendor', 'DEF_ID') > 0
    SET @sql = @sql + ', DEF_ID'
SET @sql = @sql + ' FROM DBO.VENDOR'
EXEC(@sql)
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 100 total points
ID: 39208480
I guess you're referring to this syntax issue:

if col_length('vendor, 'DEF_ID') >0
      SELECT ID, NAME, DEF_ID FROM VENDOR
else
      SELECT ID, NAME, null as def_id FROM VENDOR

I too was considering going dynamic but I think this is what the asker is looking for:

set @sql = 	'select id, name,' + 
		case when col_length('vendor', 'def_id') > 0 then 'def_id' else 'null as def_id' end + 
		' from vendor'
exec(@sql)

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39209163
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.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 39209190
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.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now