Hi Shyam,
As I said, the vendor-specific FROM clause in UPDATE .. SET .. FROM is
currently not supported by jOOQ. You simply cannot use any .from() method
on a jOOQ UPDATE statement. It's not there.
As always, with jOOQ, you have various options to work around this issue.
One option is to resort to plain SQL in this case (this is always an
option). Another option is to rewrite the semantics of your SQL Server
vendor-specific FROM clause into an equivalent predicate in the WHERE
clause. Rewriting this SQL statement should be part of your POC, in my
opinion. Please try rewriting this yourself, first.
An idea of "similar" statements. SQL Server:
UPDATE table1
SET table1.a = x,
table1.b = y
FROM table1 JOIN table2 ON (table1.id = table2.id)
Is probably equivalent, or at least "similar" to the following standard SQL
statement:
UPDATE table1
SET table1.a = x,
table1.b = y
WHERE table1.id IN (
SELECT table2.id FROM table2 ...
)
Again, doing the exercise of understanding the original T-SQL and rewriting
it to jOOQ should be part of your POC, in my opinion, as you will run into
more such cases in the future. jOOQ does not (yet) support *all*
vendor-specific SQL.
Cheers
Lukas
2014/1/23 Sha <[email protected]>
> Sorry Lukas,
> I am not able to follow the answer you provided for error 1.
> Me getting the same error even if remove all complete complex logic and
> try with a simple one line statement like below
>
> *ctx.update(employee).set(employee.COMPUTED_VAL , employee.ORIGINAL_VALUE
> ).from(employee).execute();*
>
> It is still giving error @ "* ).from(employee) * "
> i.e. *"The method from(Employee) is undefined for the type
> UpdateSetMoreStep<EmployeeRecord>"*
>
> Can you give an example for the above one for "rewrite your statement to
> an equivalent SQL standard statement where you put the table source
> declared in the additional FROM clause into the WHERE clause"
>
> Me confused to how to go interpret your suggestion and go ahead.
>
> Sorry for troubling you.
>
> ~Shyam
>
>
>
> On Wednesday, January 22, 2014 6:38:19 PM UTC+5:30, Lukas Eder wrote:
>>
>>
>> 2014/1/22 Sha <[email protected]>
>>
>> Hi Lukas,
>>>
>>> I completed translation in to JOOQ equalent of my stored proc.
>>>
>>> But in the below statement :
>>>
>>>
>>> ctx.update(employee).set(employee.COMPUTED_VALUE, ( ((DSL)
>>> ctx).decode().when( ((employee.SAL_TYPE).nullif(new
>>> BigDecimal(0))).equal(new BigDecimal(1.0)) ,
>>>
>>> (salary.VALUE).nullif(new
>>> BigDecimal(0)) )
>>> )
>>> *)).from(employee)*
>>>
>>> 1) It is giving error @ "* ).from(employee) * "
>>> i.e. *"The method from(Employee) is undefined for the type
>>> UpdateSetMoreStep<EmployeeRecord>"*
>>> What to do in resolving this ?
>>>
>>
>> jOOQ currently doesn't support this vendor-specific SQL extension:
>> https://github.com/jOOQ/jOOQ/issues/1018
>>
>> I suggest you rewrite your statement to an equivalent SQL standard
>> statement where you put the table source declared in the additional FROM
>> clause into the WHERE clause
>>
>>
>>> 2) how can ensure that the update statement is successful ?
>>> In JDBC it returns the number of records updated ....is there any
>>> provision in JOOQ ?
>>>
>>
>> Query.execute() returns the same value JDBC returns:
>> http://www.jooq.org/javadoc/3.2.x/org/jooq/Query.html#execute()
>>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/groups/opt_out.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
