[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 CutlerCommented:
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 CutlerCommented:
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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