Complex SQL to Jaspersoft statement help

0

I need to get fields from Database, that you can get in SQL with this very complex query:

SELECT "Id", "ZlecenieId", "PytanieId",

 

  (SELECT "Nazwa" FROM "Dokumenty"."KwestionariuszSamoocenySzablonPytan" WHERE "Id" = Z."PytanieId"),

  (SELECT "SciezkaRaport" FROM "Dokumenty"."KwestionariuszSamoocenySzablonPytan" WHERE "Id" = Z."PytanieId")

 

  FROM "Dokumenty"."KwestionariuszSamoocenyPytania" AS Z

  WHERE "ZlecenieId" = $P{my Parameter here} ORDER BY "PytanieId" ASC

How can I rewrite it so Jaspersoft can understand it? I've tried something like this:

SELECT  "Dokumenty"."KwestionariuszSamoocenyPytania"."Id" ,  "Dokumenty"."KwestionariuszSamoocenyPytania"."ZlecenieId",

  "Dokumenty"."KwestionariuszSamoocenyPytania"."PytanieId"

  (SELECT "Dokumenty"."KwestionariuszSamoocenySzablonPytan"."Nazwa"

                FROM  "Dokumenty"."KwestionariuszSamoocenySzablonPytan"

                WHERE "Dokumenty"."KwestionariuszSamoocenySzablonPytan"."Id"

                = Z. "Dokumenty"."KwestionariuszSamoocenyPytania"."PytanieId"),

                (SELECT  "Dokumenty"."KwestionariuszSamoocenySzablonPytan"."SciezkaRaport"

                               FROM  "Dokumenty"."KwestionariuszSamoocenySzablonPytan"

                               WHERE  "Dokumenty"."KwestionariuszSamoocenySzablonPytan"."Id"

                               = Z. "Dokumenty"."KwestionariuszSamoocenyPytania"."PytanieId")

  FROM  "Dokumenty"."KwestionariuszSamoocenyPytania" AS Z

  WHERE  "Dokumenty"."KwestionariuszSamoocenyPytania"."ZlecenieId"

  = $P{myParameterHere}

  ORDER BY  "Dokumenty"."KwestionariuszSamoocenyPytania"."PytanieId"

  ASC

But it indicates two errors:

Missing FROM at '(' – at the second SELECT clause Z)

Missing EOF at 'FROM' – at the 3rd FROM clause

Is it even possible to translate this kind of query to Jaspersoft?

EDIT:

 

I solved the problem. I copy pasted the standard SQL statement and changed it a bit, so it looks this way:

SELECT   Z."Id" , Z."ZlecenieId" , Z."PytanieId",

 

  (SELECT  "Dokumenty"."KwestionariuszSamoocenySzablonPytan"."NazwaFROM "Dokumenty"."KwestionariuszSamoocenySzablonPytan" WHERE  "Dokumenty"."KwestionariuszSamoocenySzablonPytan"."Id"  = Z."PytanieId"),

  (SELECT  "Dokumenty"."KwestionariuszSamoocenySzablonPytan"."SciezkaRaport"  FROM "Dokumenty"."KwestionariuszSamoocenySzablonPytan" WHERE "Id" = Z."PytanieId")

 

  FROM "Dokumenty"."KwestionariuszSamoocenyPytania" AS Z

  WHERE "ZlecenieId" = $P{myParameterHere} ORDER BY "PytanieId" ASC

Also, there was a problem with incorrect use of =, so I realized I also had to change the used parameter property, to Integer in this case. There was also some error I can't remember the message of right now, but I just copied the query, closed the window and opened it again. It works fine now.

@narcism, I created another thread, because I thought it to be different enough than the previous one, but after a closer look maybe you're right. Still, thanks again for quick response. After you asked what's wrong with the original SQL, I realized I could just copy-paste it instead of trying to manually "translate" it to the Jaspersoft, and see what happens. This helped a lot.

jack w.'s picture
32
Joined: Nov 9 2017 - 2:41am
Last seen: 1 year 8 months ago

I dont see any problem with your initial query at all. Why do you need to rewrite it? The only thing i woudl add wiuld be to assign an alias name to each to the results retruend from the SELECT statements.

jkeri - 1 year 9 months ago

2 Answers:

0

What is the actual problem with your initial/original SQL? Post some screen shots or screen recordings.

Note that you have already opened up a similar question(https://community.jaspersoft.com//questions/1073256/generating-fields-sq...) that you could have edited and added this SQL code. Please stick to only one.

narcism's picture
1064
Joined: Nov 22 2010 - 12:39am
Last seen: 14 hours 19 min ago
0

@jack_w


What is the point use 2 subqueries with condition WHERE "Id" = Z."PytanieId"  instead one query with left join without subqueries?

sanbez's picture
834
Joined: Jan 11 2011 - 2:06am
Last seen: 1 day 3 hours ago
Feedback
randomness