All Forums Database
mjasrotia 66 posts Joined 08/11
20 Mar 2012
Defining two derived tables using WITH clause ??

Following is a way to define a derived table using a WITH clause in the starting of the query and using it in the query thereafter. Is there a way to define more than one derived tables using WITH clause.

 

I understand the use of VT, GT and other temp tables but still need to know if there is a way to do that using WITH clause.

 

WITH TEMP_TAB(a,b,c)

AS

(Select

a,b,c

from table)

 

Thanks

Cyberness 2 posts Joined 01/10
20 Mar 2012

>Is there a way to define more than one derived tables using WITH clause.

No, just another one stupid restriction.

fnewbrough 17 posts Joined 05/11
20 Mar 2012

Unfortunately not. We encountered this issue on a DB2 conversion. It is a much cleaner way to work with derived tables. It would be nice if Teradata supported it.

dnoeth 4628 posts Joined 11/04
20 Mar 2012

It's supported in TD14

Dieter

Dieter

mjasrotia 66 posts Joined 08/11
21 Mar 2012

Thanks Dieter,

Thats a great enhancement !!

fnewbrough 17 posts Joined 05/11
21 Mar 2012

That is great news Dieter. Thanks for posting. My current client will love this information.

a2kz 16 posts Joined 05/13
26 Aug 2014

Hi Dieter,
Tried the below syntaz but it's not working
WITH TEMP_TAB(a,b,c)
AS
(Select
a,b,c
from table)
WITH TEMP_TAB2(a,x,y)
AS
(Select
a,x,y
from table)
 
Sel derived_columns from TEMP_TAB inner join TEMP_TAB2
Can you please give me the link of teradata manual where i can find more details.
Thanks,
Ambuj

dnoeth 4628 posts Joined 11/04
26 Aug 2014

Replace the 2nd WITH with a comma:

WITH TEMP_TAB(a,b,c)
AS
(Select
a,b,c
from table)
,TEMP_TAB2(a,x,y)
AS
(Select
a,x,y
from table)
 
Sel derived_columns from TEMP_TAB inner join TEMP_TAB2

 

Dieter

a2kz 16 posts Joined 05/13
26 Aug 2014

Thank you so mcuh Dieter.

andydoorey 35 posts Joined 05/09
27 Aug 2014

On a related subject, is it possible to insert this data into a table?
I wrote a pretty complex query recently using the 'with' syntax, but when I tried to use it as an insert/select query I couldn't get it to work, and had to create volatile tables instead.  Should it be possible to do so?

dnoeth 4628 posts Joined 11/04
27 Aug 2014

This should work:

INSERT INTO target
WITH cte ....
SELECT * FROM cte

 

Dieter

eddie.yasi 2 posts Joined 02/12
11 Sep 2014

One other question: is it possible to have the second WITH clause refer to the contents of the first WITH clause?  I can define two temp tables using the WITH clauses and refer to both in the final SELECT statement, but if I try to refer to the first temp table in the second temp tables definition it doesn't work, it says the first table doesn't exist.
This works:

WITH todayDate (currDate) as

( select current_date),

yesterdayDate (yestDate) as

    ( select current_date - 1)

select yestDate - currDate dateDiff

from todayDate

join yesterdayDate

on 1 = 1;

 

This does not:

 

WITH todayDate (currDate) as

( select current_date),

yesterdayDate (yestDate) as

    ( select currDate - 1 from todayDate)

select yestDate - currDate dateDiff

from todayDate

join yesterdayDate

on 1 = 1;

 

Is there any way I can use the first WITH clause's results in the second?  That's a handy way to 'accumulate' results.

 

Thanks!

 

Santanu84 122 posts Joined 04/13
11 Sep 2014

Hi
See if this is helpful. I am sure Dieter will give us something more effective.

WITH YESDATE (YDT, CDT) AS

(

SEL CDT - 1 , CDT

FROM

( SEL CURRENT_DATE AS CDT ) TODATE

)

SEL CDT - YDT AS DAYDIFF

FROM YESDATE

;

 

Thanks

Santanu

dnoeth 4628 posts Joined 11/04
11 Sep 2014

Hi Santanu,
this is a stupid bug in the implementation, you need to specify the CTEs in a wrong order:

WITH 
yesterdayDate (yestDate) as
    ( select currDate - 1 from todayDate),

todayDate (currDate) as
( select current_date)

select yestDate - currDate dateDiff
from todayDate
join yesterdayDate
on 1 = 1;

 

Dieter

Santanu84 122 posts Joined 04/13
11 Sep 2014

Thanks Dieter for your update.
Santanu

Moutusi 38 posts Joined 03/13
24 Dec 2014

 Hi Dieter,
I need to define two derived tables using WITH clause but one will be recursive. Like below:
WITH RECURSIVE TEMP_TAB(a,b,c) AS(Select a,b,c from T1 union select a,b, c+1 from TEMP_TAB)
WITH TEMP_TAB2(a,x,y) AS(Select a,x,y from T2)
Sel A,X,Y from TEMP_TAB inner join TEMP_TAB2
Is it possible if I replace 2nd WITH with comma?

