Creating multiset table with locking table for access

General
Highlighted
Enthusiast

Creating multiset table with locking table for access

Hi experts!

 

I'm having some problems when creating a multiset table with locking table for access.

 

Here's the code:

 

 

CREATE  MULTISET TABLE TEST AS LOCKING DBTST.TB_FLT_CRM_MOVEL FOR ACCESS LOCKING DBTST.TB_1_OK FOR ACCESS LOCKING DBTST.TB_2_OK FOR ACCESS LOCKING DBTST.TB_3_OK FOR ACCESS 
                                                               SELECT
										F.EMPID
										,CASE WHEN OMP.ID_TST IS NOT NULL THEN OMP.ID_TST
													  WHEN OMC.ID_TST IS NOT NULL THEN OMC.ID_TST
													  WHEN OUC.ID_TST IS NOT NULL THEN OUC.ID_TST 
													  WHEN OUP.ID_TST IS NOT NULL THEN OUP.ID_TST 
										 ELSE 0 END ID_TST_SGP_MIGRA
										,CASE 	
													 WHEN OCO.ID_TST IS NOT NULL	 AND FL_CNTA_ONLNE = 0 THEN OCO.ID_TST
											ELSE 0 END ID_TST_SGP_CNTA_ONLNE
										,CASE 	
													WHEN OTRS.ID_TST IS NOT NULL	THEN OTRS.ID_TST
											ELSE 0 END ID_TST_SGP_OTRS
										,0 AS QTD_OFRT_SGP
										,1 AS ID_PRIORIDADE
					FROM DBTST.TB_FLT_CRM_MOVEL F
					LEFT JOIN DBTST.TB_1_OK C ON F.EMPID = C.EMPID
					LEFT JOIN  DBTST.TB_2_OK OMP ON OMP.ID_TST_DM = CASE WHEN F.NOGO = 71 THEN 49 ELSE F.NOGO END AND ID_SIST_PGTO = 1 AND  OMP.TP_CA = 'MIGRA PRE-CTRL' AND OMP.BTACT = 1
					LEFT JOIN  DBTST.TB_2_OK OMC ON OMC.ID_TST_DM = F.NOGO AND ID_SIST_PGTO = 3 AND  OMC.TP_CA = 'MIGRA CTRL-POS' AND  OMC.BTACT = 1
					LEFT JOIN  DBTST.TB_2_OK OUC ON OUC.ID_TST_DM = F.UP_TST AND ID_SIST_PGTO = 3 AND OUC.TP_CA =  'UP CTRL-CTRL' AND OUC.BTACT = 1
					LEFT JOIN  DBTST.TB_2_OK OUP ON OUP.ID_TST_DM = F.UP_TST AND ID_SIST_PGTO = 2 AND  OUP.TP_CA =  'UP POS-POS' AND OUP.BTACT = 1
					LEFT JOIN DBTST.TB_3_OK CO ON CO.EMPID = F.EMPID 
					LEFT JOIN  DBTST.TB_2_OK OCO ON OCO.ID_TST  = CO.ID_TST   AND OCO.BTACT = 1
					LEFT JOIN  DBTST.TB_2_OK OTRS ON OTRS.ID_TST = F.OFRT1_PROD_NEW  AND OTRS.BTACT = 1
					WHERE F.SECID IS NOT NULL
			 WITH DATA PRIMARY INDEX (EMPID) ;

 

I get the following error message:

 

 

Syntax error, expected something like a name or a Unicode delimited identifier between  the 'AS' keyword and the 'LOCKING' keyword.

 

Does anybody know how to correct the syntax? I didn't find any reference about it.

 

Kind Regards,


Accepted Solutions
Junior Contributor

Re: Creating multiset table with locking table for access

LOCK must be the initial part of your query:

