5

I'm trying to set a default text value for the already derived column "Sub1" Is this possible? I've created placeholders for the columns to be derived using a value of 0. I'm working in Teradata if it matters..... THANKS!

select c_clm
,dt.Claimant_Name
,dt.i_ssn
,0 as "Time_Period"
,0 as "Unit_ID",
/*,idv.C_PDT AS "Product"
,er.C_FRM_POL AS "Group Product",*/
(case
 when idv.c_pdt is null then 'GLTC' 
 else idv.c_pdt
 end) as "Product_ID"
,c_ams_clm as "DBS_UserID"
,upc_indiv_org_id as "DBS_EEID"
,0 as "Categ"
,0 as Categ_sort
,0 as "Sub1"
,0 as "Sub1_Sort"
,0 as "Sub2"
,0 as "Sub2_Sort"
,0 as "MTD_Num" 
,0 as "MTD_Denom"
,dt.i_pol as "Policy_Number"
,COALESCE (erpol.n_pol,idv.n_pol, ks.n_pol)
BellevueBob
  • 9,498
  • 5
  • 29
  • 56
gfuller40
  • 1,183
  • 9
  • 19
  • 36
  • 1
    How are you deriving this column? Under what conditions would this "default value" be applied? – Joe Stefanelli Aug 30 '12 at 19:28
  • What do you mean by "default text value"? Do you mean a `TITLE` for the column or do you want to translate a zero into some text string? – BellevueBob Aug 30 '12 at 19:30
  • I mean as it stands now it creates the column "Sub1" and there is a value of "0" in it. I'd like a value of "Text" to be inserted in each row of column "Sub1" rather than "0". – gfuller40 Aug 30 '12 at 19:32
  • "Default text value"? Based on what criteria? – Kermit Aug 30 '12 at 19:41

2 Answers2

29

Are you trying to do this:

select '0' as Sub1

You can assign a text value directly, the same way as a number.

Or, are you trying to assign a value to a column when it is NULL in a SELECT. To do this, use COALESCE:

select coalesce(Sub1, '0') as Sub1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Ideally i would like to Select "default value" as sub1 so that in every row of sub1 i get "default value" as a string. – gfuller40 Aug 30 '12 at 19:33
  • 1
    If i try to do Select "defaultvaluehere" as sub1 I get an error "defaultvaluehere column not found" – gfuller40 Aug 30 '12 at 19:36
5

If you are asking how to use a character string constant to populate a column, just put in in single-quotes and give it a name. It's usually also best to specify the desired column type. For example:

select 'Text String' (VARCHAR(255)) as sub1

Note that if you use double-quotes, Teradata will think your are referring to a column by that name (hence the error message you received.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56