Link to home
Create AccountLog in
Avatar of bmsande
bmsande

asked on

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

Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

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?
Avatar of Qlemo
Square brackets are no valid delimiters in Oracle. Use double quotes instead.
Avatar of bmsande
bmsande

ASKER

Thanks, now its complaining about [ORA-00904: "STR": invalid identifier -
Of course you need to convert any MSSQL specific function to the Oracle counterpart.
STR(x) is to_string(x, 0).
Avatar of bmsande

ASKER

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

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

Avatar of bmsande

ASKER

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.

ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account