473,326 Members | 2,255 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Convert ms sql query to oracle query

I need to convert this mssql query to oracle query, please assist:


Expand|Select|Wrap|Line Numbers
  1. IF OBJECT_ID('tempdb..#P_Filetypes') IS NOT NULL
  2.     DROP TABLE #P_Filetypes
  3. SELECT Id, SpecialPermissions, DocumentAccessPermissions
  4. INTO #P_Filetypes
  5. FROM PERMIT_FILETYPES 
  6. WHERE PermissionOwnerId IN( 163 )
  7. AND ( SiteId = 1 OR SiteId = -101 )
  8.  
  9. IF OBJECT_ID('tempdb..#P_Folders') IS NOT NULL
  10.     DROP TABLE #P_Folders
  11. SELECT FolderId, SpecialPermissions, DocumentAccessPermissions
  12. INTO #P_Folders
  13. FROM RELAT_FOLDER_PERMISSIONS 
  14. INNER JOIN PERMIT_FOLDERS  ON PermissionId = Id
  15. WHERE PermissionOwnerId IN( 163 )
  16. AND ( SiteId = 1 OR SiteId = -101 ) 
  17. CREATE INDEX IX_F_ObjectId ON #P_Folders( FolderId, DocumentAccessPermissions)
  18.  
  19. SELECT *
  20. FROM 
  21. (
  22. SELECT DISTINCT D.*, FI.TypeName, FO.FolderName, U.Username CreatorName, FO.ParentId FO_ParentId, FO.ParentName FO_ParentName, FO.RootId, FO.RootName
  23. FROM BL_DOCUMENTS D 
  24. LEFT JOIN PERMIT_DOCUMENTS PD  ON D.PermissionId = PD.Id AND PD.PermissionOwnerId IN( 163 ) AND ( PD.SiteId = 1 OR PD.SiteId = -101 ) AND ( PD.DocumentAccessPermissions IS NULL OR PD.DocumentAccessPermissions NOT BETWEEN 49152 AND 65535 )
  25.  
  26. INNER JOIN 
  27. (
  28.     SELECT * FROM
  29.     (
  30.     SELECT DISTINCT
  31.     FT.TypeId,
  32.     FT.PermissionId,
  33.     FT.TypeName,
  34.     PFT.DocumentAccessPermissions
  35.     FROM BL_FILETYPES FT 
  36.     INNER JOIN PERMIT_FILETYPES PFT  ON FT.PermissionId = PFT.Id
  37.     WHERE 
  38.     FT.PermissionId IN
  39.     (
  40.         SELECT Id
  41.         FROM BL_FILETYPES 
  42.         INNER JOIN #P_Filetypes ON PermissionId = Id
  43.         WHERE 
  44.         SpecialPermissions BETWEEN 4096 AND 8191
  45.     )
  46.     ) FT
  47.     WHERE
  48.     FT.PermissionId NOT IN
  49.     (
  50.         SELECT Id
  51.         FROM BL_FILETYPES 
  52.         INNER JOIN #P_Filetypes ON PermissionId = Id
  53.         WHERE 
  54.         SpecialPermissions BETWEEN 12288 AND 16383
  55.     )
  56. ) FI ON D.TypeId = FI.TypeId
  57.  
  58. INNER JOIN 
  59. (
  60.     SELECT * FROM
  61.     (
  62.     SELECT DISTINCT
  63.      F.FolderId,
  64.     F.FolderName,
  65. F.ParentId,
  66. (SELECT FolderName FROM BL_FOLDERS WHERE FolderId = F.ParentId) ParentName,
  67. F.RootId,
  68. (SELECT FolderName FROM BL_FOLDERS WHERE FolderId = F.RootId) RootName,
  69.     DocumentAccessPermissions
  70.     FROM BL_FOLDERS F 
  71.     INNER JOIN #P_Folders PF ON F.FolderId = PF.FolderId
  72.     WHERE
  73.     F.FolderId IN
  74.     (
  75.         SELECT FolderId
  76.         FROM #P_Folders
  77.         WHERE 
  78.         SpecialPermissions BETWEEN 4096 AND 8191
  79.     )
  80.     ) F
  81.     WHERE
  82.     F.FolderId NOT IN
  83.     (
  84.         SELECT FolderId
  85.         FROM #P_Folders
  86.         WHERE 
  87.         SpecialPermissions BETWEEN 12288 AND 16383
  88.     )
  89. ) FO ON D.FolderId = FO.FolderId
  90.  
  91. LEFT JOIN 
  92. (
  93.     SELECT FolderPermissionId, FiletypePermissionId, DocumentAccessPermissions
  94.     FROM PERMIT_FOLDER_FILETYPE_INTERS PFF_INTERS
  95.     WHERE PermissionOwnerId IN(163)
  96.     AND ( SiteId = 1 OR SiteId = -101 ) 
  97. ) PFF_INTERS ON FO.PermissionId = PFF_INTERS.FolderPermissionId AND FI.PermissionId = PFF_INTERS.FiletypePermissionId
  98. INNER JOIN USRMNG_USERS U ON D.CreatorId = U.UserId
  99. WHERE
  100. (
  101.     (
  102.         FI.DocumentAccessPermissions BETWEEN 16384 AND 32767
  103.         OR 
  104.         FO.DocumentAccessPermissions BETWEEN 16384 AND 32767
  105.     )
  106.     AND FI.TypeId NOT IN
  107.     (
  108.         SELECT TypeId
  109.         FROM BL_FILETYPES 
  110.         INNER JOIN #P_Filetypes ON PermissionId = Id
  111.         WHERE 
  112.         DocumentAccessPermissions BETWEEN 49152 AND 65535
  113.     )
  114.     AND FO.FolderId NOT IN
  115.     (
  116.         SELECT FolderId
  117.         FROM #P_Folders
  118.         WHERE 
  119.         DocumentAccessPermissions BETWEEN 49152 AND 65535
  120.     )
  121. )
  122. AND  D.IsCurrent = 1 
  123. AND  D.IsDeleted = 0 
  124. )
  125. ORDER BY DocumentId DESC
