[SQL Sandbox] Error in advanced query DeleteOldest in AddModuleToUser in SQL

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!

Pedro Gonçalves wrote:

[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

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

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

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.