PDA

View Full Version : Show SQL Query



ivansc
8-May-2010, 06:14 PM
Hi,
VRW 16 RCIII
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product


Files: Dbasicos relates to Hpartida (dbasicos.codobra=hpartida.codobra)
Hpartida relates to Dpartida (hpartida.codobra=dpartida.codobra and hpartida.codipart=dpartida.codipart)
Dpartida relates to Krecurso (dpartida.codobra=krecurso.codobra and dpartida.codigo=krecurso.recurso)

(see the image included)
Connection to database ok, browse data ok, but preview get this error:

Error: 933, [Oracle][ODBC][Ora]ORA-00933: comando SQL no terminado correctamente

The option "Show SQL Query" shows this:


SELECT "DBASICOS"."codobra", "DBASICOS"."nombre", "HPARTIDA"."codipart", "HPARTIDA"."nombre", "HPARTIDA"."metrado", "HPARTIDA"."costo_unit", "HPARTIDA"."total", "DPARTIDA"."codigo", "DPARTIDA"."unidad", "DPARTIDA"."cuadrilla", "DPARTIDA"."cantidad", "DPARTIDA"."precio", "DPARTIDA"."total", "KRECURSO"."nombre"
FROM (((("ISCHOOF"."DBASICOS" "DBASICOS" INNER JOIN "ISCHOOF"."HPARTIDA" "HPARTIDA" ON "DBASICOS"."codobra" = "HPARTIDA"."codobra") INNER JOIN "ISCHOOF"."DPARTIDA" "DPARTIDA" ON "HPARTIDA"."codobra" = "DPARTIDA"."codobra") INNER JOIN "ISCHOOF"."KRECURSO" "KRECURSO" ON "DPARTIDA"."codobra" = "KRECURSO"."codobra") INNER JOIN "ISCHOOF"."KRECURSO" "KRECURSO" ON "DPARTIDA"."codigo" = "KRECURSO"."recurso") INNER JOIN "ISCHOOF"."DPARTIDA" "DPARTIDA" ON "HPARTIDA"."codipart" = "DPARTIDA"."codipart") INNER JOIN "ISCHOOF"."KRECURSO" "KRECURSO" ON "DPARTIDA"."codobra" = "KRECURSO"."codobra") INNER JOIN "ISCHOOF"."KRECURSO" "KRECURSO" ON "DPARTIDA"."codigo" = "KRECURSO"."recurso"
ORDER BY "DBASICOS"."codobra", "HPARTIDA"."codipart", "DPARTIDA"."codigo"

Apart of the meaning of the sentence, it shows an obvious sintax error: four parenthesis from left but six parenthesis from right.

I run this modified SQL in Oracle Application Express:

SELECT "DBASICOS"."codobra", "DBASICOS"."nombre", "HPARTIDA"."codipart", "HPARTIDA"."nombre", "HPARTIDA"."metrado", "HPARTIDA"."costo_unit", "HPARTIDA"."total", "DPARTIDA"."codigo", "DPARTIDA"."unidad", "DPARTIDA"."cuadrilla", "DPARTIDA"."cantidad", "DPARTIDA"."precio", "DPARTIDA"."total", "KRECURSO"."nombre"
FROM ((("ISCHOOF"."DBASICOS" "DBASICOS" INNER JOIN "ISCHOOF"."HPARTIDA" "HPARTIDA" ON "DBASICOS"."codobra" = "HPARTIDA"."codobra" ) INNER JOIN "ISCHOOF"."DPARTIDA" "DPARTIDA" ON "HPARTIDA"."codobra" = "DPARTIDA"."codobra" and "HPARTIDA"."codipart" = "DPARTIDA"."codipart") INNER JOIN "ISCHOOF"."KRECURSO" "KRECURSO" ON "DPARTIDA"."codobra" = "KRECURSO"."codobra" and "DPARTIDA"."codigo" = "KRECURSO"."recurso")
ORDER BY "DBASICOS"."codobra", "HPARTIDA"."codipart", "DPARTIDA"."codigo"

and run ok (12590 rows returned in 1,73 seconds).

From Crystal with exact the same database and relationships the SQL was:

SELECT "DBASICOS"."codobra", "DBASICOS"."nombre", "HPARTIDA"."codipart", "HPARTIDA"."nombre", "HPARTIDA"."metrado", "HPARTIDA"."costo_unit", "HPARTIDA"."total", "DPARTIDA"."codigo", "DPARTIDA"."cuadrilla", "DPARTIDA"."cantidad", "DPARTIDA"."precio", "DPARTIDA"."total", "KRECURSO"."nombre", "KRECURSO"."unidad", "KRECURSO"."precio"
FROM (("ISCHOOF"."DBASICOS" "DBASICOS" INNER JOIN "ISCHOOF"."HPARTIDA" "HPARTIDA" ON "DBASICOS"."codobra"="HPARTIDA"."codobra") INNER JOIN "ISCHOOF"."DPARTIDA" "DPARTIDA" ON ("HPARTIDA"."codobra"="DPARTIDA"."codobra") AND ("HPARTIDA"."codipart"="DPARTIDA"."codipart")) INNER JOIN "ISCHOOF"."KRECURSO" "KRECURSO" ON ("DPARTIDA"."codobra"="KRECURSO"."codobra") AND ("DPARTIDA"."codigo"="KRECURSO"."recurso")
ORDER BY "DBASICOS"."codobra", "HPARTIDA"."codipart", "DPARTIDA"."codigo"

and i can preview the report.

Would be possible to us modify and save the sql?
regards
Iván Schoof
PD.: now i can copy the sentence... this is an advance from previous release ... thanks

Vincent Oorsprong
9-May-2010, 02:08 AM
Ivan,

Thanks for the report, it has been logged in the help desk system. You should have received a notification message.

It is not possible to save and modify the SQL statement. Of course you can copy and save and modify with an external editor but you cannot have Visual Report Writer use this statement again.