• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Default value in SQL Select statement

Experts,

I have a rather simple IF statement I need help with. in the 3rd IF, I am selecting from a table. On occasion there is no match and the query returns NULL. If there in no match in the table I need to return the default value of 'ULTL'. I think the answer is either within COALESCE or IS NULL, but have not got either to work properly

DECLARE @CARR as CHAR(10);
DECLARE @QTY as INTEGER;
DECLARE @CITY as CHAR(20);
SET @CARR = null;
SET @QTY = 30;
SET @CITY = 'OSHAWA'

IF (@CARR IS NOT NULL)
      SELECT @CARR
ELSE
      IF @QTY < 15
      SELECT 'UCOU'
ELSE
      SELECT CARR_CODE FROM FMCARR
      WHERE @CITY = CITY
--ELSE SELECT 'ULTL'
0
JDCam
Asked:
JDCam
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
ELSE
      SELECT isnull(CARR_CODE, ULTL) as CARR_CODE FROM FMCARR
      WHERE @CITY = CITY
0
 
clampsCommented:
just do it like this.
Cheers
IF (@CARR IS NOT NULL)
      SELECT @CARR
ELSE
      IF @QTY < 15
      SELECT 'UCOU'
ELSE
      SELECT ISNULL(CARR_CODE, 'ULTL') FROM FMCARR
      WHERE @CITY = CITY
--ELSE SELECT 'ULTL'

Open in new window

0
 
JDCamAuthor Commented:
ged325
Modified as shown, but still returns a null column

DECLARE @CARR as CHAR(10);
DECLARE @QTY as INTEGER;
DECLARE @CITY as CHAR(20);
SET @CARR = null;
SET @QTY = 30;
SET @CITY = 'XOSHAWA'

IF (@CARR IS NOT NULL)
      SELECT @CARR
ELSE
      IF @QTY < 15
      SELECT 'UCOU'
ELSE
      SELECT ISNULL(CARR_CODE, 'ULTL') as CARR_CODE FROM FMCARR
      WHERE @CITY = CITY
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JDCamAuthor Commented:
Clamps,
same change, same result (see above)
It is retuning a null column called 'CARR_CODE'
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
remove the parens

DECLARE @CARR as CHAR(10);
DECLARE @QTY as INTEGER;
DECLARE @CITY as CHAR(20);
SET @CARR = null;
SET @QTY = 30;
SET @CITY = 'XOSHAWA'

IF @CARR IS NOT NULL
      SELECT @CARR
ELSE IF @QTY < 15
      SELECT 'UCOU'
ELSE
      SELECT ISNULL(CARR_CODE, 'ULTL') as CARR_CODE FROM FMCARR
      WHERE @CITY = CITY
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
TRY THIS...
If this fails, remove GROUP BY.

DECLARE @CARR as CHAR(10);
DECLARE @QTY as INTEGER;
DECLARE @CITY as CHAR(20);
SET @CARR = null;
SET @QTY = 30;
SET @CITY = 'OSHAWA'

IF (@CARR IS NOT NULL)
      SELECT @CARR
ELSE
      IF @QTY < 15 
      SELECT 'UCOU'
ELSE
	SET @QTY = SELECT Count(CARR_CODE) FROM FMCARR WHERE @CITY = CITY GROUP BY CARR_CODE
      	IF @QTY == 0
		SELECT ULTL
	ELSE
		SELECT CARR_CODE FROM FMCARR WHERE @CITY = CITY GROUP BY CARR_CODE
	END
END

Open in new window


Cheers
Evan
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
The other thing to check is to make sure you have NULL for the value . . . not 'NULL' (one being text, one being the value null).

Does the entry exist at all for @city?
0
 
JDCamAuthor Commented:
ged325,
there is either a match, or no entry all all. no actual NULL values in the table

acree123,
Partial sucess.
I had to remove the group by,  add a set of bracket to the SET @QTY select, remove the double == and remove the END for it to work

Now it runs with no errors, but can return 2 results. It the example below I made the 2nd IF true, but thr 3rd is still returning a null column

DECLARE @CARR as CHAR(10);
DECLARE @QTY as INTEGER;
DECLARE @CITY as CHAR(20);
SET @CARR = null;
SET @QTY = 10;
SET @CITY = 'XOSHAWA'

IF (@CARR IS NOT NULL)
      SELECT @CARR
ELSE
      IF @QTY < 15
      SELECT 'UCOU'
ELSE
      SET @QTY = (SELECT Count(CARR_CODE)
      FROM FMCARR      WHERE @CITY = CITY)
      IF @QTY = 0
            SELECT 'ULTL'
      ELSE
            SELECT CARR_CODE FROM FMCARR
            WHERE @CITY = CITY
0
 
JDCamAuthor Commented:
Got it..!!  Just had to rearrange the pieces provided.

DECLARE @CARR as CHAR(10);
DECLARE @QTY as INTEGER;
DECLARE @CITY as CHAR(20);
SET @CARR = null;
SET @QTY = 30;
SET @CITY = 'OSHAWA'

IF (@CARR IS NOT NULL)
      SELECT @CARR
ELSE
      IF @QTY < 15
      SELECT 'UCOU'
ELSE
      IF (SELECT Count(CARR_CODE)
      FROM FMCARR      WHERE @CITY = CITY) > 0
            SELECT CARR_CODE FROM FMCARR
            WHERE @CITY = CITY
ELSE SELECT 'ULTL'
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
replace the else below this.  My latest code post should work, weird why you are still getting null.

To fix your latest:

ELSE
      SET @QTY = (SELECT Count(CARR_CODE)
      FROM FMCARR      WHERE @CITY = CITY)
      IF @QTY = 0
            SELECT 'ULTL'
      ELSE
            SELECT CARR_CODE FROM FMCARR
            WHERE @CITY = CITY

another try:

ELSE
      SELECT case when CARR_CODE IS NULL then 'ULTL' else CARR_CODE end as CARR_CODE          
      FROM FMCARR
      WHERE @CITY = CITY

0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
I'm glad you got it to work,
Sorry about the lack of testing,
I had to do it with notepad.
SQL Server is not at my site.
They do ORACLE.

Talk about brain translation.
lol

Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now