Clarity

I know this isn't the right place for SQL help but since the finished version of this could do us all some good...

I'm working on a Dynamic SQL where one part is failing. I've created a little test case below to demonstrate the fail in hopes that someone can tell me what I'm doing wrong.

Here's the hard coded example:

SELECT
TOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE
, 'osumc_est_rev_date' AS ATTRIBUTE_ID
FROM INV_INVESTMENTS I
JOIN ODF_CA_IDEA A ON A.ID = I.ID
WHERE osumc_est_rev_date IS NOT NULL
ORDER BY I.LAST_UPDATED_DATE DESC

If you want to run it, any custom attribute should do. It's telling me the top last updated date for an Idea where a particular custom attribute was used (not null). The answer 11/7/2014 has been validated.

Trying to turn this little test harness dynamic.

DECLARE @CustAttr nvarchar(75)
SET @CustAttr = 'osumc_est_rev_date'

SELECT
TOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE
, @CustAttr AS ATTRIBUTE_ID
FROM ODF_CA_IDEA A
JOIN INV_INVESTMENTS I ON A.ID = I.ID
WHERE @CustAttr IS NOT NULL
ORDER BY I.LAST_UPDATED_DATE DESC

The WHERE clause isn't working. 

Ideas?

I've not really played with dynamic sql in SQL*Server, but in Oracle the way it works is that you have to tell the engine "here is a bit of dynamic SQL, please execute it for me" rather than just trying to sneak in a bit of dynamic code into a "normal" SQL statement.

Not sure I've expressed that particularly well, but I'm not in front of a DB right now to build a working example for you, but I kinda mean that the whole statement is build up (as a big string) and then passed to the SQL engine to run. So I'm saying (badly) that how you are structuring your test harness is just fundamentally wrong - your where expression evaluates to a testing that a string variable called CustAttr is not null (which because you set it earlier, it never will be).

(will add to this tomorrow unless someone else chimes in with a more lucid explanation response in the mean time!)

set nocount on

declare @table table (COLUMN_NAME varchar(200))
declare @column nvarchar(200)
declare @count int
declare @sq nvarchar(1) = CHAR(39)
declare @lf nvarchar(2) = CHAR(13) + CHAR(10)
declare @sql nvarchar(4000) = @lf

insert into @table
select column_name from niku.information_schema.columns c where c.table_name = 'odf_ca_idea' and c.is_nullable = 'yes'

select @count = count(*) from @table
while (@count > 0)
begin

     select top 1 @column = COLUMN_NAME from @table
     
     set @sql = @sql + 'select top 1 (convert(date,i.last_updated_date,106)) as last_object_updated_date'
     set @sql = @sql + @lf  + ', ' + @sq + @column + @sq + ' as columnName'
     set @sql = @sql + @lf  + ', cast(' + @column + ' as varchar(2000)) as  attribute_id'
     set @sql = @sql + @lf  + 'from niku.odf_ca_idea a'
     set @sql = @sql + @lf  + 'inner join niku.inv_investments i on a.id = i.id'
     set @sql = @sql + @lf  + 'where ' + @column + ' IS NOT NULL'
     if (@count > 1)
     set @sql = @sql + char(10) + ' union ' + char(10)
     
     delete from @table where column_name = @column
     select @count = count(*) from @table
end

execute sp_executesql @sql

Not sure if this is what you are looking for but this will give you a dynamic sql for all the top 1 for all nullable fields on a given custom object table.

So the attribute_id field get cast into a varchar to deal with different date types and allows the union to provide a single results set.

V/r,

Gene

Yep that ^ is what I meant!

Here is my version of Rob's "harness" then;

DECLARE @CustAttr nvarchar(75)
DECLARE @V_SQL nvarchar(4000)

SET @CustAttr = 'osumc_est_rev_date'

SET @V_SQL = 'SELECT ' +
'TOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE ' +
', ' + @CustAttr + ' AS ATTRIBUTE_ID '+
'FROM ODF_CA_IDEA A '+
'JOIN INV_INVESTMENTS I ON A.ID = I.ID '+
'WHERE ' + @CustAttr + ' IS NOT NULL '+
'ORDER BY I.LAST_UPDATED_DATE DESC'

EXECUTE (@V_SQL)

(I examined the CMN_ID_SP stored procedure to work out the SQL*Server dynamic SQL syntax - with Oracle you would "execute immediate" the string)

Thanks Gene, Dave.

    We're leaning a different direction now (custom object and xogging the data on the object). The data is eye opening - I think this'll be something everyone will be able to use. I'll pay back your efforts if I get to take this to completion.

Thanks!