Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-21-2008
10:01 AM

08-21-2008
10:01 AM

how to write a function to retrieve random character from A TO Z

Hi

I am trying to write a function in Teradata SQL Assistant to fetch a character at a time randomly between A to Z.

Can anyone help me in this regard?

Thanks

I am trying to write a function in Teradata SQL Assistant to fetch a character at a time randomly between A to Z.

Can anyone help me in this regard?

Thanks

14 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-21-2008
11:04 PM

08-21-2008
11:04 PM

Re: how to write a function to retrieve random character from A TO Z

There are efficient ways than writing like this(i guess)... But this is one of the way to achive what you want

sel

case when a1=1 then 'A'

when a1=2 then 'B'

when a1=3 then 'C'

end,

random(1,3) as a1

from db_name.a

sel

case when a1=1 then 'A'

when a1=2 then 'B'

when a1=3 then 'C'

end,

random(1,3) as a1

from db_name.a

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2008
06:15 AM

08-22-2008
06:15 AM

Re: how to write a function to retrieve random character from A TO Z

Hi

Am getting some question marks in place of the characters after executing the above query.

And what is that table a?

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2008
06:26 AM

08-22-2008
06:26 AM

Re: how to write a function to retrieve random character from A TO Z

Hello,

On a very basic level, you can use following:

SELECT RandChar

FROM

(

SELECT

RANDOM(1, 26) AS Rand,

CASE WHEN Rand = 1 THEN 'A'

WHEN Rand = 2 THEN 'B'

WHEN Rand = 3 THEN 'C'

WHEN Rand = 4 THEN 'D'

WHEN Rand = 5 THEN 'E'

WHEN Rand = 6 THEN 'F'

WHEN Rand = 7 THEN 'G'

WHEN Rand = 8 THEN 'H'

WHEN Rand = 9 THEN 'I'

WHEN Rand = 10 THEN 'J'

WHEN Rand = 11 THEN 'K'

WHEN Rand = 12 THEN 'L'

WHEN Rand = 13 THEN 'M'

WHEN Rand = 14 THEN 'N'

WHEN Rand = 15 THEN 'O'

WHEN Rand = 16 THEN 'P'

WHEN Rand = 17 THEN 'Q'

WHEN Rand = 18 THEN 'R'

WHEN Rand = 19 THEN 'S'

WHEN Rand = 20 THEN 'T'

WHEN Rand = 21 THEN 'U'

WHEN Rand = 22 THEN 'V'

WHEN Rand = 23 THEN 'W'

WHEN Rand = 24 THEN 'X'

WHEN Rand = 25 THEN 'Y'

WHEN Rand = 26 THEN 'Z'

END AS RandChar

) QueryAlias1

HTH.

Regards,

Adeel

On a very basic level, you can use following:

SELECT RandChar

FROM

(

SELECT

RANDOM(1, 26) AS Rand,

CASE WHEN Rand = 1 THEN 'A'

WHEN Rand = 2 THEN 'B'

WHEN Rand = 3 THEN 'C'

WHEN Rand = 4 THEN 'D'

WHEN Rand = 5 THEN 'E'

WHEN Rand = 6 THEN 'F'

WHEN Rand = 7 THEN 'G'

WHEN Rand = 8 THEN 'H'

WHEN Rand = 9 THEN 'I'

WHEN Rand = 10 THEN 'J'

WHEN Rand = 11 THEN 'K'

WHEN Rand = 12 THEN 'L'

WHEN Rand = 13 THEN 'M'

WHEN Rand = 14 THEN 'N'

WHEN Rand = 15 THEN 'O'

WHEN Rand = 16 THEN 'P'

WHEN Rand = 17 THEN 'Q'

WHEN Rand = 18 THEN 'R'

WHEN Rand = 19 THEN 'S'

WHEN Rand = 20 THEN 'T'

WHEN Rand = 21 THEN 'U'

WHEN Rand = 22 THEN 'V'

WHEN Rand = 23 THEN 'W'

WHEN Rand = 24 THEN 'X'

WHEN Rand = 25 THEN 'Y'

WHEN Rand = 26 THEN 'Z'

END AS RandChar

) QueryAlias1

HTH.

Regards,

Adeel

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2008
06:37 AM

08-22-2008
06:37 AM

Re: how to write a function to retrieve random character from A TO Z

Even then am getting a question mark in my result ....

i wonder why is it !!!

And i hope random() function will not generate the float numbers as in SQL !!!!!!!

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2008
06:41 AM

08-22-2008
06:41 AM

Re: how to write a function to retrieve random character from A TO Z

Exactly how are you executing the query?

Regards,

Adeel

Regards,

Adeel

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2008
07:12 AM

08-22-2008
07:12 AM

Re: how to write a function to retrieve random character from A TO Z

This is the query exactly what i have:

SELECT RandChar

FROM

(

SELECT

RANDOM(1, 26) AS Rand,

CASE WHEN Rand = 1 THEN 'A'

WHEN Rand = 2 THEN 'B'

WHEN Rand = 3 THEN 'C'

WHEN Rand = 4 THEN 'D'

WHEN Rand = 5 THEN 'E'

WHEN Rand = 6 THEN 'F'

WHEN Rand = 7 THEN 'G'

WHEN Rand = 8 THEN 'H'

WHEN Rand = 9 THEN 'I'

WHEN Rand = 10 THEN 'J'

WHEN Rand = 11 THEN 'K'

WHEN Rand = 12 THEN 'L'

WHEN Rand = 13 THEN 'M'

WHEN Rand = 14 THEN 'N'

WHEN Rand = 15 THEN 'O'

WHEN Rand = 16 THEN 'P'

WHEN Rand = 17 THEN 'Q'

WHEN Rand = 18 THEN 'R'

WHEN Rand = 19 THEN 'S'

WHEN Rand = 20 THEN 'T'

WHEN Rand = 21 THEN 'U'

WHEN Rand = 22 THEN 'V'

WHEN Rand = 23 THEN 'W'

WHEN Rand = 24 THEN 'X'

WHEN Rand = 25 THEN 'Y'

WHEN Rand = 26 THEN 'Z'

END AS RandChar

) QueryAlias1

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2008
07:20 AM

08-22-2008
07:20 AM

Re: how to write a function to retrieve random character from A TO Z

Can you try running the same in BTEQ and SQL Assistant and check the outputs?

Regards,

Adeel

Regards,

Adeel

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2008
07:39 AM

08-22-2008
07:39 AM

Re: how to write a function to retrieve random character from A TO Z

Even in BTEQ it is resulting question marks...

and same with SQL Assistant also ....

why is it happening like that?

is there any other way of doing this?

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-22-2008
07:56 AM

08-22-2008
07:56 AM

Re: how to write a function to retrieve random character from A TO Z

It is very weird! It is a simple SQL, it should work on both. I wonder if this issue can be because of Teradata's version. I tried this on V2R6 and TD-12. It works fine on both (in BTEQ and SQL Assistant).

Regards,

Adeel

Regards,

Adeel