Can you please anybody explain me why this give me output 1234567890. This should be giving me full text length output as written below.

Below text I've written in Sql Advance query with a simple output structure with a text attribute (also tried to give attribute length but no luck)

SET @transposedColumns ='1234567890123456789';
SELECT @transposedColumns;
Solution

Hi Rajeev,

I can do the same test.  

I think what you are doing is defining the variable @transposedColumns as in input to your SQL widget, right ?  I'm not sure why, but il looks like, if the value going in is shorter than 10, it will maintain a length of 10 for this field, regardless of what values you SET it to inside the SQL widget.  

If however, the initial value passed into the sql widget is longer than 10, full length is used of the SET value.

This could be considered a bug of the sql widget; I guess, but than again maybe input parameters are not meant to have their values changed ...

Dorine

Solution

Hello Rajeev.

It is indeed strange.

What Dorine is saying makes sense. The input parameter is declared with size enough for its initial content. If such content changes, it will become too long. Looks like a poorly unspecified situation.

If this is not an issue, but plain curiosity, I'd submit your oml through the feedback option of Service Studio (Help menu) with a link to this discussion.


Thanks to both of you. It's true, when I defined long text statically in parameter then it works. But I do have one following question. If we do have this limitation then how someone will write dynamic sql here like:

SET @sql = 'Select col1, col2, col3, col4 from table1';

EXEC @sql

There are some implementation where I don't know how many columns needs to be in query, so other than dynamic sql there would not other choice. 

Maybe not what, but you need to know how many columns you have. Or how will you define output structure?



Nuno Reis wrote:

Maybe not what, but you need to know how many columns you have. Or how will you define output structure?



Output structure is text type which receive json. See my query which I'm using. I need to do transposition of columns. Here months columns are not real column but values in the table.

SELECT ModuleName as Module, UserName as 'User Name', UserId,ActionId, ActionName as Action, TotalCount as Count,

[Apr 2020],[Apr 2021],[Feb 2019],[Jan 2017],[Jan 2018],[Jan 2021],[Jan 2022],[May 2020],[Sep 2017]  

    FROM ( 

       SELECT m.ModuleName, u.UserName,  

            u.UserId,  

            a.ActionId, 

            a.ActionName,  

            (Select  

                     count(ActionOccuranceId) 

                     from ANALYTICS_ACTIONOCCURANCE o1  

                     where  

                     o1.ActionId = o.ActionId and o1.UserId=o.UserId) as TotalCount, 

                     FORMAT(o.OccurredOn,'MMM yyyy') as OccurredOn  

                     from ANALYTICS_ACTIONOCCURANCE o 

       INNER JOIN ANALYTICS_USER u on u.UserId = o.UserId 

       INNER JOIN ANALYTICS_ACTION a on a.ActionId = o.ActionId 

       INNER JOIN  ANALYTICS_MODULE m on m.ModuleId = a.ModuleId 

       WHERE 1=1  --AND a.ModuleId=1 AND o.OccurredOn between 'Jan  1 2010 12:00AM' AND 'Jan  1 2011 12:00AM'


   )x PIVOT (count(OccurredOn) for OccurredOn in ([Apr 2020],[Apr 2021],[Feb 2019],[Jan 2017],[Jan 2018],[Jan 2021],[Jan 2022],[May 2020],[Sep 2017])) p FOR JSON AUTO

 


Rajeev Singh wrote:

Thanks to both of you. It's true, when I defined long text statically in parameter then it works. But I do have one following question. If we do have this limitation then how someone will write dynamic sql here like:

SET @sql = 'Select col1, col2, col3, col4 from table1';

EXEC @sql

There are some implementation where I don't know how many columns needs to be in query, so other than dynamic sql there would not other choice. 

 Hi Rajeev,

I'm not really getting why you do this two-step with first a SET of your query string, and then an EXEC ?  Why not just have the dynamic sql built outside of the sql widget and just execute as @sql ?  What is this extra SET step doing for you ?

