case statement question 2

[rds80]

In the case statement below I expect the result to be blank when CM.Cost is NULL or = to .0000. But when CM.Cost = .0000, I get the value as 0%.

LA.Numerator isn't equal to 0, so that isnt the problem.

COALESCE(dbo.udfFormatPct(
            (SELECT CASE WHEN CM.Cost IS NULL OR CM.Cost = .0000 THEN ''
                 ELSE CONVERT(varchar, (LA.Numerator/CM.Cost) * 100) END 
             FROM tblControlMaster CM
             INNER JOIN @LoanAmount LA
                ON LA.ControlID = CM.ControlID
             WHERE LA.NoteID = N.NoteID), 0, 1), '')

Any suggestions? or more info I could provide.

Thanks.

 

[SQLBill]

What datatype is CM.Cost?

Are you sure it is doing it to values of .0000? Could you have values of .0 or .00, or .000, or .00000?

Also, you REALLY should give a length to VARCHAR. Yes, I know that when used in CONVERT it defaults to 30, but it's still good practice.

-SQLBill

Posting advice: FAQ481-4875

 

[rds80]

CM.Cost is of type Money.
it's only .0000. On a side note, Why would a field of type money have 0 saved as .0000?
Thanks for the tip. Didn't know it defaults to 30. Always like knowing sql programmer conventions/tips.

 

[SQLBill]

Why would a field of type money have 0 saved as .0000?

Because MONEY is accurate to ten-thousands of a monetary unit.

Also, be aware that using VARCHAR or CHAR default to a length of 1 (except with CONVERT).

AW CR@#....I was getting ready to ask you to make this change to test it:
SELECT CASE WHEN CM.Cost IS NULL OR CM.Cost = .0000 THEN 'TEST'.

And that showed me the problem. (I think). CM.Cost is Money but a space/blank I believe are treated as a string. That's why you have to use the single quotes. So, instead of trying the test above, try this:

SELECT CASE WHEN CM.Cost IS NULL OR CM.Cost = .0000 THEN 123.5678

-SQLBill


Posting advice: FAQ481-4875

 

[rds80]

why would you need accuracy of ten thousandths for money when in actuality it just goes to .xx.

so if declare a variable like:
declare @Cost varchar
it only has a length of 1?

SELECT CASE WHEN CM.Cost IS NULL OR CM.Cost = .0000 THEN 123.5678

gives back values of 124%. the udf rounds the value and adds a % to the value.

so it should be '.0000' and cm.cost has to be converted to varchar?

 

[SQLBill]

Depends on the money type. Some countries money actually does go to ten-thousands. SQL Server is international.

Yes, try it:

DECLARE @test VARCHAR
SET @test = '12345'
PRINT 'Variable @test set for 12345 displays: ' + @test

Okay, what I was having you test was that the first line actually would work. This means the issue is data types. If you want the cm.Cost to return a blank when it meets the criteria in the first line, then you will need to convert all instances of cm.Cost to be VARCHAR(N) - where N is the max length you expect the value to be.

-SQLBill

Posting advice: FAQ481-4875

 

[rds80]

so there could be a coin thats worth ten thousandth of a unit?

Tried the test out..will definitely give a length to any variable I declare.

 

[SQLBill]

There could be.....but it's no just about coins. Let's say you are recording the monetary value of an item (a barrel of crude oil). Will the value of that barrel always be only two digits? No, it actually might go to four digits.

-SQLBill

Posting advice: FAQ481-4875

 

[rds80]

Interesting..thank you

 

[AlexCuse]

I think the 4 decimal places are used because of rounding issues that can come up in calculations. If you are performing a bunch of multiplication and division operations with money data type, you could get a very different result (by a few cents anyway, or even dollars if using large numbers) than you would if using decimal(10,2). So it is probably taken to 4 because this is enough to minimize inaccuracies in these types of calculations (because you really only want 2 significant digits usually).

Also, as SQL Bill says, per-unit costs are often not in whole cents. See your local gas station for another example.

Ignorance of certain subjects is a great part of wisdom

 

[rds80]

Thanks Alex.

I tried:

COALESCE(dbo.udfFormatPct(
            (SELECT CASE WHEN CONVERT(varchar(20), CM.Cost) = 'NULL' OR CONVERT(varchar(20), CM.Cost) = '.0000' THEN ''
                 ELSE CONVERT(varchar, (LA.Numerator/CM.Cost) * 100) END 
             FROM tblControlMaster CM
             INNER JOIN @LoanAmount LA
                ON LA.ControlID = CM.ControlID
             WHERE LA.NoteID = N.NoteID), 0, 1), '')

But I get the error: cannot divide by 0. LA.Numerator doesn't equal 0.

 

[gmmastros]

Try...

COALESCE(dbo.udfFormatPct(
            (SELECT Case When IsNull(CM.Cost, 0) = 0 
                         Then ''
                         Else Convert(VarChar(20), LA.Numerator / CM.Cost * 100) 
                         End
             FROM tblControlMaster CM
             INNER JOIN @LoanAmount LA
                ON LA.ControlID = CM.ControlID
             WHERE LA.NoteID = N.NoteID), 0, 1), '')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds80]

still get 0% rather then blanks, George.

 

[gmmastros]

At least you don't get an error. I suggest you try this...

COALESCE(
	[!]NullIf([/!]dbo.udfFormatPct(
            (SELECT Case When IsNull(CM.Cost, 0) = 0 
                         Then ''
                         Else Convert(VarChar(20), LA.Numerator / CM.Cost * 100) 
                         End
             FROM tblControlMaster CM
             INNER JOIN @LoanAmount LA
                ON LA.ControlID = CM.ControlID
             WHERE LA.NoteID = N.NoteID), 0, 1)[!], '0%')[/!], '')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds80]

very cool..how do you guys know about these conditional checks?

 

[gmmastros]

Rumor has it... That Alex memorized Books On Line.

Seriously, though. In Query Analyzer...

If you don't see the "object browser" on the left side of the screen, press F8. Near the bottom of the window, you will see "common object". Beneath that, there will be things like, "Configuration Functions", "Cursor Functions", Date And Time Functions", etc... Expand the sections and peruse the list of available functions. If something looks interesting, look up the syntax in Books On Line. Spend 10 minutes per day doing this, and eventually things will begin to make more sense.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds80]

Term is type int and it has NULL values.

I expect ISNULL(Term, '') to give me back blanks, but I get 0s.

Then I tried

CASE WHEN ISNULL(Term, '') = 0 THEN ''
ELSE TERM END

Still got 0s back.

Also tried COALESCE(NULLIF(ISNULL(Term, 0), 0), '') (got a little crazy). But that returned 0s.

 

[AlexCuse]

I only memorized the parts on the STUFF function. That seems to be the one I use the most in my code

Have you tried this?

select case isnumeric(term)
when 0 then ''
else term end

Good Luck,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

The problem is that you are mixing data types. When you mix data types, sql server will sometime automagically convert them for you, and sometimes it won't. Take a look at this example.

Declare @Temp Int

Select IsNull(@Temp, '')
Select IsNull(Convert(VarChar(20), @Temp), '')

Select Coalesce(@Temp, '')
Select Coalesce(Convert(varchar(20), @Temp), '')

Also, consider this...

Declare @Temp Int

Select @Temp = ''
Select @Temp

What do you think the output would be? If you guessed 0, then you were right. SQL Server automagically converted an empty string to 0 because the data type for @Temp is int.

Bottom line... you need to be very careful when mixing data types.

For more reading...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds80]

Still get 0s in the output..