I need to convert this mssql query to oracle query, please assist: - IF OBJECT_ID('tempdb..#P_Filetypes') IS NOT NULL
-
DROP TABLE #P_Filetypes
-
SELECT Id, SpecialPermissions, DocumentAccessPermissions
-
INTO #P_Filetypes
-
FROM PERMIT_FILETYPES
-
WHERE PermissionOwnerId IN( 163 )
-
AND ( SiteId = 1 OR SiteId = -101 )
-
-
IF OBJECT_ID('tempdb..#P_Folders') IS NOT NULL
-
DROP TABLE #P_Folders
-
SELECT FolderId, SpecialPermissions, DocumentAccessPermissions
-
INTO #P_Folders
-
FROM RELAT_FOLDER_PERMISSIONS
-
INNER JOIN PERMIT_FOLDERS ON PermissionId = Id
-
WHERE PermissionOwnerId IN( 163 )
-
AND ( SiteId = 1 OR SiteId = -101 )
-
CREATE INDEX IX_F_ObjectId ON #P_Folders( FolderId, DocumentAccessPermissions)
-
-
SELECT *
-
FROM
-
(
-
SELECT DISTINCT D.*, FI.TypeName, FO.FolderName, U.Username CreatorName, FO.ParentId FO_ParentId, FO.ParentName FO_ParentName, FO.RootId, FO.RootName
-
FROM BL_DOCUMENTS D
-
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 )
-
-
INNER JOIN
-
(
-
SELECT * FROM
-
(
-
SELECT DISTINCT
-
FT.TypeId,
-
FT.PermissionId,
-
FT.TypeName,
-
PFT.DocumentAccessPermissions
-
FROM BL_FILETYPES FT
-
INNER JOIN PERMIT_FILETYPES PFT ON FT.PermissionId = PFT.Id
-
WHERE
-
FT.PermissionId IN
-
(
-
SELECT Id
-
FROM BL_FILETYPES
-
INNER JOIN #P_Filetypes ON PermissionId = Id
-
WHERE
-
SpecialPermissions BETWEEN 4096 AND 8191
-
)
-
) FT
-
WHERE
-
FT.PermissionId NOT IN
-
(
-
SELECT Id
-
FROM BL_FILETYPES
-
INNER JOIN #P_Filetypes ON PermissionId = Id
-
WHERE
-
SpecialPermissions BETWEEN 12288 AND 16383
-
)
-
) FI ON D.TypeId = FI.TypeId
-
-
INNER JOIN
-
(
-
SELECT * FROM
-
(
-
SELECT DISTINCT
-
F.FolderId,
-
F.FolderName,
-
F.ParentId,
-
(SELECT FolderName FROM BL_FOLDERS WHERE FolderId = F.ParentId) ParentName,
-
F.RootId,
-
(SELECT FolderName FROM BL_FOLDERS WHERE FolderId = F.RootId) RootName,
-
DocumentAccessPermissions
-
FROM BL_FOLDERS F
-
INNER JOIN #P_Folders PF ON F.FolderId = PF.FolderId
-
WHERE
-
F.FolderId IN
-
(
-
SELECT FolderId
-
FROM #P_Folders
-
WHERE
-
SpecialPermissions BETWEEN 4096 AND 8191
-
)
-
) F
-
WHERE
-
F.FolderId NOT IN
-
(
-
SELECT FolderId
-
FROM #P_Folders
-
WHERE
-
SpecialPermissions BETWEEN 12288 AND 16383
-
)
-
) FO ON D.FolderId = FO.FolderId
-
-
LEFT JOIN
-
(
-
SELECT FolderPermissionId, FiletypePermissionId, DocumentAccessPermissions
-
FROM PERMIT_FOLDER_FILETYPE_INTERS PFF_INTERS
-
WHERE PermissionOwnerId IN(163)
-
AND ( SiteId = 1 OR SiteId = -101 )
-
) PFF_INTERS ON FO.PermissionId = PFF_INTERS.FolderPermissionId AND FI.PermissionId = PFF_INTERS.FiletypePermissionId
-
INNER JOIN USRMNG_USERS U ON D.CreatorId = U.UserId
-
WHERE
-
(
-
(
-
FI.DocumentAccessPermissions BETWEEN 16384 AND 32767
-
OR
-
FO.DocumentAccessPermissions BETWEEN 16384 AND 32767
-
)
-
AND FI.TypeId NOT IN
-
(
-
SELECT TypeId
-
FROM BL_FILETYPES
-
INNER JOIN #P_Filetypes ON PermissionId = Id
-
WHERE
-
DocumentAccessPermissions BETWEEN 49152 AND 65535
-
)
-
AND FO.FolderId NOT IN
-
(
-
SELECT FolderId
-
FROM #P_Folders
-
WHERE
-
DocumentAccessPermissions BETWEEN 49152 AND 65535
-
)
-
)
-
AND D.IsCurrent = 1
-
AND D.IsDeleted = 0
-
)
-
ORDER BY DocumentId DESC
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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,
|
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 =...
|
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...
|
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....
|
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 ?
|
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...
|
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:
...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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...
|
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...
| |