Does iReport support SQL using temp tables

[evan.warrick]

iReport is erroring when running SQL using a temp table.  Does anyone know if this SQL is not supported by Jasper?

 

Item_Production is in a flat format while CN_Log is in a attribute-value format. So I need to transform Item_Production to attribute-value format for the join with CN_log to work.

 

If(OBJECT_ID('tempdb..#tempItemProductionKV') Is Not Null)

    Drop Table #tempItemProductionKV

 

CREATE TABLE #tempItemProductionKV (

       RowNumber INT PRIMARY KEY IDENTITY (1, 1),

       Item_Number VARCHAR (100),

       [Attribute Name] VARCHAR (1000),

       [Attribute Restricted Name] VARCHAR (1000),

       [Previous Value] VARCHAR (1000),

       [New Value] VARCHAR (1000),

       [Attribute Value in Production] VARCHAR (5000),

       [update Datetime] DATETIME);

 

DECLARE @ColumnName AS NVARCHAR(MAX),

        @selectCol AS NVARCHAR(MAX),

        @dml AS NVARCHAR(MAX)

             

SET @selectCol= (

select STRING_AGG('CAST([' + CAST(x.restricted_name as NVARCHAR(MAX)) + ] as NVARCHAR(MAX)) as [' + CAST(x.restricted_name as nvarchar(max)) + ]', ',') FROM (SELECT DISTINCT fa.restricted_name from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a

INNER JOIN B_FORMAT_ATTR fa on fa.FORMAT_ATTR_ID = RIGHT(a.COLUMN_NAME, LEN(a.column_name)-2)

WHERE VIEW_NAME = 'ITEM_Production' AND COLUMN_NAME like 'F%' AND RESTRICTED_NAME<> 'Item_Number') x)

 

 SET @ColumnName= (

select STRING_AGG('[' + CAST(x.restricted_name as NVARCHAR(MAX)) + ]', ',') FROM (SELECT DISTINCT fa.restricted_name from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a

INNER JOIN B_FORMAT_ATTR fa on fa.FORMAT_ATTR_ID = RIGHT(a.COLUMN_NAME, LEN(a.column_name)-2)

WHERE VIEW_NAME = 'ITEM_Production' AND COLUMN_NAME like 'F%' AND RESTRICTED_NAME<> 'Item_Number') x)

 

 

SET @dml = 'SELECT Item_Number,b.Name as [Attribute Name],KeyRestrictedName as [Attribute Restricted Name],c.CN_Old_Value as [Previous Value],c.CN_New_Value as [New Value], KeyValue as [Attribute Value in Production],c.CN_Changed_Datetime as [update Datetime]

FROM

(SELECT Item_Number,' + @selectCol + ' FROM  ITEM_Production) x

unpivot

(

    KeyValue

    for KeyRestrictedName in (' + @ColumnName + ')

) u

Join B_FORMAT_ATTR b on b.RESTRICTED_NAME=u.KeyRestrictedName and PROFILE_ID=10117

Join CN_Log c on c.CN_PK1= Item_Number and c.CN_Attribute_Name=b.Name

Where CN_Changed_Datetime >= ''2021-08-15''

and CN_Changed_Datetime <= ''2021-12-15''

Order by InternalRecordId ASC'

 

Insert Into #tempItemProductionKV

EXEC sp_executesql @dml

 

Select * from #tempItemProductionKV

[Scott Andersen]

Have you tried using nested queries?

[jgust]

Move your code into a stored procedure then use a data adapter with plsql language.  That plsql language is not just for Oracle but it works on sql server too.  When you change the SQL language a new default parameter is created as $P{ORACLE_REF_CURSOR} which holds the result of the procedure.

Here is an example of how to call a stored procedure:

{ call SP_RPT_STAFF_PAYROLL_SPEC_PGM($P{PS_M_ACAD_YR},$P{PS_M_COLL_NAME},$P{PS_M_SUBM_NUM},$P{PS_M_ORG_ID},$P{PS_M_SSN_SELECTION},$P{ORACLE_REF_CURSOR}) }

[evan.warrick]

Thank you!