SQL syntax error on progress
8 October, 2009
We came across few SQL syntax errors, any suggestions?
Thanks,
--------------------------------------------------------------------------------
GL Transaction Listing
--------------------------------------------------------------------------------
Report SQL
SELECT DISTINCT "gl_analysis_code"."id_anal_code", "gl_analysis_code"."desc_anal_code", "gl_tran"."narr_tran", "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_tran"."date_tran" BETWEEN '2006-03-01' AND '2007-03-31')
GL Transaction Listing
General Ledger, 23/07/2007 4:35 PM
Error retrieving results
An error occurred retrieving the results from the database.
Please check the SQL syntax and try again.
GL Transaction Listing
--------------------------------------------------------------------------------
Report SQL
SELECT DISTINCT "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_analysis_code"."id_anal_code" = '110461')
Report SQL
SELECT DISTINCT "gl_analysis_code"."id_anal_code", "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_tran"."date_tran" BETWEEN '2005-04-01' AND '2006-03-31')
--------------------------------------------------------------------------------
Report SQL
SELECT DISTINCT "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_tran"."date_tran" BETWEEN '2005-01-01' AND '2006-03-31')
SELECT DISTINCT "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_tran"."date_tran" BETWEEN '2005-01-01' AND '2006-03-31' AND "gl_analysis_code"."id_anal_code" = '110461')
Thanks,
--------------------------------------------------------------------------------
GL Transaction Listing
--------------------------------------------------------------------------------
Report SQL
SELECT DISTINCT "gl_analysis_code"."id_anal_code", "gl_analysis_code"."desc_anal_code", "gl_tran"."narr_tran", "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_tran"."date_tran" BETWEEN '2006-03-01' AND '2007-03-31')
GL Transaction Listing
General Ledger, 23/07/2007 4:35 PM
Error retrieving results
An error occurred retrieving the results from the database.
Please check the SQL syntax and try again.
GL Transaction Listing
--------------------------------------------------------------------------------
Report SQL
SELECT DISTINCT "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_analysis_code"."id_anal_code" = '110461')
Report SQL
SELECT DISTINCT "gl_analysis_code"."id_anal_code", "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_tran"."date_tran" BETWEEN '2005-04-01' AND '2006-03-31')
--------------------------------------------------------------------------------
Report SQL
SELECT DISTINCT "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_tran"."date_tran" BETWEEN '2005-01-01' AND '2006-03-31')
SELECT DISTINCT "gl_tran_detail"."perd_tran", "gl_tran"."date_tran", "gl_tran_detail"."doc_ref", "gl_tran_detail"."value_tran" FROM "PUB"."gl_analysis_code" INNER JOIN "PUB"."gl_tran" ON ("gl_analysis_code"."id_anal_code" = "gl_tran"."id_anal_code") INNER JOIN "PUB"."gl_tran_detail" ON ("gl_tran"."id_glacc" = "gl_tran_detail"."id_glacc") WHERE ("gl_tran"."date_tran" BETWEEN '2005-01-01' AND '2006-03-31' AND "gl_analysis_code"."id_anal_code" = '110461')
RE: SQL Error on OpenEdge
We had a similar issue here also. The SQL is correct, but the error returned from the database is:
[Error Code: -210012, SQL State: HY000] [DataDirect][OpenEdge JDBC Driver][OpenEdge]
Column ColumnName in table PUB.TableName has value exceeding its max length or precision.
(You should be able to confirm you have the same error by looking through your Yellowfin logs).
This means that the data contained in ColumnName exceeds the length value specified for the column.
4GL code doesn't use this length, and can over-populate a column past its defined length.
SQL will return an error if it tries to retrieve a column where this has happened.
You will need to extend the SQL length of the column or truncate the data on the column where the length has been exceeded.
We had a similar issue here also. The SQL is correct, but the error returned from the database is:
[Error Code: -210012, SQL State: HY000] [DataDirect][OpenEdge JDBC Driver][OpenEdge]
Column ColumnName in table PUB.TableName has value exceeding its max length or precision.
(You should be able to confirm you have the same error by looking through your Yellowfin logs).
This means that the data contained in ColumnName exceeds the length value specified for the column.
4GL code doesn't use this length, and can over-populate a column past its defined length.
SQL will return an error if it tries to retrieve a column where this has happened.
You will need to extend the SQL length of the column or truncate the data on the column where the length has been exceeded.