Skip to content
+1-888-319-3663

COMMUNITY FORUM

Accessing Global Variable from Child Map

Jothikrishnan asked 1 year ago
Hi,
I’ve have added a child map on the Parent Map Task and marked all the checkboxes. Im updating Emp Class and Emp ID and trying to use it on the Child Map Source. But always fails saying validation failed. “source column does not exist in the source data”
 
SELECT GBL_EMPLOYEEID as EmpID, *   FROM UPR41201 u 
LEFT JOIN UPR40600 ON PAYRCORD = u.PAYROLCD   
WHERE u.EMPLCLAS= GBL_EMPCLASS
 
Please help
 
Thanks,
Jothikrishnan Narasimmaraj replied 1 year ago

I’m trying to import Emp paycodes from a class as the Default from Emp Class filed doesnt rolldown the paycodes though i have it mapped with Local Constant as 1.

Answers
Lorren Zemke answered 1 year ago
Since GBL_EMPLOYEEID and GBL_EMPCLASS are not columns in your SQL table, they cannot be referenced in a SQL Query because validation expects them to be columns.
If you put single quotes around those two global variables as below, SmartConnect replaces the global variables with the actual value and should then return the value. https://www.eonesolutions.com/Manuals/SmartConnect/SmartConnect%202015/?page=data_source_variables
 
SELECT ‘GBL_EMPLOYEEID’ as EmpID, *   FROM UPR41201 u 
LEFT JOIN UPR40600 ON PAYRCORD = u.PAYROLCD   
WHERE u.EMPLCLAS= ‘GBL_EMPCLASS’

 
This also assumes that on the Run Map task the Use Variables from Parent Map is checked.
Jothikrishnan Narasimmaraj replied 1 year ago

Thank you Lorren. That was the first thing I tried but no luck and I do have the Use Variables from Parent Map checked and in fact all 3 checkboxes there.

Also in the below example, it didnt replace anything with or without single quotes

SELECT ‘GBL_EMPLOYEEID’ as EmpID, * FROM UPR41201 u
LEFT JOIN UPR40600 ON PAYRCORD = u.PAYROLCD
WHERE u.EMPLCLAS=’GBL_EMPCLASS’

/*
DECLARE @EmpID varchar(100)
Set @EMPID = GBL_EMPLOYEEID

DECLARE @EmpClass varchar(100)
Set @EMPCLASS = GBL_EMPCLASS

SELECT @EmpID as EmpID, * FROM UPR41201 u
LEFT JOIN UPR40600 ON PAYRCORD = u.PAYROLCD
WHERE u.EMPLCLAS= @EmpClass */

Results from Trace:

exec sp_executesql N’UPDATE [DataSourceBase] SET QueryText = @p0, PreviewCount = @p1, InstanceName = @p2 WHERE DataSourceBaseId = @p3′,N’@p0 nvarchar(max) ,@p1 int,@p2 nvarchar(4000),@p3 uniqueidentifier’,@p0=N’USE xPRO

/*
SELECT ”GBL_EMPLOYEEID” EmpID, * FROM UPR41201 u
LEFT JOIN UPR40600 ON PAYRCORD = u.PAYROLCD
WHERE u.EMPLCLAS=”GBL_EMPCLASS” */

DECLARE @EmpID varchar(100)
Set @EMPID = GBL_EMPLOYEEID

DECLARE @EmpClass varchar(100)
Set @EMPCLASS = GBL_EMPCLASS

SELECT @EmpID as EmpID, * FROM UPR41201 u
LEFT JOIN UPR40600 ON PAYRCORD = u.PAYROLCD
WHERE u.EMPLCLAS= @EmpClass ‘,@p1=10,@p2=N”,@p3=’0437F06F-82EA-4044-A69B-ABF8010EBF1E’

Jothikrishnan Narasimmaraj replied 1 year ago

I figured out. I duplicated the Child Map from another and it had a Calculated Column which I missed to delete. And it seems it was looking for a column on the calculated field which was not in the source query. Would have been easier if error message says what column it was looking for exactly.
anyways..Sorry…my bad. Thanks for you help.


If you would like to submit an answer or comment, please sign in to the eOne portal.