August 25, 2008 at 8:48 am
I have a simple situation here. Need to work on the logic.
this is a simple query that i run on Execute sql task
"SELECT Top (1) ENCT_HDR_BATCH_ID AS Value
FROM dbo.tblErrorReportsLoadingHDR"
This value will be either 837P or DRUG. If it is 837P i want to set this to M and if it is DRUG i want to set it as P. Then once i set this value, I will have another proc that needs a single paramater, but i want to give this value output that proc.
How YOU DO IT ?
SO here is the simple example.
I have Execute Sql task , where i run above query , the result it returns is either 837P or DRUG, If 837P then set the value M to a variable ortherwise if it is DRUG set the value P to a variable then pass this value to another proc that is looking for this paramater.
Also, is there anyway to debug or see that what value is being assigned to my variable.
Something like dataviewrs , whhere you see teh actual data being flown
THANKS
August 25, 2008 at 10:13 am
Where are you creating and assigning the value ('M' or 'P') to a variable?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 11:38 am
Because my Proc that ia m calling on the next step requires either P or M as input parameters. The logic is build in that way within the proc.
August 25, 2008 at 12:30 pm
To view runtime varible values - You can set Breakpoints to your tasks by right click task - > Edit Breakpoint ->choose appropriate event. In your case 'OnVariableValuechaged' event would be suitable. Then while debugging you can open the watch window and type your variable name to see the runtime value. http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx
You can either return 'M' or 'P' directly from your select
SELECT Top (1) CASE ENCT_HDR_BATCH_ID
WHEN '837P' THEN 'M'
WHEN 'DRUG' THEN 'P'
END AS Value
FROM dbo.tblErrorReportsLoadingHDR
Or you can use Expressions to set the value later.
Then set the resultset to package varible and then use this variable as parameter in your subsequent Execute Task.
HTH
August 25, 2008 at 2:34 pm
MUKTI ........., I believe I am doing right here. But I don't know what is wrong here. Let me give you my proc str
ALTER PROCEDURE [dbo].[uspOmniCaidException]
@type char(1)-- 'P' for Medical Claim and 'G' for Pharmacy Claim
AS
SET NOCOUNT ON
IF (@type = 'P')
-- Errors for Medical Claims
BEGIN
UPDATE dbo.CLM_LN
SETOMNICAID_CLM_ID = ENCT_TCN_NUM,
RCVD_FR_OMNICAID_TS = GETDATE()
FROM dbo.CLM_LN l
INNER JOIN dbo.tblErrorReportsLoadingDTL e
ON l.VEND_CLM_NBR = e.ENCT_MCO_TCN_DAT
AND l.CLM_LN_NBR = e.ENCT_LI_NUM
INSERT INTO dbo.CLM_LN_CHG_MSG (
EDI_CLM_ID,
VEND_ID,
VEND_CLM_NBR,
CLM_LN_NBR,
CLM_LN_VRSN_NBR,
MSG_CD,
MSG_SRC_CD,
MSG_TXT,
CRTE_USR_ID,
CRTE_TS
) SELECTISNULL(l.EDI_CLM_ID, ABS( CAST ( CAST ( NEWID() AS VARBINARY ) AS INT ) )),
ISNULL(l.VEND_ID, ABS( CAST ( CAST ( NEWID() AS VARBINARY ) AS INT ) )),
--here instead of two cast function we can use checksum function as
--ISNULL(l.EDI_CLM_ID, ABS(CHECKSUM(NEWID())),
e.ENCT_MCO_TCN_DAT,
e.ENCT_LI_NUM,
ISNULL(l.CLM_LN_VRSN_NBR, 1),
e.ENCT_LI_EXC_CD,
'ER',
'OmniCaid Error',
USER,
GETDATE()
FROMdbo.tblErrorReportsLoadingDTL e
LEFT OUTER JOIN dbo.CLM_LN l
ON e.ENCT_MCO_TCN_DAT = l.VEND_CLM_NBR
AND e.ENCT_LI_NUM = l.CLM_LN_NBR
END
ELSE
-- Errors for Pharmacy Claims
BEGIN
UPDATE d
SET OMNICAID_CLM_ID = ENCT_TCN_NUM,
RCVD_FR_OMNICAID_TS = GETDATE()
FROM dbo.DRUG_CLM d
INNER JOIN dbo.tblErrorReportsLoadingDTL e
ON d.VEND_CLM_NBR = e.ENCT_MCO_TCN_DAT
-- Add matched Error records to DRG_CLM_CHG_MSG table
INSERT INTO dbo.DRG_CLM_CHG_MSG (
VEND_CLM_NBR,
DRG_CLM_VRSN_NBR,
MSG_CD,
MSG_SRC_CD,
MSG_TXT,
CRTE_USR_ID,
CRTE_TS
) SELECTe.ENCT_MCO_TCN_DAT,
ISNULL(d.DRG_CLM_VRSN_NBR, 1),
e.ENCT_LI_EXC_CD,
'ER',
'OmniCaid Error',
USER,
GETDATE()
FROMdbo.tblErrorReportsLoadingDTL e
LEFT OUTER JOIN dbo.DRUG_CLM d
ON e.ENCT_MCO_TCN_DAT = d.VEND_CLM_NBR
END
SET NOCOUNT OFF
SO in the First execute sql task I have this query--SELECT Top (1) SUBSTRING(ENCT_HDR_BATCH_ID, 4, 4) AS Value
FROM dbo.tblErrorReportsLoadingHDR
This is my hdr tbl data
H000X123408143XYZPXFFS023837P
H000X123408143XYZPXFFS024837P
H000X123408143XYZPXFFS025837P
H000X123408143XYZPXFFS026837P
H000X123408143XYZPXMVS001837P
H000X123408143XYZPXMVS002837P
then on the result set page I have a value assign to a variable ValuetoPass , pora meter is empty.
Then on the next exec sqwl task I run this above query like
exec dbo.uspOmniCaidException 0.
then on parameter page i have a variable Valuetopass as input with parameter o
But everytime my proc goes and runs into Else part of the proc.
Also i tried to see teh variable result by setting up brekpoints. i cannot.
THANKS
August 25, 2008 at 5:18 pm
Thats sounds right, hope the parameter variable Data Type for the second Execute SQL Task is varchar. The only other thing i can think of is to check the variable Valuetopass itself and check if no expression is set for it.
Not sure why you cannot watch the variable in watch window. Try using a simple script task to check the value. Something like
MsgBox(Dts.Variables.Item("Valuetopass").Value.ToString)
Don't forget to put the variable Valuetopass on the read only list of script last.
August 26, 2008 at 12:56 am
I think SUBSTRING(ENCT_HDR_BATCH_ID, 4, 4) is wrong
use SUBSTRING(ENCT_HDR_BATCH_ID, 4, 1) and then try it...
as 4,4 will take space i think... so use 4,1 so it will take only P not any other value...
you can use trim function also TRIM(SUBSTRING(ENCT_HDR_BATCH_ID, 4, 4))
Don't know it will solve issue or not..
Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply