I’m new to SQL. This statement looks for differences between two UID fields in the student table that are not quite the same – student.std_school_residence_id and student.std_district_service_id.
The student.std_school_residence_id links to the school table, which itself links to the district table (via school.sch_district_id). OTOH, student.std_district_service_id links directly to the district table (district.dist_district_id).
So the comparison on which I’m getting an error with this,
queryText = "SELECT student.std_student_id, "
queryText += "(SELECT school.sch_district_id FROM school WHERE school.sch_school_id = student.std_school_residence_id) "
queryText += "AS distResFmSchRes FROM student, school WHERE ";
if(distOfService_B) {
if(includeNulls_B) {
queryText += "distResFmSchRes IS DISTINCT FROM student.std_district_service_id";
} else {
queryText += "distResFmSchRes <> student.std_district_service_id";
}
}
The error I’m getting in the console is,
ERROR: column "distresfmschres" does not exist
Position: 183
Wrapped java.lang.RuntimeException: com.servoy.j2db.dataprocessing.DataException: ERROR: column "distresfmschres" does not exist
Position: 183 (/Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/browse_form.js#5761)
at /Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/browse_form.js:5761 (browse_distResNotDistService2)
at /Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/distResNotDistService_dlg.js:217 (onClick_btn_ok)
Does the SQL need to be rewritten?
Thank you,
Don
Hi Don,
Other than perhaps Sybase no SQL vendor allows SELECT list aliasses being re-used, it’s not conform the SQL standard to do so. You need to use the actual SQL used in the SELECT list.
So your code would look like this:
var queryText = "SELECT student.std_student_id, \
(SELECT school.sch_district_id FROM school WHERE school.sch_school_id = student.std_school_residence_id ) AS distResFmSchRes \
FROM student, school \
WHERE ";
if (distOfService_B) {
if(includeNulls_B) {
queryText += "(SELECT school.sch_district_id FROM school WHERE school.sch_school_id = student.std_school_residence_id ) IS DISTINCT FROM student.std_district_service_id";
} else {
queryText += "(SELECT school.sch_district_id FROM school WHERE school.sch_school_id = student.std_school_residence_id ) <> student.std_district_service_id";
}
}
Also how are you joining student with school? Right now you have a cross-join, not sure if you want that.
HI Robert,
So I don’t really need this part where I select from schools, since it is not used below?
... (SELECT school.sch_district_id FROM school WHERE school.sch_school_id = student.std_school_residence_id ) AS distResFmSchRes...
I’m intending inner join, but I’m testing for nulls in the “IS DISTINCT FROM” case. How is it that I’m using a cross join?
Thanks very much,
Don
Hi Don,
It’s a cross join because there is no real join criteria.
Essentially it’s the following SQL:
SELECT column, otherEleborateColumn
FROM student, school
And then perhaps some WHERE clause that is not really a join criteria.
So essentially you are creating a cartesian product here, a.k.a. a cross-join.
This means that each row of student is joined to every row of school.
So lets say you have 100 students and 100 schools then you end up with a resultset of 10,000 rows (100x100).
Also consider as a best practice that joins use the following notation:
SELECT *
FROM table1 JOIN table2 ON (table1.columnA=table2.columnB)
JOIN table3 ON (table2.columnC=table3.columnC)
WHERE table1.columnD = 'someCriteria'
In other words, don’t put the join criteria in the WHERE clause and IF it has to be then make it one that joins over real columns.
Hope this helps.
Hi Robert,
Okay, I think I’m a little clearer. But what if I am comparing two different school-type fields in student (std_school_residence_id and std_school_enrollment_id), both of which have associations (joins) to the school table, in terms of their districts? When I try something like this, it generates an error,
queryText = "SELECT student.std_student_id, school.sch_district_id FROM student "
if(includeNulls_B) {
queryText += "LEFT OUTER JOIN school"
} else {
queryText += "INNER JOIN school"
}
queryText += " ON (student.std_school_residence_id = school.sch_school_id) WHERE "
queryText += "student.tenant_id = \'" + globals.currentSELPA_id + "\' AND "
queryText += "school.sch_district_id "
if(includeNulls_B) {
queryText += " IS DISTINCT FROM "
} else {
queryText += " <> ";
}
queryText += "school.sch_district_id IN (SELECT school.sch_district_id FROM school WHERE \
school.sch_school_id = student.std_school_enrollment_id)";
The error generated is,
Wrapped java.lang.RuntimeException: com.servoy.j2db.dataprocessing.DataException: ERROR: operator does not exist: boolean = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 265 (/Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/browse_form.js#5784)
at /Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/browse_form.js:5784 (browse_distResNotDistService2)
at /Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/distResNotDistService_dlg.js:217 (onClick_btn_ok)
However, if I change this line:
queryText += "school.sch_district_id IN (SELECT school.sch_district_id FROM school WHERE \
to this:
queryText += "(SELECT school.sch_district_id FROM school WHERE \
…it runs. Why is the second one correct (or is it actually correct)?
Thank you,
Don
Hi Don,
The error states you try to use a condition in the WHERE clause where you compare a boolean value with a character varying (VARCHAR) value. PostgreSQL doesn’t have an operator that works with these 2 datatypes.
Or to quote the error:
ERROR: operator does not exist: boolean = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Looking at your SQL the problem is most likely the following:
school.sch_district_id <> school.sch_district_id
IN (SELECT school.sch_district_id FROM school WHERE school.sch_school_id = student.std_school_enrollment_id)
As you can see school.sch_district_id <> school.sch_district_id will result in a boolean (true/false) and then tries to see if this value is in the resultset of the subselect. And I assume your sch_district_id is in fact a VARCHAR.
Also instead of using <> I would use NOT IN (subselect).
By the way INNER and OUTER are in fact ‘noise words’ so you can actually drop them. JOIN is an INNER join and LEFT JOIN is an OUTER join.
Also I see you use prefixes on your table columns that make them unique to that table. In that case you can drop the tablename in front of it to make your SQL less verbose. ![:)]( "Smile")
So your code would then look like this:
queryText = "SELECT std_student_id, sch_district_id FROM student "
if(includeNulls_B) {
queryText += "LEFT JOIN "
} else {
queryText += "JOIN "
}
queryText += "school ON (std_school_residence_id = sch_school_id) "
queryText += "WHERE student.tenant_id = '" + globals.currentSELPA_id + "' AND "
queryText += "sch_district_id "
if(includeNulls_B) {
queryText += "IS DISTINCT FROM "
} else {
queryText += "NOT IN ";
}
queryText += "(SELECT sch_district_id FROM school WHERE sch_school_id = std_school_enrollment_id)";
Hope this helps.
That’s a very big help. It will no doubt also make things easier to debug. I have already made mistakes like using the table name more than once (“student.student.std_student_id”) or omitted the prefix (“student.student_id”). Thank you, Robert!
I’m not sure I can use “NOT IN” because of the other parts of the IF statement which pass through this statement also -?
For this same strand of SQL, there is another option to compare the district of the student’s school of residence (“std_school_residence_id”) with the district of the school where the service is provided (student_service.svc_school_id). This table is between the student and the school table, as a related many to both.
I can’t think of a way to phrase the SQL, and what I have generates errors (the svcMostRecent_B section). I have a feeling that I am somehow getting into circular reasoning, because I start with selecting std_student_id and end this phrase with a link to std_student_id (svc_student_id = std_student_id).
Can you tell me where I have gotten it wrong?
Thanks so much,
Don
if(schoolOfResidence_B) {
queryText = "SELECT std_student_id, sch_district_id FROM student "
if(includeNulls_B) {
queryText += "LEFT JOIN school"
} else {
queryText += "JOIN school"
}
queryText += " ON (std_school_residence_id = sch_school_id) WHERE ";
queryText += "student.tenant_id = \'" + globals.currentSELPA_id + "\' AND ";
queryText += "sch_district_id "
if(includeNulls_B) {
queryText += "IS DISTINCT FROM "
} else {
queryText += "<> ";
}
if(distOfService_B) {
queryText += "std_district_service_id";
} else if(schoolOfEnrollment_B) {
queryText += "(SELECT sch_district_id FROM school WHERE "
queryText += "sch_school_id = std_school_enrollment_id)";
} else if(otherDistrict_B) {
queryText += "std_district_other_id";
} else if(svcMostRecent_B) {
// TODO this part doesn't work,
// highest-ranking non-dropped service --
queryText += "(SELECT sch_district_id FROM school WHERE "
queryText += "sch_school_id IN (SELECT svc_school_id FROM student_service WHERE "
queryText += "(svc_school_id = sch_school_id) AND "
queryText += "((svc_drop_d IS NULL) OR (svc_drop_d::date >= DATE(CURRENT_DATE))) AND "
queryText += "(svc_student_id = std_student_id) "
queryText += "ORDER BY svc_service_rank_order ASC, svc_start_d DESC LIMIT 1))"
}
dsStdnt = databaseManager.getDataSetByQuery('selpa_mgr_sql',queryText,null,10000);
forms.student_browse.foundset.loadRecords(dsStdnt);
browse_showRelated();
}
Hi Don,
What are the errors you get ?
Also is there a specific reason you first fetch the data in a DataSet and then load a foundset? Is that filled DataSet used somewhere else ?
If not then I would load the SQL straight into the foundset so Servoy will load the data only once.
Hi Don,
Your last part of the query (the part that gives you errors) can also be rewritten to use a join instead of a subselect. Joins are almost always faster than subselects.
Also casting CURRENT_DATE to a date is not needed since that already returns a date.
Your SQL looks like this (without the many parentheses):
(
SELECT
sch_district_id
FROM
school
WHERE
sch_school_id IN
(
SELECT
svc_school_id
FROM
student_service
WHERE
svc_school_id = sch_school_id AND
(svc_drop_d IS NULL OR svc_drop_d::date >= CURRENT_DATE) AND
svc_student_id = std_student_id
ORDER BY
svc_service_rank_order ASC, svc_start_d DESC
LIMIT 1
)
)
Rewritten to use a JOIN:
(
SELECT
sch_district_id
FROM
school JOIN student_service ON (svc_school_id = sch_school_id)
WHERE
svc_student_id = std_student_id AND
(svc_drop_d IS NULL OR svc_drop_d::date >= CURRENT_DATE)
ORDER BY
svc_service_rank_order ASC, svc_start_d DESC
LIMIT 1
)
Hope this helps.
Hi Robert,
It works! Hooray!!!
There is a case I would like to allow for in this last clause, and I was wondering if it is feasible. The includeNulls_B option is a check box on a dialog, “Include blank vs. non-blank entries”. If std_school_residence_id is null, and there is no record available in the student_service table, then this query currently returns a match. From the user point of view, though, this is a null = null sort of case, which shouldn’t return a match. Is there some additional proviso that I could add so that this case would not return a match?
I’m guessing that I couldn’t do this query with Servoy commands, unless I ran at least two For() loops and accumulated pk values in an array. Is it correct that this approach would take much longer than the direct SQL approach?
Thanks so much for your help,
Don
Hi Robert,
Regarding loading the foundset directly, I get an error when I try it that way,
ERROR: subquery has too many columns
Position: 58
Wrapped com.servoy.j2db.dataprocessing.DataException: ERROR: subquery has too many columns
Position: 58 (/Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/browse_form.js#5793)
at /Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/browse_form.js:5793 (browse_distResNotDistService2)
at /Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/distResNotDistService_dlg.js:217 (onClick_btn_ok)
…but the dataset approach works fine.
Thank you,
Don