select group by

[AlEr]

Цитата:

"As you can see of the SQL statement below which is the result of your code generated to the Oracle database, the SELECT'ED fields are being LOWERCASED. This is due to the nature of the GROUP BY and using functional indexes, that we need to use a function on the fields for the optimizer to be able to use the functional index, if defined.
Using GROUP BY with Axapta 3.0/Oracle, you need to do some UPPERCASE string manipulation on the variables which you want to show/print.

SELECT SUBSTR(NLS_LOWER(A.ACCOUNTNUM),1,10),SUBSTR(NLS_LOWER(A.DIMENSION),1,10),SUBSTR(NLS_LOWER(A.DIMENSION2_),1,10),SUBSTR(NLS_LOWER(A.DIMENSION3_),1,10),A.TRANSTYPE,SUBSTR(NLS_LOWER(B.ACCOUNTNUM),1,10),SUBSTR(NLS_LOWER(B.CURRENCYCODE),1,3),SUBSTR(NLS_LOWER(B.ACCOUNTNAME),1,60)
FROM LEDGERTRANS A,LEDGERTABLE B
WHERE (SUBSTR(NLS_LOWER(A.DATAAREAID),1,3)=NLS_LOWER('DMO'))
AND (SUBSTR(NLS_LOWER(B.DATAAREAID),1,3)=NLS_LOWER('DMO'))
GROUP BY SUBSTR(NLS_LOWER(A.ACCOUNTNUM),1,10),SUBSTR(NLS_LOWER(A.DIMENSION),1,10),SUBSTR(NLS_LOWER(A.DIMENSION2_),1,10),SUBSTR(NLS_LOWER(A.DIMENSION3_),1,10),A.TRANSTYPE,SUBSTR(NLS_LOWER(B.ACCOUNTNUM),1,10),SUBSTR(NLS_LOWER(B.CURRENCYCODE),1,3),SUBSTR(NLS_LOWER(B.ACCOUNTNAME),1,60)
ORDER BY SUBSTR(NLS_LOWER(A.ACCOUNTNUM),1,10),SUBSTR(NLS_LOWER(A.DIMENSION),1,10),SUBSTR(NLS_LOWER(A.DIMENSION2_),1,10),SUBSTR(NLS_LOWER(A.DIMENSION3_),1,10),A.TRANSTYPE,SUBSTR(NLS_LOWER(B.ACCOUNTNUM),1,10),SUBSTR(NLS_LOWER(B.CURRENCYCODE),1,3),SUBSTR(NLS_LOWER(B.ACCOUNTNAME),1,60)"

Или другой workaround - выводить print Table1::find().Field1; внутри цикла...

С уважением,
ALER