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:
SELECTTOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE, 'osumc_est_rev_date' AS ATTRIBUTE_IDFROM INV_INVESTMENTS IJOIN ODF_CA_IDEA A ON A.ID = I.IDWHERE osumc_est_rev_date IS NOT NULLORDER 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'SELECTTOP 1 (CONVERT(DATE,I.LAST_UPDATED_DATE,106)) LAST_OBJECT_UPDATED_DATE, @CustAttr AS ATTRIBUTE_IDFROM ODF_CA_IDEA AJOIN INV_INVESTMENTS I ON A.ID = I.IDWHERE @CustAttr IS NOT NULLORDER BY I.LAST_UPDATED_DATE DESC
The WHERE clause isn't working.
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 ondeclare @table table (COLUMN_NAME varchar(200))declare @column nvarchar(200)declare @count intdeclare @sq nvarchar(1) = CHAR(39)declare @lf nvarchar(2) = CHAR(13) + CHAR(10)declare @sql nvarchar(4000) = @lfinsert into @tableselect column_name from niku.information_schema.columns c where c.table_name = 'odf_ca_idea' and c.is_nullable = 'yes'select @count = count(*) from @tablewhile (@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 @tableendexecute 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.
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.