trying to insert into a log table from a select statement that can return null or multiple rows

General
Enthusiast

trying to insert into a log table from a select statement that can return null or multiple rows

Hello.

I want to do something like:

INSERT INTO db.ExecLog 
((
SELECT TRIM(err) || ': ' || TRIM(CAST(COALESCE(CNT,'0') AS VARCHAR(50)))
FROM db.admin_5_CLDRptYr_errV WHERE CNT>0),
CURRENT_TIMESTAMP); -- casting cnt as varchar(10) stripped last digit from 5 digit numbers

which works fine as long as one error is inserted.

 

I figured out if there are no errors, I need to do something like:

INSERT    INTO db.ExecLog
    SELECT d, CURRENT_TIMESTAMP
    FROM        (SELECT
            CASE WHEN                ((SELECT err FROM    db.admin_5_CLDRptYr_errV WHERE    CNT>0) 
                IS NULL ) THEN 'errors: 0'
            ELSE
                (SELECT TRIM(err) || ': ' || TRIM(CAST(CNT AS VARCHAR(50)))
                FROM    db.admin_5_CLDRptYr_errV 
                WHERE    CNT>0)
            END AS d) z

which I believe will work if there's zero or one error, or it will return the first error and not all of them if
there's more than one.

I need it to work for all the errors returned from my view. I'm thinking there's no way around using a cursor
to iterate through each error in the view and do the insert. The examples of cursors doing inserts I'm finding online have responses
like "don't use a cursor for this, you don't need it."

The couple examples I copied from and tried to modify seemed to be using different syntax:
 FOR errs AS curs CURSOR FOR SELECT TRIM(err) || ': ' || TRIM(CAST(CNT AS VARCHAR(50))) d
                FROM    db.admin_5_CLDRptYr_errV 
                WHERE    CNT>0
 DO
    INSERT INTO errs.d ,CURRENT_TIMESTAMP;
-- no end DO? or is it not needed if it's only one statement, in which case it would use BEGIN/END? END FOR; ------------- another example I modified
DECLARE ed VARCHAR(200); DECLARE multiErrs CURSOR FOR SELECT TRIM(err) || ': ' || TRIM(CAST(CNT AS VARCHAR(50))) d FROM db.admin_5_CLDRptYr_errV WHERE CNT>0 OPEN multiErrs; WHILE(1=1)DO -- not sure if I can avoid having a condition here FETCH multiErrs INTO ed; INSERT INTO amhdprod.ExecLog(execDesc, LastUpdated) VALUES(ed, CURRENT_TIMESTAMP); End while CLOSE multiErrs;

 So I want to insert 2 field values in a table, one is a description, the second is always CURRENT_TIMESTAMP.  The view determining the description can return null, one, or multiple rows.

Can I do this without using a cursor and if so, how?

 

Thanks for your help.


Accepted Solutions
Teradata Employee

Re: trying to insert into a log table from a select statement that can return null or multiple rows

Of course you can. Just include a column list with the INSERT/SELECT like you do with INSERT VALUES:

INSERT INTO db.ExecLog (execDesc) 
	SELECT 
		TRIM(err) || ': ' || TRIM(CNT)
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	UNION ALL
	SELECT 
		'error count: 0'
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	HAVING 
		COUNT(*)=0
1 ACCEPTED SOLUTION
8 REPLIES
Teradata Employee

Re: trying to insert into a log table from a select statement that can return null or multiple rows

Instead of INSERT with a value list that includes a scalar subquery, do INSERT / SELECT.

For the "no errors" case, either use a separate statement or UNION ALL:

 

By the way, the issue you worked around using VARCHAR(50)  is due to implicit type conversion triggered by different data types in COALESCE.

COALESCE(TRIM(CNT),'0') or TRIM(COALESCE(CNT,0)) will not have this problem.

INSERT INTO db.ExecLog 
SELECT err || ': ' ||TRIM(COALESCE(CNT,0)), CURRENT_TIMESTAMP
FROM db.admin_5_CLDRptYr_errV WHERE CNT>0
UNION ALL
SELECT 'errors:0', CURRENT_TIMESTAMP
FROM db.admin_5_CLDRptYr_errV WHERE CNT>0
HAVING COUNT(*)=0;  

 

Enthusiast

Re: trying to insert into a log table from a select statement that can return null or multiple rows

Thanks for your response.

 

First thing I did this morning was redefine my table so current_timestamp is the default value of the lastUpdated field, so I never have to enter it again, but I do have to explicitly name the other field, like

