need help converting mssql query to oracle

I have the following query that accepts a variable from the user, then presents the report.  It works fine when the app is on a sql server, but is complaining when it its ran on an oracle instance.  The error is:  ORA-00923 FROM keyword not found where expected.

Anyway, can someone help get the syntax converted for oracle?
SELECT DTREE.DATAID as [Item ID],
DTree_1.Name AS [Parent Folder],
DTree.Name AS [Child Item Name],
 CASE DTree.SubType 
 WHEN 0 THEN 'Folder' 
 WHEN 144 THEN 'Document' ELSE 'OTHER' END "Object Type",
 CASE DTreeACL.Permissions 
 WHEN 128 THEN 'No Rights' 
 WHEN 130 THEN 'S' 
 WHEN 36995 THEN 'S/SC' 
 WHEN 36999 THEN 'S/SC/AI' 
 WHEN 62666 THEN 'S/M' 
 WHEN 65670 THEN 'S/M/AI' 
 WHEN 102531 THEN 'S/SC/M' 
 WHEN 102535 THEN 'S/SC/M/AI' 
 WHEN 102547 THEN 'S/SC/M/EP' 
 WHEN 102551 THEN 'S/SC/M/EP/AI' 
 WHEN 118915 THEN 'S/SC/M/DV' 
 WHEN 118919 THEN 'S/SC/M/AI/DV' 
 WHEN 118923 THEN 'S/SC/M/DV/D' 
 WHEN 118927 THEN 'S/SC/M/AI/DV/D' 
 WHEN 118931 THEN 'S/SC/M/EP/DV' 
 WHEN 118939 THEN 'S/SC/M/EP/DV/D' 
 WHEN 118935 THEN 'S/SC/M/EP/AI/DV' 
 WHEN 118943 THEN 'S/SC/M/DP/AI/DV/D' 
 WHEN 127107 THEN 'S/SC/M/DV/R' 
 WHEN 127111 THEN 'S/SC/M/AI/DV/R' 
 WHEN 127115 THEN 'S/SC/M/DV/D/R' 
 WHEN 127119 THEN 'S/SC/M/AI/DV/D/R' 
 WHEN 127123 THEN 'S/SC/M/EP/DV/R' 
 WHEN 127127 THEN 'S/SC/M/EP/AI/DV/R' 
 WHEN 127131 THEN 'S/SC/M/EP/DV/D/R' 
 WHEN 127135 THEN 'S/SC/M/EP/AI/DV/D/R' 
 WHEN 233603 THEN 'S/SC/M/EA' 
 WHEN 233607 THEN 'S/SC/M/EA/AI' 
 WHEN 233619 THEN 'S/SC/M/EP/EA' 
 WHEN 233623 THEN 'S/SC/M/EP/EA/AI' 
 WHEN 249987 THEN 'S/SC/M/EA/DV' 
 WHEN 249991 THEN 'S/SC/M/EA/AI/DV' 
 WHEN 249995 THEN 'S/SC/M/EA/DV/D' 
 WHEN 249999 THEN 'S/SC/M/EA/AI/DV/D' 
 WHEN 250003 THEN 'S/SC/M/EP/EA/DV' 
 WHEN 250007 THEN 'S/SC/M/EP/EA/AI/DV' 
 WHEN 250011 THEN 'S/SC/M/EP/EA/DV/D' 
 WHEN 250015 THEN 'S/SC/M/EP/EA/AI/DV/D' 
 WHEN 258179 THEN 'S/SC/M/EA/DV/R' 
 WHEN 258183 THEN 'S/SC/M/EA/AI/DV/R' 
 WHEN 258187 THEN 'S/SC/M/EA/DV/D/R' 
 WHEN 258191 THEN 'S/SC/M/EA/AI/DV/D/R' 
 WHEN 258195 THEN 'S/SC/M/EP/EA/DV/R' 
 WHEN 258199 THEN 'S/SC/M/EP/EA/AI/DV/R' 
 WHEN 258203 THEN 'S/SC/M/EP/EA/DV/D/R' 
 WHEN 258207 THEN 'Full Permissions' 
 WHEN 16777215 THEN 'Admin' ELSE 'New: ' + STR(DTreeACL.Permissions) END Permissions,
 KUAF.Name "Group Name/Login Name" ,
 KUAF.FirstName + ' ' + KUAF.LastName as [User Name],
 CASE DTREEACL.RIGHTID 
 WHEN '-1' THEN 'Public Access' 
 WHEN '-2' THEN 'System Administrators' ELSE 'User ID: ' + STR(DTREEACL.RIGHTID) END [Privileges Assigned To]
 FROM DTreeACL 
 INNER JOIN DTree ON DTreeACL.DataID = DTree.DataID 
 INNER JOIN KUAF ON DTreeACL.RightID = KUAF.ID 
 INNER JOIN DTree DTree_1 ON DTree.ParentID = DTree_1.DataID 
 WHERE (DTreeACL.Permissions <> 128) and kuaf.deleted=0 and DTree.ParentID = %1 
 ORDER BY DTree_1.Name, DTree.Name

