CODEWITHSUNDEEP
sql
Nate Pet
Nate Pet

Reputation: 46322

SQL Default Values

I have a select statement which generates a Type and quantity:

I normally have the following

Type       Qty
TypeOne    1
TypeTwo    23
TypeThree  1

Say in a certain situation, I do not get a value returned for Two I would get with:

  Select Type, Qty from Tbl1

My data would look like

Type      Qty
TypeOne    1
TypeThree  1

If i do not get a certain value I like to default that in (in this case since TypeTwo does not exist, I like to default it to 0 and add that entry)

Type Qty

TypeOne    1
TypeTwo    0
TypeThree  1

Upvotes: 0

Views: 290

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52675

One easy way is to have a Type Table that contains the columns Type and default then you could do the following.

   Select 
        type.Type, 
        COALESCE(tbl1.Qty,type.default) qty
   from
       type 
       LEFT JOIN Tbl1
       ON type.type = tbl1.type

If you don't have a type table, depending on the Database you could either create a temporary table or use a WITH Clause to create the mapping between type and default value as well as the list of the types

for example

With Type
( SELECT `TypeOne` type, 0 default 
  UNION ALL SELECT `TypeTwo` type, 4 default 
  UNION ALL SELECT `TypeThree` type, 3 default )
Select 
    type.Type, 
    COALESCE(tbl1.Qty,type.default) qty
from
    type 
    LEFT JOIN Tbl1
    ON type.type = tbl1.type

Upvotes: 4

Related Questions