Mercator fails when no data found 1

[MikeyLuken]

Hi, I'm having an issue that I can't seem to resolve. I'm pretty new to Mercator so any assistance would be appreciated.

I'm using Mercator 6.7.

What I need to do is pull all data from 7 tables based on a last updated date field, and insert/update the data into 7 exact tables on another server.

The problem occurs when no updated data is found in one of the tables. What it appears to be trying to do is attempting to insert null data into the table which Oracle won't allow.

I've tried using this code in the functional map, but it won't fail.
=IF(ABSENT(ATTRIBUTE_ID Column:FC_FROM_EXTENDED_ATTRIBS), FAIL("no data found"))

What I need it to do is: if no data is found in the input card, ignore that input card and go on to the next.

I appreciate any assistance.
Thanks,
Mike

 

[janhes]

I think you need to do the check before entering the functional map.

=IF(PRESENT(ATTRIBUTE_ID Column:FC_FROM_EXTENDED_ATTRIBS), do Functional_map)

It shouldn't fail if there is no data though. This may be a 6.7 bug. You should be using at least 6.7.1.

 

[BocaBurger]

Input cards require data, unless the object is optional.
You can't "skip" an input card, if the tree is defined as required.
If you are trying to send a null to a not null field, it should fail there too. There are several options.
In the input type tree, define a default value if the field is none.
In your map rule define a default value if input is not present.
Set your DB output to send only good rows.

Where is the map failing? Input or output?
What version of Oracle are you using with 6.7.x?



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[MikeyLuken]

janhes, BocaBurger

Thank you for your replies.

It's failing in the output section, I wanted to skip the output(insert/update) if there was no data found.

Thanks to janhes I can force the FAIL() to abort the map with the code:
=IF(PRESENT(ATTRIBUTE_ID Column:Row:C_FROM_EXTENDED_ATTRIBS), F1_EXTENDED_ATTRIBS(Row:C_FROM_EXTENDED_ATTRIBS), FAIL("no data"))

Unfortunately, if I remove the FAIL() it seems to go call the functional map, as it doesn't go on to the next card.

I'm using Oracle 11i.

Any suggestions would be appreciated.

Thanks,
Mike

 

[MikeyLuken]

Also, I just noticed that I don't currently have insert privleges which is obviously an issue itself. I'm trying to resolve that now.

Would that cause the error. Obviously that would cause an error, but if there are no results should it just ignore the insert/update?

Thanks,
Mike

 

[BocaBurger]

Oracle 11i? Even 10i is not supported by 6.7.x.

To allow the good data to be sent to the DB and the bad data be sent to a file, use the -BD option in the command line.

See resource adapters reference guide.





BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[MikeyLuken]

BocaBurger,

Thanks again for your reply, the -bd option seems to have resolved the problem.

Quick Question: How does Mercator constitute good and bad data? I've sent some e-mails trying to locate that guide.

Thanks,
Mike

 

[BocaBurger]

If the DB says it's good, then it's good. There is no validation on output, unless you do data auditing.




BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[MikeyLuken]

Ok, Thanks for the assistance on all of this. I really appreciate the help.

Thanks,
Mike

 

[BocaBurger]

You are welcome.

Wait 'til you get the bill.






BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!