topic Re: Source Look up in Oracle DB in Qlik Replicate

Source Look up in Oracle DB

pguddera21 — Tue, 19 Sep 2023 19:50:17 GMT

I am trying to do a source look up to create a new column from col 1 utilizing

source_lookup(10000,'Schema','Table1','Col1','VNDRID=:1', $VNDRID)

Both tables have the VNDRID column and this is in Oracle DB. Both tables have the same data type of

VARCHAR2(8 CHAR) in oracle DB.

The issue is when I run this source lookup and the task finishes. Only the col1 where VNDRID = 'N/A' match. The rest return as NULL for col1.

We have valid VNDRID that I check and match in both tables however, those are not getting pulled.

Is there any reason on why those would get ignored? We have no filters on this job.

Re: Source Look up in Oracle DB

Heinvandenheuvel — Wed, 20 Sep 2023 03:08:44 GMT

It sure looks like you are doing the right things.

The two 'classical' reasons for this to fail is 1) trailing spaces and 2) character-set discrepancies.

It's interesting to see how 'N/A' appears to work - which would suggest it is NOT a trailing spaces issue. Still, I would run some test with the lookups being 8 chars, and toss is some test values with 8 uppercase characters [A-Z]. I might also run a test without a bind variable just using single quoted text which you'll have double-single quote to make to parse.

Personally I'd go in with the big guns: Oracle Trace Event 10046. Google for "Oracle SQL trace with bind variables". Maybe you can just set that event for the Oracle Session(s) associated with the right repctl task instance.

fwiw - possibly interesting reading: "difference between varchar2(10) and varchar2(10 char) in oracle" ->

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532325800346614530

Good luck, and let us know!

Hein

Re: Source Look up in Oracle DB

Dana_Baldwin — Mon, 25 Sep 2023 21:47:21 GMT

Hi @pguddera21

If one of the replies helped to resolve the issue, please mark it as a solution & like it to make it easier for others to find.
Thanks in advance!

Re: Source Look up in Oracle DB

pguddera21 — Tue, 26 Sep 2023 13:37:39 GMT

It looks like the N/A results showed up before the valid entries. When I filtered those out in the lookup, it matched correctly.