Limit the data based on Prompt date with SQL modified

[Sébastien Medhat 2]

Hello,

I have an Information Link that uses a modifiedSQL.

I'd like to include Prompts to limit the data based on a list of dates selected by the user.

How can I integrate the value selected by the user in my modified SQL please

Can I modify mySQL and write something like :

SELECT XXXXX

 

FROM XXXXX

 

WHERE XXXXX AND date = By using Prompts wih the modified SQL, i'm getting the error below, which is why i'm asking this question :

Error message: Could not open information link.

ImportException at Spotfire.Dxp.Data:

Failed to execute data query (HRESULT: 80131500)

Stack Trace:

at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.ExecuteInformationLink()

at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.KnownColumnsInformationLinkDataRowReader.TryCreateCxxRepresentation(CxxSession session, Int64 exclusiveStartRowIndex, UInt64 maxRowsThatWillBeRead, CxxTable& table)

at Spotfire.Dxp.Data.Cxx.CxxDataTransfer.CreateTable(DataRowReader dataRowReader, CxxSession session, PartialDataLoadReport report, Advancer rowAdvancer, Boolean needsReset, Int64 exclusiveStartRowIndex, UInt64 maxRowsThatWillBeRead)

at Spotfire.Dxp.Data.Cxx.CxxDataTransfer.CreateTable(DataRowReader dataRowReader, CxxSession session, PartialDataLoadReport report, Boolean needsReset, UInt64 maxRowsToConsume)

at Spotfire.Dxp.Data.Cxx.CxxCreateViewResult.Create(DataRowReader reader, IDataPropertyContainer defaultProperties, String documentTitle, CxxSession session, Boolean addNewProperties, PartialDataLoadReport loadReport, UInt64 maxRowsToConsume)

at Spotfire.Dxp.Data.ColumnFactory.CreateColumnsPrivate(DataRowReader reader, String documentTitle, IDataPropertyContainer properties, GlobalMethodRegistry globalMethodRegistry, CxxSession session, Boolean addNewProperties, PartialDataLoadReport report, Func`2 resultPropertiesCreator, UInt64 maxRowsToConsume)

at Spotfire.Dxp.Data.ColumnFactory.CreateColumns(DataRowReader reader, String documentTitle, IDataPropertyContainer properties, GlobalMethodRegistry globalMethodRegistry, CxxSession session, Boolean addNewProperties, PartialDataLoadReport report, Func`2 resultPropertiesCreator, UInt64 maxRowsToConsume)

at Spotfire.Dxp.Data.Producers.SourceColumnProducer.c__DisplayClass70_0.b__0()

at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)

at Spotfire.Dxp.Data.Producers.SourceColumnProducer.CreateView(CxxSession session, GlobalMethodRegistry globalMethodRegistry, DataSourceConnection connection, DataRowReader initialReader, IDataPropertyContainer defaultColumnProperties, UInt64 maxRowsToConsume, Boolean disableReset, PartialDataLoadReport& partialLoadReport)

at Spotfire.Dxp.Data.Producers.SourceColumnProducer.GetColumnsAndProperties(DataSourceConnection connection)

at Spotfire.Dxp.Data.Persistence.DataItem.PerformUpdate(SourceColumnProducer producer, DataSourceConnection connection)

at Spotfire.Dxp.Data.Persistence.DataItem.Update(SourceColumnProducer producer, DataSourceConnection connection)

at Spotfire.Dxp.Data.Persistence.DataPool.d__15.MoveNext()

at Spotfire.Dxp.Data.Producers.SourceColumnProducer.OnConfigure()

at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()

at Spotfire.Dxp.Framework.DocumentModel.Node.c.b__47_0(Node node)

at Spotfire.Dxp.Framework.DocumentModel.UndoableListAvlLeaf`1.ForEachChild(Action`1 action, Boolean includeFrozen)

at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()

at Spotfire.Dxp.Framework.DocumentModel.Node.c.b__47_0(Node node)

at Spotfire.Dxp.Framework.DocumentModel.UndoableList`1.ForEachChild(Action`1 action, Boolean includeFrozen)

at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()

at Spotfire.Dxp.Framework.DocumentModel.Node.c.b__47_0(Node node)

at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.c__DisplayClass92_0.b__0(IDocumentNodeChild documentNodeChild)

at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.ForEachChild(IDocumentNodeChild[] children, Action`1 action)

at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()

at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, IndeterminateProgressFormatter progressFormatter, ProgressOperation operation)

at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.ConfigureAndAttachFromNew()

at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.AttachSubTreeWhileExecuting(UndoableNodeBase newOwner)

at Spotfire.Dxp.Framework.DocumentModel.UndoableNode.Spotfire.Dxp.Framework.DocumentModel.IUndoableNode.AttachItemToUndoableNode(Object item)

at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.c__DisplayClass43_0.b__0()

at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException, Boolean isStreamingProperty)

at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.Insert(Int32 index, TNode item)

at Spotfire.Dxp.Data.DataTableCollection.c__DisplayClass71_0.b__0()

at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException, Boolean isStreamingProperty)

at Spotfire.Dxp.Data.DataTableCollection.Add(DataTable dataTable)

at Spotfire.Dxp.Application.PartiallyOpenedDataSource.d__9.MoveNext()

at Spotfire.Dxp.Application.AnalysisApplication.d__95.MoveNext()

at Spotfire.Dxp.Application.AnalysisApplication.d__83.MoveNext()

at Spotfire.Dxp.Application.AnalysisApplication.ConsumeDataLoadPromptRequests(IEnumerable`1 prompts)

at Spotfire.Dxp.Application.AnalysisApplication.Open(DataSource source, DocumentOpenSettings settings)

at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenDataSourceWithoutPrompting(DataSource dataSource, DocumentOpenSettings documentOpenSettings, IServiceProvider serviceProvider)

at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenDataSource(InformationLinkDataSource dataSource, DocumentOpenSettings documentOpenSettings, IServiceProvider serviceProvider)

at Spotfire.Dxp.Forms.Application.FormsProgressService.ProgressThread.DoOperationLoop()

 

InformationModelException at Spotfire.Dxp.Data:

An Internal Error has occurred. -tag not found in query. (HRESULT: 80131500)

Stack Trace:

at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.OpenJob(InformationLinkElement informationLink, IList`1 parameters, Boolean isReferenced)

at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.c__DisplayClass61_0.b__0()

at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.ExecuteInformationLink(InformationLinkElement informationLink, IList`1 parameters, Boolean isReferenced)

at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.ExecuteInformationLink()

 

InformationModelServiceException at Spotfire.Dxp.Services:

An Internal Error has occurred. -tag not found in query. (HRESULT: 80131509)

Stack Trace:

at Spotfire.Dxp.Services.WebServiceBase`1.InvokeService[T](ServiceMethod`1 serviceMethod, ExceptionFactoryMethod exceptionFactoryMethod, String customMethodNameForLogging)

at Spotfire.Dxp.Services.Data.InformationModel.QueryManagerService.InvokeService[T](ServiceMethod`1 serviceMethod)

at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.OpenJob(InformationLinkElement informationLink, IList`1 parameters, Boolean isReferenced)

Thank you in advanced for your help.

[Shandilya Peddi]

Check the resolution suggested in the following knowledge base article to see if it helps,https://support.tibco.com/s/article/Error-while-running-modified-SQL-for...

[Sébastien Medhat 2]

Thank you very much, it worked! You wouldn't believe how much time I spent to solve this issue!

 

Do you know how can I sort my date list in ascending order in the prompt pop-up

 

Thank you in advanced for your help.