| Author |
Message
|
| giorginus80 |
 Posted: Wed Dec 17, 2008 3:11 am Post subject: Convert a Select to a Type |
 |
|
Centurion
Joined: 08 Jul 2008
Posts: 124
Location: Rome, Italy
|
Hi, I need to convert a select statement (it's in a ROW variable) into a Field of an MRM message (this field is mapped into a type in the mxsd). This select have the alias name exact as the fields name of the type. I tried to parse the ROW object but I don't know if it's possible.
my code:
| Code: |
DECLARE mySelect ROW;
SET mySelect = PASSTHR-U('SELECT a.field1 AS "field-1", a.field2 AS "field-2" FROM table a');
|
I put PASSTHR-U because if I put PASSTHRU the server mqseries.net give me an error
Then I have a type in the mxsd with field-1 and field-2.
So I try to parse mySelect into this type but I don't know the right way.
I tried:
| Code: |
DECLARE parseOptions INTEGER BITOR(ValidateContent, ValidateValue, ValidateLocalError);
DECLARE toParse REFERENCE TO OutputRoot.MRM.io:body; --body is the right type with field-1 field-2
CREATE LASTCHILD OF toParse
PARSE ( mySelect
OPTIONS parseOptions
SET 'MySet'
TYPE 'fieldType'
FORMAT 'XML1');
|
P.S. I can't paste my code, it gives me a server error on mqseries.net
Last edited by giorginus80 on Wed Dec 17, 2008 3:25 am; edited 7 times in total |
|
| Back to top |
|
 |
| AkankshA |
| Posted: Wed Dec 17, 2008 3:18 am Post subject: Re: Convert a Select to a Type |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
| giorginus80 wrote: |
P.S. I can't paste my code, it gives me a server error on mqseries.net |
remove semo colon from your post and you shall be able to post the code too..
_________________
Cheers |
|
| Back to top |
|
|
| giorginus80 |
| Posted: Wed Dec 17, 2008 3:22 am Post subject: Re: Convert a Select to a Type |
|
|
Centurion
Joined: 08 Jul 2008
Posts: 124
Location: Rome, Italy
|
It's passthrue into the code expression gives an error in the server post....anyway I hope I explained well the parse problem  somebody can help me? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Dec 17, 2008 4:17 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
PARSE only takes a BLOB value, not a ROW.
You should be able to simply use SET to assign the results of the SELECT into the output message...
SET OutputRoot.MRM.io:body = PASSTHRU('SELECT a.field1 AS "field-1", a.field2 AS "field-2" FROM table a')
? |
|
| Back to top |
|
|
| giorginus80 |
| Posted: Wed Dec 17, 2008 5:47 am Post subject: |
|
|
Centurion
Joined: 08 Jul 2008
Posts: 124
Location: Rome, Italy
|
| mqjeff wrote: |
PARSE only takes a BLOB value, not a ROW.
You should be able to simply use SET to assign the results of the SELECT into the output message...
SET OutputRoot.MRM.io:body = PASSTHRU('SELECT a.field1 AS "field-1", a.field2 AS "field-2" FROM table a')
? |
Now I try....but is it possible to transform ROW into a Blob...or a String? |
|
| Back to top |
|
|
| giorginus80 |
| Posted: Wed Dec 17, 2008 6:09 am Post subject: |
|
|
Centurion
Joined: 08 Jul 2008
Posts: 124
Location: Rome, Italy
|
I tried but the result isn't good, I don't know why but once I put the query direct into the field, the chieldren are all CHARACTER, and the numbers are all converted in a bad way.
Is there any way to get the result query into a Type and then put it into the field? |
|
| Back to top |
|
|
| giorginus80 |
| Posted: Wed Dec 17, 2008 6:56 am Post subject: |
|
|
Centurion
Joined: 08 Jul 2008
Posts: 124
Location: Rome, Italy
|
| I really need to convert it into a blob or a string, because the result of the query have to be inserted in a CDATA field, and I dont' know how to use it |
|
| Back to top |
|
|
| Esa |
| Posted: Wed Dec 17, 2008 8:06 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
what about concatenating the fields in your sql statement instead:
select field1 || field2 as myblob from from mytable |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Dec 17, 2008 8:10 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
As a genral rule of thumb, I never do a select into anywhere BUT the Environment Folder.
set Environment.SelectResults[] = PASSTHRU('Select...')
Even if the select returns only 1 row the above syntax allows multiple rows to be returned if the select requires it.
Using the Environment folder for select results allows you to see what got returned in a subsequent trace Node.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| giorginus80 |
| Posted: Wed Dec 17, 2008 8:13 am Post subject: |
|
|
Centurion
Joined: 08 Jul 2008
Posts: 124
Location: Rome, Italy
|
| Esa wrote: |
what about concatenating the fields in your sql statement instead:
select field1 || field2 as myblob from from mytable |
I solved the first part :
| Code: |
CREATE LASTCHILD OF Environment.myQuery.DOMAIN 'XMLNSC';
SET Environment.myQuery.XMLNSC.data[] = PASSTHR-U etc....
|
then
| Code: |
DECLARE folderBlob BLOB ASBITSTREAM( Environment.myQuery.XMLNSC,
InputRoot.Properties.Encoding,
InputRoot.Properties.CodedCharSetId );
DECLARE folderChar CHAR CAST(folderBlob AS CHAR CCSID InputRoot.Properties.CodedCharSetId);
SET dv9.(XMLNSC.CDataField)valore = folderChar;
|
all works ok, cdata is created but the number values are wrong...for example one field is converted to '3.7E+1' may be some options to add when casting char? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Dec 17, 2008 11:42 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
3.7E+1 is a valid format for a floating point number. Perhaps this is the datatype in the Table?
To remove this you may have to cast it to a char with a defined format pattern before putting it all into the CDATA.
See the FORMAT options on the CAST Statement for some of your options.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| giorginus80 |
| Posted: Thu Dec 18, 2008 2:06 am Post subject: |
|
|
Centurion
Joined: 08 Jul 2008
Posts: 124
Location: Rome, Italy
|
| smdavies99 wrote: |
3.7E+1 is a valid format for a floating point number. Perhaps this is the datatype in the Table?
To remove this you may have to cast it to a char with a defined format pattern before putting it all into the CDATA.
See the FORMAT options on the CAST Statement for some of your options. |
Ok now I try thanks |
|
| Back to top |
|
|
|
|
