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 ¿?¿?¿?
ROW_NUMBER () OVER (PARTITION BY .. ORDER BY ..) AS NUMROW
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.....
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.
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,