DSXchange

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

Why not just select user_code column without the CASE construct and handle the non-KSKS values within the job?

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.

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.

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.

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.

If you remove the 'sparse' property of the query and remove the duplicates, you will get that in a single lookup.

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

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?