Using a variable as a field name - Syntax question

Question

Post reply

Using a variable as a field name - Syntax question

Kaminskit

SSC Rookie

Points: 34
More actions
September 14, 2012 at 6:40 am

#266576

I'm trying to use a variable but don't understand the syntax. I'm following the suggestions from this earlier posting
http://www.sqlservercentral.com/Forums/Topic864747-338-1.aspx">
http://www.sqlservercentral.com/Forums/Topic864747-338-1.aspx
The code below is what I am trying to reproduce, only using a variable for the column name:
select d_tid
from rtns.itas_rtn_ct_1120_cor
I have tried many different versions of this code below:
declare @ColumnName varchar(50) --<< ERRORS ON THIS LINE<<
declare @sql nvarchar(max)
set @ColumnName = 'd_tid'
set @sql = 'select ' + @ColumnName + 'from' + 'rtns.itas_rtn_ct_1120_cor'
print @sql
EXEC(sql)
The error message is:
PLS-00103: Encountered the symbol “@” when expecting one of the following…
Is the '@' symbol simple a shorthand note for something or does the '@' symbol really need to be there?
Are the semi-colons ";" needed at the end of the lines or not?
When I try this a different way:
DECLARE
ColumnName varchar(50);
sql nvarchar(max); --<< ERRORS ON THIS LINE<<
Begin
set ColumnName = 'd_tid'
set sql = 'select ' + ColumnName + 'from' + 'rtns.itas_rtn_ct_1120_cor'
print sql
exec(sql)
END
The error message is:
PLS-00103: Encountered the symbol “SQL” when expecting one of the following…
This is in an enviroment where 'create' premissions are not allowed.
The versions are:
Oracle Database 10g
PL/SQL 8.0.0.1480
Thanks for the help. As you can tell by my simple question I'm new to SQL, but have 25 years dev with MS Access. I'm in a new world now.
Tom

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247155 More actions · Answer 1

I'm afraid you might be in the wrong place. This is a SQL Server forum, not Oracle.

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 2

1: Works OK for me with the following issues:

declare @ColumnName varchar(50) --<< ERRORS ON THIS LINE<<

declare @sql nvarchar(max)

set @ColumnName = 'd_tid'

set @sql = 'select ' + @ColumnName + ' from ' + 'rtns.itas_rtn_ct_1120_cor'

print @sql

EXEC(@sql)

You need spaces around 'from' in the derived SQL, the same as if you typed it yourself.

Also need @sql in EXEC().

In second:

DECLARE

ColumnName varchar(50);

sql nvarchar(max); --<< ERRORS ON THIS LINE<<

DECLARE

@ColumnName varchar(50),

@sql nvarchar(max); --<< ERRORS ON THIS LINE<<

You need a comma after the first declaration. Semicolon shows the end of a line.

& you do need the @ - it denotes a variable...

GSquared SSC Guru Points: 260824 More actions · Answer 3

PL/SQL doesn't declare variables with @ symbols on them, per the samples I found. Here's an example: http://www.plsqltutorial.com/plsql-variables/

The problem you're running into is that T-SQL (Microsoft's version of SQL) and PL/SQL (Oracle's version) are quite different in many details. What you've written is T-SQL, and Oracle can't figure out what you're trying to do.

In PL/SQL, if I'm reading it correctly, you would do this:

DECLARE

ColumnName varchar(50);

sql nvarchar(max);

I'm not sure "nvarchar(max)" is an allowed type in Oracle. Might be, might not be. But the syntax appears to require that each variable have a semicolon after it.

Beyond that, I recommend hitting an Oracle site for further help. This is an MS SQL Server site, and you'll get better help on a site more dedicated to Oracle.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Kaminskit SSC Rookie Points: 34 More actions · Answer 4

Apologizes for posting to the wrong site/forum. I was following up on an earlier post for a similar question.

@Laurie- The missing spaces around the ‘ From ‘ is helpful for sure. I tried the code as you suggested and still get the same errors with the “@” which as GSquared points out is likely an Oracle, PL/SQL issue.

@GSquared- Thanks for that insight. The different forums for the many ‘flavors’ of SQL all start looking alike to a newbie.

I’ll keep working on this and will try the suggestions and links.

Thanks again,

Tom

GSquared SSC Guru Points: 260824 More actions · Answer 5

Yep. Knowing which forum/site to use isn't helped by the fact that they're all "SQL". Definitely feel for you on that one.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Using a variable as a field name - Syntax question

Cookies on SQLServerCentral