Saturday, March 22, 2014
function date_trunc("unknown", "unknown") is not unique - SQL
Recently I started working on Japser Studio professional for my new project Cloud to generate the reports. I was very new to all cloud technologies including PostgreSql. I faced lot of problems during this time. below is one of such problem, which kill lot of my time. Here is problem and its solution.
Problem statement: Need to generate usage data reports.
To generate usage data reports, I need to compare the existing timestamp from the table with user given data. For comparing data I used date_trunc() to get year/month/day etc. Here I was getting above error. Below is the query for that where ts is from test_reports table.
select * from test_reports where (date_trunc('day',ts)
ERROR: function date_trunc("unknown", "unknown") is not unique
Hint: Could not choose a best candidate function. You may need to add explicit type casts. [SQL State=42725]
This error is due to data type(timestamp) mismatching. By seeing/analysing the error, I found its due to mismatching the time. But no idea how to solve it and googled for it like hell. Luckily our TL has suggested the solution that prepending timestamp to the user input variable.
select * from test_reports where (date_trunc('day',ts)
Jasper studio professional:
Need to create the input parameter and pass that parameter as an argument to the sql query. Here are the steps to create the parameter.
- Go to outline window in the Jasper studio
- Right click on the Parameters tab and click on "Create Parameter"
- Then click on the created parameter and go to the properties window
- Change the name to appropriate one
- Change the class name to java.sql.timestamp
- Change the default expression to new Date() as it will give the current date as default value
- Make the isPrompt check box enable for getting the data from user
Now we can able to read the date value from the user into this newly created parameter.
Usage of the parameter in generating reports:
Need to follow below syntax for using parameter in the reports generation.
$P!{parameter_name}
select * from test_reports where ts < '$P!{user_date}'
If we use same parameter in date_trunc() function, we will face type mismatch error.
select * from test_report where date_trunc('day',update_ts)
To solve that error, you can prepend 'timestamp' which will convert to required type.
select * from test_report where date_trunc('day',update_ts)
'$P!{user_date}')
Thanks for reading !!!!
Subscribe to:
Post Comments (Atom)
Popular Posts
-
A universally unique identifier ( UUID ) is an identifier standard used in software construction, standardized by the Open...
-
Recently I started working on Japser Studio professional for my new project Cloud to generate the reports. I was very new to all cloud ...
-
Below is C program for AVL Tree implementation. #include<stdio.h> #include<malloc.h> typedef struct bst { int info; int hei...
-
strcmp is another string library function which is used to compare two strings and returns zero if both strings are same , returns +ve valu...
-
One of the complex operation on binary search tree is deleting a node. Insertion is easy by calling recursive insertion. But deletion wont...
-
We have recently faced one tricky issue in AWS cloud while loading S3 file into Redshift using python. It took almost whole day to inde...
-
Object slicing: when a derived class object is assigned to a base class object. only base class data will be copied from derived class and...
-
We have faced lot of weird issues while loading S3 bucke t files into redshift. I will try to explain all issues what we faced. Before go...
-
Below code is to find the cube root of a given integer number with out using pow math library function. Its very simple and brute force...
-
Recently we faced one issue in reading messages from SQS in AWS cloud where we are processing same message multiple times. This issue we...
1 comment:
I used
cast($P{user_date} as timestamp)
which seems cleaner.
Post a Comment