[SQL Sandbox] Error in advanced query DeleteOldest in AddModuleToUser in SQL
Question
Forge component by Leonardo Fernandes

Hi guys, great component here, thanks for sharing!


For some reason though, which I was not yet able to troubleshoot, I got the below error right after accessing the Execute web screen - any guess of what could be possibly fixed here?


[1] Error executing query. 
at ssSQL.Actions.FuncActionAddModuleToUser.QueryDeleteOldest(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, Int32 qpinUserId, Int32 qpinQtyToKeep) 
at ssSQL.Actions.ActionAddModuleToUser(HeContext heContext, Int32 inParamModuleId, Int32 inParamUserId) 
at ssSQL.Flows.FlowQuery.ScrnExecute.Preparation(HeContext heContext) at ssSQL.Flows.FlowQuery.ScrnExecute.Page_Load(Object sender, EventArgs e) 
at System.Web.UI.Control.OnLoad(EventArgs e) 
at System.Web.UI.Control.LoadRecursive() 
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) 

[2] Error in advanced query DeleteOldest in AddModuleToUser in SQL (DELETE t FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {ModuleHistory}.[Id] DESC) AS Seq,* FROM {ModuleHistory} WHERE {ModuleHistory}.[UserId] = @UserId ) t WHERE Seq > @QtyToKeep): ORA-00933: SQL command not properly ended


Thank you!

Solution

Hi all,

You can replicate the error when you have an Oracle database. Oracle does not recognize this syntax.

I've rewritten it to: (Action: AddModuleToUser)

DELETE FROM {ModuleHistory}
WHERE {ModuleHistory}.[Id] in ( SELECT {ModuleHistory}.[Id] 
                                FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {ModuleHistory}.[Id] DESC) AS Seq,{ModuleHistory}.*
                                       FROM {ModuleHistory}
                                       WHERE {ModuleHistory}.[UserId] = @UserId
                                     )
                                WHERE Seq > @QtyToKeep
                              )

There are also errors in the Save and Execute action of the Execute interface screen. This is because of the datatype of the column Statements in the entity SavedStatements and StatementHistory. In a Oracle Database you cannot have a where clause with a clob datatype. So I've solved this with a list filter.

See also the attached OML file.


 

SQLOracle.oml

Confirmed. Just found the clob datatype error following the first fix (ORA-00933: SQL command not properly ended). Thank you, Ties!

Pedro Gonçalves wrote:

Hi guys, great component here, thanks for sharing!


For some reason though, which I was not yet able to troubleshoot, I got the below error right after accessing the Execute web screen - any guess of what could be possibly fixed here?


[1] Error executing query. 
at ssSQL.Actions.FuncActionAddModuleToUser.QueryDeleteOldest(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, Int32 qpinUserId, Int32 qpinQtyToKeep) 
at ssSQL.Actions.ActionAddModuleToUser(HeContext heContext, Int32 inParamModuleId, Int32 inParamUserId) 
at ssSQL.Flows.FlowQuery.ScrnExecute.Preparation(HeContext heContext) at ssSQL.Flows.FlowQuery.ScrnExecute.Page_Load(Object sender, EventArgs e) 
at System.Web.UI.Control.OnLoad(EventArgs e) 
at System.Web.UI.Control.LoadRecursive() 
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) 

[2] Error in advanced query DeleteOldest in AddModuleToUser in SQL (DELETE t FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {ModuleHistory}.[Id] DESC) AS Seq,* FROM {ModuleHistory} WHERE {ModuleHistory}.[UserId] = @UserId ) t WHERE Seq > @QtyToKeep): ORA-00933: SQL command not properly ended


Thank you!

Hi Pedro,

This looks like an SQL syntax error. Can you try testing the advanced SQL and see what is the generated SQL statement ?

Thanks 

Ravi


mvp_badge
MVP

Hi Pedro. I will be looking at this issue in the next days. Will advise here when done. Thanks for the heads up.

Thanks João, 


I'm sure it is a simple matter of troubleshooting that query as Ravi mentioned. Just didn't have the time to focus on that yet ;) 


The main purpose would be to setup this component on my current customer's infrastructure to enable its developers with additional tools when handling Advanced SQL logic.


Appreciate the help,

Pedro

mvp_badge
MVP

Hi Pedro, I had a look at the error you reported, and I couldn't replicate it on the Application.

Do you informing which version you're using? If not the last one, do you mind upgrading it? It might be an old issue already fixed on the latest version.

Thank you for the heads up once more.

Hi João,


Sorry for the delay in getting back. I've finally found a new opportunity with a project using Oracle. However, I've still noticed that the latest version 3.2.7 in Forge was still not working and giving the exact same error. After going through the below suggestions, I've found Ties Brukx's working. Marked as solved. 


Kindly release his correction to a new component version, please.


Regards!

Solution

Hi all,

You can replicate the error when you have an Oracle database. Oracle does not recognize this syntax.

I've rewritten it to: (Action: AddModuleToUser)

DELETE FROM {ModuleHistory}
WHERE {ModuleHistory}.[Id] in ( SELECT {ModuleHistory}.[Id] 
                                FROM ( SELECT ROW_NUMBER() OVER (ORDER BY {ModuleHistory}.[Id] DESC) AS Seq,{ModuleHistory}.*
                                       FROM {ModuleHistory}
                                       WHERE {ModuleHistory}.[UserId] = @UserId
                                     )
                                WHERE Seq > @QtyToKeep
                              )

There are also errors in the Save and Execute action of the Execute interface screen. This is because of the datatype of the column Statements in the entity SavedStatements and StatementHistory. In a Oracle Database you cannot have a where clause with a clob datatype. So I've solved this with a list filter.

See also the attached OML file.


 

SQLOracle.oml

Confirmed. Just found the clob datatype error following the first fix (ORA-00933: SQL command not properly ended). Thank you, Ties!

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.