Dorine

 

EDIT, I didn't look at details of your query, but just out of curiousity, where is the json being formed in it, I don't really see that .

Dorine Boudry wrote:

Rajeev Singh wrote:

Thanks to both of you. It's true, when I defined long text statically in parameter then it works. But I do have one following question. If we do have this limitation then how someone will write dynamic sql here like:

SET @sql = 'Select col1, col2, col3, col4 from table1';

EXEC @sql

There are some implementation where I don't know how many columns needs to be in query, so other than dynamic sql there would not other choice. 

 Hi Rajeev,

I'm not really getting why you do this two-step with first a SET of your query string, and then an EXEC ?  Why not just have the dynamic sql built outside of the sql widget and just execute as @sql ?  What is this extra SET step doing for you ?

Dorine

 

EDIT, I didn't look at details of your query, but just out of curiousity, where is the json being formed in it, I don't really see that .

 This is what I'm doing here. Can you suggest any other options.

SET @transposedColumns = STUFF(
        (SELECT DISTINCT ','+QUOTENAME(FORMAT(OccurredOn,'MMM yyyy')) as [Month]
        from {Analytics_ActionOccurance} 
        FOR XML PATH(''), TYPE
    ).value('.','NVARCHAR(MAX)'),1,1,'');

SET @query = ('SELECT ModuleName as Module, UserName as ''User Name'', UserId,ActionId, ActionName as Action, TotalCount as Count,'+@transposedColumns+' 
    FROM (
       SELECT m.ModuleName, u.UserName, 
            u.UserId, 
            a.ActionId,
            a.ActionName, 
            (Select 
                     count(ActionOccuranceId)
                     from [OSDEV1].DBO.[OSUSR_00K_ANALYTICS_ACTIONOCCURANCE] o1 
                     where  o1.ActionId = o.ActionId and o1.UserId=o.UserId) as TotalCount,
                     FORMAT(o.OccurredOn,''MMM yyyy'') as OccurredOn 
                     from [OSDEV1].DBO.[OSUSR_00K_ANALYTICS_ACTIONOCCURANCE] o
       INNER JOIN [OSDEV1].DBO.[OSUSR_00K_ANALYTICS_USER] u on u.UserId = o.UserId
       INNER JOIN [OSDEV1].DBO.[OSUSR_00K_ANALYTICS_ACTION] a on a.ActionId = o.ActionId
       INNER JOIN  [OSDEV1].DBO.[OSUSR_00K_ANALYTICS_MODULE] m on m.ModuleId = a.ModuleId
       WHERE 1=1 '
       + (CASE WHEN ISNULL(@moduleId,0) <> 0 THEN ' AND a.ModuleId='+CONVERT(NVARCHAR,@moduleId) ELSE '' END) 
       + (CASE WHEN (@startDate <> '1900-01-01 00:00:00' and @endDate <> '1900-01-01 00:00:00') THEN ' AND o.OccurredOn between '''+convert(nvarchar(255),@startDate)+''' AND '''+convert(nvarchar(255),@endDate)+'''' ELSE ' AND 2=2' END)
       +') x PIVOT (count(OccurredOn) for OccurredOn in ('+@transposedColumns+')) p FOR JSON AUTO');
      EXEC @query;
       

 

Hello Rajeev.

I think that Dorine's suggestion is that you send the strings as parameters to the Advanced SQ, instead of doing it inside. This way you have more control over what is sent (and if you use OS variables, you don't need to send them all inside, just the string).

But you are really using more SQL content than OS, so your way is not bad.

If SQL sets the rules here, I'd probably use a SQL view/stored procedure and call it from OS.

The main idea is still the same: less code.

yeah,

this is too advanced sql for me, so my natural reflex is to pull all logic in the flow, so I can see with debugging what exactly is happening.  So sorry, can't really help further with this

Thanks guys for your prompt reply. It is really helpful and solved my problem some extent. Really appreciated.