Hi All, I’m trying to create a Data Insightrule that checks to see how many times a vendor number exists in a table.
Here’s my syntax:-
BEGIN
IF (SQL(‘RETAIL_LOOKUP_TABLES_BO4’, ‘select count(v_lev_levnr) from lookup_vendor where v_lev_levnr = $V_LEV_LEVNR’) > 1) RETURN FALSE;
ELSE RETURN TRUE;
END
This validates successfully but when I test the rule with a non unique vendor number I get the message:-'Dataservices execution failed for (14.0) 08-29-12 09:52:45 (E) (22408:16524) DBS-070401: |Data flow DF_TEST_RULE_3|Transform Query2 ODBC data source error message for operation : ’
Can anyone tell me what this message means? And has anyone managed to get a ‘unique value’ rule working? I’m sure this must be possible as Information Steward has uniqueness profiling capability as standard.
Well, I managed to sort out the problem so here’s the correct syntax incase anyone else has the same problem:-
DECLARE
# declare variables
$V_Count_Vendor_Num int;
BEGIN
$V_Count_Vendor_Num = SQL(‘RETAIL_LOOKUP_TABLES_BO4’, 'select count(v_lev_levnr) from lookup_vendor where v_lev_levnr = ’ || $v_lev_levnr);
IF ($V_Count_Vendor_Num = 1)
BEGIN
RETURN TRUE;
END
ELSE
IF ($V_Count_Vendor_Num > 1)
BEGIN
RETURN FALSE;
END
ELSE
RETURN FALSE;
END
Also, here’s the syntax for two parameters:-
DECLARE
# declare variables
$V_Count_Articles int;
BEGIN
$V_Count_Articles = SQL(‘RETAIL_LOOKUP_TABLES_BO4’, ‘select count(MATNR) from MARA where MATNR =
‘’ || $P_MATNR || ‘’ AND MANDT = ‘’ || $P_MANDT || ‘’’);
IF ($V_Count_Articles = 1)
BEGIN
RETURN TRUE;
END
ELSE
IF ($V_Count_Articles > 1)
BEGIN
RETURN FALSE;
END
ELSE
RETURN FALSE;
END
(BOB member since 2004-06-09)