CODEWITHSUNDEEP
sqlviewdefaultisnull
djobert
djobert

Reputation: 55

default value for column in SQL view

I have an SQL view vith many columns. ID (land parcel ID) ex : 7465-85-7468 Building (ID of a specific building if needed) ex : 102 Room (ID of a specific room if needed) ex : 0023

I have an ID for each row, but for most of the rows, there is not Building ID or Room ID

I'd like to fill the blank rows with '000' for building, and '0000' for Room.

I could Concat these fields in a fourth field (full ID)

It tried to get a default value with "ISNULL", but it didn't work.

For Now, I have somethins like this

ID / Building / Room / Full ID

7848-05-6956 / / / 7848-05-6956--

6985-26-7485 / 102 / 0000 / 6985-26-7485-102-0000

7236-12-0145 / / 0223 / 7236-12-0145--0223

I'd need this

ID / Building / Room / Full ID

7848-05-6956 / 000 / 0000 / 7848-05-6956-000-0000

6985-26-7485 / 102 / 0000 / 6985-26-7485-102-0000

7236-12-0145 / 000 / 0223 / 7236-12-0145-000-0223

Upvotes: 0

Views: 2795

Answers (1)

Alf47
Alf47

Reputation: 571

If the field is not nullable, the data may be stored as empty strings rather than NULL's.

Try doing something like the following:

SELECT x.ID, x.Building, x.Room, ID + '-' + Building + '-' + Room AS [Full ID]
FROM (
  SELECT
    ID
  , CASE WHEN ISNULL(Building, '') = '' THEN '000' ELSE Building END AS Building
  , CASE WHEN ISNULL(Room, '') = '' THEN '0000' ELSE Room END AS Room
  FROM MyTable ) x

This will handle both NULL and empty string and it will allow you to build Full ID without executing the CASE statements twice.

Upvotes: 5

Related Questions