Random record selection in the subquery

Database
Enthusiast

Random record selection in the subquery

Hi All,

I am writing SP to generate dynamic SQL query which creates insert statements.

I want for a particular column the data should be fetched from another table. I have tried sample and top but teradata doesnt allow sub queries with sample statements.

Here is the sample scenario which I am trying to achieve

INSERT INTO TABLE_A

VALUES ( 1,(SEL COL1 FROM TABLE_X SAMPLE 1),(SEL COL1 FROM TABLE_Y SAMPLE 1));

Is there any work around to solve the above issue.

Thanks in Advance.

8 REPLIES
Teradata Employee

Re: Random record selection in the subquery

Hi,

You obviously need to change your approach of writting INSERT/SELECT query. Easier approach should be to write one SELECT statement first, before generating it in SP dynamically.

Example:

Generate something like 

SELECT 1, A.Col1, B.Col1 From Table1 A , Table2 B;

And then simply use it in INSERT.

For further details, please provide sample data for the tables you mentioned.

HTH!

Regards, MAC

Enthusiast

Re: Random record selection in the subquery

Hi MAC,

Thanks for the reply. Here is the sample data that I am trying to achieve.

TABLE A

Cust_id     Cust_type_CD  Cust_Location

1                 Ind                  Newyork

2                 Org                 Washington

Table X

Cust_Typ_Cd      Desc

Ind                      Individual Customer

Org                     Organization Customer

Table Y

Location Id      Location

100                   Amsterdam

101                   London

103                   Newyork

104                   Paris

105                   Washington

I want random Cust_typ_cd and Location to be inserted to the Table A.

Please let me know how to achieve, the above result.

I can rewrite the SQL statement incase I have only one table(say Table X or Y alone), But the number of columns like this decided dynamically based on the table structure. I cant like the table X and Y as they are not related to each other(Reference tables).

Enthusiast

Re: Random record selection in the subquery

I have written SP to pass the parameters of the table and column for which the data and from which the sample record will be sent back to the calling procedure.

Like the below

REPLACE PROCEDURE SAMPLE_DATA(IN TABNAME VARCHAR(30),IN COLNAME VARCHAR(30), IN DBNAME VARCHAR(30),OUT VAL VARCHAR(30))

BEGIN

DECLARE SQLSTR VARCHAR(1000);

DECLARE CUR_SAMPLEDATA CURSOR FOR S1;

SET SQLSTR='SEL ' || COLNAME || ' FROM ' || DBNAME || '.' || TABNAME || ' SAMPLE 1;';

PREPARE S1 FROM SQLSTR;

OPEN CUR_SAMPLEDATA;

FETCH CUR_SAMPLEDATA      INTO VAL;

CLOSE CUR_SAMPLEDATA;   

END;

Please let me know if there is any alternative or simpler approach to get this done.

Thanks

Teradata Employee

Re: Random record selection in the subquery

Hi,

You can have one SELECT as below:

SELECT

1, 

A.Cust_type_CD,

B.Cust_Location

FROM

TableX A

JOIN

TableY B

ON RANDOM(100, 105) = B.Location_Id;

HTH!

Regards, MAC

Enthusiast

Re: Random record selection in the subquery

Hi Mac,

The Above query produces random number of records.

Sometimes it's not producing any and some times multiple records are coming. But I need only one value for Cust_type_CD and  Cust_Location value for the PK 1.

Thanks

Senior Apprentice

Re: Random record selection in the subquery

Just do a cross join of derived tables:

INSERT INTO TABLE_A
select 1, a.col1, b.col1
from
(SEL COL1 FROM TABLE_X SAMPLE 1) as a,
(SEL COL1 FROM TABLE_Y SAMPLE 1) as b;

Dieter

Enthusiast

Re: Random record selection in the subquery

Thanks for the solution Dieter, It works as I need.

Re: Random record selection in the subquery

Dieter, 

I have a query which selects the top SELECT top 180000 records exported to a file and it takes upto 1 hour. How can I optimize this. Are there any features that SQL teradata Java edition would support.

How can I imcrease the buffer size in a teradataSQl assistant?