Delete from subquery in TPT

Tools & Utilities
Enthusiast

Delete from subquery in TPT

Hi everyone,

 

I am performing a .tpt job where the first step must apply the following sentence:

 

DELETE FROM TABLE1 WHERE DATE1 = (SELECT DISTINCT DATE2 FROM TABLE2);

 

This sentence works fine in SQL Assistant, but I get a syntax error when executing in TPT via the DDL_OPERATOR.

 

Any idea?

7 REPLIES 7
Teradata Employee

Re: Delete from subquery in TPT

Is the syntax error from TPT or from the database? (What is the actual message text?)

 

If the actual query contains single quotes, remember they must be escaped as two in a row since the entire SQL statement is a quoted string in TPT.

Enthusiast

Re: Delete from subquery in TPT

It seems that the syntax error comes from TPT, it shows the following message:

 

DDL_OPERATOR: TPT10508: RDBMS error 3706: Syntax error: expected something between the word 'DATE1' and '='.

 

The .tpt part related to this first step is written in the following way:

 

STEP FIRST
(
   APPLY
     ('DELETE FROM TABLE1 WHERE DATE1 = (SELECT DISTINCT DATE2 FROM TABLE2);')
         TO OPERATOR (DDL_OPERATOR );
);

 

I have tried this statement as above and also using double quotes in the following way:

 

STEP FIRST
(
   APPLY
     ('DELETE FROM TABLE1 WHERE DATE1 = "(SELECT DISTINCT DATE2 FROM TABLE2)";')
         TO OPERATOR (DDL_OPERATOR );
);

 

However, it reports the same error and it does not work, but the SQL statement DELETE FROM TABLE1 WHERE DATE1 = (SELECT DISTINCT DATE2 FROM TABLE2 works completely fine in Teradata SQL Assistant.

 

Thanks in advance.

Ambassador

Re: Delete from subquery in TPT

I doubt that DELETE FROM TABLE1 WHERE DATE1 = (SELECT DISTINCT DATE2 FROM TABLE2); works completely fine in Teradata SQL Assistant. Unless there's only a single row returned by the subquery it will fail with a [3669] More than one value was returned by a subquery.

 

Try replacing = with IN.

 

Enthusiast

Re: Delete from subquery in TPT

Yesterday, I checked DELETE FROM TABLE1 WHERE DATE1 = (SELECT DISTINCT DATE2 FROM TABLE2); with a single row, in order to verify the syntax in Teradata SQL Assistant and it worked completely fine (even though I have checked it again and it is right).

 

On the other hand, I have also checked DELETE FROM TABLE1 WHERE DATE1 IN (SELECT DISTINCT DATE2 FROM TABLE2); in Teradata SQL Assistant with a table endowed with multiple values and it also works fine, as expected. Nevertheless, I still get the same syntax error in TPT:

 

DDL_OPERATOR: TPT10508: RDBMS error 3706: Syntax error: expected something between the word 'DATE1' and the 'IN' keyword.

 

I have tried by adding in the select distinct statement double quotes " ", two single quotes '' '', single quotes ' ', percentage % %, etc, but does not work.

 

Thank you again.

Enthusiast

Re: Delete from subquery in TPT

I have just solved the error. The problem was due to the introduction of a variable within TPT, which was wrongly separated by myself in the .tpt job:

 

'DELETE FROM ' || @TABLE1 || 'WHERE DATE1 = (SELECT DISTINCT DATE2 FROM TABLE2); 

 

when the appropriate sentence must be:

 

'DELETE FROM ' || @TABLE1 || ' WHERE DATE1 = (SELECT DISTINCT DATE2 FROM TABLE2); 

 

Thank you very much for your help and apologies for the misunderstanding, it was very simple.

 

Best regards.

Teradata Employee

Re: Delete from subquery in TPT

Just for future reference, when asked whether the error was a TPT error or a DBS error (and the answer was "TPT error"), the error was always a DBS error, as in:

 

RDBMS error 3706: Syntax error: expected something between the word 'DATE1' and the 'IN' keyword.

 

Note, how TPT is reporting the DBS error.

-- SteveF
Highlighted
Enthusiast

Re: Delete from subquery in TPT

Yes, thank you very much feinholz for your note.

 

Best regards.