| Author |
Message
|
| paustin_ours |
 Posted: Tue May 25, 2010 11:43 am Post subject: passing NULL value to oracle passthru |
 |
|
Yatiri
Joined: 19 May 2004
Posts: 667
Location: columbus,oh
|
I am having a hard time trying to pass NULL values as parameter to oracle passthru. I keep getting missing NULL keyword error.
SET Environment.test[] = PASSTHRU('SELECT T.test, T.data FROM testusr.testdata T WHERE T.col1 IS ? AND T.col2 = ?'
VALUES (NULL,'InputRoot.MQRFH2.usr.Noun'));
If i replace WHERE T.col1 IS ? with WHERE T.col1 = ? then it is not erroring out but the flow is not working as expected because if it
recognises the NULL, i should get something back from the table and i am not getting anything back.
I am surprised i am not seeing any post related to this. I thought this site was the Broker Mecca. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue May 25, 2010 11:58 am Post subject: Re: passing NULL value to oracle passthru |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| paustin_ours wrote: |
| I thought this site was the Broker Mecca. |
You just don't get religious sites the way you used to in this jaundiced, secular age. 
| paustin_ours wrote: |
| I am surprised i am not seeing any post related to this |
You should instead be pleased you've found something new.
So the SQL query is actually using a WHERE clause which includes a test for a NULL value in one column? So you're looking for {NULL,<string value>}?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| paustin_ours |
| Posted: Tue May 25, 2010 12:09 pm Post subject: Re: passing NULL value to oracle passthru |
|
|
Yatiri
Joined: 19 May 2004
Posts: 667
Location: columbus,oh
|
| Quote: |
| You just don't get religious sites the way you used to in this jaundiced, secular age. |
I am all for secularism but what we have is false secular or pseudo secular system which is a dangerous thing. Anyways lets not go there. 
| Quote: |
| So the SQL query is actually using a WHERE clause which includes a test for a NULL value in one column? So you're looking for {NULL,<string value>}? |
I want the result value for condition where col1 is NULL and the col2 is equal to the input tree specified. For some reason, the NULL is not getting passed on correctly for the statement to execute correctly.[/quote] |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue May 25, 2010 12:20 pm Post subject: Re: passing NULL value to oracle passthru |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| paustin_ours wrote: |
| I want the result value for condition where col1 is NULL and the col2 is equal to the input tree specified. For some reason, the NULL is not getting passed on correctly for the statement to execute correctly. |
Right, getting it slowly. Next question: given that only the value of the usr.Noun clause can change (NULL being hard coded into the query) what's the issue with coding it as the tooling seems to be insisting, i.e. moving the hard coded NULL keyword so it reads T.col1 IS NULL?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| ling_71_99 |
| Posted: Tue May 25, 2010 12:32 pm Post subject: passing NULL value to oracle passthru |
|
|
Novice
Joined: 19 Nov 2007
Posts: 11
Location: Canada
|
Possibly you need use COALESCE(T.MYCOL, 'WAS NULL') to identify those null values for the column.
_________________
Mike |
|
| Back to top |
|
|
| paustin_ours |
| Posted: Tue May 25, 2010 1:10 pm Post subject: Re: passing NULL value to oracle passthru |
|
|
Yatiri
Joined: 19 May 2004
Posts: 667
Location: columbus,oh
|
| Quote: |
| i.e. moving the hard coded NULL keyword so it reads T.col1 IS NULL? |
i tried this same thing an hour ago and concluded it did not work. What i missed is that it dint work because i had the InputRoot in single quotes when i tried the IS NULL. I just noticed that.
Moving the NULL worked just fine. Thanks Victor. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Tue May 25, 2010 10:15 pm Post subject: Re: passing NULL value to oracle passthru |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
Chalk another one up ....
| paustin_ours wrote: |
| Thanks Victor. |
It would probably be easier to change your name me thinks  |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed May 26, 2010 4:30 am Post subject: Re: passing NULL value to oracle passthru |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| WMBDEV1 wrote: |
Chalk another one up ....
| paustin_ours wrote: |
| Thanks Victor. |
It would probably be easier to change your name me thinks |
Probably, but I'm just not going to. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
