problem in render_sql()

Scheduler Javascript
#1

Hi,

I created one nested select statement with left joins, its not displaying the data. When i am removing nested SELECT its working properly.
is render_sql() will support nested SELECT statement?

Please give me a solution for this.

Regards,
Jobin K Joseph

#2

Hello,

Can you please provide you sql statement?

Right now i tried following:

$scheduler->render_sql( "SELECT event_user.event_user_id, event_user.event_id, event_user.user_id, user.username FROM event_user LEFT JOIN user ON event_user.user_id = user.user_id ", "event_user_id", "event_user_id, event_id, user_id, username" );
And it worked correctly.

Best regards,
Ilya

#3

I’m using PostgreSQL 8.3

String SQL = " SELECT "

	+ " DISTINCT(APPT.id) AS appt_id, "
	+ " APPT.Start_Date AS start_date, "
	+ " APPT.End_Date AS end_date, "
	+ " APPT.pi AS pi, "
	+ " APPT.fi AS fi, "
	+ " APPT.type AS type, "
	+ " APPT.status AS status, "
	+ " APPT.rec_type AS rec_type, "
	+ " APPT.event_pid AS event_pid, "
	+ " APPT.event_length AS event_length, "
	+ " COALESCE(TYP.Name, '') AS appt_type, "
	+ " COALESCE(STA.Name, '') AS appt_status, "
	+ " APPT.a_key AS a_key, "
	+ " COALESCE(APPT.pKey, -1) AS p_key, "
	+ " (PAT.first_name || ' ' || PAT.last_name) AS full_name, "
	+ " COALESCE(APPT.Notes, '') AS appt_notes, "

	+ " (SELECT (COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')) "
	+ " FROM name_table WHERE name_key = APPT.name_key LIMIT 1) AS p_full_name, "

	+ " (SELECT COALESCE(f_name, '') "
	+ " FROM f_name_table WHERE f_key = APPT.f_key LIMIT 1) AS s_name "

	+ " FROM "
	+ " tbl_Appt APPT "
	+ " LEFT JOIN Type TYP ON APPT.Sched_Type = TYP.Type_ID "
	+ " LEFT JOIN tbl_tatus STA ON APPT.Sched_Status = STA.Status_ID "
	+ " LEFT JOIN pat PAT ON APPT.pat_Key = PAT.pat_Key "

	+ " WHERE "
	+ " rpi IN (" + rpiList + ") "
	+ " AND spi IN(" + spiList + ") ";
	+ " AND APPT.a_Key = '" + aKey + "' ";
	
SchedulerConnector.render_sql(SQL, "appt_id","start_date,end_date,full_name,rec_type,event_pid,event_length,appt_type,appt_status,p_key,appt_notes,type,status,pi,fi,p_full_name,s_name");
#4

and also ERROR log contain

Error during data selecting
Invalid SQL: SELECT   DISTINCT(APPT.id) AS appt_id,  APPT.Start_Date AS start_date,  APPT.End_Date AS end_date,  APPT.pi AS pi,  APPT.fi AS fi,  APPT.type AS type,  APPT.status AS status,  APPT.rec_type AS rec_type,  APPT.event_pid AS event_pid,  APPT.event_length AS event_length,  COALESCE(TYP.Name, '') AS appt_type,  COALESCE(STA.Name, '') AS appt_status,  APPT.a_key AS a_key,  COALESCE(APPT.pKey, -1) AS p_key,  (PAT.first_name || ' ' || PAT.last_name) AS pat_full_name,  COALESCE(APPT.Notes, '') AS appt_notes,  (SELECT (COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')) FROM name_table WHERE  name_key = APPT.name_key
ERROR: syntax error at end of input

COALESCE() is working porperly, I’m using Nested SELECT.
render_sql() will support nested SELECT or not ?

#5

when I’m removing LIMIT 1 in SELECT its showing ERROR

ERROR: more than one row returned by a subquery used as an expression

I’m using connection pooling code to connect to PostgreSQL 8.3 database
SchedulerConnector Conn = new SchedulerConnector(con, DBType.PostgreSQL);

I found out that LIMIT is making problem,
how can i solve this problem ?
Please give me a solution.

#6

Hello,

  1. Locate db_common.php file and comment following line.
$sql= preg_replace("/[ \n\t]+limit[\n ,0-9]/i","",$sql);

Check if it helps.

  1. Also try to edit this part:

[code] + " FROM "

  • " tbl_Appt APPT "[/code]
    Change to:

[code] + " FROM "

  • " tbl_Appt APPT "[/code]

Best regards,
Ilya

#7

sorry my codes are in JAVA, I’m using dhtmlx java connector

#8

I commented

		//sql = limit_regex.split(sql)[0]; //drop limit part;

from DataRequest.java (inside DHTMLX java connector)
now its working, thanks for the information.
it will make any other issue in future?
please replay.

#9

This is a temporary solution. We will have to look into this issue and post update here as soon as possible.

Best regards,
Ilya

#10

Thanks Ilya.

#11

Hi,

When I can expect the updates? next release or as a patch?

Regards,
Jobin K Joseph

#12

Hello,

The format of the update (patch or changes in the next release) will be determined upon further investigation of the issue. I can’t comment on that yet.
Though results should be available on the next week.

Best regards,
Ilya

#13

Thanks for the information.

#14

Hello,

There are reasons for skipping the limit part in the SQL queries. Connector itself sets limits in case of dynamic loading and some others. So limits in the query and ones set in the code would not work together.

In your case if everything worked correctly so far you can leave this line commented.
Another (I believe more solid one) approach would be to work more closely with your database, e.g. create necessary views so LIMIT statements wouldn’t be used in a query.

I am sorry for the inconvenience and hope this clears the issue.

Best regards,
Ilya

#15

Thanks Ilya.