CODEWITHSUNDEEP
sql-serversql-server-2005
Izulien
Izulien

Reputation: 413

SQL Server SELECT Money as blank space instead of 0.00

I am having trouble getting my values to come back as blank instead of 0.00 in a table variable.

Table Setup/Declaration.

        DECLARE @PolicyCoverages TABLE (
        CoverageType varchar(100),
        ScheduleLimit money,
        BlanketLimit money,
        Deductible money,
        Premium money
    )

    INSERT INTO @PolicyCoverages (CoverageType, ScheduleLimit, BlanketLimit, Deductible, Premium)    
    SELECT  'Words for a description',
            '10000.00', 
            '0.00', 
            '2500.00', 
            '142.50'

Once all the data is in place (there are many selects that pull in data) I want to pull specific information as shown below.

SELECT CoverageType,
       CASE WHEN ScheduleLimit < 0 THEN '' ELSE ScheduleLimit END AS ScheduleLimit,
       CASE BlanketLimit WHEN '0.00' THEN '' ELSE BlanketLimit END AS BlanketLimit,
       Deductible, 
       Premium 
    FROM @PolicyCoverages
    WHERE (ScheduleLimit > '0.00') OR (BlanketLimit > '0.00')

I do NOT want to display 0.00 I instead want a blank space ''. I've tried converts, I tried cast, and the two shown WHEN attempts.

WHEN CAST(ScheduleLimit as varchar(10)) < 0 THEN ''
WHEN Schedulelilmit < 0 THEN CONVERT(varchar(10), '')

No matter which way I attempt this it comes over as 0.00

Upvotes: 2

Views: 5086

Answers (3)

Izulien
Izulien

Reputation: 413

Sean Lange got it! Displaying the NULL did correct my issue. The front end (which I don't have control of) displays NULL values as blanks already so setting it to NULL corrects all.

SELECT CoverageType,
       CASE ScheduleLimit WHEN 0 THEN NULL ELSE ScheduleLimit END AS ScheduleLimit,
       CASE BlanketLimit WHEN 0 THEN NULL ELSE BlanketLimit END AS BlanketLimit,
       Deductible, 
       Premium 
    FROM @PolicyCoverages
    WHERE (ScheduleLimit > 0) OR (BlanketLimit > 0)

Upvotes: 0

sepupic
sepupic

Reputation: 8687

If you want '' that is character, you need to convert your money to varchar like this:

SELECT CoverageType,
       CASE WHEN ScheduleLimit < 0 THEN '' ELSE cast(ScheduleLimit as varchar(30)) END AS ScheduleLimit,
       CASE BlanketLimit WHEN 0 THEN '' ELSE cast(BlanketLimit as varchar(30)) END AS BlanketLimit,
       Deductible, 
       Premium 
    FROM @PolicyCoverages
    WHERE (ScheduleLimit > '0.00') OR (BlanketLimit > '0.00')

If you don't want your money become varchar, you should use NULL instead of ''.

You cannot display money and char in the same column, so you have no other choices: or you convert all the column to varchar, or you preserve money but use NULL instead of ''.

Upvotes: 2

Dheerendra
Dheerendra

Reputation: 308

Use the SPACE(n) function to get the spacebars. 'n' is an integer

Upvotes: 0

Related Questions