Regarding Update querry at JDBC Sender adapter

[Former Member]

Dear all

need you help for this requirement

My Select statement is working fine where flag column has some time null value

below is the Select statement and ( EMP_MAXQTY_AUTH_FLAG is Flag field here & EMPLOYEE_MST is table name)

SELECT EMP_ORG_CD,EMP_CD,EMP_NAME,EMP_STATUS,EMP_MAXQTY_AUTH_FLAG FROM EMPLOYEE_MST WHERE EMP_MAXQTY_AUTH_FLAG<>'Y'or EMP_MAXQTY_AUTH_FLAG is null

but update querry is as given below

UPDATE EMPLOYEE_MST SET EMP_MAXQTY_AUTH_FLAG='Y' WHERE ((EMP_MAXQTY_AUTH_FLAG <>'Y') OR (EMP_MAXQTY_AUTH_FLAG is NULL))

Please suggest the exact query

if Flag row ahs some value any thing it is updating , but when there is NULL value ,

problem is coming

Please help.....

Regards

Priya

[former_member187339 · Accepted solution]

Hi Priya,

Try these command first in the database. IF you get/upodate all records then put them in the Communication channel

For Selecting

SELECT EMP_ORG_CD,EMP_CD,EMP_NAME,EMP_STATUS,EMP_MAXQTY_AUTH_FLAG FROM EMPLOYEE_MST WHERE EMP_MAXQTY_AUTH_FLAG = 'Y'or EMP_MAXQTY_AUTH_FLAG is NULL or EMP_MAXQTY_AUTH_FLAG = ''

For Update

UPDATE EMPLOYEE_MST SET EMP_MAXQTY_AUTH_FLAG= 'Y' WHERE ((EMP_MAXQTY_AUTH_FLAG = 'Y') OR (EMP_MAXQTY_AUTH_FLAG is NULL) OR EMP_MAXQTY_AUTH_FLAG = '')

Regards

Suraj

[Former Member · Accepted solution]

Many Thanks Suraj

this Update querry helps us , our equirement is done

updating the fields at SQL where there is NULL value

Kind Regards

Priya

[Former Member]

HI,

Check if the field EMP_MAXQTY_AUTH_FLAG is compatible to accept the NULL values.

Generally in Database table fields this settings is marked to not accept NULL Values.

Instead of NULL try to pass "" or blank space.

Thanks

Swarup

[Former Member]

Hi Swarup

Instead of NULL i am trying to pass " " is not getting executed , when i am running this querry at SQL it self

please suggest

Regards

Priya

[Former Member]

Double quotes are not accepted in SQL. You should use single quotes ' '.

Thanks

swarup

[Former Member]

Hi Swarup

if i am using this querry using ' '

like

UPDATE EMPLOYEE_MST SET EMP_MAXQTY_AUTH_FLAG='Y' WHERE ((EMP_MAXQTY_AUTH_FLAG <>'Y') OR (EMP_MAXQTY_AUTH_FLAG = ' '))

ANS SELECT LIKE This

SELECT EMP_ORG_CD,EMP_CD,EMP_NAME,EMP_STATUS,EMP_MAXQTY_AUTH_FLAG FROM EMPLOYEE_MST WHERE EMP_MAXQTY_AUTH_FLAG<>'Y'or EMP_MAXQTY_AUTH_FLAG is null

the same data is picking by again and again and again

Please help

Regards

Priya

[former_member187339]

Hi Priya,

I think it is the mistake in query:

SELECT EMP_ORG_CD,EMP_CD,EMP_NAME,EMP_STATUS,EMP_MAXQTY_AUTH_FLAG FROM EMPLOYEE_MST WHERE EMP_MAXQTY_AUTH_FLAG'Y'or EMP_MAXQTY_AUTH_FLAG is null

means select all record whose *EMP_MAXQTY_AUTH_FLAG is 'Y' or Null *

UPDATE EMPLOYEE_MST SET EMP_MAXQTY_AUTH_FLAG='Y' WHERE ((EMP_MAXQTY_AUTH_FLAG 'Y') OR (EMP_MAXQTY_AUTH_FLAG = ' '))

means update those records where *EMP_MAXQTY_AUTH_FLAG is 'Y' or Null * and the new value to be updated is 'Y'

Change your update query to

UPDATE EMPLOYEE_MST SET EMP_MAXQTY_AUTH_FLAG='D' WHERE ((EMP_MAXQTY_AUTH_FLAG 'Y') OR (EMP_MAXQTY_AUTH_FLAG = ' '))

Regards

Suraj

[aashish_sinha]

Hi,

>>UPDATE EMPLOYEE_MST SET EMP_MAXQTY_AUTH_FLAG='Y' WHERE ((EMP_MAXQTY_AUTH_FLAG 'Y') OR (EMP_MAXQTY_AUTH_FLAG is NULL))

are you missing eeuals in where statement. Place is (EMP_MAXQTY_AUTH__FLAG 'Y'_)

Please correct it it will start working.

Also try

UPDATE EMPLOYEE_MST SET EMP_MAXQTY_AUTH_FLAG='Y' WHERE ((EMP_MAXQTY_AUTH_FLAG 'Y') OR (EMP_MAXQTY_AUTH_FLAG = ""))

Regards

Aashish Sinha

Edited by: Aashish Sinha on Sep 8, 2009 8:15 AM

[Former Member]

Hi Ashish

as per your reply for

UPDATE EMPLOYEE_MST SET EMP_MAXQTY_AUTH_FLAG='Y' WHERE ((EMP_MAXQTY_AUTH_FLAG 'Y') OR (EMP_MAXQTY_AUTH_FLAG = ""))

"" is not getting executed , when i am running this querry at SQL it self

any more suggesion

Regards

Priya

Edited by: Priya Gupta on Sep 8, 2009 8:49 AM