PBCS - Data Management - Mapping Question

elwayisgod

Hi John,

So I have been unsuccessful in getting ANY SQL to actually do anything to any field I try it on.  I was trying to change a value in a 'source' column with anything just to get it to work.  So in this example I want to change '1210 Inventory : Inventory In Stock' to 'New_Account'.  Really just change it to anything.

So I follow the instructions you provided on how to add a map and use SQL.  I'm using this SQL:

However results don't show anything changing:

I get an error in the log:

So I think something wrong somewhere.  The idea is if I can change any field to anything then atleast I have a working method/template to go off of.  My source is NetSuite, so maybe I need the actual table name and field name and my SQL needs to reference that instead of the column name that is displayed in DM?   I'm not sure I can find out that information or not or if it's available anywhere.

Anyways, thanks for any suggestions, this is driving me crazy.  Would love to use this feature as it seems really powerful.

JohnGoodwin

First of all if you are checking explicitly then it should be an equals sign, for example

WHEN ACCOUNT='ACC1' THEN 'ACC2'

IS is for functions like IS NULL

elwayisgod

Thanks John!!!

Great progress as the '=' will work on explicit maps 

So original issue was I have blank fields coming in and I want to map those to 'No XXXX' or whatever.   So I'm experimenting on the 'Version' field as it's coming in blank thus was trying to get it to change via SQL.

But results don't map and I get this error:

JohnGoodwin

The database column will not be called Version, look at your target application in Data Management and see which "Data Table Column Name" version is assigned to, it will be a UD column

elwayisgod

I have 'Type' field mapped to Version.

Target Appplication:

Import Format:

JohnGoodwin

That looks like your source NetSuite app, I mean check the Version data column for your PBCS application which is your target.

elwayisgod

So I changed my Target application and hardcoded in UD1 for Data Table Column Name.  I guess I'm confused as to what this field is for and if I should always have something defined here for the fields I'm interested in.  I'm not FDMEE expert by any means, but I do understand data integration.  And what Sequence is used for.

My new SQL that works perfectly is:

New results are correct as 'Bill' gets changed to NULL and others that aren't = 'Bill' stay as is..

So next step is modifying the source NetSuite 'saved search' to include Product and Department fields.   Once that is done I can then test changing NULL values for those two fields.  We have to modify this saved search as we can't get any other saved search to return results for some reason.  Only this one actually gives us rows of data in DM.

All this help is much appreciated.  So confusing

Stay tuned.

elwayisgod

OK.  So assign a UD value to any dimensions that are not the 'required' dims.  Then that is technically the field name to use in the SQL.  At least that's how I got it to work.  Still fuzzy on 'Sequence' as I left those blank.

Whoohooo.  Finally.

The NULL values mapping to 'No Dept NS' for Entity dim and 'No Product NS' for Product dim .