SELECT CASE query works in SQL Server but NOT in Oracle due to error ORA-00923: FROM keyword not found where expected

The Below SELECT and CASE  query works perfectly well in SQL Server however I get the error in Oracle as " ORA-00923: FROM keyword not found where expected " on the last line which I don't understand why .

My query is

 SELECT
CASE WHEN count1 > COALESCE(count2,0)  THEN count1 
	WHEN count2 > COALESCE(count3,0) THEN count2 
	ELSE COALESCE(count3,0) END AS ADDUNITS 
FROM
(
  SELECT
    (SELECT max(ADDUNITS)  FROM MODDATA where upper('p4') IN (upper(MOD1),upper(MOD2),upper(MOD3))
	AND upper('et') IN (upper(MOD1),upper(MOD2),upper(MOD3))
	AND (upper(MOD1) IN (upper('p4'), upper('et')) OR upper(MOD1) IS null OR upper(MOD1) = '' ) 
	AND (upper(MOD2) IN (upper('p4'), upper('et')) OR upper(MOD2) IS null OR upper(MOD2) = '') 
	AND (upper(MOD3) IN (upper('p4'), upper('et')) OR upper(MOD3) IS null OR upper(MOD3) = '') 
	AND ICD10 IS NULL) AS count1,
    (SELECT max(ADDUNITS) FROM MODDATA  WHERE upper('p4') IN (upper(MOD1),upper(MOD2),upper(MOD3)) 
		AND (upper(MOD1) IN ('p4') OR upper(MOD1) IS null OR upper(MOD1) = '') 
		AND (upper(MOD2) IN ('p4') OR upper(MOD2) IS null OR upper(MOD2) = '') 
		AND (upper(MOD3) IN ('p4') OR upper(MOD3) IS null OR upper(MOD3) = '') 
		AND ICD10 = 'T88') AS count2,
	(SELECT max(ADDUNITS) FROM MODDATA  WHERE upper('et') IN (upper(MOD1),upper(MOD2),upper(MOD3)) 
		AND (upper(MOD1) IN ('et') OR upper(MOD1) IS null OR upper(MOD1) = '') 
		AND (upper(MOD2) IN ('et') OR upper(MOD2) IS null OR upper(MOD2) = '') 
		AND (upper(MOD3) IN ('et') OR upper(MOD3) IS null OR upper(MOD3) = '') AND ICD10 = 'T88') AS count3) as counts

Open in new window


Please suggest suitable modification so that above query works in both SQL Server and Oracle.
LVL 9
ronan_40060Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Russ SuterSenior Software DeveloperCommented:
Take out the "AS" keyword when naming column and table aliases. There are just some things that each platform is picky about. Table & column aliasing happens to be one of Oracle's bugaboos.
ronan_40060Author Commented:
Thank you

with AS keyword removed my query now looks like below   and I still get  ORA-00923: FROM keyword not found where expected on the last line , please suggest

 SELECT
CASE WHEN count1 > COALESCE(count2,0)  THEN count1 
	WHEN count2 > COALESCE(count3,0) THEN count2 
	ELSE COALESCE(count3,0) END ADDUNITS 
FROM
(
  SELECT
    (SELECT max(ADDUNITS)  FROM MODDATA where upper('p4') IN (upper(MOD1),upper(MOD2),upper(MOD3))
	AND upper('et') IN (upper(MOD1),upper(MOD2),upper(MOD3))
	AND (upper(MOD1) IN (upper('p4'), upper('et')) OR upper(MOD1) IS null OR upper(MOD1) = '' ) 
	AND (upper(MOD2) IN (upper('p4'), upper('et')) OR upper(MOD2) IS null OR upper(MOD2) = '') 
	AND (upper(MOD3) IN (upper('p4'), upper('et')) OR upper(MOD3) IS null OR upper(MOD3) = '') 
	AND ICD10 IS NULL) count1,
    (SELECT max(ADDUNITS) FROM MODDATA  WHERE upper('p4') IN (upper(MOD1),upper(MOD2),upper(MOD3)) 
		AND (upper(MOD1) IN ('p4') OR upper(MOD1) IS null OR upper(MOD1) = '') 
		AND (upper(MOD2) IN ('p4') OR upper(MOD2) IS null OR upper(MOD2) = '') 
		AND (upper(MOD3) IN ('p4') OR upper(MOD3) IS null OR upper(MOD3) = '') 
		AND ICD10 = 'T88') count2,
	(SELECT max(ADDUNITS) FROM MODDATA  WHERE upper('et') IN (upper(MOD1),upper(MOD2),upper(MOD3)) 
		AND (upper(MOD1) IN ('et') OR upper(MOD1) IS null OR upper(MOD1) = '') 
		AND (upper(MOD2) IN ('et') OR upper(MOD2) IS null OR upper(MOD2) = '') 
		AND (upper(MOD3) IN ('et') OR upper(MOD3) IS null OR upper(MOD3) = '') AND ICD10 = 'T88') count3) counts

Open in new window

sdstuberCommented:
you can't select from nothing in oracle.

your counts query didn't have a  FROM clause, for oracle you use the dummy table "DUAL"

