Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

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

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

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

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

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

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

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

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.