| Author |
Message
|
| joemq |
 Posted: Tue May 19, 2015 9:48 pm Post subject: Displaying data properly in the output xml |
 |
|
Novice
Joined: 18 May 2012
Posts: 10
|
I have a requirement where to fetch the table one column data consists of combination of ESQL non-reserved keywords (AS, FOR, FORMAT, DATE, DECIMAL, CHAR) and string manipulation functions (TRIM,SUBSTING...) and CAST function(Ex: C).I have to take that column value which is a combination of all separated by semi colon(EX: AS; FOR; CAST; CHAR; SUBSTRING; FROM). Use this one column values for the creation of output data. For example data is comming from the mapping like this (Inputroot.xmlnsc.DATA.Value1) and column value will be used for data manipulation.
EXample:
CAST(Inputroot.xmlnsc.DATA.Value1 AS CHAR); --- In this CAST;AS;CHAR all coming from database table column.
Please give us some idea to do it. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed May 20, 2015 4:39 am Post subject: Re: Displaying data properly in the output xml |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| joemq wrote: |
| Please give us some idea to do it. |
Never mind how - why? 
Is this some kind of crazed attempt to store transformation logic in a database? What use case does this fit?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 20, 2015 5:08 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Are you really prepared to incur the costs of using Exec, or the pain of using lots of {}? |
|
| Back to top |
|
|
| joemq |
| Posted: Wed May 20, 2015 5:28 am Post subject: |
|
|
Novice
Joined: 18 May 2012
Posts: 10
|
Are you really prepared to incur the costs of using Exec, or the pain of using lots of {}?
Yes we have requirement to do it. My scenario like i am grabbing a value from database and applying this Special column values (CAST; CHAR, AS) for data manuplilation. Can you please give some idea to do it
ex:-
CAST(tabRef.{dbRef.VAL2} AS CHAR);
-- In above mentioned value tabRef.{dbRef.VAL2}is getting from table reference and for this i have to do apply CAST ,CHAR and AS which also i am getting from another database table column . Finally value is going to xml. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 20, 2015 5:37 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
CAST won't execute the value.
The value in the Database should already be a character.
Or is the value in the database *exactly* like "InputRoot.XMLNSC.Message.Body.Field" ?
You either need to use Exec as a function, or you need to use {} around *every* level of the tree in that expression. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed May 20, 2015 5:38 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
I thought that was Evaluate and not Exec ?? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 20, 2015 5:40 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| fjb_saper wrote: |
| I thought that was Evaluate and not Exec ?? |
  |
|
| Back to top |
|
|
| joemq |
| Posted: Wed May 20, 2015 5:49 am Post subject: |
|
|
Novice
Joined: 18 May 2012
Posts: 10
|
Hi Jeff,
No. The value in the database will be CHAR or DECIMAL or DATE (this value tabRef.{dbRef.VAL2}). I have to apply CAST for this one which i am taking it from another Database table column[column values (CAST;CHAR,AS) or (CAST,DECIMAL,AS) etc].
How can we use Exec as fuction and how to do above scenario? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 20, 2015 6:08 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The value of a single column in a database table should have a single type. That is, it should be a Character, and NOT a Date and Not Decimal. Or otherwise one of the three, and not all of the three.
Again, the function is evaluate and not exec... and you shouldn't use it or need it for your scenario.
Go talk to your DBA. Or your senior developer. |
|
| Back to top |
|
|
| joemq |
| Posted: Wed May 20, 2015 6:14 am Post subject: |
|
|
Novice
Joined: 18 May 2012
Posts: 10
|
| Column data type is CHARACTER and value present will be like this CAST;CHAR;AS (OR)CAST;DECIMAL;AS (OR) CAST;DATE;AS;FORMAT . I have to take this value and use this for data manipulation. |
|
| Back to top |
|
|
| joemq |
| Posted: Wed May 20, 2015 6:16 am Post subject: |
|
|
Novice
Joined: 18 May 2012
Posts: 10
|
| mqjeff wrote: |
The value of a single column in a database table should have a single type. That is, it should be a Character, and NOT a Date and Not Decimal. Or otherwise one of the three, and not all of the three.
Again, the function is evaluate and not exec... and you shouldn't use it or need it for your scenario.
Go talk to your DBA. Or your senior developer. |
Column data type is CHARACTER and value present will be like this CAST;CHAR;AS (OR)CAST;DECIMAL;AS (OR) CAST;DATE;AS;FORMAT . I have to take this value and use this for data manipulation. Is there anyway we can do this? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 20, 2015 6:55 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| joemq wrote: |
| Column data type is CHARACTER and value present will be like this CAST;CHAR;AS (OR)CAST;DECIMAL;AS (OR) CAST;DATE;AS;FORMAT . I have to take this value and use this for data manipulation. Is there anyway we can do this? |
That's a terrible column format. Apply a trout to the table designer. 
But you can just split that on ";" and test the middle value and use an IF statement to call the relevant CAST statement. |
|
| Back to top |
|
|
|
|