LOCKING DBTST.TB_FLT_CRM_MOVEL FOR ACCESS
LOCKING DBTST.TB_1_OK FOR ACCESS
LOCKING DBTST.TB_2_OK FOR ACCESS LOCKING DBTST.TB_3_OK FOR ACCESS
CREATE  MULTISET TABLE TEST AS 
 (  SELECT ...
1 ACCEPTED SOLUTION
5 REPLIES

Re: Creating multiset table with locking table for access

Can you please try by executing the below ddl statement. I removed the "locking" statement while creating the table. 

Hope it works out as I tried the following and it worked

create table <target_table> as
(select <table1>.<col_name>
from
<table1>
INNER JOIN
<table2>
ON (<table1>.<col_name>=<table2>.<col_name>))with data ;

 

 



CREATE MULTISET TABLE TEST AS (LOCKING DBTST.TB_FLT_CRM_MOVEL FOR ACCESS LOCKING DBTST.TB_1_OK FOR ACCESS LOCKING DBTST.TB_2_OK FOR ACCESS LOCKING DBTST.TB_3_OK FOR ACCESS SELECT F.EMPID ,CASE WHEN OMP.ID_TST IS NOT NULL THEN OMP.ID_TST WHEN OMC.ID_TST IS NOT NULL THEN OMC.ID_TST WHEN OUC.ID_TST IS NOT NULL THEN OUC.ID_TST WHEN OUP.ID_TST IS NOT NULL THEN OUP.ID_TST ELSE 0 END ID_TST_SGP_MIGRA ,CASE WHEN OCO.ID_TST IS NOT NULL AND FL_CNTA_ONLNE = 0 THEN OCO.ID_TST ELSE 0 END ID_TST_SGP_CNTA_ONLNE ,CASE WHEN OTRS.ID_TST IS NOT NULL THEN OTRS.ID_TST ELSE 0 END ID_TST_SGP_OTRS ,0 AS QTD_OFRT_SGP ,1 AS ID_PRIORIDADE FROM DBTST.TB_FLT_CRM_MOVEL F LEFT JOIN DBTST.TB_1_OK C ON F.EMPID = C.EMPID LEFT JOIN DBTST.TB_2_OK OMP ON OMP.ID_TST_DM = CASE WHEN F.NOGO = 71 THEN 49 ELSE F.NOGO END AND ID_SIST_PGTO = 1 AND OMP.TP_CA = 'MIGRA PRE-CTRL' AND OMP.BTACT = 1 LEFT JOIN DBTST.TB_2_OK OMC ON OMC.ID_TST_DM = F.NOGO AND ID_SIST_PGTO = 3 AND OMC.TP_CA = 'MIGRA CTRL-POS' AND OMC.BTACT = 1 LEFT JOIN DBTST.TB_2_OK OUC ON OUC.ID_TST_DM = F.UP_TST AND ID_SIST_PGTO = 3 AND OUC.TP_CA = 'UP CTRL-CTRL' AND OUC.BTACT = 1 LEFT JOIN DBTST.TB_2_OK OUP ON OUP.ID_TST_DM = F.UP_TST AND ID_SIST_PGTO = 2 AND OUP.TP_CA = 'UP POS-POS' AND OUP.BTACT = 1 LEFT JOIN DBTST.TB_3_OK CO ON CO.EMPID = F.EMPID LEFT JOIN DBTST.TB_2_OK OCO ON OCO.ID_TST = CO.ID_TST AND OCO.BTACT = 1 LEFT JOIN DBTST.TB_2_OK OTRS ON OTRS.ID_TST = F.OFRT1_PROD_NEW AND OTRS.BTACT = 1 WHERE F.SECID IS NOT NULL) WITH DATA PRIMARY INDEX (EMPID) ;

 

Enthusiast

Re: Creating multiset table with locking table for access

Hi!

 

I am not sure if it is a misconception of mine, but I would like to use this create table as select with multiple locking statements for all the tables I am currently using in the query.

 

This table is created many times during the day - not the best thing to do - and I am afraid I could lock any other user is trying to do a select statement for these tables.

 

Any ideas?

 

Thanks!

Junior Contributor

Re: Creating multiset table with locking table for access

LOCK must be the initial part of your query:

LOCKING DBTST.TB_FLT_CRM_MOVEL FOR ACCESS
LOCKING DBTST.TB_1_OK FOR ACCESS
LOCKING DBTST.TB_2_OK FOR ACCESS LOCKING DBTST.TB_3_OK FOR ACCESS
CREATE  MULTISET TABLE TEST AS 
 (  SELECT ...
Enthusiast

Re: Creating multiset table with locking table for access

It worked just fine.

 

Thanks!

Teradata Employee

Re: Creating multiset table with locking table for access

If it is the same query and same table all the time, then a much better practice would be to DELETE ALL TEST; follwed by INSERT SELECT to populate it. Then there would not be the additional cost and locking for dropping and recreating the table.

 

If this strategy is used, make sure the DELETE ALL is committed (either standalone statement in Teradata mode or follw with commit in ANSI mode) prior to doing the INSERT SELECT.