Multiple strings as one alias

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Multiple strings as one alias

select 'ya0010','rx0711','lme721','rz5251','acd688','jl3213','lw0814' as user_id from dual

 

When I run the above query,the output diaplays only 'lw0814' as user_id.

 

How to display all the strings under one alias name?

 

For example, select ('ya0010','rx0711','lme721','rz5251','acd688','jl3213','lw0814') as user_id from dual;-- which is throwing errors.

 

Please help.

5 REPLIES
Junior Contributor

Re: Multiple strings as one alias

Your query returns 7 columns, the last one has an alias.

When you need to write a list a quoted strings you must double each single quote like this

select '''ya0010'',''rx0711'',''lme721'',''rz5251'',''acd688'',''jl3213'',''lw0814''' as user_id from dual

And remove FROM DUAL, this is not Oracle :-)

Enthusiast

Re: Multiple strings as one alias

I want to display the output as below,

User_id
ya0010
rx0711
lme721
rz5251
acd688
jl3213
lw0814

Thank you..
Teradata Employee

Re: Multiple strings as one alias

There are many possible ways of doing what you want to do, but it needs to be put into context.

Here's some examples of code that can return the result from your example:

/* simplistic */

SELECT User_Id FROM (SELECT 'ya0010' AS User_id) AS SubQry

UNION ALL

SELECT User_Id FROM (SELECT 'rx0711' AS User_id) AS SubQry

UNION ALL

SELECT User_Id FROM (SELECT 'lme721' AS User_id) AS SubQry

UNION ALL

SELECT User_Id FROM (SELECT 'rz5251' AS User_id) AS SubQry

UNION ALL

SELECT User_Id FROM (SELECT 'acd688' AS User_id) AS SubQry

UNION ALL

SELECT User_Id FROM (SELECT 'jl3213' AS User_id) AS SubQry

UNION ALL

SELECT User_Id FROM (SELECT 'lw0814' AS User_id) AS SubQry;

 

User_id

acd688

jl3213

lme721

lw0814

rx0711

rz5251

ya0010

 

/* two ways by coding a pivot from columns to rows with a single column */

SELECT DECODE(UIDNOS.UIDNO, 1, UIDS.UID2, 2, UIDS.UID3, 3, UIDS.UID4, 4, UIDS.UID5, 5, UIDS.UID6, 6, UIDS.UID1, 7, UIDS.UID7) AS User_Id_1

       , CASE

            WHEN    UIDNOS.UIDNO = 1

                THEN   UIDS.UID1

            WHEN UIDNOS.UIDNO = 2

                THEN UIDS.UID2

            WHEN UIDNOS.UIDNO = 3

                THEN UIDS.UID3

            WHEN UIDNOS.UIDNO = 4

                THEN UIDS.UID4

            WHEN UIDNOS.UIDNO = 5

                THEN UIDS.UID5

            WHEN UIDNOS.UIDNO = 6

                THEN UIDS.UID6

            WHEN UIDNOS.UIDNO = 7

                THEN UIDS.UID7

END AS User_ID_2

FROM

(

    SELECT 'acd688' AS UID1,'jl3213' AS UID2,'lme721' AS UID3,'lw0814' AS UID4,'rx0711' AS UID5,'rz5251' AS UID6,'ya0010' AS UID7

) AS UIDS

CROSS JOIN

(

    SELECT  DAY_OF_CALENDAR AS UIDNO

    FROM    SYS_CALENDAR.CALENDAR

    WHERE   DAY_OF_CALENDAR BETWEEN 1 AND 7

) AS UIDNOS

ORDER BY User_ID_1,User_Id_2 ;

 

User_Id_1 User_ID_2

acd688 rz5251

jl3213 acd688

lme721 jl3213

lw0814 lme721

rx0711 lw0814

rz5251 rx0711

ya0010 ya0010

 

Or lookup the TD_UNPIVOT function.

Enthusiast

Re: Multiple strings as one alias

you can try below:

 

SELECT * FROM (SELECT 'ya0010' AS user_id) A
UNION
SELECT * FROM (SELECT 'rx0711' AS user_id) b
UNION
SELECT * FROM (SELECT 'lme721' AS user_id) c
UNION
SELECT * FROM (SELECT 'rz5251' AS user_id) d
UNION
SELECT * FROM (SELECT 'acd688' AS user_id) e
UNION
SELECT * FROM (SELECT 'jl3213' AS user_id) f
UNION
SELECT * FROM (SELECT 'lw0814' AS user_id) f

Teradata Employee

Re: Multiple strings as one alias

See STRTOK function in the documentation and in a number of topics here in the forums.