COALESCE is a simple function which takes one or more column names and a replacement (default) value which can be displayed in case of the missing data. For example, in an employee table you want to display all the data of the column emp_first_name and in case there is no data present you want to display a constant value; no_first_name. Then the following query would help you achieve what you want.
SELECT COALESCE (emp_first_name,’no_first_name’)
FROM employee
In case you want to display emp_last_name (another column in employee table) with the missing data for emp_first_name, then run the query
SELECT COALESCE (emp_first_name, emp_last_name)
FROM employee
But there is a chance that both of them could be empty and you want to display something in case both missing then go for the following query.
SELECT COALESCE (emp_first_name, emp_last_name,’no_name’)
FROM employee
Another better and effective way to implement ‘IF THEN ELSE’ condition on the Select statement is to use the CASE expression. Following query would display the current date and if the current date is equal to ‘2007/06/26′ then it would display the string ‘HI’ in case if the current date is not equal to ‘2007/06/26′ then it would display ‘HELLO’. Try this out…
SELECT (CASE date WHEN ‘2007/06/26′ THEN ‘HI’ ELSE ‘HELLO’ END)
Popularity: 50% [?]
Our Random Articles
- New Year Wishes 2010
- New Year Wishes
- How to use EXTRACT function with date and time columns
- How to Post on a Separate/New Page in WordPress
- Usage of Macro in Teradata
More Links
14 Comments
I want to retrieve only the hour part and minute part from the date. Please can u help me in this regard?
what is the CASE syntax for
if 2 then two
if 3 then three
else other
…
Hi,
Please provide a little more info on your need. A simple CASE statement syntax is as follows
CASE <column-name>
WHEN val 1/Cond 1 THEN result 1
WHEN val 2/Cond 2 THEN result 2
……………………….
……………………….
……………………….
WHEN val n/Cond n THEN result n
[ ELSE default/unknown result ]
END [Column Alias]
Hi,
I am new to Teradata and I want to show date in following format :
19-Jan-2008
Please tell me using which function i can show the date in the above format.
Thanks and Regards
Jasmin
Hi Jasmin,
You can use the following;
SELECT date(format ‘dd-mmm-yyyy’);
This might not work on SQL Assistant but would work on BTEQ session.
Hi
i am new to Teradta can any one tell me that is ARRAY is there in teradata and also please tell me how to use it in a stored procedure.
Thanks and Regards
Jasmin
Hi Jasmin,
I am not sure whether we have arrays in Teradata. But, what is your need, if you can elaborate, may be we can work out something else.
Hi,
1) I have 5 huge tables with disjoi1nt data columns except the PI and date column.I need to merge the data into one table so that, one row per account(PI) is created with individual data columns from the 5 tables. The accts may or maynot match between the tables. How do I do this without performance issues? LEFT JOIN?
2) I have a one-rowed table which has NUPI of run-date. Some data tables have acct-key as UPI and run-date as PPI.But some data tables have neither acct-key nor run-date columns. I might have to build join across tables-with-columns, tables-without-those-columns and the one-row table to evaluate data in CASE statements. EXPLAIN shows product join and is badly skewed. How can I rewrite this better?
Ex: T1(huge table with columns)
T2(huge table without columns)
SR(single row table)
sel
Sum(case
when T1.t1 = ‘X’ and
SR.code = ‘ ‘ and
T2.t2 = ‘Y’
then ‘GOOD ACCT’
end) as qualified_acct
from
T1, T2, SR
where
T1.acct-key = T2.acct-key and
T1.run-date = SR.run-date;
Hi Tanmayi,
For the 1st question, you should use FULL OUTER JOIN instead of LEFT JOIN. As you mentioned you want all the rows( matching and not matching) FULL OUTER JOIN would be more applicable.
I am a bit confused on your 2nd question, when you say T2 does
not have neither acct-key nor run-date columns then how can you join it with acct-key with T1 as under:
T1.acct-key = T2.acct-key and
I guess, I am missing something here, can you please elaborate a bit more.
Hi,
Can we create and execute macros inside the procedure and how to use this?
Thanks & Regards
Punam
hi every one,
I am new to teradata. i want to teradata sql pdf’s.
Thanks,
krisha.
Hi Krisha,
Welcome to the world of Teradata. Well when you talk about PDFs do you mean to say Teradata Documention PDFs or something else.
For TeraData Documentation you can goto the following site:
http://www.info.teradata.com/
Thanks
you can use this sql for retrieving the hour and time respectively;;
select (extract (hour from current_timestamp(0)) || extract(minute from current_timestamp(0)))
Trackback & Pingback
Popular Articles