CASE Statement won't let me use a DECODE statement..

Hello,
I have a Decode statement that has a Select statement that gives me an Oracle error - ORA-00936: missing expression

When I run the Select statement by itself, it runs fine.  Is it that I can't have a select statement like this below in a decode statement?  Or do I need to change the logic or syntax for this to work?
"If the member is a 'minor' then I want to show the column - mem_formatted_name, else just show a null value instead..."
Thanks

CASE WHEN DECODE(SELECT MINOR.MEM_FIRST_NAME
                 FROM   TPA_REPORT.MEM_MEMBER MINOR,
                             TPA_REPORT.MEM_MEMBER PARENT
                 WHERE MINOR.MEM_UID = PARENT.MEM_UID
                 AND     MINOR.MEM_MEM_UID_POLICY_HOLDER = PARENT.MEM_UID, NULL, NULL, MINOR.MEM_FORMATTED_NAME)
WHEN pvt_provider_type = 'HCF' THEN Faculty.pvd_name
WHEN pvt_provider_type = 'PHY' THEN Doctor.pvd_name END MailToName
MachinegunnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
your select within a decode must be inside parentheses

and your WHEN of the decode must be compared to something

here's a simple example

SELECT CASE
           WHEN DECODE((SELECT 1 FROM DUAL), 1, 'one', 'other') = 'one' THEN 'query returned 1'
           ELSE 'query returned something other than 1'
       END
  FROM DUAL;
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
you're missing something at:
NULL, MINOR.MEM_FORMATTED_NAME)
WHEN pvt_provider_type = 'HCF' THEN Faculty.pvd_name

it should read something like:
NULL, MINOR.MEM_FORMATTED_NAME) = 'whatever' THEN 'whatever'
WHEN pvt_provider_type = 'HCF' THEN Faculty.pvd_name
0
sdstuberCommented:
if your decode results shouldn't be compared to anythign because it is a result
then make it your ELSE condition to the case


SELECT CASE
           WHEN pvt_provider_type = 'HCF'
           THEN
               faculty.pvd_name
           WHEN pvt_provider_type = 'PHY'
           THEN
               doctor.pvd_name
           ELSE
               DECODE(
                   (SELECT minor.mem_first_name
                      FROM tpa_report.mem_member minor, tpa_report.mem_member parent
                     WHERE minor.mem_uid = parent.mem_uid
                       AND minor.mem_mem_uid_policy_holder = parent.mem_uid),
                   NULL, NULL,
                   minor.mem_formatted_name
               )
       END
           mailtoname
  FROM yourtable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MachinegunnerAuthor Commented:
Thanks for the replies!  I went ahead with this logic and it works now, thanks again.
With all the help, will split the pot three ways, as all of them would have worked.
Thanks again.

CASE
    WHEN pvd_provider_type = 'HCF' THEN FACILITY.pvd_name
    WHEN pvd_provider_type = 'PHY' THEN DOCTOR.pvd_name
    ELSE DECODE((SELECT 'Y'
                 FROM  TPA_REPORT.MEM_MEMBER MINOR,
                       TPA_REPORT.MEM_MEMBER PARENT
                 WHERE MINOR.MEM_UID = MEM_MEMBER.MEM_UID
                 AND   MINOR.MEM_MEM_UID_POLICY_HOLDER = PARENT.MEM_UID), NULL, NULL, MINOR.mem_formatted_name)
 END MailToName,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.