>Hi all
>
>I must say I had been wanred about the new ENGINEBEHAVIOR but this is the first I have encountered. I was required to add 2 more fields to the GROUP BY for it to function as per the new behaviour. No problem, but what I cannot visualize how this can really affect my result set. And will this behaviour also affect SQL Pass Throughs?
>
>Please advise.
>
>This following is the original SQL
>

	SELECT iItemID, SUM(bQty - bClrQty) AS bQty, iSID, cBk, iNo ;
>		FROM TempQuotUnClr ;
>		WHERE (bQty - bClrQty) # 0 ;
>		GROUP BY iItemID, iSID ;
>		ORDER BY cBk, iNo, iItemID ;
>		INTO CURSOR QuotUnClrList

>
>and this is the new one with the additional fields in GROUP BY
>

	SELECT iItemID, SUM(bQty - bClrQty) AS bQty, iSID, cBk, iNo ;
>		FROM TempQuotUnClr ;
>		WHERE (bQty - bClrQty) # 0 ;
>		GROUP BY iItemID, iSID, cBk, iNo ;
>		ORDER BY cBk, iNo, iItemID ;
>		INTO CURSOR QuotUnClrList

>
>UPDATE: And how will this one fare in the same situation, though I have not encountered it yet.
>

SELECT MAX(iSrNo) + 1 AS iNextNo ;
>	FROM v_LKmAccounts ;
>	WHERE iPID = liPID ;
>	GROUP BY iPID ;
>	INTO CURSOR Temp

Try this and you will see the difference:

CREATE CURSOR TempQuotUnClr (iItemID I, bQty N(10,2), bClrQty N(10,2), iSID I, cBk C(20), iNo I)
INSERT INTO TempQuotUnClr VALUES (1, 10, 20, 1, [ccc], 1)
INSERT INTO TempQuotUnClr VALUES (1, 20, 30, 1, [bbb], 1)
INSERT INTO TempQuotUnClr VALUES (1, 40, 50, 1, [ccc], 2)

SET ENGINEBEHAVIOR 70
SELECT iItemID, SUM(bQty - bClrQty) AS bQty, iSID, cBk, iNo ;
        FROM TempQuotUnClr ;
        WHERE (bQty - bClrQty) # 0 ;
        GROUP BY iItemID, iSID ;
        ORDER BY cBk, iNo, iItemID ;
        INTO CURSOR QuotUnClrList

BROWSE NORMAL

SET ENGINEBEHAVIOR 90
SELECT iItemID, SUM(bQty - bClrQty) AS bQty, iSID, cBk, iNo ;
        FROM TempQuotUnClr ;
        WHERE (bQty - bClrQty) # 0 ;
        GROUP BY iItemID, iSID, cBk, iNo ;
        ORDER BY cBk, iNo, iItemID ;
        INTO CURSOR QuotUnClrList
BROWSE NORMAL

In SPT (if you ask for data from SQL Server or any other RDB) you have seen the new behaviuor already. Because in all RDBMS you always must put ALL non agregate fields in GROUP BY.