[DBCleaner] Oracle - Issue with advanced query PruneSolutionVersions.GetSolutions

[DBCleaner] Oracle - Issue with advanced query PruneSolutionVersions.GetSolutions

  
Forge Component
(32)
Published on 2017-11-16 by Johan den Ouden
32 votes
Published on 2017-11-16 by Johan den Ouden

Hi guys,

Just installed this version in a Oracle DB system, and I'm having issues with the advance query PruneSolutionVersions.GetSolutions. The query is using the top keyword and also doing a order by inside a sub query which is not supported.

With this error the automatic timer is unable to run properly in Oracle.


Solution

Well answering my own question :). And maybe helping someone with the same issue.

I ended up changing the following queries for oracle with the following specific Oracle SQL.

  • PruneSolutionVersions.GetSolutions
Select aux.id, aux.SS_Key, aux.Version, aux.Creation, aux.Creation_By, aux.Name, aux.Description, aux.Solution_Id, aux.Publish, aux.Publish_by from (
SELECT {Solution_Version}.*, row_number() over (ORDER BY {Solution_Version}.[Creation] DESC) as rnum 
FROM {Solution_Version}
WHERE
    {Solution_Version}.[Publish] <> @NullDateTime AND
    {Solution_Version}.[Solution_Id] = @SolutionId 
) aux
Where aux.rnum > @OldVersions
  • PruneApplicationVersions.GetApplications
Select aux.ID, aux.NAME, aux.DESCRIPTION, aux.APPLICATIONID, aux.CREATEDBY, aux.CREATEDON from (
SELECT {APPLICATION_VERSION}.*, row_number() over (ORDER BY {APPLICATION_VERSION}.[CREATEDON] DESC) as rnum 
FROM {APPLICATION_VERSION}
WHERE
    {APPLICATION_VERSION}.[APPLICATIONID] = @ApplicationId
    ORDER BY {APPLICATION_VERSION}.[CREATEDON] DESC
) aux
Where aux.rnum > @OldVersions
  • DeleteApplicationVersion.DeleteApplicationVersion
BEGIN
DELETE {APP_VERSION_MODULE_VERSION} WHERE {APP_VERSION_MODULE_VERSION}.[APP_VERSION_ID] = @ApplicationVersionId;
DELETE {APPLICATION_VERSION} WHERE {APPLICATION_VERSION}.[ID] = @ApplicationVersionId;
END;


Solution

David Cruz wrote:

Well answering my own question :). And maybe helping someone with the same issue.

I ended up changing the following queries for oracle with the following specific Oracle SQL.

  • PruneSolutionVersions.GetSolutions
Select aux.id, aux.SS_Key, aux.Version, aux.Creation, aux.Creation_By, aux.Name, aux.Description, aux.Solution_Id, aux.Publish, aux.Publish_by from (
SELECT {Solution_Version}.*, row_number() over (ORDER BY {Solution_Version}.[Creation] DESC) as rnum 
FROM {Solution_Version}
WHERE
    {Solution_Version}.[Publish] <> @NullDateTime AND
    {Solution_Version}.[Solution_Id] = @SolutionId 
) aux
Where aux.rnum > @OldVersions
  • PruneApplicationVersions.GetApplications
Select aux.ID, aux.NAME, aux.DESCRIPTION, aux.APPLICATIONID, aux.CREATEDBY, aux.CREATEDON from (
SELECT {APPLICATION_VERSION}.*, row_number() over (ORDER BY {APPLICATION_VERSION}.[CREATEDON] DESC) as rnum 
FROM {APPLICATION_VERSION}
WHERE
    {APPLICATION_VERSION}.[APPLICATIONID] = @ApplicationId
    ORDER BY {APPLICATION_VERSION}.[CREATEDON] DESC
) aux
Where aux.rnum > @OldVersions
  • DeleteApplicationVersion.DeleteApplicationVersion
BEGIN
DELETE {APP_VERSION_MODULE_VERSION} WHERE {APP_VERSION_MODULE_VERSION}.[APP_VERSION_ID] = @ApplicationVersionId;
DELETE {APPLICATION_VERSION} WHERE {APPLICATION_VERSION}.[ID] = @ApplicationVersionId;
END;


Can you publish your modifications and we will get to support of oracle database.



Hi Mika,

Here is the version that I've changed with the queries described before. I used the GetDatabaseProvider() = "Oracle" on all queries I've changed except the delete (which I think also works in SQL server).

Thanks


Mika Roivainen wrote:

David Cruz wrote:

Well answering my own question :). And maybe helping someone with the same issue.

I ended up changing the following queries for oracle with the following specific Oracle SQL.

  • PruneSolutionVersions.GetSolutions
Select aux.id, aux.SS_Key, aux.Version, aux.Creation, aux.Creation_By, aux.Name, aux.Description, aux.Solution_Id, aux.Publish, aux.Publish_by from (
SELECT {Solution_Version}.*, row_number() over (ORDER BY {Solution_Version}.[Creation] DESC) as rnum 
FROM {Solution_Version}
WHERE
    {Solution_Version}.[Publish] <> @NullDateTime AND
    {Solution_Version}.[Solution_Id] = @SolutionId 
) aux
Where aux.rnum > @OldVersions
  • PruneApplicationVersions.GetApplications
Select aux.ID, aux.NAME, aux.DESCRIPTION, aux.APPLICATIONID, aux.CREATEDBY, aux.CREATEDON from (
SELECT {APPLICATION_VERSION}.*, row_number() over (ORDER BY {APPLICATION_VERSION}.[CREATEDON] DESC) as rnum 
FROM {APPLICATION_VERSION}
WHERE
    {APPLICATION_VERSION}.[APPLICATIONID] = @ApplicationId
    ORDER BY {APPLICATION_VERSION}.[CREATEDON] DESC
) aux
Where aux.rnum > @OldVersions
  • DeleteApplicationVersion.DeleteApplicationVersion
BEGIN
DELETE {APP_VERSION_MODULE_VERSION} WHERE {APP_VERSION_MODULE_VERSION}.[APP_VERSION_ID] = @ApplicationVersionId;
DELETE {APPLICATION_VERSION} WHERE {APPLICATION_VERSION}.[ID] = @ApplicationVersionId;
END;


Can you publish your modifications and we will get to support of oracle database.