unable to complie - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unable to complie

Author

Topic

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-26 : 07:08:35

hi
i WAS ABLE TO COMPLIE this proc in the morning but droped unknowningly now iam unable to complie
all the column 'empr_id' exists when i run this speratly the select statement it works

CREATE PROCEDURE search_trans
(
@tpa_id varchar(6),
@empr_id varchar(18) = NULL,
@empe_key int = NULL,
@service_start_dte datetime = NULL,
@user_id_key int,
@acct_type_cde varchar(4) = NULL,
@batch_count int = 20
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @total_errors tinyint,
@TXN_PROCESS_CDE_VALID smallint,
@DuplicateFlag int,
@user_type_cde smallint,
@USER_TYPE_MBI smallint,
@USER_TYPE_SYSTEM smallint

SELECT @total_errors = 0,
@TXN_PROCESS_CDE_VALID = 1,
@DuplicateFlag = 16,
@user_type_cde = dbo.udf_GetUserTypeCode(@user_id_key),
@USER_TYPE_MBI = 1,
@USER_TYPE_SYSTEM = 16,
@tpa_id = dbo.udf_IsDefaultString(@tpa_id, NULL),
@empr_id = dbo.udf_IsDefaultString(@empr_id, NULL),
@empe_key = dbo.udf_IsDefaultInt32(@empe_key, NULL),
@acct_type_cde = dbo.udf_IsDefaultString(@acct_type_cde, NULL),
@service_start_dte = dbo.udf_IsDefaultDateTime(@service_start_dte, NULL)

IF @tpa_id IS NULL
BEGIN
EXECUTE @total_errors = dbo.usp_ERR_InsertUserError @error_cde = 18015, -- TODO error code
@error_cnt = @total_errors
END

IF @total_errors > 0
BEGIN
GOTO EXITSP
END

DECLARE @TEMP_DUPLICATES TABLE
(
tpa_id VARCHAR(6) NOT NULL,
empr_id VARCHAR(18) NOT NULL,
empe_key INT NOT NULL,
service_start_dte datetime, -- TODO finish this declaration,
service_end_dte datetime,
cardholder_name VARCHAR(2000),
txn_amt_orig decimal(19,4),
approved_amt int,
denied_amt int,
merchant_name VARCHAR(50),
MTC char(4),
notes varchar(255),
txn_options smallint
)
INSERT INTO @TEMP_DUPLICATES
(
tpa_id,
empr_id,
empe_key,
service_start_dte,
service_end_dte,
cardholder_name,
txn_amt_orig,
--approved_amt
denied_amt,
merchant_name,
MTC,
notes,
is_dup
)
SELECT TOP 20 -- TODO latest entered @batch_count value duplicate records
FT.tpa_id,
FT.empr_id,
FT.empe_key,
FT.service_start_dte,
FT.service_end_dte,
(e.first_name+ ', '+ e.last_name ) AS cardholder_name, -- TODO: construct name with

firstname,lastname with joining with employee/dep tables if needed should decrypt the data
--E.first_name if cardholder_name is accessed by employee then these two column can be ignored,
--E.last_name,
FT.txn_amt_orig,
--approved_amt, -- --Need to to put condition to how to get approved amt,
FT.denied_fee, -- This column considered for denied_amt as this column doesnot exists
FT.merch_name,
FT.merch_id, -- MTC column on UI
FT.notes,
is_dup = '1'
FROM dbo.[vw_PDB_FLEX_TXN] FT (NOLOCK) INNER JOIN
dbo.[EMPLOYEE] E ON (E.[tpa_id] = FT.[tpa_id] AND E.[empr_id] = FT.[empr_id] AND E.[empe_key] =

FT.[empe_key])
inner join dbo.[TPA] T ON (E.[tpa_id] = T.[tpa_id]) INNER JOIN
dbo.[EMPLOYER] ER on (E.[tpa_id] = ER.[tpa_id] and E.[empr_id] = ER.[empr_id])
WHERE -- Duplicate records
FT.origin_cde >= 50 -- manual transactions
AND FT.txn_adjud_cde = 1 -- approved transactions
AND FT.txn_cde in ('10')
AND FT.txn_process_cde = @TXN_PROCESS_CDE_VALID -- valid
AND FT.[tpa_id] = @tpa_id
AND FT.[service_start_dte] = @service_start_dte
AND FT.[etxn_seq_num] = 1
AND ((@acct_type_cde IS NULL) OR (@acct_type_cde IS NOT NULL AND FT.[acct_type_cde] =

@acct_type_cde))
AND (FT.txn_options & @DuplicateFlag = @DuplicateFlag)
AND FT.reimb_key is null -- not reimbused yet
-- Employer Id optional
AND ((@empr_id IS NULL) OR (@empr_id IS NOT NULL AND T.[empr_id] = @empr_id))
-- User has access to employer
AND (@user_type_cde IN (@USER_TYPE_MBI, @USER_TYPE_SYSTEM)
OR (@user_type_cde NOT IN (@USER_TYPE_MBI, @USER_TYPE_SYSTEM)
AND T.[empr_id] IN (SELECT [empr_id]
FROM dbo.[USER_EMPLOYER]
WHERE [tpa_id] = @tpa_id
AND user_id_key = @user_id_key)))
AND ((@empe_key IS NULL) OR (@empe_key IS NOT NULL AND FT.empe_key = @empe_key))
-- TPA is not active (All Status Except Perm Inactive)
AND T.[tpa_status_cde] < 4
-- Employer is active (All Status Except Perm Inactive)
AND ER.[empr_status_cde] < 4
ORDER BY FT.txn_dte DESC
--UNION ALL

-- join @TEMP_DUPLICATES table with dbo.[vw_PDB_FLEX_TXN]
-- to find out for each of above 20 records, possible duplicate claims
-- investigate more to make join/union/insert into -- the final goal is this SP should return all 'y' and 'n' data

EFFICIENTLY (NO CURSORS IN SP)

INSERT INTO @TEMP_DUPLICATES
(
tpa_id,
empr_id,
empe_key,
service_start_dte,
service_end_dte,
cardholder_name,
txn_amt_orig,
--approved_amt,
denied_amt,
merchant_name,
MTC,
notes,
is_dup
)
SELECT t.tpa_id,
t.empr_id,
t.empe_key,
t.service_start_dte,
t.service_end_dte,
(e.first_name+ ', '+ e.last_name ) AS cardholder_name, -- TODO: construct name with firstname,

lastname with joining with employee/dep tables
t.txn_amt_orig,
--t.approved_amt,
t.denied_fee,-- This column considered for denied_amt as this column doesnot exists
t.merch_name,
t.MTC,
t.notes ,
is_dup = '0'
FROM dbo.[vw_PDB_FLEX_TXN] T (NOLOCK) INNER JOIN @TEMP_DUPLICATES TP ON
(TP.tpa_id = T.tpa_id
AND TP.empr_id = T.empr_id
AND TP.empe_key = T.empe_key
AND TP.service_start_dte = T.service_start_dte
AND TP.[txn_amt_orig] = T.[txn_amt_orig]
)
INNER JOIN dbo.[EMPLOYEE] E ON E.empr_id = T.empr_id

WHERE (T.txn_options & @DuplicateFlag != @DuplicateFlag)
AND T.[tpa_id] = @tpa_id
AND T.[service_start_dte] = @service_start_dte
AND T.txn_cde in ('10','11','12')
AND T.[etxn_seq_num] = 1
AND T.txn_process_cde = @TXN_PROCESS_CDE_VALID -- valid

-- NEED TO RETURN @TEMP_DUPLICATES ROWS
RETURN(0)

EXITSP:

IF @total_errors > 0
BEGIN
EXECUTE dbo.usp_ERR_GetUserErrors
END

RETURN (@total_errors)

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

error:
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70
Invalid column name 'is_dup'.
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70
Invalid column name 'empr_id'.
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70
Invalid column name 'empr_id'.
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 139
Invalid column name 'is_dup'.

krmm

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-26 : 07:19:54

>> i WAS ABLE TO COMPLIE this proc in the morning
Not this version you weren't.

The create @TEMP_DUPLICATES has a txn_options column but the inserts call it is_dup.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-26 : 07:55:35

CHANGED txn_options IN @TEMP....TO IS_DUP GIVES me
empr_id not found
select statment as all the column checked

Server: Msg 207, Level 16, State 3, Procedure usp_TXN_SearchDupFlexTxn, Line 65
Invalid column name 'empr_id'.
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 65
Invalid column name 'empr_id'.

krmm

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-26 : 08:25:24

The column named "empr_id" does not exist in the table you think it does.

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-26 : 08:27:22

ya when i dry run the select statment it does not give error

krmm

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-26 : 08:30:59

i just now ran this select query it does not give me error

krmm

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-26 : 08:46:44

hi
able to complie to there was error in the join which was refered by other table which was refering the empr_id column which was not present
i will be running this proc with some more condition later the day
thanxs all

krmm

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-26 : 08:51:02

"Unable to complie"?
"Unable to comply"?

Do we have a Borg here?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources