Home » SQL & PL/SQL » SQL & PL/SQL » case statemnent
case statemnent [message #620436] Fri, 01 August 2014 07:32 Go to next message
mist598
Messages: 917
Registered: February 2013
Location: Hyderabad
Senior Member
Hi,
If fullname is >2 then i need morethen 3 is not possible , if <2 then need to display Fullname
SELECT CASE 
         WHEN ( Length (:fullname) - Length (Replace (:fullname, '||', ':')) ) > 
              2 THEN 
         'morethen 3 is not possible' 
         WHEN ( Length (:fullname) - Length (Replace (:fullname, '||', ':')) ) < 
              2 THEN 
         'Full name' 
         ELSE 'Nothing' 
       END "name" 
FROM   dual 

Re: case statemnent [message #620447 is a reply to message #620436] Fri, 01 August 2014 08:38 Go to previous messageGo to next message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
May I first ask what you are trying to accomplish?
(post a working testcase?)
Re: case statemnent [message #620448 is a reply to message #620447] Fri, 01 August 2014 08:41 Go to previous messageGo to next message
mist598
Messages: 917
Registered: February 2013
Location: Hyderabad
Senior Member
Hi , thanks for reply, we existing package we need to change as like as shown below example.


Thanks
Re: case statemnent [message #620450 is a reply to message #620448] Fri, 01 August 2014 08:45 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Your statement "works" but has an unexpected result for len=2:
WITH data(fullname) AS
 (SELECT 'AAAAAA'  FROM dual UNION ALL
  SELECT 'A||'     FROM dual UNION ALL
  SELECT 'A||||'   FROM dual UNION ALL
  SELECT 'A||||||' FROM dual UNION ALL
  SELECT ''        FROM dual UNION ALL
  SELECT NULL      FROM dual)
SELECT fullname, Replace (fullname, '||', ':')rep, Length (fullname) - Length (Replace (fullname, '||', ':'))len, CASE 
         WHEN ( Length (fullname) - Length (Replace (fullname, '||', ':')) ) > 
              2 THEN 
         'morethen 3 is not possible' 
         WHEN ( Length (fullname) - Length (Replace (fullname, '||', ':')) ) < 
              2 THEN 
         'Full name' 
         ELSE 'Nothing' 
       END name 
  FROM data;

FULLNAME   REP LEN      NAME
---------------------------------------------
AAAAAA	AAAAAA	0	Full name
A||	A:	1	Full name
A||||	A::	2	Nothing
A||||||	A:::	3	morethen 3 is not possible
			Nothing
			Nothing

[Updated on: Fri, 01 August 2014 08:47]

Report message to a moderator

Re: case statemnent [message #620451 is a reply to message #620448] Fri, 01 August 2014 08:49 Go to previous messageGo to next message
martijn
Messages: 278
Registered: December 2006
Location: Netherlands
Senior Member
hmmmm
let me rephrase that question Idea
What does the input look like (test case)?
What does the desired output look like?
What doe your query ouput look like. (in other words...where is something going wrong)

(
btw...your opening post didn't state a question Smile
And your second didn't also not contain a question Smile
So now I don't know what to answer Smile
)
Re: case statemnent [message #620452 is a reply to message #620451] Fri, 01 August 2014 09:03 Go to previous messageGo to next message
mist598
Messages: 917
Registered: February 2013
Location: Hyderabad
Senior Member
Hi,

We can allow 3 user's and we cannot allow more than 3 user's
with concatination more than one user

[Updated on: Fri, 01 August 2014 09:04]

Report message to a moderator

Re: case statemnent [message #620456 is a reply to message #620452] Fri, 01 August 2014 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59081
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Question
Ununderstandable.
What does mean "If fullname is >2"? Is 'Michel' > 2 (I hope so)?

Once more, where is the test case?

[Updated on: Sat, 02 August 2014 02:06]

Report message to a moderator

Re: case statemnent [message #620479 is a reply to message #620456] Sat, 02 August 2014 01:11 Go to previous message
jpatel7
Messages: 1
Registered: February 2014
Location: Surat
Junior Member
please provide proper details
Previous Topic: Rights query
Next Topic: how to select data from subpartition table
Goto Forum:
  


Current Time: Tue Sep 16 06:45:06 CDT 2014

Total time taken to generate the page: 0.06852 seconds