February 6, 2012 at 5:26 pm
I am using a regular expression for a package variable and using the variable as a source to get the data in data flow task..but the expression seems to be wrong as its giving me the error: "Command text was not set for the command object"
The regular expression is:
"SELECT DISTINCT
pat_id,
TO_CHAR(pat_uid) AS pat_uid,
hmo,
member_number,
CONCAT(substr(date_of_birth, 1, 2), CONCAT(substr(date_of_birth, 4, 2), substr(date_of_birth, 7, 4))) AS date_of_birth, last_name,
CONCAT(CONCAT(first_name, ' '), middle_initial) AS first_name,
CASE
WHEN REGION_HMOA = 'EAS' THEN '94294'
WHEN REGION_HMOA = 'SF' THEN '94294499'
WHEN REGION_HMOA = 'SOL' THEN '94294696'
WHEN REGION_HMOA = 'SAC' THEN '94294497'
ELSE '94294489699'
END AS Medical_Group_id,
CASE
WHEN hmo = 'BS' THEN '002'
WHEN hmo = 'WH' THEN '027'
WHEN hmo = 'CC' THEN '016'
WHEN hmo = 'CG' THEN '022'
WHEN hmo = 'PC' THEN '012'
WHEN hmo = 'AE' THEN '020'
WHEN hmo = 'HN' THEN '007'
WHEN hmo = 'HA' THEN '027'
WHEN hmo = 'SN' THEN '018'
END AS health_plan_id
FROM XYZ_TABLE where HMO = '"+ @[User::Health_Plan_ID] + "' ORDER BY pat_uid"
Can somebody please tell me whats wrong with it..Thanks in advance..
February 7, 2012 at 12:45 am
What is the datatype of the Health_Plan_ID variable?
If it is an integer, you need to cast it to a string first.
ps: this is just an expression, regular expressions are something totally different
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2012 at 12:47 am
edit: not a double post
Did you set the property of the variable to EvaluateAsExpression?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2012 at 11:13 am
Thank you for the reply guys..the problem was EvaluateAsExpression was not set to true..its working now..thanks again for the help 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply