187473 - no subject - Hi,\n\nI have created a Oracle SQL view

madhubindug over 8 years ago

Hi,

I have created a Oracle SQL view and one of the column is a difference of date and time, the data type shown in the view description is of Interval Day To Second. Now what is the corresponding type I have to give while creating the custom data type in Appian. I would be using this column for calculating the Average, aggregation function [AVG] in a!queryEntity works for data types [[Number (Integer), Number (Decimal), Interval (Day to Second)]].

Thanks,
Madhu

Discussion posts and replies are publicly visible

0 sikhivahans over 8 years ago

@madhubindu Did you try casting the result to a data type desired by you in the view itself? I generally cast the result to the data type (by keeping Appian's primitive data types in mind) desired by me in the view initially so that I can map it conveniently in Appian later on. I am not sure if it's an elegant solution and let's see what other practitioners opine on this.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Tim
Certified Lead Developer
over 8 years ago

Agree with @sikhi, you should look at using the EXTRACT() function in Oracle so that it returns a primitive type that maps to Appina.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 narasimhaadityac
A Score Level 2
over 8 years ago

Hi Mahdubindu, Looking at the scenario, I believe the source dates are defined as TIMESTAMP due to which the difference column is formulated as INTERVAL. if you are not going to deal with the timezones then we have two options to update source date columns as DATE so that the difference comes as NUMBER or use an explicit conversion (to_number) to get the value as NUMBER and you can map it as a xsd:decimal while mapping in XSD.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Tim
Certified Lead Developer
over 8 years ago

TO_NUMBER() will not work on an interval value, hence why EXTRACT() was suggested. I assume the columns are Date and Time for a reason.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 madhubindug over 8 years ago

Hi All, I have converted the difference of two stamps into seconds which is a number and have mapped to a int in XSD, using this seconds value, i tried intervalds() to get the days, hours, min and secs value, but for this 2397484 it is returning -ve number. Any suggestions to convert the seconds value to days, hours, min and secs format.
Regards,
Madhu
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel