All Forums Database
thinkman 15 posts Joined 05/09
11 May 2009
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.

joedsilva 505 posts Joined 07/05
11 May 2009

You should probably ask your boss to get you some kind of data mining tool ... :oNow 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, COLUMNNAMEFROM DBC.COLUMNSWHERE DATABASENAME = 'myDBofInterest'AND TABLENAME IN('tableofInterest1','tableofInterest2')) XORDER 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 ...

Jimm 298 posts Joined 09/07
11 May 2009

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, COLUMNNAMEFROM DBC.COLUMNSWHERE DATABASENAME = 'myDBofInterest'AND TABLENAME IN('tableofInterest1','tableofInterest2')) XORDER 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.

thinkman 15 posts Joined 05/09
12 May 2009

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

gander_ss 74 posts Joined 02/07
12 May 2009

use this......date between add_months(date,0) and add_months(date,-4)

thinkman 15 posts Joined 05/09
12 May 2009

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_RCVDFROM VREMEDY_SYM_TICKETWHERE 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.

gander_ss 74 posts Joined 02/07
12 May 2009

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

thinkman 15 posts Joined 05/09
12 May 2009

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.

gander_ss 74 posts Joined 02/07
12 May 2009

first run this Q:SELECT TTKT_NO, TTKT_STS, SYMP_USERID, SYMP_ACCT_NO, SYMP_USER_TN, CRTN_DTTM, DESK, DTTM_RCVDFROM VREMEDY_SYM_TICKETWHERE DESK = 'TE' AND (CRTN_DTTM BETWEEN ADD_MONTHS(CRTN_DTTM,0) AND ADD_MONTHS(CRTN_DTTM, -4)) and SYMP_USERID is not nullAnd check wheter u are getting any result for this.if yes then use this oneSELECT TTKT_NO, TTKT_STS, SYMP_USERID, SYMP_ACCT_NO, SYMP_USER_TN, CRTN_DTTM, DESK, DTTM_RCVDFROM VREMEDY_SYM_TICKETWHERE 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.

thinkman 15 posts Joined 05/09
12 May 2009

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.

thinkman 15 posts Joined 05/09
12 May 2009

I have another newbie question, how do I run a query right after another?

thinkman 15 posts Joined 05/09
12 May 2009

first code, teh answer returned with no data, just the headers, i am going to try to second code now.

gander_ss 74 posts Joined 02/07
12 May 2009

No it's not normalFor this conatct with u'r TERADATA DBA and get more spool memory allocation for the user which u are using.

thinkman 15 posts Joined 05/09
12 May 2009

again, which the second code, same resultthe answerset only display the header, without any kind of data. Please advise

gander_ss 74 posts Joined 02/07
12 May 2009

Last thing .........run this...select count(*) from VREMEDY_SYM_TICKETand then this select count(*) from VREMEDY_SYM_TICKET where SYMP_USERID is nullIf the answer is same for both then there is no data in the table as per u'r specified where clause.

thinkman 15 posts Joined 05/09
12 May 2009

first one i got 32012338and i am running the 2nd code now

thinkman 15 posts Joined 05/09
12 May 2009

Hi gander_ssthe result for the 2nd count is 3396446

thinkman 15 posts Joined 05/09
12 May 2009

I try with this:SELECT TTKT_NO, TTKT_STS, SYMP_USERID, SYMP_ACCT_NO, SYMP_USER_TN, CRTN_DTTM, DESK, DTTM_RCVDFROM VREMEDY_SYM_TICKETWHERE (CRTN_DTTM BETWEEN ADD_MONTHS(CRTN_DTTM,0) AND ADD_MONTHS(CRTN_DTTM, -4));it gives me no result, just the headers. I think it has to do with the last line (CRTN_DTTM BETWEEN ADD_MONTHS(CRTN_DTTM,0) AND ADD_MONTHS(CRTN_DTTM, -4)). Because if the condition were DESK = 'TE', it will show me the result....

dnoeth 4628 posts Joined 11/04
12 May 2009

