BTEQ with REPEAT-USING -> variable always evaluates to value 1

Database

BTEQ with REPEAT-USING -> variable always evaluates to value 1

Hello fellow-Teradatans,

 

I am actually trying to run a loop in BTEQ and since Teradata doesn't allow that, I read through other forums and felt REPEAT is one option. 

 

Now, I'm facing a peculiar error while working with REPEAT. I put values 0,1,2,3 in a txt file in 4 rows and am trying to print these values.

Though SELECT runs 4 times, it prints the value 1 each time. It doesn't result in 0,1,2,3 as I would expect.

Is my expectation/understanding of REPEAT itself incorrect?

 

Below are the contents of my script and the file. 

 

.btq script

 

/****************************************************/

.IMPORT vartext '' file=sample_list.dat

.REPEAT *

using num(SMALLINT)

SELECT :num;

/****************************************************/

 

sample_list.dat

0

1

2

3

 

What am I missing? Why is the value of num always 1 in the below results ? I would expect the results to show 0 1 2 3 in 4 of the iterations, as I have 0 1 2 3 in the file.

Is that not how REPEAT actually works?

 

Here's the output results:

 

.run file repeat_bteq.btq

BTEQ -- Enter your SQL request or BTEQ command:

 

.IMPORT vartext '' file=sample_list.dat

BTEQ -- Enter your SQL request or BTEQ command:

 

.REPEAT *

BTEQ -- Enter your SQL request or BTEQ command:

 

using num(SMALLINT)

SELECT :num;

*** Starting Row 0 at Tue Jul 25 22:28:03 2017

 

 

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

 

   num

------

     1

 

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

 

   num

------

     1

 

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

 

   num

------

     1

 

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

 

   num

------

     1

*** Warning: Out of data.

*** Finished at input row 4 at Tue Jul 25 22:28:03 2017

*** Total number of statements: 4,  Accepted : 4,  Rejected : 0

 

*** Total elapsed time was 1 second.

 

*** Total requests sent to the DBC = 4

*** Successful requests per second =  4.000

 

BTEQ -- Enter your SQL request or BTEQ command:

*** Warning: EOF on INPUT stream.

 


Accepted Solutions
Senior Apprentice

Re: BTEQ with REPEAT-USING -> variable always evaluates to value 1

Hi,

 

The problem is the definition of num in your script. When using 'vartext' files all fields in the USING clause must be defined as VARCHAR(n). You have defined 'num' as SMALLINT which means that BTEQ is expecting a 2-byte integer value, not a (in my case) 2 byte character value.

 

Here is my script output.

.IMPORT vartext '' file=sample_data.txt
+---------+---------+---------+---------+---------+---------+---------+----

.REPEAT *
+---------+---------+---------+---------+---------+---------+---------+----

using num(varchar(2))

SELECT :num
 *** Starting Row 0 at Wed Jul 26 09:00:53 2017
 

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

num
---
0

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

num
---
1

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

num
---
2

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

num
---
3

(To be honest I'm surprised that BTEQ actually)

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: BTEQ with REPEAT-USING -> variable always evaluates to value 1

Hi,

 

The problem is the definition of num in your script. When using 'vartext' files all fields in the USING clause must be defined as VARCHAR(n). You have defined 'num' as SMALLINT which means that BTEQ is expecting a 2-byte integer value, not a (in my case) 2 byte character value.

 

Here is my script output.

.IMPORT vartext '' file=sample_data.txt
+---------+---------+---------+---------+---------+---------+---------+----

.REPEAT *
+---------+---------+---------+---------+---------+---------+---------+----

using num(varchar(2))

SELECT :num
 *** Starting Row 0 at Wed Jul 26 09:00:53 2017
 

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

num
---
0

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

num
---
1

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

num
---
2

 *** Query completed. One row found. One column returned. 
 *** Total elapsed time was 1 second.

num
---
3

(To be honest I'm surprised that BTEQ actually)

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: BTEQ with REPEAT-USING -> variable always evaluates to value 1

Thanks Dave. In fact I saw your answer to another problem and I fixed this last night.

Even I was surprised that BTEQ didnt throw a warning or an error.

Tags (1)
  • Tags: