Jump to content
Changes to the Jaspersoft community edition download ×

Complex SQL to Jaspersoft statement help


jack w.

Recommended Posts

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.

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...