You can translate the question and the replies:

Facing Problem with interpolation variable use

Hi Team, -Source is Oracle, using this JDBC source Requirement: I want to paraterized the base view with a date column in it, when anyone tries to run that bv it should ask them date before it runs - Date column name is start_date and its in timestamp format(2023-1-1 15:12:12.444) Steps followed so far: I am creating a base view from ORacle Datasource by using the following query Select start_date, id,name,lastname, cc, mail from abc.efg where start_date = @startdate While we created and ran this bv with to_date('yyyy-mm-dd','2023-12-12')it resulted in following error Missing in or Out parameter at index 1 While we tried with to_localdate('yyyy-mm-dd','2023-12-12') it resulted in "Recieved exception with message ORA00932 inconsistent datatypes expected timestamp got number While I tried tried givng directly dates start_date = '2023-12-12', it was in red state because it was expecting timestamp, If i give timestamp(2023-1-1 15:12:12.444) it works fine ------------------- I also tried creating the baseview with query wherein I gave cast(start_date as timestamp and later changed it to date in BV but still no changes error was there I dont want to create a new view on top of base view, I want that base view should handle it Please suggest
user
09-10-2023 11:20:10 -0400
code

4 Answers

Any response please?
user
10-10-2023 04:26:45 -0400
Hi, I was able to reproduce the error “Missing parameters” when the SQL sentence variables are not delegated as parameters to the source, also the “inconsistent datatypes” error was due to the mismatch between the timestamp and number data type values in the Oracle data source. I would follow the below steps to parameterize the query: For instance, create a base view using the create from query option with the below query: ```select * from schemaname.tablename where field=@interpolationvariable``` Navigate to the Options tab of the base view, where you will have the Search methods. Under the Search methods, you could change the **Delegate SQL Sentence variable as parameters** to **Yes** which is available in the wrapper source configuration that allows to delegate the variables in the query as parameters to the source without any issue. In addition to this, the datatype of the interpolation variable and the fields can be changed to the required type (i.e localdate) in the Edit tab of the base view. Please ensure to change the source type to date respectively. Please refer to the following documents for more information: * [Using the Create BAse View from Query option](https://community.denodo.com/kb/en/view/document/Using%20the%20Create%20Base%20View%20From%20Query%20Option) * [JDBC Sources](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries:~:text=are%20not%20supported.-,Creating%20Base%20Views%20from%20SQL%20Queries,-We%20strongly%20recommend) Hope this helps!
Denodo Team
10-10-2023 08:58:46 -0400
code
The probelm is that source is expecting the resuly in timestamp format(date : time) However I am passing it this way to_date('yyyy-mm-dd','2021-01-01') Plus this is not available in base view: Delegate SQL Sentence variable as parameters to Yes Still same sort of errors expeted time stamp passing number etc the source is oracle
user
10-10-2023 09:27:07 -0400
Hi, In general, even when the data is passed in the condition is changed to date using the to_localdate function, the query will be delegated to the source and the date value(2021-01-01) passed in the parameter will be compared with the start_date field which is defined as timestamp in the source. Hence the source would expect a timestamp value to compare which results in the error “inconsistent datatypes expected timestamp got number”. This is the expected behavior and in order to overcome this behavior I would create a [selection view](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_derived_views/creating_selection_views/creating_selection_views#:~:text=Creating%20Selection%20Views-,Creating%20Selection%20Views,-A%20Selection%20view ) over the base view and define a new field with the to_localdate function which can be used to pass the date values as expected. In order to do that I would perform the following steps: Open the Virtual DataPort Administration Tool and select the desired base view. Right click on the base view, and navigate to New>Selection option which will create a new selection view over that base view. Now, move to the Output tab, and create a new field by clicking on the “New field”, which opens a dialog box prompting you to enter the field name and field expression. Enter a valid field name and the field expression as follows: ``` to_localdate('yyyy-MM-dd','<view_name.start_date>') ``` Finally, save the selection view and now, I could query using the new field that is created to retrieve the results based on the date values. However, if this is not feasible then alternatively, you could make use of a work around using the [WITH clause](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/queries_select_statement/with_clause/with_clause ) in the VQL Shell with the syntax suggested below: ``` with temporary_view_name as ( select start_date, id, name, lastname, cc, mail, formatdate('yyyy-MM-dd','start_date') as alias_name from view_name ) select * from temporary_view_name where alias_name='date_value'; ``` Hope this helps!
Denodo Team
12-10-2023 00:16:24 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here