1

I have a string having only digits and a space.
I am using CASE and NVL function to get the output.
If string is NULL then use the same string value else use 1.
I am able to different behavior for SQL and PLSQL.
Can anyone provide some explanation about this behavior and which one is the best approach to follow.

in SQL

NVL function works and provides result without TRIM

WITH T AS (SELECT ' 1234' LV FROM DUAL )
SELECT '"'||NVL(LV,1)||'"'  LV
FROM T;

LV    
-------
" 1234"

CASE gives ORA-00932 error

WITH T AS (SELECT ' 1234' LV FROM DUAL )
SELECT CASE WHEN LV IS NOT NULL THEN LV ELSE 1 END
FROM T;


Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

in PLSQL

NVL provides result with space

SET SERVEROUTPUT ON
DECLARE
LV VARCHAR2(10):=' 1234';
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE CASE -"'||LV||'"');
LV:=NVL(LV,1);
DBMS_OUTPUT.PUT_LINE('AFTER CASE -"'||LV||'"');
END;

Result:
BEFORE CASE -" 1234"
AFTER CASE -" 1234"

Case Statement TRIMs the result

SET SERVEROUTPUT ON
DECLARE
LV VARCHAR2(10):=' 1234';
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE CASE -"'||LV||'"');
LV:=CASE WHEN LV IS NOT NULL THEN LV ELSE 1 END;
DBMS_OUTPUT.PUT_LINE('AFTER CASE -"'||LV||'"');
END;

Result:
BEFORE CASE -" 1234"
AFTER CASE -"1234"
hemalp108
  • 1,209
  • 1
  • 15
  • 23
  • `WITH T AS ( SELECT ' 1234' LV FROM DUAL ) SELECT CASE WHEN LV IS NOT NULL THEN LV ELSE '1' END FROM T;` – MT0 Nov 25 '16 at 14:40
  • @MT0 of course this will return what we expect but it not explains why Oracle do the cast to number in `case` and don't in `nvl` – Kacper Nov 25 '16 at 14:41
  • 2
    The explanation is that this is what Oracle does. Read Oracle's documentation on `NVL function` and `CASE expressions`. The documentation clearly explains how each behaves when the datatypes involved are all different. – Matthew McPeak Nov 25 '16 at 14:42
  • @MatthewMcPeak: In this scenario what shall we prefer NVL or CASE? – hemalp108 Nov 25 '16 at 14:47
  • http://stackoverflow.com/questions/13712763/are-a-case-statement-and-a-decode-equivalent this thread may give some more explanations. It is comparing `case` with `decode` not `nvl` but I think `nvl` works more like `decode` than `case` – Kacper Nov 25 '16 at 14:51
  • @hemalp108 either is fine. The preference is for using the correct datatype for your null output with whichever one you decide to go for – Boneist Nov 25 '16 at 14:53
  • @Boneist: My code is written in PLSQL, so I suppose using NVL is a better choice over CASE as it does not trim the result. However as Tom Kyte suggested NVL is slower than DECODE [link](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7806711400346248708). So I am a bit skeptical about the choice. – hemalp108 Nov 25 '16 at 14:57
  • 2
    There's always COALESCE; that can be faster than NVL because it performs short circuiting. – Boneist Nov 25 '16 at 15:03
  • COALESCE will also TRIM the string, correct? In case I need to keep the string as it is then I can use type casting. – hemalp108 Nov 25 '16 at 15:11
  • 2
    `COALESCE` will not trim anything in either SQL or PL/SQL, if you do not rely on implicit casting. `LV := coalesce(LV, '1')` is probably the best way to write your statement - or use `CASE` but also with `'1'`, not `1`. You have just run into an excellent illustration of why you should use compatible data types everywhere and not rely on Oracle to "overlook" some mismatches and to silently provide a "fix" for you: the "fix" is correct half the time AT BEST. –  Nov 25 '16 at 15:18
  • 2
    @hemalp108 I'm struggling to see why this is an issue at all. You're getting inconsistent results because you aren't using the correct datatypes, and the different methods handle the implicit conversions in different ways. You know you're after a string, so define the replacement-for-null-value as a string and you don't have to worry about anything. – Boneist Nov 25 '16 at 15:26
  • Yes correct. It is better not to rely upon implicit type casting. It makes better sense now. – hemalp108 Nov 25 '16 at 16:14

1 Answers1

2

Problem is in your case statement.

CASE WHEN LV IS NOT NULL THEN LV ELSE 1 END

Here LV is of type varchar and in else statement, You are assigning 1 as an integer value which created problem for different data types.

Just update your case statement as mentioned below and it will work in oracle

CASE WHEN LV IS NOT NULL THEN LV ELSE '1' END

maulik kansara
  • 1,087
  • 6
  • 21