The BETWEEN condition is just wrong and useless:wrong: the first value must be <= the second value and your's vice versa.useless: you need to use DATE or CURRENT_DATE instead of CRTN_DTTM, else you compare CRTN_DTTM to itself.correct: (CRTN_DTTM BETWEEN ADD_MONTHS(CURRENT_DATE,-4) AND ADD_MONTHS(CURRENT_DATE, 0))Regarding SYMP_USERID <> '?':You should check if "Display this string for Null data fields" in Tools->Options->General is set to '?' then '?' is probably a NULL and SYMP_USERID IS NOT NULL is the correct condition.Btw, i usually change that option to ' ', so it's easier to spot."sql assistant crashes whenever I run a large query"SQL Assistant keeps the whole answer set in memory, maybe it's just too many MBs.What are you trying to do with that large number of rows on the screen?Dieter

Dieter

thinkman 15 posts Joined 05/09
13 May 2009

"sql assistant crashes whenever I run a large query"SQL Assistant keeps the whole answer set in memory, maybe it's just too many MBs.What are you trying to do with that large number of rows on the screen?My coworker has no issue running even larger queries, what's the reason for the crash? I am still waiting a call back from my workplace's DBA, because one member told this might be due to memory allocation.

thinkman 15 posts Joined 05/09
13 May 2009

for some reason the query wouldn't run on teradata assistant....SELECT TTKT_NO, TTKT_STS, SYMP_USERID, SYMP_ACCT_NO, SYMP_USER_TN, CRTN_DTTM, DESK, DTTM_RCVDFROM VREMEDY_SYM_TICKETWHERE (CRTN_DTTM BETWEEN ADD_MONTHS(CURRENT_DATE,-4) AND ADD_MONTHS(CURRENT_DATE, 0));the error code is"5407: Invalid operation on an ANSI Datetime or Interval value.

thinkman 15 posts Joined 05/09
13 May 2009

I solved the problem!this code works:SELECT TTKT_NO, TTKT_STS, SYMP_USERID, SYMP_ACCT_NO, SYMP_USER_TN, CRTN_DTTM, DESK, DTTM_RCVDFROM VREMEDY_SYM_TICKETWHERE CAST (CRTN_DTTM AS DATE) BETWEEN ADD_MONTHS(CURRENT_DATE,-4) AND CURRENT_DATE

MBSconnection 29 posts Joined 07/13
17 Jul 2013

We have SCHEMA NAME and VIEWS only.
What would be a Quick Query to show COLUMN NAMES as well as DATATYPES from VIEWS.
I can do Select * FROM SCHEMA.TABLENAMe but that bring VALUES. I am trying to do quick copy and paste of all colums and Data types in a Excel Sheet.
We are restricted to see TABLE NAME. 
 
Thank;s

dnoeth 4628 posts Joined 11/04
19 Jul 2013

HELP COLUMN databasename.viewname.*;
 
Dieter

Dieter

MBSconnection 29 posts Joined 07/13
07 Aug 2013

thanks. Lot of info I don't even know how to make sense, but I got what I needed. 

Column Name Type Nullable Format Max Length Decimal Decimal Fra Indexed? Unique? Primary? Char Type Temporal Column

NA_ID                 I  N -(10)9                         4 null null Y N P null N

M_NBR                I  N -(10)9                         4 null null Y N P null N

T_NBR                 I2 Y -(5)9                         2 null null Y N S null N

NA_ID                I  Y -(10)9                         4 null null Y N S null N

 

What is I2? CV is VARCHAR, I is Integer, What is CF? and What is PM Data Type?

 

In a  row that has Indexed Y, Unique N and Primary P does that mean this field is a Primary Key Field? and Nulls are not allowed?

 

In a  row that has Indexed Y, Unique N and Primary S What does that S mean?

 

Thanks for Help!

 

 

MBSconnection 29 posts Joined 07/13
08 Aug 2013

Dieter thanks alot. Found your another Post where you have explained it all! 
Great!

You must sign in to leave a comment.