Sep 28 '14 #1
1 3207
Luuk
1,047 Expert 1GB
Try to make your SQL readable, an re-post using '[code] tags.

I only think that 'OBJECT_ID('tempdb..#P_Filetypes')' might not be valid in Oracle, and maybe the '#' as start of a tablename.
Sep 28 '14 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Asfand Yar Qazi | last post by:
Ahem.. Anyway, here's whats happening... construct tables in MySQL: DROP TABLE EMP; CREATE TABLE EMP (EMPNO INT(4) NOT NULL, ENAME CHAR(6) NOT NULL,
4
by: laurenq uantrell | last post by:
I am trying to determine which of three stored procedure designs are fastest in the Query Analyzer: One query is a straight SELECT query with all desired rows and a dozen (tblName.RowName =...
6
by: johntarr | last post by:
I hope I am not asking about something that has been done before, but I have searched and cannot find an answer. What I am trying to do is to run a query, and then perform some logic on the...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
1
by: imnewtoaccess | last post by:
Hi all, I have created a query in msaccess with a parameter. Now I want to query that query but without the parameter. Is it possible ?
0
by: =?Utf-8?B?VG9ueSBBLg==?= | last post by:
I'm trying to develop a parameter query using Query Builder, the database is Access 2003. I placed a ? in the column to we quered and linked it to a checkbox on the form. When the user selects an...
3
by: clarencemo | last post by:
Hello all, I have a append query that works great if I just run the query via Access. I need to convert that query into VBA code. Here is the SQL View of the query I'm trying to convert: ...
1
by: laxmikumar999 | last post by:
Hi all, I have a data as given below. ID RATE1 DATE1 RATE2 DATE2 CODE 10 A 2007-FEB-01 U 2007-JAN-01 BIG 10 B 2007-FEB-01 V 2007-JAN-01 BIG 10 C 2007-JAN-01 W 2007-FEB-01 SMALL...
0
by: rohitpk | last post by:
Hello all, can anyone conver the following ORACLE query to MySQl equivalent query, $account_fields = $this->oracle("SELECT ROUND(SUM(TO_DATE(PAID_DATE) - TO_DATE(INVOICE_DATE))/COUNT(*)) AS...
3
by: Venmathi | last post by:
Hi , How to convert the below Oracle Query to DB2. The Query is working fine in Oracle but not in DB2. Please Help. Query: Select f.folder_id, f.identifier From FOLDER f, STOREENT se...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.