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.

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.

The error persists on 3.3.1.
Ties Brukx fix works.

Solution for AddToHistory Oracle CLOB error. Use dbms_lob.compare().

Just noticed this same error again in another infrastructure. Dear MVP team, can we update the component with the above fix? Thank you!