Accessing Global Variable from Child Map
Jothikrishnan asked 3 years ago
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,
Lorren Zemke answered 3 years ago
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 3 years 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 3 years 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.
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.