What's the quickest way to find out all the values under an attributes

Database
Enthusiast

What's the quickest way to find out all the values under an attributes

Hello all database gurus out there, I am a newbie in the database field and would like to thank each one of you in advance for helping out.

I have to pull some data for my manager, and this is the first time I use teradata sql assistant (fyi, it's version 7.1) My question is, is there anyway to find out the values of each field (attribute), before I make a query to run it? It's very time consuming, and the dictionary of the database does not provide this information.

For example,

Atrribute "CALL_TYPE" has value "TECH", "BO" and "OTR". I found out by using a query to select *. This is alright is I only have a few attributes, but I have over 30 attributes. Thank you very much for your help.
25 REPLIES
Enthusiast

Re: What's the quickest way to find out all the values under an attributes

You should probably ask your boss to get you some kind of data mining tool ... :o

Now if you are really stuck, the below SQL to "generate" all the required SQLs for you.

SELECT 'SELECT ' || TRIM(COLUMNNAME) || ' FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ' GROUP BY 1 ORDER BY 1;'
FROM
(
SELECT DATABASENAME, TABLENAME, COLUMNNAME
FROM DBC.COLUMNS
WHERE DATABASENAME = 'myDBofInterest'
AND TABLENAME IN
(
'tableofInterest1'
,'tableofInterest2'
)
) X
ORDER BY DATABASENAME , TABLENAME, COLUMNNAME
;

From the o/p please run only those queries for attributes which are of genuine interest to you, don't run this especially on stuff like key columns (which should be of no interest to you), because these SQLs can be costly for highly unique attributes like keys... And your DBA could get mad at you (So don't mention me either ....).

Otherwise this will save you lot of typing ...

Enthusiast

Re: What's the quickest way to find out all the values under an attributes

Slight change to Joe's answer:

SELECT '; SELECT ' || TRIM(COLUMNNAME) || ' FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ' (TITLE '')
GROUP BY 1 ORDER BY 1;'
FROM
(
SELECT DATABASENAME, TABLENAME, COLUMNNAME
FROM DBC.COLUMNS
WHERE DATABASENAME = 'myDBofInterest'
AND TABLENAME IN
(
'tableofInterest1'
,'tableofInterest2'
)
) X
ORDER BY DATABASENAME , TABLENAME, COLUMNNAME
;
Select ';' (Title '')
;

This will generate a multi-statement request. That means it will run all the queries together instead of serially - much faster.
Enthusiast

Re: What's the quickest way to find out all the values under an attributes

thank you very much guys.

question, in teradata, how do I put a criteria on the date?

I need info for the past four months, do I do like this:

Where Date >= Feb 12, 2009
Enthusiast

Re: What's the quickest way to find out all the values under an attributes

use this......

date between add_months(date,0) and add_months(date,-4)
Enthusiast

Re: What's the quickest way to find out all the values under an attributes

um.... no luck still, can somebody tell me what's wrong with this statement:

SELECT TTKT_NO, TTKT_STS, SYMP_USERID, SYMP_ACCT_NO, SYMP_USER_TN, CRTN_DTTM, DESK, DTTM_RCVD
FROM VREMEDY_SYM_TICKET
WHERE DESK = 'TE' AND SYMP_USERID <> '?' AND (CRTN_DTTM BETWEEN ADD_MONTHS(CRTN_DTTM,0) AND ADD_MONTHS(CRTN_DTTM, -4));

It executed the query for a few minutes, but the answer only contain the header, no data at all. I need to put the last 4 month statement, or else the data is too large (to the point it crashes my PC....)

Please please help, thank you all.

Enthusiast

Re: What's the quickest way to find out all the values under an attributes

What is this

SYMP_USERID <> '?'

If u are comparing with ? as a string then fine But want to compare with Null then use

SYMP_USERID IS NOT NULL
Enthusiast

Re: What's the quickest way to find out all the values under an attributes

under SYMP_USERID, some field contains just a "?", and I want to ignore those fields. Is that the proper way to do it?

Thank you very much guys for helping me out, I am very new to SQL and have no previous experience.
Enthusiast

Re: What's the quickest way to find out all the values under an attributes

first run this Q:

SELECT TTKT_NO, TTKT_STS, SYMP_USERID, SYMP_ACCT_NO, SYMP_USER_TN, CRTN_DTTM, DESK, DTTM_RCVD
FROM VREMEDY_SYM_TICKET
WHERE DESK = 'TE' AND (CRTN_DTTM BETWEEN ADD_MONTHS(CRTN_DTTM,0) AND ADD_MONTHS(CRTN_DTTM, -4)) and
SYMP_USERID is not null

And check wheter u are getting any result for this.

if yes then use this one

SELECT TTKT_NO, TTKT_STS, SYMP_USERID, SYMP_ACCT_NO, SYMP_USER_TN, CRTN_DTTM, DESK, DTTM_RCVD
FROM VREMEDY_SYM_TICKET
WHERE DESK = 'TE' AND (CRTN_DTTM BETWEEN ADD_MONTHS(CRTN_DTTM,0) AND ADD_MONTHS(CRTN_DTTM, -4)) and
SYMP_USERID not in ('?')

If it's still not working then post the exact error which u will get.
Enthusiast

Re: What's the quickest way to find out all the values under an attributes

Ok, I am going to try this now.

Also, i notice sql assistant crashes whenever I run a large query, is this normal? It says something about out of memory, abort, ignore and retry, but non of the option really help.

it happened just now at 22300026/890036 records, it crashed.