Click here to Skip to main content
16,018,525 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have this dynamic query:

DECLARE @cols AS NVARCHAR(MAX),
            @query  AS NVARCHAR(MAX),
            @archivedate date = '5/1/2019'; 
    
    WITH 
    E(n) AS(
        SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
    ),
    E2(n) AS(
        SELECT a.n FROM E a, E b
    ),
    E4(n) AS(
        SELECT a.n FROM E2 a, E2 b
    ),
    cteTally(n) AS(
        SELECT TOP((SELECT TOP (1) COUNT(DISTINCT ratechangedate) datecount
                    FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive]
                    WHERE ArchiveDate = @archivedate
                    GROUP BY account
                    ORDER BY datecount DESC)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
        FROM E4
    )        
    SELECT @cols = (SELECT REPLACE( '
        ,MIN( CASE WHEN index_num = <<index_num>> THEN ratechangedate END) AS [date <<index_num>>]
        ,MIN( CASE WHEN index_num = <<index_num>> THEN new_noterate END)   AS [rate <<index_num>>]' , '<<index_num>>', n)
                FROM cteTally
                ORDER BY n
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
    
    set @query = 
    N'WITH a AS (
        SELECT a.account,
            dense_rank() OVER ( PARTITION BY a.account ORDER BY ratechangedate) AS index_num,
            CONVERT(VARCHAR(10),ratechangedate,101)[ratechangedate],
            new_noterate
        FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive] a
        WHERE archivedate = @date
        )
    SELECT a.account' + @cols + N' 
    FROM a
    GROUP BY a.Account;'
    
    
    EXECUTE sp_executesql @query, N'@date datetime', @date = @archivedate;


What I have tried:

I want to remove the `NULL` values and replace it with an empty string I tried just putting `ISNULL(CONVERT(VARCHAR(10),ratechangedate,101),'')` and `ISNULL(new_noterate,'')` in the @query part of the code but I get these errors:
<pre>

<pre>> Msg 207, Level 16, State 1, Line 10  
> Invalid column name 'ratechangedate'.
>
> Msg 207, Level 16, State 1, Line 12  
> Invalid column name 'ratechangedate'.
>
> Msg 207, Level 16, State 1, Line 14  
> Invalid column name 'ratechangedate'.  

> ...... and so on for every other "line" up to a value of 34 .......


Any help would be appreciated.
Posted
Updated 9-Jul-19 8:24am

1 solution

I think you are searching for the COALESCE (Transact-SQL)[^] function.

For example:
SQL
COALESCE(CONVERT(VARCHAR(10), a.ratechangedate, 101), '')
SQL
COALESCE(a.new_noterate, '')

Note that I qualified column names with table alias 'a', as the real problem here is that the engine does not recognize the column names.
 
Share this answer
 
v2
Comments
Member 14525600 9-Jul-19 14:32pm    
Could you provide a more detailed answer?
Maciej Los 9-Jul-19 15:41pm    
Follow the link and there you'll find example usage of the function.
Maciej Los 9-Jul-19 15:41pm    
Short And To The Point!
phil.o 9-Jul-19 17:44pm    
Thanks :)
Member 14525600 9-Jul-19 16:06pm    
Again a more detailed answer will help

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900