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:[email protected] **********************************************************************