INSERT INTO db.ExecLog (execDesc) VALUES  ('statusDescription');    

 CNT is an integer field, so I didn't know TRIM worked on it, but apparently it does.

 

I ended up using:

INSERT INTO db.ExecLog  (execDesc) VALUES (
	(SELECT 
		TRIM(err) || ': ' || TRIM(CNT)
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	UNION ALL
	SELECT 
		'errors: 0'
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	HAVING 
		COUNT(*)=0))	;
Since CNT will never be null in the first SELECT, I should be able to drop the COALESCE entirely.

Much prefer to not use a cursor, though.  There shouldn't be a lot of results returned from the error view, but still...

FYI, I think I have to use the UNION, because when I had it set up as a separate statement, it failed with a 'cannot insert null
value' error when there the SELECT returned no rows (the statusDescription was null because the result set was null). Thanks for your help.

 

Junior Contributor

Re: trying to insert into a log table from a select statement that can return null or multiple rows

Why are you still using VALUES instead of Fred's INSERT/SELECT?

It will fail if there are multiple rows returned.

Enthusiast

Re: trying to insert into a log table from a select statement that can return null or multiple rows

Actually, because of the data I have, I couldn't really test it yesterday, so today I added a couple of unconditional errors to my view to make sure they're all logged.  I thought I had to do the insert into db.tbl (fld) values(val) syntax after I changed the log table definition so the lastupdated field defaulted to current_timestamp so I could only insert one field value and I had to explicitly tell it which one.

 

Without the (fld) values() syntax, it says 'the positional assignment list has too few values' because, I'm assuming, the lastupdated field isn't required and it can't determine which field to put the value in.  Even with a default value defined, it could still be my intention to insert values into lastupdated.  It's not looking at the data type and understanding which field I want the values inserted into.

 

INSERT INTO db.ExecLog 
	SELECT 
		TRIM(err) || ': ' || TRIM(CNT)
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	UNION ALL
	SELECT 
		'error count: 0'
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	HAVING 
		COUNT(*)=0	;

When I try it with the (fld) values((select...union select)) syntax it generates the error I always had before, more than one value was inserted by a subquery:

INSERT INTO db.ExecLog (execDesc) VALUES (
    (SELECT 
        TRIM(err) || ': ' || TRIM(CNT)
    FROM 
        db.admin_5_CLDRptYr_errV 
    WHERE 
        CNT>0
    UNION ALL
    SELECT 
        'error count: 0'
    FROM 
        db.admin_5_CLDRptYr_errV 
    WHERE 
        CNT>0
    HAVING 
        COUNT(*)=0))     

 so I'm OK with not using the (fld) values syntax if that limits it to one row, but then I'm not sure what kind of syntax I need to use to insert a set of values from a SELECT statement into a table with a default value.  Is that unsupported?  Do I need to go back and change my log table definition to not have CURRENT_TIMESTAMP as the default?

 

This syntax works for me:

INSERT INTO db.ExecLog  
	SELECT 
		TRIM(err) || ': ' || TRIM(CNT), CURRENT_TIMESTAMP
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	UNION
	SELECT 
		'error count: 0', CURRENT_TIMESTAMP
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	HAVING 
		COUNT(*)=0

but I was hoping to avoid explicitly referencing the current_timestamp every time I want to log an activity.

 

Maybe this is the only time I need to add it and I can leave the default defined in the table so I don't have to change all the other log inserts back to inserting both fields.

 

Anyways, thanks for your help.  Really didn't want to use a cursor, but apparently can't mix default values with inserting multiple rows.

Junior Contributor

Re: trying to insert into a log table from a select statement that can return null or multiple rows

INSERT INTO db.ExecLog (execDesc) 
	SELECT 
		TRIM(err) || ': ' || TRIM(CNT)
	FROM ...
Teradata Employee

Re: trying to insert into a log table from a select statement that can return null or multiple rows

Of course you can. Just include a column list with the INSERT/SELECT like you do with INSERT VALUES:

INSERT INTO db.ExecLog (execDesc) 
	SELECT 
		TRIM(err) || ': ' || TRIM(CNT)
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	UNION ALL
	SELECT 
		'error count: 0'
	FROM 
		db.admin_5_CLDRptYr_errV 
	WHERE 
		CNT>0
	HAVING 
		COUNT(*)=0
Enthusiast

Re: trying to insert into a log table from a select statement that can return null or multiple rows

Thanks! that worked!

Less code the better, in my opinion.

Enthusiast

Re: trying to insert into a log table from a select statement that can return null or multiple rows

thanks, that works!