From Database Pass Issue

[Former Member]

Hey experts,

I've been posting on here a lot lately but I keep getting answers so I'm going to keep coming back! Anyway, I'm trying to do an evaluation of several values returned by a SQL query and I'm using a From Database pass to accomplish this. I have a table of users that I created that contains only the following fields:

MSKEYVALUE, FIRSTNAME, LASTNAME, MSKEY

The source of my From Database pass pulls in this entire table:

select * from UsersToBeEvaluated

In my destination, I'm writing to a table called EvaluatedUsers and it looks something like this:

Target                    Script          Source

MSKEY                                     MSKEY

MSKEYVALUE                         MSKEYVALUE

RESULT                z_evaluate  FIRSTNAME||LASTNAME||MSKEYVALUE

When I ran my job the first time and put a bunch of uWarning lines in the "z_evaluate" script to see what my variable values were, Par is being passed in empty. I'm passing in three values separated by the double pipe ("||") then splitting them inside the script. Why is Par empty? Will From Database only allow one value in each of the source entries? If so, how can I best accomplish what I want to do, that is evaluate these three variables and have the results come out in a temporary table? Thanks in advance for any assistance.

[Former Member · Accepted solution]

Nevermind all. I got this figured out. I decided to use a To ASCII pass instead and that actually works out really well as this job is supposed to create a report that the IDM Admin can use for troubleshooting purposes. In CSV form, the results of this pass can be easily read in a spreadsheet program.

[Former Member]

From Passes don't combine source attributes well in the destination grid. Not at all. The easy workaround to that is to combine the values in the source statement, something like:

select MSKEYVALUE, FIRSTNAME, LASTNAME, MSKEY, FIRSTNAME+'||'+LASTNAME+'||'+MSKEYVALUE as COMBINEDVALUE from ....

Then use

Z_RESULT   z_Evaluate COMBINEDVALUE

in the destination of the From DB pass. Or use an EntryScript to do the same thing.

Br,

Per Christian

Message was edited by: Per Krabsetsve

[Former Member]

This is a really good answer and I'm sure I'll use this one someday. I wouldn't have thought to combine them in the source tab. Thanks Per. I'm dreading the day when you disappear from SCN due to you no longer being an SAP employee.

[Former Member]

FYI: Chris, I tried that combined value thing you mentioned as a need for it has already come up. It did not work. 

[Former Member]

Was it the SQL that failed or the was the value missing in the destination? Last time I tried this it worked fine, but I used the "insert data source template" function to verify that the additional "combined" value was there, and also no scripting. Also make sure to convert or cast numbers to varchar as some databases will do concatenations of 'BOB'+1000 = NULL which will leave your combinedvalue empty all the time (if I remember correctly).

[Former Member]

The query I tried actually failed in TOAD so I never got it into IDM. I figured if it didn't work there, it wouldn't work in IDM. You may actually be onto something with the varchar thing. The combined value I was trying to do was:

select MX_FS_PERSONNEL_NUMBER as PersonnelNo + "||46"

where ...

The script I have needs that 46 argument. The error I was getting back was that it wasn't a valid number. No biggie actually. I ended up figuring something else out. Thanks!

[Former Member]

I'm pretty sure you have the order and quote types wrong in select MX_FS_PERSONNEL_NUMBER as PersonnelNo + "||46"

It should be select MX_FS_PERSONNEL_NUMBER + '||46' as PersonnelNo

On the other hand, if you're using Toad you might be on Oracle, in which case I got it wrong from the start and  you need to use || to concatenate

SELECT MX_FS_PERSONELL_NUMBER || '||46' as PersonellNo FROM ....

-

Br

[Former Member]

Oops... Typo. I did enter it in IDM the way you wrote it. I posted it here wrong.

[Former Member]

Hi,

i have a job to run using TODATABASE pass.............. like my source one database and destination is one database which is connected through the jdbc:odbc driver ..............

so when i run the job the error is

source is a table not a view ...........

The key attribute (e.g. MSKEYVALUE) is missing or the value is empty

To Database initPass

java.lang.Throwable

Please if any one can help me with this to execute the job.............

Thanks,

Anurag.

[terovirta]

screen shots would help..

But as a shot in the dark, do you have the "use Identity Store" checked on the source tab? If you do remove that and define the DB connection.

regards, Tero

[Former Member]

HI,

i'm sharing the screen shots of my source and dest ........ like source is ECCDEV and destination is cua system......................... i need to solve it at any cost so please help me out with ur knowledge...............if u have  a demo of todatabase pass would be helpful

Thanks,

Anurag.

[former_member2987]

Brandon, I've seen stuff like this happen before.  Switching to a To Database pass will prove helpful as well.

Matt

[Former Member]

The thing with that is, the destination tab doesn't have any option to "Delete Table Before Loading" or "Add Entries to Table" or whatever. You just enter the name of the table your putting into the Database and that's it. How does IDM handle that in a To Database pass? I was trying to look up the documentation for some answers but the stupid online help link was down for most of the day again. In the end, I decided on the To ASCII file because I'm eliminating a step anyway. I'm creating the CSV file directly as a part of the job and then my Termination Script will email it to the IDM Administrator. In the end, this is the best solution but still, how does To Database handle existing tables? Will it wipe the table before loading? I would think that but I don't know and I couldn't use documentation to find out.

[former_member2987]

You can always do a SQL Updating and delete the pass manually.

Matt

[terovirta]

Graphical version (SQL Server) of what Matt said

regards, Tero

[Former Member]

I always wondered what the SQL Updating checkbox was for. That will come in handy someday. Thanks all.