Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
srikar22
Contributor III
Contributor III

Target Lookup

We are unable to load the data using the target lookup getting an error

target_lookup('NO_CACHING','TargetSchema','TargetTable','max(load_date)','','') < $LOAD_DATE

We are using the following Target lookup  to load the table only when the Load date from the source table is greater than Load date in Target table.

We are using this target_lookup in FullLoad Filter

 

Here is the error.

Stream component 'st_1_ORA_FINCAPRD' terminated
Stream component failed at subtask 1, component st_1_ORA_FINCAPRD
Error executing source loop
Endpoint is disconnected
Failed to init unloading table 'SNAPLOGIC'.'WEEKLY_CCS_EXPEND'
ORA-00911: invalid character

Labels (1)
9 Replies
SwathiPulagam
Support
Support

Hi @srikar22 ,

 

Below is the article regarding Target_lookup for your reference:

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-...

If you still need assistance, I recommend contacting Professional Service.

Because Target_lookup impacts the performance of your data load so implement it with the help of our expertise.

 

Thanks,
Swathi

srikar22
Contributor III
Contributor III
Author

Hello Swathi,

I already looked up that post.Please let us know how to form a Target lookup for my use case.

Thanks

Heinvandenheuvel
Specialist III
Specialist III

>>> target_lookup('NO_CACHING','TargetSchema','TargetTable','max(load_date)','','') < $LOAD_DATE

Please provide the EXACT syntax uses. Cleary this is just a made up example with at least faked target names and perhaps more was wrong, but i think it was fine here.

>>> Failed to init unloading table 'SNAPLOGIC'.'WEEKLY_CCS_EXPEND'

>>> ORA-00911: invalid character

The (TARGET|SOURCE)_LOOKUP function perform a string manipulation to generate SQL code:

select EXP from SCHM.TBL where EXP

You did not provide an EXP string, so the result became: 

select max(load_date)  from TargetSchema,TargetTable  where

That's not valid SQL and Oracle complained. 

I suggest you retry using for example 1=1 as EXP.

Hein.

 

srikar22
Contributor III
Contributor III
Author

Thank you.

I am trying to avoid duplicates in the target table

I am trying to load target data using a filter on load_date. if the source table load_date is greater than Target table load_date then only insert the data if not ignore the replication to target

Here is the syntax I am using 

$LOAD_DATE > target_lookup('NO_CACHING','qlik_replicate','weekly_ccs_expend_old','max(load_date)','load_date=:1','$LOAD_DATE')

 

Here is the Log Message 

Target endpoint does not support data enrichment feature

Source is Oracle

Target is Redshift

 

Thanks

Heinvandenheuvel
Specialist III
Specialist III

 

You first post reported "ORA-00911: invalid character" strongly suggesting an Oracle endpoint, and nothing to the contrary was indicated.

Now you write "Target is Redshift". That's a whole other animal. 

And indeed "Target endpoint does not support data enrichment feature"

Replicate R&D apparently believes that it cannot provide a useable solution (performance wise) for a Redshift target. It would be too slow, and soo expensive queries to perform against a datawarehouse style target. A similar restriction exists for Snowflake.

You may want to look for a better design. Perhaps just using error handling "update row if exists" which will pre-delete (if exists) and re-insert. That's cheaper than testing to avoid dups. And check out (search) "Full Load Passthru" filtering to avoid bringing in too much data. For example: https://community.qlik.com/t5/Official-Support-Articles/Filter-for-last-90-days-of-data-in-Qlik-Repl...

Hein.

Heinvandenheuvel
Specialist III
Specialist III

@srikar22  -   I tried to reproduce "ORA-00911: invalid character" but did not get it so far.  What replicate version? What was the exact lookup statement you used? I only got "[SOURCE_CAPTURE ]E: Failed to rebind parameter for data lookup".

I got that when using  source_lookup(.... TBL,'','').  as well as source_lookup(.... TBL,'1=1',''). 

I could not use source_lookup(.... TBL) omitting COND,COND_PARAMS completely. That gives a parse error in the designer gui.

What did work for me is : source_lookup(.... TBL,'1=1') - omitting COND_PARAMS.

@Dana_Baldwin , @john_wang  - maybe someone can submit a minor code/documentation enhancement to allow lookups wit not condition, essentially leaving out the WHERE clause. And we may want to document the '1=1' style workaround and being able to omit the COND_PARAMS maybe writing ....,COND [,COND_PARAMS] )

Hein.

john_wang
Support
Support

Hello @Heinvandenheuvel ,

I can open an internal ticket for this issue. It appears that the WHERE clause is essential to ensure that the primary key or unique index is utilized, rather than performing a full table scan. This will help prevent the lookup() query from impacting the database performance.

Additionally, the lookup() query should return exactly one row if the row exists. Without a WHERE clause, it is challenging to guarantee the uniqueness of the result set.

Regards,

John.

 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist III
Specialist III

>>  It appears that the WHERE clause is essential to ensure that the primary key or unique index is utilized, rather than performing a full table scan.

I get that, but that's not up to Replicate to decide. And Replicate should never knowingly send bad SQL to a database IMHO specially here where it is probably easy to fix.

The intended/expected (target|source)_lookup is a possibly very large table of values with a unique key.

But that's not what customers come up with. They use "exists" to give a singleton yeah/nay. They use a min or max with a 1 hour or 1 day cache. They can design a control table with a single row and a cut-off date, or enable flag. In Oracle they can use a SELECT X FROM DUAL perhaps just to see if they are in development or prod ?!

In short, I believe there are many circumstance where no selection expression may be needed and it would be nice if that was simply allowed. If it is not allowed then report during task edit/load/activate - do not send a broken statement to a DB to find out.

fwiw,

Hein.

john_wang
Support
Support

Thanks for the detailed explanation, @Heinvandenheuvel , I will open an internal ticket.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!