Open in new window

bmsandeAsked:
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.

Daniel WilsonCommented:
There's nothing wrong with the FROM clause.

I think one of the CASE clauses is messing it up.  Would you drop those one by one to determine which one is giving you trouble?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Square brackets are no valid delimiters in Oracle. Use double quotes instead.
bmsandeAuthor Commented:
Thanks, now its complaining about [ORA-00904: "STR": invalid identifier -
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Of course you need to convert any MSSQL specific function to the Oracle counterpart.
STR(x) is to_string(x, 0).
bmsandeAuthor Commented:
of course i don't know what is specific to mssql and what is specific to oracle, that is why i need assistance.  I change the str functions to to_string, see below, and now recieve the following error.   [ORA-00904: "TO_STRING": invalid identifier

SELECT DTREE.DATAID as "Item ID",
DTree_1.Name AS "Parent Folder",
DTree.Name AS "Child Item Name",
 CASE DTree.SubType 
 WHEN 0 THEN 'Folder' 
 WHEN 144 THEN 'Document' ELSE 'OTHER' END "Object Type",
 CASE DTreeACL.Permissions 
 WHEN 128 THEN 'No Rights' 
 WHEN 130 THEN 'S' 
 WHEN 36995 THEN 'S/SC' 
 WHEN 36999 THEN 'S/SC/AI' 
 WHEN 62666 THEN 'S/M' 
 WHEN 65670 THEN 'S/M/AI' 
 WHEN 102531 THEN 'S/SC/M' 
 WHEN 102535 THEN 'S/SC/M/AI' 
 WHEN 102547 THEN 'S/SC/M/EP' 
 WHEN 102551 THEN 'S/SC/M/EP/AI' 
 WHEN 118915 THEN 'S/SC/M/DV' 
 WHEN 118919 THEN 'S/SC/M/AI/DV' 
 WHEN 118923 THEN 'S/SC/M/DV/D' 
 WHEN 118927 THEN 'S/SC/M/AI/DV/D' 
 WHEN 118931 THEN 'S/SC/M/EP/DV' 
 WHEN 118939 THEN 'S/SC/M/EP/DV/D' 
 WHEN 118935 THEN 'S/SC/M/EP/AI/DV' 
 WHEN 118943 THEN 'S/SC/M/DP/AI/DV/D' 
 WHEN 127107 THEN 'S/SC/M/DV/R' 
 WHEN 127111 THEN 'S/SC/M/AI/DV/R' 
 WHEN 127115 THEN 'S/SC/M/DV/D/R' 
 WHEN 127119 THEN 'S/SC/M/AI/DV/D/R' 
 WHEN 127123 THEN 'S/SC/M/EP/DV/R' 
 WHEN 127127 THEN 'S/SC/M/EP/AI/DV/R' 
 WHEN 127131 THEN 'S/SC/M/EP/DV/D/R' 
 WHEN 127135 THEN 'S/SC/M/EP/AI/DV/D/R' 
 WHEN 233603 THEN 'S/SC/M/EA' 
 WHEN 233607 THEN 'S/SC/M/EA/AI' 
 WHEN 233619 THEN 'S/SC/M/EP/EA' 
 WHEN 233623 THEN 'S/SC/M/EP/EA/AI' 
 WHEN 249987 THEN 'S/SC/M/EA/DV' 
 WHEN 249991 THEN 'S/SC/M/EA/AI/DV' 
 WHEN 249995 THEN 'S/SC/M/EA/DV/D' 
 WHEN 249999 THEN 'S/SC/M/EA/AI/DV/D' 
 WHEN 250003 THEN 'S/SC/M/EP/EA/DV' 
 WHEN 250007 THEN 'S/SC/M/EP/EA/AI/DV' 
 WHEN 250011 THEN 'S/SC/M/EP/EA/DV/D' 
 WHEN 250015 THEN 'S/SC/M/EP/EA/AI/DV/D' 
 WHEN 258179 THEN 'S/SC/M/EA/DV/R' 
 WHEN 258183 THEN 'S/SC/M/EA/AI/DV/R' 
 WHEN 258187 THEN 'S/SC/M/EA/DV/D/R' 
 WHEN 258191 THEN 'S/SC/M/EA/AI/DV/D/R' 
 WHEN 258195 THEN 'S/SC/M/EP/EA/DV/R' 
 WHEN 258199 THEN 'S/SC/M/EP/EA/AI/DV/R' 
 WHEN 258203 THEN 'S/SC/M/EP/EA/DV/D/R' 
 WHEN 258207 THEN 'Full Permissions' 
 WHEN 16777215 THEN 'Admin' ELSE 'New: ' + to_string(DTreeACL.Permissions,0) END Permissions,
 KUAF.Name "Group Name/Login Name" ,
 KUAF.FirstName + ' ' + KUAF.LastName as "User Name",
 CASE DTREEACL.RIGHTID 
 WHEN '-1' THEN 'Public Access' 
 WHEN '-2' THEN 'System Administrators' ELSE 'User ID: ' + to_string(DTREEACL.RIGHTID,0) END "Privileges Assigned To"
 FROM DTreeACL 
 INNER JOIN DTree ON DTreeACL.DataID = DTree.DataID 
 INNER JOIN KUAF ON DTreeACL.RightID = KUAF.ID 
 INNER JOIN DTree DTree_1 ON DTree.ParentID = DTree_1.DataID 
 WHERE (DTreeACL.Permissions <> 128) and kuaf.deleted=0 and DTree.ParentID = %1 
 ORDER BY DTree_1.Name, DTree.Name

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Apologees, that was BS I posted. Use to_char instead of STR. Another incompatibility is that Oracle needs || instead of + if you concat strings - see attached code. When using  || you even can omit the to_char, as it is clear both arguments need to be strings. I left the function in for clearity.

If you are not experienced with Oracle, you should state so from the start. We cannot tell which level of knowledge you have, only derive it from the data provided - and might be completely wrong because of that.
SELECT DTREE.DATAID as "Item ID",
DTree_1.Name AS "Parent Folder",
DTree.Name AS "Child Item Name",
 CASE DTree.SubType 
 WHEN 0 THEN 'Folder' 
 WHEN 144 THEN 'Document' ELSE 'OTHER' END "Object Type",
 CASE DTreeACL.Permissions 
 WHEN 128 THEN 'No Rights' 
 WHEN 130 THEN 'S' 
 WHEN 36995 THEN 'S/SC' 
 WHEN 36999 THEN 'S/SC/AI' 
 WHEN 62666 THEN 'S/M' 
 WHEN 65670 THEN 'S/M/AI' 
 WHEN 102531 THEN 'S/SC/M' 
 WHEN 102535 THEN 'S/SC/M/AI' 
 WHEN 102547 THEN 'S/SC/M/EP' 
 WHEN 102551 THEN 'S/SC/M/EP/AI' 
 WHEN 118915 THEN 'S/SC/M/DV' 
 WHEN 118919 THEN 'S/SC/M/AI/DV' 
 WHEN 118923 THEN 'S/SC/M/DV/D' 
 WHEN 118927 THEN 'S/SC/M/AI/DV/D' 
 WHEN 118931 THEN 'S/SC/M/EP/DV' 
 WHEN 118939 THEN 'S/SC/M/EP/DV/D' 
 WHEN 118935 THEN 'S/SC/M/EP/AI/DV' 
 WHEN 118943 THEN 'S/SC/M/DP/AI/DV/D' 
 WHEN 127107 THEN 'S/SC/M/DV/R' 
 WHEN 127111 THEN 'S/SC/M/AI/DV/R' 
 WHEN 127115 THEN 'S/SC/M/DV/D/R' 
 WHEN 127119 THEN 'S/SC/M/AI/DV/D/R' 
 WHEN 127123 THEN 'S/SC/M/EP/DV/R' 
 WHEN 127127 THEN 'S/SC/M/EP/AI/DV/R' 
 WHEN 127131 THEN 'S/SC/M/EP/DV/D/R' 
 WHEN 127135 THEN 'S/SC/M/EP/AI/DV/D/R' 
 WHEN 233603 THEN 'S/SC/M/EA' 
 WHEN 233607 THEN 'S/SC/M/EA/AI' 
 WHEN 233619 THEN 'S/SC/M/EP/EA' 
 WHEN 233623 THEN 'S/SC/M/EP/EA/AI' 
 WHEN 249987 THEN 'S/SC/M/EA/DV' 
 WHEN 249991 THEN 'S/SC/M/EA/AI/DV' 
 WHEN 249995 THEN 'S/SC/M/EA/DV/D' 
 WHEN 249999 THEN 'S/SC/M/EA/AI/DV/D' 
 WHEN 250003 THEN 'S/SC/M/EP/EA/DV' 
 WHEN 250007 THEN 'S/SC/M/EP/EA/AI/DV' 
 WHEN 250011 THEN 'S/SC/M/EP/EA/DV/D' 
 WHEN 250015 THEN 'S/SC/M/EP/EA/AI/DV/D' 
 WHEN 258179 THEN 'S/SC/M/EA/DV/R' 
 WHEN 258183 THEN 'S/SC/M/EA/AI/DV/R' 
 WHEN 258187 THEN 'S/SC/M/EA/DV/D/R' 
 WHEN 258191 THEN 'S/SC/M/EA/AI/DV/D/R' 
 WHEN 258195 THEN 'S/SC/M/EP/EA/DV/R' 
 WHEN 258199 THEN 'S/SC/M/EP/EA/AI/DV/R' 
 WHEN 258203 THEN 'S/SC/M/EP/EA/DV/D/R' 
 WHEN 258207 THEN 'Full Permissions' 
 WHEN 16777215 THEN 'Admin' ELSE 'New: ' || to_char(DTreeACL.Permissions) END Permissions,
 KUAF.Name "Group Name/Login Name" ,
 KUAF.FirstName || ' ' || KUAF.LastName as "User Name",
 CASE DTREEACL.RIGHTID 
 WHEN '-1' THEN 'Public Access' 
 WHEN '-2' THEN 'System Administrators' ELSE 'User ID: ' || to_char(DTREEACL.RIGHTID) END "Privileges Assigned To"
 FROM DTreeACL 
 INNER JOIN DTree ON DTreeACL.DataID = DTree.DataID 
 INNER JOIN KUAF ON DTreeACL.RightID = KUAF.ID 
 INNER JOIN DTree DTree_1 ON DTree.ParentID = DTree_1.DataID 
 WHERE (DTreeACL.Permissions <> 128) and kuaf.deleted=0 and DTree.ParentID = %1 
 ORDER BY DTree_1.Name, DTree.Name

Open in new window

bmsandeAuthor Commented:
thanks, i ran your statement, and received the following error:
[ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
I also removed the "to_char" and received the same thing.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That is a flaw of your original query. You use
CASE DTREEACL.RIGHTID 
 WHEN '-1' THEN 'Public Access' 
 WHEN '-2' THEN 'System Administrators' ELSE 'User ID: ' + STR(DTREEACL.RIGHTID) END [Privileges Assigned To]

Open in new window

which compares a string ('-1') against a number (DTREEACL.RIGHTID). You should stay consistent with your data types. In this case, replace '-1' by -1 and '-2' by -2, that should do.

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
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
Query Syntax

From novice to tech pro — start learning today.