jack w. Posted November 13, 2017 Share Posted November 13, 2017 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" ASCHow 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" ASCBut it indicates two errors:Missing FROM at '(' – at the second SELECT clause Z)Missing EOF at 'FROM' – at the 3rd FROM clauseIs 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"."Nazwa" FROM "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" ASCAlso, 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. Link to comment Share on other sites More sharing options...
narcism Posted November 13, 2017 Share Posted November 13, 2017 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-sql-query) that you could have edited and added this SQL code. Please stick to only one. Link to comment Share on other sites More sharing options...
sanbez Posted November 13, 2017 Share Posted November 13, 2017 @jack_wWhat is the point use 2 subqueries with condition WHERE "Id" = Z."PytanieId" instead one query with left join without subqueries? Link to comment Share on other sites More sharing options...
jkeri Posted November 14, 2017 Share Posted November 14, 2017 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now