Group by or distinct in for-loop

Question

Hello, 
 
 Is it possible to make a for-loop with a SQL "group by" or "select distinct" statement? 
 Br, Carl

Nacho_Alferez · Accepted Answer

You can try it with the "For" instruction using the "Sql" keyword. Have a look at this link: http://online-help.sageerpx3.com/erp/wp-static-content/static-pages/en_US/v7dev/4gl_sql.html The advantage of using this option is that you can execute any kind of SQL sentences (DISTINCT, GROUP BY, Sub-Select, UNION, UNION ALL, PIVOT, etc.). I don't know if it's possible to execute a stored procedure that returns a rowset, I have to check it... but I don't think so. Disadvantage: Not too much. If you are programming a vertical development, sometimes you will need to create two different sql sentences: one in SQL-SERVER and another one in ORACLE. I show you an example: Local Char SQLTEX(250) SQLTEX = "SELECT DISTINCT FIELD_0 FROM TABLE" SQLTEX -= "WHERE XXX_0=1" If << CONDITION1 >> SQLTEX -= "AND YYY_0='" + MASK_FIELD + "'" Endif If << CONDITION2 >> SQLTEX -= "AND ZZZ_0='" + MASK_FIELD + "'" Endif For (Char FIELD(30)) From num$(GTYPDBA*2+1) Sql SQLTEX(1..2) As [ZSQL] # go through rows Next "GTYPDBA" is a global variable that stores the database type (2: SQL Server | 5: Oracle). I hope I have been able to help you. Regards Nacho Alférez

Sage X3

Group by or distinct in for-loop

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING