Clarity

Expand all | Collapse all

Can a Dynamic SQL(where columns are created dynamically) be converted to NSQL and be used in Grid portlet..?

  • 1.  Can a Dynamic SQL(where columns are created dynamically) be converted to NSQL and be used in Grid portlet..?

    Posted 12-26-2018 05:26 AM

    #Trying to create a new portlet , where an assignment label/field needs to be pulled into the portlet.

    The availability of column(assignment label) is dynamic , then only if user enters teh value, teh column shall be available in DB, in such instance, can an NSQL be generated ..?

    Able to create SQL, but unable to convert such dynamic NSQL.



  • 2.  Re: Can a Dynamic SQL(where columns are created dynamically) be converted to NSQL and be used in Grid portlet..?

    Posted 12-26-2018 12:41 PM

    Please provide more details.

    Using left joins it is possible to get data columns where there are no specific values.

    Are you saying you have the SQL? If so please share it.



  • 3.  Re: Can a Dynamic SQL(where columns are created dynamically) be converted to NSQL and be used in Grid portlet..?

    Posted 12-26-2018 01:14 PM

    Hello,

     

    The requirement is suppose there are category A, B and C in database so i want to display amount for category A, B and C column wise, if anyone adds new category D then my query automatically takes category D as 4th column in query output. I prepared the SQL where i have taken category values from database in a variable and passed that into PIVOT, but now i am unable to convert it into NSQL. 

     

    Output should be displayed as 

    A

    B

    C

    D

    1000

    1000

    1000

    1000

     

    SQL Code:-

     

    DECLARE @cols AS VARCHAR(1000)='';
    DECLARE @query AS VARCHAR(1000)='';
    SELECT @cols = @cols + QUOTENAME(PRCATEGORY) + ',' FROM (select distinct PRCATEGORY from PRJ_RESOURCES ) as tmp
    set @query = ,
    N'SELECT * from
    (
    select ptt.PRPROJECTID,prj_resources.PRCATEGORY , PAA.ACTCOST_SUM
    from Niku.PrTask PTT inner Join niku.prassignment PAA ON PAA.prTaskId=PTT.prid inner join niku.prj_resources on PAA.PRRESOURCEID = prj_resources.prid
    inner join niku.srm_resources on PAA.PRRESOURCEID = srm_resources.id
    where ptt.PRISTASK=1 and prj_resources.PRCATEGORY is NOT NULL and srm_resources.RESOURCE_TYPE in (0,1,2,3)
    ) src
    pivot
    (
    SUM(PAA.ACTCOST_SUM) for PAA.PRCATEGORY in ( ' + @cols + ')
    ) piv'

    execute(@query)

     

     

     

    Any help would be much appreciated. 



  • 4.  Re: Can a Dynamic SQL(where columns are created dynamically) be converted to NSQL and be used in Grid portlet..?

    Posted 01-02-2019 01:09 AM

    You could consider doing this as a JasperSoft report, instead of a Portlet.



  • 5.  Re: Can a Dynamic SQL(where columns are created dynamically) be converted to NSQL and be used in Grid portlet..?

    Posted 12-27-2018 09:45 AM

    Hello ManjuN,

     

    Try creating a 2 dimensional portlet. The columns on the second dimension are dynamic based on query output, as the Category gets added or removed, it will be reflected in the portlet.

     

    You will need atleast one column on the first dimension. If you don't have any column for the first dimension, try using a fixed text - "Resource Cost by Category".