All Forums Database
donal28 9 posts Joined 06/12
13 Jun 2012
Combining Multiple Insert Statements with Calculations Fields

Hi All

 

I have a table with 55 fields which I need to populate. Most of the fields can be hard coded in as below with 'NOT APPLICABLE' but mixed in between theses there are fields that will require calculations to be done which are derived from other tables(see below CASE statement as an example). The Issue I am coming across is that I want to populate the fields in the order they are listed but Cannot use the INSERT INTO clause inside the CASE Clause so basically I am struggling on how best to lay this out so that the fields are populated sequentially. I could run individual insert statements like below but ideally would like a solution where I can run one complete statement which populates the whole 55 fields. Hope I have explained this OKAY.

Any help on htis would be very much appreciated

 

Regards

Donal

 

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (BOR_OCCUPATION_STATUS_CURR = 'NOT_APPLICABLE')

 

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (BOR_OCCUPATION_STATUS_ORIGN = 'NOT_APPLICABLE')

 

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (BOR_AGE_AT_ORIGINATION = 'NOT_APPLICABLE')

 

TRIM(CASE

INSERT  INTO  DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO (BORROWER_ID)

select  CUST_ID

FROM DDEWV50P.CRDM_ENTERPRISE_PERIODIC_FACT

where CUST_ID like  "B_")

 

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (YEAR_OF_BIRTH = 'NOT_APPLICABLE')

 

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (BORROWER_SEX = 'NOT_APPLICABLE')

 

ulrich 816 posts Joined 09/09
13 Jun 2012

You might need to spend some reasonable time on the SQL basics 

Each of your insert into tab values xxx will create a row - populated only one column. 

Insert / Select statement where you define some constants should do.

Insert into tab

select 'NA',

          'NA',

          CASE when a.x = 1 then 1 else 0 end,

...

form tablea;

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

22 Feb 2013

 

Hi,

 

 

I want to run multiple COLLECT STATS statements simultaneously on the Teradata database.

 

How can i achieve  this using BTEQ script, can i run these multiple statements in BTEQ ?

I tried following way 

 

collect stats on table column (row_wid)

;collect stats on table column (order_wid);

 

(Above syntax we can follow for multiple select and insert

 

Ex:

INSERT INTO X

SELECT * FROM A

;INSERT INTO X

SELECT * FROM B;

 

This works.

)

 

but its not working for collect stats, is there any limitations on this syntax?

 

Is there any other way to achieve for same?

 

Thanks in adavance.

 

Thanx,
Mahesh

KS42982 137 posts Joined 12/12
22 Feb 2013

No, you can't run COLLECT STATS statements like that in BTEQ. You have do something like:
collect stats on table column (row_wid);
collect stats on table column (order_wid);

dnoeth 4628 posts Joined 11/04
22 Feb 2013

Hi Mahesh,
you have to split the collects into multiple scripts and then use multiple BTEQs to run them.
Or you have a look at the approach i described in 
http://developer.teradata.com/node/16550
Dieter

Dieter

24 Feb 2013

 
 
Hi Dieter,
Thanks for your reply,
Already we implemented same way (splitting into multiple scripts) except same is there any another way to run multiple statements through BTEQ.Long back we segregated into multiple scripts (13 Scripts) still it was running for long time (>4hours).
Thanks in advance

Thanx,
Mahesh

24 Feb 2013

Sorry ! you have already given a link for same.Let me read your article soon we will get back to you.

Thanx,
Mahesh

You must sign in to leave a comment.