69
Views
5
Comments
[SQL Sandbox] Error in advanced query DeleteOldest in AddModuleToUser in SQL
Question
Forge component by Leonardo Fernandes
19
Published on 24 Feb 2021

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!

Champion
Rank: #248

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
Rank: #21

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

Staff
Rank: #98

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
Rank: #21

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.

Rank: #10985

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