Iqd file

[alf]

Hello !!
I have made the following IQD file to be used as data source in Transformer but the ROW_NUMER().....throws a syntax error i do not catch...The message says a syntax error near OVER....

COGNOS QUERY
STRUCTURE,1,1
DATABASE,DB2W
TITLE,ReportPrueba
BEGIN SQL

with prueba as(

select     
      
           T1."BRANGRP" as c1,
      T1."BRGRDESC" as c2,
      ROW_NUMBER() OVER (ORDER BY T1."BRANGRP") AS NumRow
           
         
from    "M90"."VMQTBRGRP" T1
ORDER BY T1."BRANGRP"
   
)
select * from prueba

END SQL

COLUMN,0,BrandGrp
COLUMN,1,BrandGrpDesc
COLUMN,2,NumRow

ANY IDEA ¿?¿?¿?

[cognostechie]

ROW_NUMBER () OVER (PARTITION BY .. ORDER BY ..) AS NUMROW

[alf]

Hello, thanks for your response.
No luck with your hint, i have tried by adding PARTITION and keeps returning "A syntax error near OVER"...It must be a nonsense but where the hell.....

[cognostechie]

Do you have access to Framework Manager (maybe a dumb question)? If yes, then this is what you can do:

1> Make sure your SQL runs in the database
2> Create a Database query subject in FM, insert the SQL, make the SQL type as 'Native'
3> Save the project
4> In the externalize method property, select IQD.
5> Publish the package, it will create an IQD file.

Then it should work because FM will re-write the SQL for what is acceptable by Transformer

I haven't done it but worth a try. By the way, if you have access to FM, then it's better to use a package instead of IQD.

[alf]

Thank you very much for your answer !!
Now i have to try what you say as i have no experience with Framework.
i will let you know soon result,