April 24, 2012 at 7:15 pm
--Delete Admissions To be Updated
DELETE FROM dbo.SmartAdmissions
WHERE ([Admission ID] IN
( SELECT dbo.FormatInt([Admission ID])
FROM smartadmissionupload))
BEGIN TRY
--Append all Admissions
INSERT INTO dbo.SmartAdmissions
(
[SMARTUploadID],
[Agency ID],
[Clinic ID],
[National Provider Identifier],
[Client Intake ID],
[Client ID],
[Admission ID],
[Facility Identifier],
[Unique Client ID],
[Admission Creation Date],
[Agency Client ID],
[Agency Client ID bsas],
[Social Security Number],
[Client Type],
[Date of Admission],
[Transaction Type],
[Number of Prior Admissions],
[Source of Referral],
Sex, Race, Ethnicity, [Date of Birth],
[County of Residence], [Zip Code], [Marital Status],
[Highest School Grade Completed], [Employment Status],
[Family Income], [Primary Source of Income],
[Living Arrangement], [Number of Dependant Children],
[Health Coverage],
[Currently Pregnant?],
[Current Mental Health Problems?],
[Tobacco use in the past 30 days?],
[Number of days waiting to enter treatment],
[ASI Medical Score],
[ASI Employment Score], [ASI Alcohol Score],
[ASI Drug Score], [ASI Legal Score], [ASI Family Score],
[ASI Psychiatric Score],
[In a Controlled Environment past 30 Days?],
[POSIT Substance Abuse Score],
[POSIT Physical Health Score], [POSIT Mental Health Score],
[POSIT Family Score], [POSIT Peer Score],
[POSIT Education Status Score], [POSIT Vocational Status Score],
[POSIT Social Skill Score],
[POSIT Leisure Recreatal Score], [POSIT Aggression Score],
[POSIT STD HIV Risk Score], [Primary Substance],
[Primary Severity], [Primary Frequency],
[Primary Route], [Primary Age of First Use],
[Secondary Substance], [Secondary Severity],
[Secondary Frequency], [Secondary Route],
[Secondary Age of First Use], [Tertiary Substance],
[Tertiary Severity], [Tertiary Frequency],
[Tertiary Route], [Tertiary Age of First Use],
[Treatment Setting], [Attending Grades K-12],
[Attending GED Program], [Attending Vocational Training],
[Attending Higher Education],
[Number of Arrests in the Past Year],
[Number of Arrests in the Past 30 Days],
[Special Funding 1], [Special Funding 2],
[Special Funding 3],
[Special Project 1], [Special Project 2],
[Special Project 3],
[Number of Days in Support Group in Last 30 Days],
[Number of Days Attended AA/NA in Last 30 Days],
[Additional Information],
[Information_Code],
[Is Submit Without ASI Scores], [Intake Date],
[Intake Creation Date],
HATSAdmissionID,
SrcFileDate )
SELECT
dbo.FormatInt(dbo.SMARTAdmissionUpload.[ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Agency ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Clinic ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[National Provider Identifier]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Client Intake ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Client ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Admission ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Facility Identifier]),
dbo.SMARTAdmissionUpload.[Unique Client ID],
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Admission Creation Date]),
dbo.SMARTAdmissionUpload.[Agency Client ID],
dbo.FormatAgencyClientID(dbo.SMARTAdmissionUpload.[Agency Client ID]),
dbo.SMARTAdmissionUpload.[Social Security Number],
dbo.SMARTAdmissionUpload.[Client Type],
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Date of Admission]),
dbo.SMARTAdmissionUpload.[Transaction Type],
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Prior Admissions]),
dbo.SMARTAdmissionUpload.[Source of Referral],
dbo.FormatInt(dbo.SMARTAdmissionUpload.Sex),
dbo.SMARTAdmissionUpload.Race,
dbo.FormatInt(dbo.SMARTAdmissionUpload.Ethnicity),
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Date of Birth]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[County of Residence]),
dbo.SMARTAdmissionUpload.[Zip Code],
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Marital Status]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Highest School Grade Completed]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Employment Status]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Family Primary Source of Income]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Source of Income]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Living Arrangement]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Dependant Children]),
dbo.FormatInt(SMARTAdmissionUpload.[Health Coverage]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Currently Pregnant?]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Current Mental Health Problems?]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tobacco use in the past 30 days?]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of days waiting to enter treatment]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Medical Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Employment Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Alcohol Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Drug Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Legal Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Family Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Psychiatric Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[In a Controlled Environment past 30 Days?]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Substance Abuse Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Physical Health Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Mental Health Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Family Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Peer Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Education Status Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Vocational Status Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Social Skill Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Leisure Recreatal Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Aggression Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT STD HIV Risk Score]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Substance]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Severity]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Frequency]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Route]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Age of First Use]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Substance]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Severity]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Frequency]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Route]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Age of First Use]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Substance]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Severity]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Frequency]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Route]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Age of First Use]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Treatment Setting]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Attending Grades K-12]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Attending GED Program]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Attending Vocational Training]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Attending Higher Education]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Arrests in the Past Year]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Arrests in the Past 30 Days]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Funding 1]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Funding 2]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Funding 3]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Project 1]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Project 2]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Project 3]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Days in Support Group in Last 30 Days]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Days Attended AA/NA in Last 30 Days]),
dbo.SMARTAdmissionUpload.[Additional Information],
case
when patindex('%migrated%',Lower(dbo.SMARTAdmissionUpload.[Additional Information]))>0
then 1
else
0
end,
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Is Submit Without ASI Scores]),
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Intake Date]),
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Intake Creation Date]),
dbo.SMARTAdmissionUpload.HATSAdmissionID,
dbo.SMARTAdmissionUpload.[SrcFileDate] as datetime
FROM dbo.SMARTAdmissionUpload
WHERE (dbo.SMARTAdmissionUpload.Ignore = 0) and
(dbo.FormatAgencyClientID(dbo.SMARTAdmissionUpload.[Agency Client ID]) >0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Admission Creation Date]) > 0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Date of Admission]) > 0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Date of Birth]) > 0 )
END TRY
BEGIN CATCH
insert into tblDownload_Import
(Description) values ('error: ' + (CONVERT(VARCHAR, ERROR_Number()) + ' msg: ' + ERROR_Message()))
END CATCH
insert into tblDownload_Import
(Description) values ('bottom of sProcess_Upload_Data_Admissions')
return
I need help on the above stored procedure:
Here is explanation about the stored procedure
Once a month excel files are uploaded into the database
These are the things I wanted it to do
1. I wanted the old record in SMARTAdmissions table to be deleted as the upload process start
2. then as data are added to SMARTAdmissionUpload table, I want data to be inserted to SMARTAdmissions table.
It has been working perfectly before but now when data are uploaded to SMARTAdmissionUpload table they are not being inserted to SMARTAdmissions table any longer.
April 24, 2012 at 11:03 pm
How many records in the source table?
Hate to say this, but this insert is marred by a ton of rbar code. The highly extensive use of scalar functions is going to kill performance. For some insight on this, please read this blog post: http://www.sqlservercentral.com/blogs/lynnpettis/2009/05/07/comparing-hardcoded-functions-in-line-tvf-s-and-scalar-functions/.
I was only running the scalar function against one column, you are doing to approximately 50 (more or less).
April 24, 2012 at 11:16 pm
These are the things I wanted it to do
1. I wanted the old record in SMARTAdmissions table to be deleted as the upload process start
2. then as data are added to SMARTAdmissionUpload table, I want data to be inserted to SMARTAdmissions table.
1. You already have a query for deleting data from SMARTAdmissions table, so I assume that part to be working.
2. What do you mean by "then as data are added to SMARTAdmissionUpload table, I want data to be inserted to SMARTAdmissions table"??....Is the SP running as part of an INSERT TRIGGER??
Plus, if it has been working properly till now...then I don't see any problems in the code that would make it stop all of a sudden.
Its pretty hard to understand what the problem is just by looking at your code. You should elaborate a little more and tell about the changes you made to the tables or any other enitities relating to the tables in the given code.....since the last time it worked properly.
April 25, 2012 at 12:13 am
There can be several reason for data not getting inserted. You have to drill down to identify the problem and trouble shoot it.
You are having several conditions while Inserting the data into SMARTAdmission table, these Conidition may also be cause. Try selecting the records before inserting like below,
Select Count(*)
FROM dbo.SMARTAdmissionUpload
WHERE (dbo.SMARTAdmissionUpload.Ignore = 0) and
(dbo.FormatAgencyClientID(dbo.SMARTAdmissionUpload.[Agency Client ID]) >0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Admission Creation Date]) > 0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Date of Admission]) > 0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Date of Birth]) > 0 )
If the query doesn't give any results, the problem is on the where condition. If the query returns results but data is not getting inserted then the problem is on the SMARTAdmission table and we can take a different approach.
---------------------------------------------------
Thanks,
Satheesh.
April 25, 2012 at 4:01 am
when i ran the select count (*) statement of the where clause, i got this error message: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
Please how do I fix it.
April 25, 2012 at 4:21 am
deebabat (4/25/2012)
when i ran the select count (*) statement of the where clause, i got this error message: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to bigint.
Please how do I fix it.
Post your query please, then maybe we'll be able to use something other than omniscience to help you with this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 25, 2012 at 4:30 am
I posted the query under the "Stored Procedure help". The one you replied to is a reply to an advice given by somebody (Erode).
April 25, 2012 at 5:02 am
"Select Count(*)" will give you only the count of the records in your result.
Adding the count will not cause you this error. The error should be on Where condition's you have in the query or you must be making some error while copy pasting or may be a formatting problem. For the same reason only Phil might have asked you to post the query you are executing.
---------------------------------------------------
Thanks,
Satheesh.
April 25, 2012 at 7:40 am
Erode P. Satheesh (4/25/2012)
"Select Count(*)" will give you only the count of the records in your result.Adding the count will not cause you this error. The error should be on Where condition's you have in the query or you must be making some error while copy pasting or may be a formatting problem. For the same reason only Phil might have asked you to post the query you are executing.
Select Count(*)
FROM dbo.SMARTAdmissionUpload
WHERE (dbo.SMARTAdmissionUpload.Ignore = 0) and
(dbo.FormatAgencyClientID(dbo.SMARTAdmissionUpload.[Agency Client ID]) >0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Admission Creation Date]) > 0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Date of Admission]) > 0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Date of Birth]) > 0 )
ERODE that was the query you gave me to run but when I ran it I got the error message: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
Remember you sent this in reply to the original question I posted.
April 25, 2012 at 8:06 am
Your error is happening because you have some value in one of those columns that is being converted (either implicitly or explicitly) to a bigint and the source column contains data that is not able to be converted.
I can only imagine the performance of this query. As Lynn stated you have lots of RBAR code in here. Somewhere around 50 scalar functions in the select list alone. Then you have a few more scalar functions in the where clause. If you have more than 5 or 6 rows in the source table this is going to take forever to run.
I counted 5 different scalar functions
FormatInt
MakeDate
FormatFloat
FormatAgencyClientID
fn_Is_SMART_Date
Then your data is not normalized either. What are you going to do when you need to add a 4th SpecialFunding? or a 4th Special Project?
If you want some real help you are going to have to put in some serious effort. Post the ddl for the table and ALL these functions. Then post some sample data along with the desired output after running this thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2012 at 7:59 am
This may be obvious, but if you just want to make it work for now, comment out all but the first column and run the select.
Then uncomment the next column etc until you find the column causing the error.
Then identify the value(s) failing the conversion and decide what to do about it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply