July 14, 2010 at 10:12 am
Hi all,
having a problem trying to select Data using DSQL for two of the fields.
The majority of the fields in this table are named MEYYYYMM so the last two fields in the table are ME201005 and 201006. Next month the last field will be named ME201007. I am trying to set up a query that will pull out the ID field as well as the last two fields using DSQL as the last two field names will change each month, however when I run the code instead of select the last field (using one in this example) it actually assigns the Variable as the value to a field with no name. code below.
DECLARE @YYYYMM varchar(6)
DECLARE @sqlString varchar(200)
SET @YYYYMM = CONVERT(VARCHAR, GETDATE(), 112)
SET @SQLString = 'SELECT ID, '+@YYYYMM+' FROM JData'
EXEC(@SQLString)
GO
How do I go about selecting the last field using DSQL? I'm sure I have the syntax wrong in the code aboove, can someone please advise?
Thanks in advance,
Mitch.
September 3, 2010 at 12:18 pm
Mitch, I'm not sure if you are still having a problem, since this is an older post. But I just couldn't resist pointing out that this is absolutely terrible table design. However, I realize there are reporting tables with intentional denormalization. You could try something like this...
DECLARE @OrdPos INT,
@LastCol VARCHAR(50),
@PenUltCol VARCHAR(50),
@SQL VARCHAR(1000)
SELECT @OrdPos = (SELECT
MAX(ordinal_position)
FROM information_schema.columns
WHERE table_name = 'Jdata')
SELECT @LastCol = (SELECT
column_name
FROM information_schema.columns
WHERE table_name = 'Jdata'
AND ordinal_position = @OrdPos)
SELECT @PenUltCol = (SELECT
column_name
FROM information_schema.columns
WHERE table_name = 'Jdata'
AND ordinal_position = @OrdPos-1)
SELECT @SQL = 'SELECT ID, ' + @PenUltCol + ', ' + @LastCol +
' FROM Jdata'
EXEC(@SQL)
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply