Guys,

I have a SQL sub select query which works in SQLite DB and I have cloned
the same DB into 4Dv15.2 DB as the following structure shows:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE base SYSTEM "http://www.4d.com/dtd/2007/base.dtd"; >
<base>
<table name="Departments" uuid="8CBD8A7D5F61436BAFB704188B209F2A" id="13"
prevent_journaling="true">
<field name="DEPT_ID" uuid="2FD49951AF784E37AEB6E1DEDB5A9761" type="4"
unique="true" id="1">
<field_extra/>
</field>
<field name="DEPT_NAME" uuid="04EF7B4537014495AD0EB727F054BDC4" type="10"
limiting_length="80" id="2">
<field_extra/>
</field>
<primary_key field_name="DEPT_ID"
field_uuid="2FD49951AF784E37AEB6E1DEDB5A9761"/>
<table_extra>
<editor_table_info fields_ordering="1" displayable_fields_count="7">
<color red="255" green="255" blue="255" alpha="0"/>
<coordinates left="1392.43359375" top="71.58203125" width="170.30078125"
height="192.00390625"/>
</editor_table_info>
</table_extra>
</table>
</base>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE base SYSTEM "http://www.4d.com/dtd/2007/base.dtd"; >
<base>
<table name="Students" uuid="8CB0D58F50CD4341864846D8922F32E6" id="14"
prevent_journaling="true">
<field name="STUDENT_ID" uuid="E539618DB44648D6BAF6BB73C5929A9E" type="4"
unique="true" id="1">
<field_extra/>
</field>
<field name="STUDENT_NAME" uuid="89FB29C342B54140A309AD4320DC7F13"
type="10" limiting_length="80" id="2">
<field_extra/>
</field>
<field name="GENDER" uuid="DFA84F3E5D6C446094DA9169FB62ACB6" type="10"
limiting_length="80" id="3">
<field_extra/>
</field>
<field name="DEPT_ID" uuid="BDD20CEE2B9A4F11B9BB93477D0EA825" type="4"
id="4">
<field_extra/>
</field>
<primary_key field_name="STUDENT_ID"
field_uuid="E539618DB44648D6BAF6BB73C5929A9E"/>
<table_extra>
<editor_table_info fields_ordering="1" displayable_fields_count="9">
<color red="255" green="255" blue="255" alpha="0"/>
<coordinates left="1676.79296875" top="64.98046875" width="195.40625"
height="231.62109375"/>
</editor_table_info>
</table_extra>
</table>
</base>

When run the same query in 4D SQL it doesn't work:

*SELECT* Departments*.*DEPT_NAME*, (**SELECT* COUNT*(*) **FROM* Students
*WHERE* Departments*.*DEPT_ID=Students*.*DEPT_ID*) **AS* StudentCount *FROM*
Departments *ORDER* *BY* StudentCount *DESC**, *Departments*.*DEPT_NAME

I get Error when executing the method "_Test7" at line number 12

Generic parsing error. Parsing failed in or around the following substring
interval - ( 204, 210 ) - ...E, (SELECT CO...

Error code: 1301
Generic parsing error. Parsing failed in or around the following substring
interval - ( 204, 210 ) - ...E, (SELECT CO...
component: 'SQLS'
task -27, name: 'P_4'


I believe the SQL syntax for 4D is different than other vendors. What are
the differences in SQL in 4D than standard SQLs?

This is what I have to accomplish:

A university uses 2 data tables, Students and Departments, to store data
about its students and the departments associated with each major. Write a
query to print the respective department name and number of students
majoring in each department for all departments in the Departments table
(even ones with no current students). Sort your results by descending
number of students; if two or more departments have same number of
students, then sort those departments alphabetically by department name.

Help!!!
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**********************************************************************

Reply via email to