You can translate the question and the replies:

Date Expression convert to decimal to varchar

Hi, I'm new to this forum and also just started to use Denodo. I'm using SQL Server and were converting some SQL codes to Denodo. I'm trying to convert this code below but i'm getting an error specially this portion cast(serialid as varchar)+cast(cast(dateadd(hh,8,st.createddatetime) as decimal(12,4)) as varchar). any help is very much appreciated. Thank you. ``` case when substring(serialid,1,1) = 'T' then serialid else cast(serialid as varchar)+cast(cast(dateadd(hh,8,st.createddatetime) as decimal(12,4)) as varchar) end itemnum ```
user
30-08-2017 01:59:57 -0400

3 Answers

Hi, Virtual DataPort uses a VQL language which extends the similar behaviour of SQL. You could use the SQL queries in Virtual DataPort by changing it to VQL Standards. In your case, I would do the following changes: | SQL Standards | VQL Standards| | -------- | -------- | -------- | | Substring | Substr(arg0,arg1) | | dateadd | addhour(date,inc) | | "+" | Concat(arg0,arg1) | For more information on VQL standards, you could refer the Knowledge Base article [Microsoft SQL Server SQL to Denodo VQL Quick Reference. ](https://community.denodo.com/kb/view/document/Microsoft%20SQL%20Server%20SQL%20to%20Denodo%20VQL%20Quick%20Reference?category=VQL://) Hope this helps you.
Denodo Team
30-08-2017 09:48:21 -0400
Thanks for the reply. Can you please provide me an expression on the sample using the VQL codes.
user
31-08-2017 04:53:28 -0400
Hi, Based on VQL standards, I would execute the expression like below in the VQL shell ``` Select case when substr(serialid,1,1) = 'T' then cast(serialid as varchar) ,else concat( cast(serialid as varchar) ,(cast(addhour(st.createddatetime,8) as varchar) as varchar)) end itemnum from <view_name> ``` Ensure that when using concat function, both the fields should have same datatype. For more information on “**concat**”, “**substring**” and “**addhour**” functions  refer the section Text and Date Processing functions in [Syntax of Condition Functions](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/syntax_of_condition_functions#syntax-of-condition-functions://) of the Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
05-09-2017 06:27:00 -0400
You must sign in to add an answer. If you do not have an account, you can register here