Case Question

[Zeroanarchy]

Hi thanks for taking the time to look at this. I am interested to know if this is possible in a Oracle query.

What I am trying to do is create two columns and the second column is based on the first columns value.

So in the select query I have hte following. The issue is that Oracle tells me the "Covered" is an invalid identifier.

CASE WHEN BENEFITLIMIT.Payable = Null
THEN 'No'
WHEN BENEFITLIMIT.URL = Null
THEN 'No'
ELSE 'Yes'
END Covered,

CASE WHEN Covered = 'No'
THEN 'Blank'
ELSE LPS_SERVICES.WAITINGPERIODTIME
END WaitingPeriod

All help appreciated.

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SantaMufasa]

Zero,

Yes, you can achieve what you want with a little "trick":

SELECT Covered
      ,CASE WHEN Covered = 'No'
            THEN 'Blank'
            ELSE WAITINGPERIODTIME
       END WaitingPeriod
  FROM (SELECT CASE WHEN BENEFITLIMIT.Payable = Null
                    THEN 'No'
                    WHEN BENEFITLIMIT.URL = Null
                    THEN 'No'
                    ELSE 'Yes'
               END Covered
              ,WAITINGPERIODTIME
          FROM LPS_SERVICES...);

Let us know how this works for you.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Zeroanarchy]

Thanks Mufasa for your help, I am getting a "missing right parenthesis" any ideas??

SELECT Covered
      ,CASE WHEN Covered = 'No'
            THEN 'Blank'
            ELSE WAITINGPERIODTIME
       END WaitingPeriod
  FROM (SELECT CASE WHEN BENEFITLIMIT.Payable = Null
                    THEN 'No'
                    WHEN BENEFITLIMIT.URL = Null
                    THEN 'No'
                    ELSE 'Yes'
               END Covered,
	LPS_SERVICES.LSERVICENAME,
	BENEFITLIMIT.Payable, 
	BENEFITLIMIT.SSTATENAME, 
	BENEFITLIMIT.SITEPREFIX, 
	BENEFITLIMIT.LPRODUCTTYPE, 
	BENEFITLIMIT.URL
FROM  
		BENEFITLIMIT
		LPS_SERVICES

RIGHT JOIN LPS_SERVICES 

WHERE 
BENEFITLIMIT.SORT_ORDER	= LPS_SERVICES.SORT_ORDER

Thanks

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SantaMufasa]

Yes, Zero...as the error message says, you are "missing (a)right parenthesis"...at least I cannot see one anywhere. Put a right paren at the end, try it again, and let us know the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

WHEN BENEFITLIMIT.Payable = Null

In my version of Oracle, this would never result in a true,
surely it would be

WHEN BENEFITLIMIT.Payable IS Null

Or maybe this is a functionality I wasn't aware of?

 

[SantaMufasa]

Jim, you are absolutely correct...Since I was focussing on the CASE statement, I failed to focus on other aspects. When comparing a value (in Oracle) to NULL, one must use IS NULL and IS NOT NULL for correct results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Thanks Dave (I thought i was going mad and had missed some new functionality :lol

 

[Zeroanarchy]

Thanks Mufasa and Jim for your help, I seem to still be a little stuck on this. Unfortunetly I am now getting a
"FROM Keyword not found where expected."

SELECT Covered,
       CASE WHEN Covered = 'No'
            THEN 'Blank'
            ELSE WAITINGPERIODTIME
       END WaitingPeriod
  FROM  (SELECT CASE WHEN BENEFITLIMIT.Payable is Null
                    THEN 'No'
                    WHEN BENEFITLIMIT.URL is Null
                    THEN 'No'
                    ELSE 'Yes'
               END Covered,
    LPS_SERVICES.LSERVICENAME,
    BENEFITLIMIT.Payable, 
    BENEFITLIMIT.SSTATENAME, 
    BENEFITLIMIT.SITEPREFIX, 
    BENEFITLIMIT.LPRODUCTTYPE, 
    BENEFITLIMIT.URL
FROM  
    BENEFITLIMIT
    LPS_SERVICES
RIGHT JOIN 
    LPS_SERVICES 
WHERE 
    BENEFITLIMIT.SORT_ORDER    =LPS_SERVICES.SORT_ORDER)

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[Zeroanarchy]

Thanks guys looks like I have solved the problem, missing comar. I should pay more attention to detail.

Cheers


ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[Zeroanarchy]

Ok sorry looks like I still have an error that I can not solve. "From Keyword not found where expected" Line "13"
Line 13 is Bold.

Thanks in advance.

Here is the code

SELECT
	    LPS_SERVICES.LSERVICENAME,
	    BENEFITLIMIT.Payable,
		LPS_SERVICES.WAITINGPERIODTIME,
	    BENEFITLIMIT.SSTATENAME,
	    BENEFITLIMIT.SITEPREFIX,
	    BENEFITLIMIT.LPRODUCTTYPE,
	    BENEFITLIMIT.URL,
		Covered,
		CASE WHEN Covered = 'No'
	            THEN 'Blank'
	            ELSE LPS_SERVICES.WAITINGPERIODTIME [b]
	       END LPS_SERVICES.WAITINGPERIODTIME
[/b]	  FROM  (SELECT CASE WHEN BENEFITLIMIT.Payable is Null
	                    THEN 'No'
	                    WHEN BENEFITLIMIT.URL is Null
	                    THEN 'No'
	                    ELSE 'Yes'
	               END Covered
FROM
        BENEFITLIMIT
RIGHT OUTER JOIN
LPS_SERVICES ON BENEFITLIMIT.SORT_ORDER	= LPS_SERVICES.SORT_ORDER
WHERE
LPS_SERVICES.DISPLAY	=	'Y');

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[jimirvine]

change

END LPS_SERVICES.WAITINGPERIODTIME

to

END WAITINGPERIODTIME

you might also consider

CASE Covered WHEN  'No'
                THEN 'Blank'
                ELSE LPS_SERVICES.WAITINGPERIODTIME END

No need for a searched case expression in this instance

HTH
Jim

 

[cmmrfrds]

It looks like you are creating an inline view and passing the info back to an outer Select. If so, format is like.

SELECT
LSERVICENAME,
Payable,
WAITINGPERIODTIME,
SSTATENAME,
SITEPREFIX,
LPRODUCTTYPE,
URL,
Covered,
CASE WHEN Covered = 'No'
THEN 'Blank'
ELSE WAITINGPERIODTIME
END WAITINGPERIODTIME2
FROM
(SELECT
LPS_SERVICES.LSERVICENAME,
BENEFITLIMIT.Payable,
LPS_SERVICES.WAITINGPERIODTIME,
BENEFITLIMIT.SSTATENAME,
BENEFITLIMIT.SITEPREFIX,
BENEFITLIMIT.LPRODUCTTYPE,
BENEFITLIMIT.URL,
CASE WHEN BENEFITLIMIT.Payable is Null
THEN 'No'
WHEN BENEFITLIMIT.URL is Null
THEN 'No'
ELSE 'Yes'
END Covered
FROM
BENEFITLIMIT
RIGHT OUTER JOIN
LPS_SERVICES ON BENEFITLIMIT.SORT_ORDER = LPS_SERVICES.SORT_ORDER
WHERE
LPS_SERVICES.DISPLAY = 'Y');