SELECT CASE
           WHEN count1 > COALESCE(count2, 0) THEN count1
           WHEN count2 > COALESCE(count3, 0) THEN count2
           ELSE COALESCE(count3, 0)
       END
           addunits
  FROM (SELECT (SELECT MAX(addunits)
                  FROM moddata
                 WHERE UPPER('p4') IN (UPPER(mod1), UPPER(mod2), UPPER(mod3))
                   AND UPPER('et') IN (UPPER(mod1), UPPER(mod2), UPPER(mod3))
                   AND (UPPER(mod1) IN (UPPER('p4'), UPPER('et')) OR UPPER(mod1) IS NULL OR UPPER(mod1) = '')
                   AND (UPPER(mod2) IN (UPPER('p4'), UPPER('et')) OR UPPER(mod2) IS NULL OR UPPER(mod2) = '')
                   AND (UPPER(mod3) IN (UPPER('p4'), UPPER('et')) OR UPPER(mod3) IS NULL OR UPPER(mod3) = '')
                   AND icd10 IS NULL)
                   count1,
               (SELECT MAX(addunits)
                  FROM moddata
                 WHERE UPPER('p4') IN (UPPER(mod1), UPPER(mod2), UPPER(mod3))
                   AND (UPPER(mod1) IN ('p4') OR UPPER(mod1) IS NULL OR UPPER(mod1) = '')
                   AND (UPPER(mod2) IN ('p4') OR UPPER(mod2) IS NULL OR UPPER(mod2) = '')
                   AND (UPPER(mod3) IN ('p4') OR UPPER(mod3) IS NULL OR UPPER(mod3) = '')
                   AND icd10 = 'T88')
                   count2,
               (SELECT MAX(addunits)
                  FROM moddata
                 WHERE UPPER('et') IN (UPPER(mod1), UPPER(mod2), UPPER(mod3))
                   AND (UPPER(mod1) IN ('et') OR UPPER(mod1) IS NULL OR UPPER(mod1) = '')
                   AND (UPPER(mod2) IN ('et') OR UPPER(mod2) IS NULL OR UPPER(mod2) = '')
                   AND (UPPER(mod3) IN ('et') OR UPPER(mod3) IS NULL OR UPPER(mod3) = '')
                   AND icd10 = 'T88')
                   count3
          FROM DUAL) counts

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

sdstuberCommented:
Something like this should be equivalent and legal syntax in both systems

SELECT CASE
           WHEN count1 > COALESCE(count2, 0) THEN count1
           WHEN count2 > COALESCE(count3, 0) THEN count2
           ELSE COALESCE(count3, 0)
       END
           addunits
  FROM (SELECT MAX(
                   CASE
                       WHEN UPPER('p4') IN (UPPER(mod1), UPPER(mod2), UPPER(mod3))
                        AND UPPER('et') IN (UPPER(mod1), UPPER(mod2), UPPER(mod3))
                        AND (UPPER(mod1) IN (UPPER('p4'), UPPER('et')) OR UPPER(mod1) IS NULL OR UPPER(mod1) = '')
                        AND (UPPER(mod2) IN (UPPER('p4'), UPPER('et')) OR UPPER(mod2) IS NULL OR UPPER(mod2) = '')
                        AND (UPPER(mod3) IN (UPPER('p4'), UPPER('et')) OR UPPER(mod3) IS NULL OR UPPER(mod3) = '')
                        AND icd10 IS NULL
                       THEN
                           addunits
                   END)
                   count1,
               MAX(
                   CASE
                       WHEN UPPER('p4') IN (UPPER(mod1), UPPER(mod2), UPPER(mod3))
                        AND (UPPER(mod1) IN ('p4') OR UPPER(mod1) IS NULL OR UPPER(mod1) = '')
                        AND (UPPER(mod2) IN ('p4') OR UPPER(mod2) IS NULL OR UPPER(mod2) = '')
                        AND (UPPER(mod3) IN ('p4') OR UPPER(mod3) IS NULL OR UPPER(mod3) = '')
                        AND icd10 = 'T88'
                       THEN
                           addunits
                   END)
                   count2,
               MAX(
                   CASE
                       WHEN UPPER('et') IN (UPPER(mod1), UPPER(mod2), UPPER(mod3))
                        AND (UPPER(mod1) IN ('et') OR UPPER(mod1) IS NULL OR UPPER(mod1) = '')
                        AND (UPPER(mod2) IN ('et') OR UPPER(mod2) IS NULL OR UPPER(mod2) = '')
                        AND (UPPER(mod3) IN ('et') OR UPPER(mod3) IS NULL OR UPPER(mod3) = '')
                        AND icd10 = 'T88'
                       THEN
                           addunits
                   END)
                   count3
          FROM moddata
         WHERE icd10 IS NULL OR icd10 = 'T88') counts

Open in new window

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
sdstuberCommented:
I do hope the the query isn't real though, because it doesn't really make much sense in either SQL Server or Oracle.
conditions like these will never be true...

UPPER(mod1) IN ('p4')

UPPER(mod1) IN ('et')
Russ SuterSenior Software DeveloperCommented:
You're right. I missed that SELECT against nothing in the subquery. I was focusing on the outer query.

I have a program that must work against either an Oracle or Microsoft SQL Server database and there are some things I have done to Microsoft SQL Server (and to Oracle) to make them function more alike. The simplest thing was to add a view to the SQL Server database. I do it like this:
CREATE VIEW dbo.DUAL AS SELECT 'X' AS DUMMY
GO

GRANT SELECT ON dbo.DUAL TO guest
GO

Open in new window

ronan_40060Author Commented:
Thank you Sdbuser and Russ for your time and expert comments , it solved my issue and improved my understanding on the Use of CASE statements .
Thanks again for your time and help. much appreciated :)

Best regards,
ronan
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
SQL

From novice to tech pro — start learning today.