ORDER BY cannot be used in sub queries

General
Enthusiast

ORDER BY cannot be used in sub queries

Hi,

Recently our TD system got upgraded to Teradata 14.10.0206 from Teradata 13.11. After the upgradation, I can see an unusual behavior.

the script never used to fail previously, but now its failing after the upgradation.

Below is the scenario:

CREATE VOLATILE TABLE test1
(
col1 VARCHAR(8) CHARACTER SET UNICODE NOT CASESPECIFIC,
col2 INTEGER,
col3 VARCHAR(8) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX ( col1 )
ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE test2
(
col1 VARCHAR(8) CHARACTER SET UNICODE NOT CASESPECIFIC,
col2 INTEGER,
col3 VARCHAR(8) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX ( col1 )
ON COMMIT PRESERVE ROWS;

INSERT INTO test1 ('abc',1,'def');
INSERT INTO test1 ('def',1,'ghi');
INSERT INTO test1 ('abc',2,'def');
INSERT INTO test1 ('def',2,'ghi');

INSERT INTO test2 SELECT * FROM test1 ORDER BY 1,2;

 

The above insert statement into test2 from test1 (using ORDER BY clause) isfailing with the below error:

INSERT Failed. 3706: Syntax error: ORDER BY is not allowed in subqueries.

 This query never used to fail in the previous version of teradata but is failing in the latest version of TD. Kindly help me on this situation and let me know if this is a bug in TD 14.

Regards,

Soumik

 

 

Tags (1)
4 REPLIES
Junior Contributor

Re: ORDER BY cannot be used in sub queries

Hi Soumik,

this query should have failed in every TD release, there's no ORDER BY allowed in an INSERT/SELECT (unless there's a TOP, too).

If it worked before it was a bug, now it's correct :-)

Enthusiast

Re: ORDER BY cannot be used in sub queries

Thanks Dieter!

Could you please help me the reason for failure of the below script as its related to my posted question:

INSERT INTO HR_STG_T.PstnToEmpl_WT (PstnCd, effper, prsnlNbr)
WITH SOURCE(pstncd, prsnlNbr,  leftper, effper, rightper)
AS
(
SELECT pstncd
, CAST(prsnlNbr AS VARCHAR(20)) AS PrsnlNbr
, CASE WHEN BEGIN(effper) = MIN(BEGIN(effper)) OVER (PARTITION BY pstncd)

            AND BEGIN(effper) <> DATE '0001-01-01'
            THEN
                            PERIOD(DATE'0001-01-01', BEGIN(effper) )
            ELSE
                            NULL
            END AS leftper, effper

,  CASE WHEN END(effper) = MIN(BEGIN(effper)) OVER (PARTITION BY pstncd ORDER BY BEGIN(effper)  ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
            OR END(effper) = COALESCE(MIN(BEGIN(effper)) OVER (PARTITION BY pstncd ORDER BY BEGIN(effper) ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
, DATE'9999-12-31')
            OR END(effper)=DATE '9999-12-31'
            THEN
                            NULL
            ELSE
                            PERIOD(END(effper),COALESCE(MIN(BEGIN(effper))
OVER (PARTITION BY pstncd ORDER BY BEGIN(effper) ROWS BETWEEN 1 FOLLOWING
AND 1 FOLLOWING ), DATE'9999-12-31'))                   
            END AS rightper
FROM  HR_STG_T.PstntoEmpl_DtCrctd_WT
)

SELECT pstncd, effper, CAST(PrsnlNbr AS VARCHAR(20)) AS PrsnlNbr
FROM SOURCE

UNION ALL

SELECT pstncd, leftper, CAST(NULL AS VARCHAR(20)) AS PrsnlNbr
FROM SOURCE
WHERE leftper IS NOT NULL

UNION ALL

SELECT pstncd, rightper, CAST(NULL AS VARCHAR(20)) AS PrsnlNbr
FROM SOURCE
WHERE rightper IS NOT NULL
ORDER BY 1,2
;

Above query never failed previously, but after the upgradation, this query has started failing with the same error - ORDER BY cannot be used in subqueries.

Kindly help.

Regards,

Soumik

Junior Contributor

Re: ORDER BY cannot be used in sub queries

This is exactly the same, remove ORDER BY, it is and always was absolutely useless for an INSERT.

Enthusiast

Re: ORDER BY cannot be used in sub queries

Thanks for your help, Dieter! :)

Regards,

Soumik