All Forums Teradata Applications
p12345 9 posts Joined 11/13
08 Jan 2016
DOT CHARACTER FOR BTEQ, FASTLOAD AND MULTILOAD COMMANDS

Hi,
I just want to know whether dot character used before bteq , fastload and multiload commands is mandatory or not.
I have been searching about it for a past couple of days, but I am getting mixed response. In the teradata official website, it is written that dot character is optional but recommended, whereas in the bteq manual it is written that dot character is mandatory.
Please help on the issue.

dnoeth 4628 posts Joined 11/04
08 Jan 2016

The period is optional for BTEQ & FastLoad, but mandatory for MultiLoad/TPump/FastExport.
This is from the FastLoad manual:

The following syntax rules apply when using Teradata FastLoad commands:

• Commands may begin with a period, but do not have to begin with a period.

• If there is no leading period, then there must be a semicolon at the end.

• If the command has a leading period, it must all be on one line. Commands that begin

with a period cannot span multiple lines.

 

Afaik the same rules apply to BTEQ although .HELP BTEQ states:

 

BTEQ commands must be preceded by a period '.'

character and the last command step of an instruction may end

with a semicolon ';' character. If a command does not start with

a period, BTEQ may assume that it is an SQL request and submit it

to the Teradata Database.

 

 

Dieter

p12345 9 posts Joined 11/13
08 Jan 2016

But in the above statement, for BTEQ is it mentioned that period should be there before bteq commands, otherwise BTEQ will assume as a SQL statement. 
That means , period is mandatory for BTEQ commands. 
 
Am I right on that ?

dnoeth 4628 posts Joined 11/04
08 Jan 2016

When you try it (e.g. run HELP BTEQ;) you will notice that it's not mandatory :)
 

Dieter

siva13081986 8 posts Joined 08/14
09 Jan 2016

Hi friends,
 
                     can you please help me in this.....
 
 
source :
 
col1  col2
1       a
2       b
1       c
1       d
2       r
3       f
2       h
 target should be.....
 
col1   col2
 1       acd
 2       brh
 3         f
 
thanks,
siva.
 

p12345 9 posts Joined 11/13
10 Jan 2016

The above result is simply a vertical pivot where col2 values are getting concatenated based on the matching col1 values. The below query will give the result:
SELECT 
X.COL1
,MAX(CASE WHEN X.RNO = 1 THEN X.COL1 ) || MAX(CASE WHEN X.RNO = 2 THEN X.COL1 ) || MAX(CASE WHEN X.RNO = 3 THEN X.COL1) AS COL2
FROM
(SELECT COL1,COL2,ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2 ) AS RNO ) X
GROUP BY X.COL1 ;

You must sign in to leave a comment.