Thanks,
Moutusi

ashk660 10 posts Joined 10/12
05 Mar 2015

Hi All, I am getting below syntax erro while using with clause.

Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'with' keyword.'.

 

 

CREATE PROCEDURE DB.test
  (
  IN  p_sequence         integer,
  IN  p_job_name         varchar(256),
  IN  p_task_name        varchar(256),
  IN  p_job_id           integer,
  IN  p_task_id          integer,
  OUT p_return_msg       varchar(256),
  OUT p_status           integer
  )
BEGIN

  --=====================================================
  -- Control variables used in most programs
  --=====================================================
  DECLARE v_msgtext         varchar(255);  -- Text for audit_trail
  DECLARE v_sql             varchar(255);  -- Text for SQL statements
  DECLARE v_set             integer;       -- commit set
  DECLARE v_analyze_flag    integer;       -- analyze flag
  DECLARE v_step            integer;       -- return code
  DECLARE v_update_count    integer;       -- no of records updated
  DECLARE v_insert_count    integer;       -- no of records inserted
  DECLARE v_count           integer;       -- General counter
  DECLARE v_sql_code        integer;       -- SQL Error Code for Audit Trail
  DECLARE v_sql_error       varchar(255);  -- SQL Error Code for Audit Trail as varchar

  --=====================================================
  -- Variables
  --=====================================================
  DECLARE v_dss_update_time        timestamp;   -- Used for date insert

  --=====================================================
  -- Exceptions
  --=====================================================
  DECLARE EXIT HANDLER
    FOR SQLEXCEPTION
    BEGIN
      SET v_sql_code = SQLCODE;
      LOCK ROW FOR ACCESS
      SELECT ErrorText
      INTO   :v_sql_error
      FROM   dbc.ErrorMsgs
      WHERE  ErrorCode = :v_sql_code;
      SET v_msgtext = 'Unhandled Exception in test. '||
        ' Step ' || CAST(v_step AS VARCHAR(64)) ||
        '   SQL Error Code: ' || CAST(v_sql_code AS VARCHAR(10)) || ' - ' || v_sql_error;
      SET p_return_msg = v_msgtext;
      CALL [METABASE].WsWrkAudit('F', :p_job_name, :p_task_name, :p_sequence
        , :v_msgtext, :v_sql_code, :v_sql_error, :p_task_id, :p_job_id);
      SET p_status = -3;
    END;

  --=====================================================
  -- Main
  --=====================================================
  SET v_step = 100;
  SET v_dss_update_time = CURRENT_TIMESTAMP;
  SET v_update_count = 0;
  SET v_insert_count = 0;

  --=====================================================
  --Insert custom code here.
  --Information can be written to the audit log via the 
  --procedure WsWrkAudit, and to the detail log via WsWrkError
  --*****************************************************
  
  with created_raw(fact_dt, teammemberkey, document_create_count) as
	(
        SELECT Cast(Cast(fact_dt AS DATE) AS TIMESTAMP(3)) AS fact_dt,
                 creator_teammemberkey                       AS teammemberkey,
                 1                                           AS document_create_count
        FROM   [km_article_create_fact]
        WHERE  fact_dt >= :cutoff_date
 
       ),
  created_grouped(fact_dt,teammemberkey,document_create_count) as
  (
  SELECT fact_dt,
         teammemberkey,
         Sum(document_create_count) AS document_create_count
  FROM   created_raw
  GROUP BY fact_dt, teammemberkey)
       
  insert into [st_km_teammember_day_fact]
	(	fact_dt,
		teammemberkey,
		articles_created )
	select
		fact_day,
		teammemberkey,
		document_create_count
	from
 created_grouped;
--E--line 103-- SPL1027:E(L103), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'with' keyword.'.
		

  --*****************************************************
  --End of custom code.
  --=====================================================

  SET v_step = 300;

  --=====================================================
  -- All Done report the results
  --=====================================================
  -- Work out the return message
  SET p_status = 1;
  SET v_msgtext = 'test completed successfully.  '
    || CAST(v_insert_count AS VARCHAR(64)) || ' records inserted.  '
    || CAST(v_update_count AS VARCHAR(64)) || ' records updated.';
  SET p_return_msg = v_msgtext;
  CALL [METABASE].WsWrkAudit('S', :p_job_name, :p_task_name, :p_sequence
    , :v_msgtext, :v_sql_error, :v_sql_error, :p_task_id, :p_job_id);

END;

 

 

Ashish

dnoeth 4628 posts Joined 11/04
07 Mar 2015

Hi Ashish,
your syntax is wrong, it's INSERT INTO ... WITH SELECT ... instead of WITH SELECT ... INSERT INTO ...

Dieter

ashk660 10 posts Joined 10/12
09 Mar 2015

Thanks Dieter. 
Could you please tell me how I can use this with Update statement?
Regards,
Ashish

Ashish

dnoeth 4628 posts Joined 11/04
09 Mar 2015

Hi Ashish,
afaik you can't use WITH in an Update.

Dieter

ashk660 10 posts Joined 10/12
12 Mar 2015

Thanks Dieter.

Ashish

You must sign in to leave a comment.