ODBC problem
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
ODBC problem
I used a query in ODBC which is
select user_id,
case user_code
when 'KSKS' then 'KSKS'
Else 'NULL'
end as user_code
from user_spec_ct
where ORCHESTRATE.user_id=user_id;
the problem is when I execute this query in aqua data studio(tool I use to execute queries) this works fine
but when I use this in ODBC connector I get a msg in ODBC which says
Invalid Property value
expecting other input
can anyone correct it if there is any change in the query
select user_id,
case user_code
when 'KSKS' then 'KSKS'
Else 'NULL'
end as user_code
from user_spec_ct
where ORCHESTRATE.user_id=user_id;
the problem is when I execute this query in aqua data studio(tool I use to execute queries) this works fine
but when I use this in ODBC connector I get a msg in ODBC which says
Invalid Property value
expecting other input
can anyone correct it if there is any change in the query
Regards,
Kenny
Kenny
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Actually, the table has multiple userID's the table is something like...
user_id,user_code
123,ABAB
123,CDCD
123,KSKS
123,EFEF
456,UNKNOWN
456,CDCD
456,ABAB
and the output I require is
123,KSKS
456,Null
i.e If KSKS is found get the ID and Code
if not found get the ID and code(null)
Is this possible to keep it in the constraint of a transformer?
If so pls let me know.
user_id,user_code
123,ABAB
123,CDCD
123,KSKS
123,EFEF
456,UNKNOWN
456,CDCD
456,ABAB
and the output I require is
123,KSKS
456,Null
i.e If KSKS is found get the ID and Code
if not found get the ID and code(null)
Is this possible to keep it in the constraint of a transformer?
If so pls let me know.
Regards,
Kenny
Kenny
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's a new problem - your original SQL wouldn't handle that either.
You can still do it in a Transformer, by outputting only rows where the key (user_id) is different from that in the previous row.
You can still do it in a Transformer, by outputting only rows where the key (user_id) is different from that in the previous row.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
Yes, ray my SQL would not get the result required and the output I get using the given query is
123,KSKS
123,Null
456,Null
so I thought of removing the extra row with transformer.
please help me with the function which you were telling about using in transformer with out using the query as ODBC is not supporting the query.
123,KSKS
123,Null
456,Null
so I thought of removing the extra row with transformer.
please help me with the function which you were telling about using in transformer with out using the query as ODBC is not supporting the query.
Regards,
Kenny
Kenny
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
A pair of stage variables, not a function. One stage variable effects the comparison while the second "remembers" the value from the previous row. The technique has been outlined on DSXchange many times.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
sai,thanks for the response
My job is invoked as webservice so it runs always,sparse lookup is the best way i can do.
ray,
I searched the forum but could not find the right stagevar.
can you post me the 2 stage variables for the transformer to get the required output
INPUT
user_id,user_code
123,ABAB
123,CDCD
123,KSKS
123,EFEF
456,UNKNOWN
456,CDCD
456,ABAB
OUTPUT
123,KSKS
456,Null
My job is invoked as webservice so it runs always,sparse lookup is the best way i can do.
ray,
I searched the forum but could not find the right stagevar.
can you post me the 2 stage variables for the transformer to get the required output
INPUT
user_id,user_code
123,ABAB
123,CDCD
123,KSKS
123,EFEF
456,UNKNOWN
456,CDCD
456,ABAB
OUTPUT
123,KSKS
456,Null
Regards,
Kenny
Kenny
-
- Participant
- Posts: 248
- Joined: Fri Jul 30, 2010 9:04 am
I tried doing in the following way to get the output.
The input for the transformer is in the following way
INPUT
user_id,user_code
123,ABAB
123,CDCD
123,KSKS
123,EFEF
456,UNKNOWN
456,CDCD
456,ABAB
Using If then else condition the input is changed to
user_id,user_code
123,Null
123,Null
123,KSKS
123,Null
456,Null
456,Null
456,Null
after that by partitioning the input in transformer
partition type--entire
sorting--performsort
selected---unique from (stable or unique)
selected key as user_id
options as ascending
I can see the output in the required format i.e
123,KSKS
456,Null
please let me know whether the way I did is the right way or not?
The input for the transformer is in the following way
INPUT
user_id,user_code
123,ABAB
123,CDCD
123,KSKS
123,EFEF
456,UNKNOWN
456,CDCD
456,ABAB
Using If then else condition the input is changed to
user_id,user_code
123,Null
123,Null
123,KSKS
123,Null
456,Null
456,Null
456,Null
after that by partitioning the input in transformer
partition type--entire
sorting--performsort
selected---unique from (stable or unique)
selected key as user_id
options as ascending
I can see the output in the required format i.e
123,KSKS
456,Null
please let me know whether the way I did is the right way or not?
Regards,
Kenny
Kenny