Re: Standard Sub Select SQL query not working in 4D as expected

[Cameron Adams]

Hi,


you could try something like this  (the outer join is needed so all departments 
are included in the result and the order-by should list the results the way you 
want them).


select D.[DEPT_NAME], COUNT(*)

from   [DEPARTMENTS] D

   left outer join [STUDENTS] S on S.[DEPT_NO] = D.[DEPT_NO]

group by D.[DEPT_NAME]

order by 2 desc, 1



As to the differences between 4D SQL and other vendors, I just write my query 
and see what 4D objects to.



Cam Adams Programmer
[https://s3-ap-southeast-2.amazonaws.com/genie-email-sigs/Gensol_Logo_new.png]
P (07) 3870 4085 F (07) 3870 4462 W geniesolutions.com.au

This email has been sent from Genie Solutions. The information in this email is 
confidential and may be legally privileged. It is intended solely for the 
addressee. Access to this email by anyone else is unauthorised. If you are not 
the intended recipient, any disclosure, copying, distribution or any action 
taken or omitted to be taken in reliance on it, is prohibited and may be 
unlawful. When addressed to our customers any opinions or advice contained in 
this email are subject to the terms and conditions expressed in the Customer 
Service Level Agreement.


[https://s3-ap-southeast-2.amazonaws.com/genie-email-sigs/gensol_Tri.png]




From: 4D_Tech <4d_tech-boun...@lists.4d.com> on behalf of Balinder Walia 

Sent: Wednesday, 21 September 2016 07:47
To: 4D iNug Technical
Subject: Standard Sub Select SQL query not working in 4D as expected

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:


http://www.4d.com/dtd/2007/base.dtd; >



















http://www.4d.com/dtd/2007/base.dtd; >
























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
**
**
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
**

Standard Sub Select SQL query not working in 4D as expected

[Balinder Walia]

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:


http://www.4d.com/dtd/2007/base.dtd; >



















http://www.4d.com/dtd/2007/base.dtd; >
























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
**