User-defined query in DRS stage

[gangs]

Hi,

I am trying to update fields through an update query in DRS stage & my query looks like this

update <tbl> set fld1 = :1, fld2 =:2, fld3 =:3 where fld4 := 4 and fld3 = <constant value>

i.e., i need to update a field thats also used in the where condition. the update is not succesful and the director shows an error with the query formation repeatedly. is this not possible? let me know more about this.

thanks,

[ray.wurlod]

Welcome aboard.

Any field that is to appear in the WHERE clause must be marked as Key in the Columns grid. Key in this context does not necessarily mean primary key; it means search key.

[gangs]

Welcome aboard.

Any field that is to appear in the WHERE clause must be marked as Key in the Columns grid. Key in this context does not necessarily mean primary key; it means search key. ...

Well, I in fact, have marked this as key in the respective stage...

[chulett]

From what I recall, the DRS stage uses the ? parameter markers, not the numbered ones - they are exclusive to OCI.

[gangs]

From what I recall, the DRS stage uses the ? parameter markers, not the numbered ones - they are exclusive to OCI. ...

hi,
I dont think the issue is to do with that coz I've just taken the generated query & modified that in the user-defined section. Moreover this worked fine when I didnt have the field that I update in the where clause but that updated more rows than that need to be (which is not the correct result expected.)

thanks,

[chulett]

Ok, fair enough. Post your actual error then. And ensure that you have exactly 4 colums defined in the stage and that the 4th one is the only one marked as a Key.

[gangs]

Well, here it is. I have a total of 7 fields in the col grid of which three are search only (marked key) and three are update only. there is one field that needs to appear in the where as well updated. I get the same error even if I mark it a key or not (I am using a user-defined query).
I actually get a set of 4 warning msgs for each row (basically msgs from SQL Server odbc driver).
- Incorrect syntax near ':'
- Statements could not be prepared.
the next two are actually DS infos
- <displays the set of field values for that row in the link. for the field thats being updated shows the update value>
- Row rejected

let me know if i need to give more info...

[chulett]

ensure that you have exactly 4 colums defined in the stage and that the 4th one is the only one marked as a Key.

7 columns would require 7 parameter markers to bind them. Any field used in the 'where' clause must be marked as a Key field.

Unless we're talking ODBC here, anything else will *not* work.

[Minhajuddin]

Hey gangs,

Can you do us a favour by posting the User Defined SQL Query you are using?

[gangs]

Hey gangs,

Can you do us a favour by posting the User Defined SQL Query you are using?

s.. i shud ve done this in the first instance but thot the stating it generic would give more understanding abt wats the exact issue behind than just getting the soln.

the query i am using is

Code: Select all

UPDATE CUST_ADDR SET EFF_END_DT=TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),CUR_IND=:5,LASTUPD_EW_DTTM=TO_DATE(:6, 'YYYY-MM-DD HH24:MI:SS'),BATCH_SID=:7 WHERE SETID=:1 AND PART_ID=:2 AND SRC_SYS_ID=:3 AND CUR_IND='Y'

the critical field is CUR_IND. i am checking for a constant in the where clause

Just curious to know more on this statement

Unless we're talking ODBC here, anything else will *not* work

what is it that u meant will work/ will not work

thanks again

[chulett]

my query looks like this

update <tbl> set fld1 = :1, fld2 =:2, fld3 =:3 where fld4 := 4 and fld3 = <constant value>

So... what you are doing doesn't actually "look like this". It never helps to post "generic" examples as they make it too easy for people trying to help to be led astray.

Not work = generate error. In other words, not work.

So, for the DML you just posted you need to have:

1) 7 columns defined in the stage
2) Columns 1, 2 and 3 marked as Key fields

And it "should" work.

Funny thing is, though - when *I* use the DRS stage to generate SQL it always uses the ? parameter markers. What is your target database? I've been assuming Oracle because of your use of the numbered parameter markers, but I guess we need to ask. And the fact that you get an error saying "Incorrect syntax near ':' seems to bear this out.

Try changing them all to ? and see if that works, if it still complains after checking the two items posted above.

[Minhajuddin]

Hi Chulett,
I don't think the error is with the ':', because Even I get colons (:) in a DRS stage, I work on 7.5x2.

@gangs
But the query looks just fine to me. I don't think there's a problem with the Date conversion too, because you said it was working fine previously.

Let's analyze this thing and get back to you.

[chulett]

Hmm... UNIX server, DS 7.5.1A - question marks. And it specifically complained about the colons. Still, the actual errors were not posted - they were paraphrased instead - so who knows.

I'll step away and let you guys 'work it out'.

[gangs]

Hmm... UNIX server, DS 7.5.1A - question marks. And it specifically complained about the colons. Still, the actual errors were not posted - they were paraphrased instead - so who knows.

I'll step away and let you guys 'work it out'.

i am actually working on the solns u had suggested. plz give me a couple of moments & will get back with the observations

sorry for the delay (as got held up with some other work too)

[Minhajuddin]

Come on Chulett, You are THE GURU