String Parsing And Expression Builder....
I can't figure this one out. I don't have enough knowledge of the string functions I guess.
I need to pull a value out of a variable I setup in a for each loop. The value is the filename/path of each source file being processed. Let's say the variable that has the source file path is called VAR1.
One sort of off topic thing I've noticed is when watch the variable in bebug mode and I look at the value of VAR1 it has double back slashes. Here's an example of the value of VAR1:
"\\L3KRZR6.na.xerox.net\C$\Documents and Settings\ca051731\Desktop\Project4\DPT_20070926.ver"
How come the back slashes have been doubled? And do I need to account for that when I start parsing the string value?
Anyway, I need to grab part of the filename from VAR1 and I need the value populated at the start of the for each loop container - ideally when I capture VAR1 in the for each container. I'll be using the string in drop table, create table and create index statements before the actual Data Flow task within the overall package
In the above example I need to grab the characters before the underscore and after the last \. So I'd need the string "DPT" captured in this example.
The actual string could be 1 to 3 characters long, even though this example has it as 3 long.
Underscores could exist anywhere in the actual UNC path once this package is moved to our actual system environments so I can't key off of the underscore.
Because I can't count on the string being a fixed lenght I can't just use a positional string function and grab specific text starting/ending at specific points.
Is there a way to use the various string functions in the expression builder to grab the text between the right most underscore and the right most back slashes or something like that? Ideally I'd like to setup a new expression based packed scope variable called VAR2 and build it using string functions applied to VAR1.
View Complete Forum Thread with Replies
Related Forum Messages:
Expression Builder
Hi All, I want to build following code into expression builder . I don't know how to write that things into expression builder. so can you help me out from this problems. 1.Daily stock Inventory by stores " & FormatDateTime( date() , vbshortdate ) 2. " Weekly stores Queue Productivity for " & date()-8 3.Customer Data Entry Monthly Report for stores " & Month( now()-27) & "/" & Year(now()-27) 4."Customer of stores " & FormatDateTime( now() , vbshortdate ) & " - stock" 5.customers opens account " & FormatDateTime( now() -8, vbshortdate ) & " (STOCK1) Thanks Aric
View Replies !
SSIS-Expression Builder
am trying to do the following in SSIS. I have Execute Process Task, in which I have the values are set for Executable, Arguments and working Directory manually. I keep changing this if the Connection Manager points to Dev or when it points to QA or to Prod. Instead I want to do like this. If my Connection Manager points to Dev, the Executale , Arguments and Working Directory should automatically change. Else if it points to QA, the Executale , Arguments and Working Directory should automatically change and similarly for Prod. Is this possible in Expression Builder in Execute Process Task. If so, how?. Thank you
View Replies !
Conditions In Expression Builder
Nobody answered my previous question. So, I am putting it this way. How to write an IF condition in Expression builder. I want to do something like this. IF (variable1='ABC' then value = '.......' else variable1 ='DEF' then value = '.......' else variable1='GHI' then value = '.......' END Thank you.
View Replies !
Expression Builder Question
Hi all of you, When you're making expressions with your own variables and system variables and so on. How could I see the contents for each variable? When you have an expression like this: "Cargas de Hacienda. " + SUBSTRING( @[System:ackageName], 7,20) + " " You can perfectly click above "Evaluated value" and see the resutl but I mean, individually over each variable on the variables listview is not possible. Thanks for any input or advice, Enric
View Replies !
Bug In Report Builder When Using Expression
I ran in to a strange problem in Report Builder. I drop a few fields from my Report Model on a simple talbe report. Some of them are straight forwards attributes and some of them are expressions. Now in the table layout of a report I have say a attribute named as "Account Number". This attribute is actually a field from a table. I change the column header to a two line column header with €œAccount€? on one line and €œNumber€? on second line. No problem, piece of cake. Now tried the same thing on an expression type attribute, say "Principal Balance". When I split the column header on two line I get this following error. ============================================================== Semantic query compilation failed: e MeasureNotFound One of the SubtotalMeasures.MeasureName properties of the SemanticQuery refers to the Measure Expression 'Principal Balance', which does not exist. (SemanticQuery ''). ---------------------------- An error has occurred during report processing. ============================================================== If you read the error you will realize that the query is trying to find an expression named "Principal" (newline char) "Balance" and it fails. This expression is nothing but a sum aggregation on a field in the underlying table. If I put the "Principal Balance" back on one line report runs like a charm. Also note that exact same report in Designer no issues at all. FYI: I am in SQL Server 2005 with SP2. (tried it both on Standard and Enterprise versions.)
View Replies !
String Parsing
How to remove same repeated string in a column per row from a table? Looked at replace, stuff string functions, but none take a column name as a parameter. Help is appreciated. Thanks,
View Replies !
T-sql String Parsing
I have a series of strings (field name FullName) in a table (FullNames) that look like this: FullNames -------------- macdonald, ronald Doe, John I need to extract the first and last names. Here's my code: select substring (fullname, 1, patindex( '%,%', fullname)-1) + ' ' + substring (fullname, patindex( '%,%', fullname)+1, len(fullname) ) from FullNames I hate having to use patindex twice in the same SELECT. Is there any way around this? TIA, Barkingdog
View Replies !
SQL String Parsing
I have a string that is coming from a legacy system ###T1937###U1875###U1960###U3287###U5926###U6388###U4408###T1909###U2620###U5025###U6354###U7072###U7074###U6715###U6714###U4085###U6441###U7067###U7073###U2392###U6348###U7758###U6717###U7755###U7069###U7756###U6350###U6760###U7070###D0002###D0001###U6238###U6349###U6353###U6355###F0005###U7750###U6351###U7757### How can I convert above string to comma separted values like one below so that it can be used for IN Clause for my SQL 'T1937','U1875','U1960','U3287','U5926','U6388','U4408','T1909','U2620','U5025','U6354','U7072','U7074','U6715','U6714','U4085','U6441','U7067','U7073','U2392','U6348','U7758','U6717','U7755','U7069','U7756','U6350','U6760','U7070','D0002','D0001','U6238','U6349','U6353','U6355','F0005','U7750','U6351','U7757' Thanks in advance
View Replies !
String Parsing
I found an article about string parsing but its done using db2 http://www.ibm.com/developerworks/db2/library/techarticle/0303stolze/0303stolze1.html can anybody translate to Transact SQL specifically the example of create function elemIdx i didnt understand how he used recursion may b cuz the language is odd to me i didnt get it Thanks much
View Replies !
Expression Builder Elipse Button Does Not Appear
Hello, I am attempting to create an expression for a package variable in a SQL Server Integration Services project, but the elipse button in the expression property for the variable does not appear (Visual Studio 2005). I've tried two different screen resolutions, no luck. Why would the elipse not be there? Is there a keystroke I can use to open the exrpression builder? Thank you for your help! cdun2
View Replies !
SQL String Data Parsing
<p> Hi everybody, I was hoping to get some advice something I can't quite get my head around. I have a SQL db which contains a table with ratings using the AJAX rating control. When someone rates an object, I need to select the current rating and then use those numbers to; - calculate the new average - add new score to total score - increment number of votes by one. I thought this can be best achieved using the SELECT statement and then parsing the SELECT string. (is the string comma separated?) using each array, i'd need to convert this into integers and then do the calculation. and re-upload the data to the ratings table (using the UPDATE statement). Is this the best way of proceeding? I have tried initially to write the code using three sql statements. But that would mean to many requests from the server, right? Below is the conde I have writting already.int myrating; myrating = Rating1.CurrentRating;string getscore = "SELECT " + "RatingScore" +"FROM Rating " + "WHERE ItemID= '" + _ItemID+ "'";string getcount = "SELECT " + "RatingCount" +"FROM Rating " + "WHERE ItemID = '" + _ItemID + "'";string getaverage = "SELECT " + "RatingAverage " +"FROM Rating " +"WHERE ItemID = '" + _ItemID + "'"; int _ratingscore;int _newscore; _ratingscore = int.Parse(getscore); _newscore = _ratingscore + myrating; //add new rating score to old scoreint _ratingcount; int _newcount;_ratingcount = int.Parse(getcount); _newcount = _ratingcount + 1; //increase count by 1int _ratingaverage; int _newaverage;_ratingaverage = int.Parse(getaverage); _newaverage = _newscore / _newcount; //calculate new average rating otherwise otherwise would i be best off to do the following?... string[] dbRatings = SQLstring.Split(','); ?? Any help would be appreciated. Many thanks in advance. Phil </p>
View Replies !
Parsing Character String
I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length. Here are the function and the stored procedure wher eit occurs: CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1)) RETURNS @Results TABLE (Item nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z -- ERO FIRST TIME IN LOOP SELECT @INDEX = 1 WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(Item) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END Procedure: Code SnippetCREATE PROCEDURE [dbo].[RPTPatientAnalysis] ( @stateList CHAR(2), @employerIdList VARCHAR(4000), @payerIdList VARCHAR(4000) ) AS SELECT p.PAT_ID, p.PAT_FirstName, ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName, p.PAT_LastName, p.PAT_Gender, CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB, p.PAT_AddressStreet1, ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2, p.PAT_AddressCity, p.PAT_AddressStateProvince, p.PAT_AddressPostalCode, ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress, p.PAT_PhoneNumber, ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name, ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName, ISNULL(p.PAT_Comment,'') AS PAT_Comment, ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID, ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification, ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName, ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail, ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission, p.PAT_LastEandMCodingDateTime, p.PAT_Desceased, p.PAT_PCP_ID, p.PAT_LastUpdatedDateTime, ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType, ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing, ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal, ISNULL(p.PAT_PortalID,0) AS PAT_PortalID, ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name, ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName, pcp.PRIMCP_ID, ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID, ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName, ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname, pcp.PRIMCP_DateDeactivated, ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax, ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice, ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1, ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2, ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City, ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State, ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip, ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1, ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2, ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City, ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State, ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip, ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax, pati.PATINS_InsuranceTypeID, ISNULL(pati.PATINS_Account,'') AS PATINS_Account, ISNULL(pati.PATINS_Group,'') AS PATINS_Group, ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType, ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount, ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount, ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays, ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay, ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay, ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar, ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan, ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description, ISNULL(ip.INSP_Name,'') AS INSP_Name, ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice, ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies, CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate, ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive, CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate, CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate FROM dbo.patient AS p LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1 LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,',')) AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,',')) AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,',')) Is there a faster / more efficient way to accomplish the above? Any insight would be appreciated!!
View Replies !
Parsing Character String
I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length. Here are the function and the stored procedure where it occurs: CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1)) RETURNS @Results TABLE (Item nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL ZERO -- FIRST TIME IN LOOP SELECT @INDEX = 1 WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(Item) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END -------------------- ...and the stored procedure: CREATE PROCEDURE [dbo].[RPTPatientAnalysis] ( @stateList CHAR(2), @employerIdList VARCHAR(4000), @payerIdList VARCHAR(4000) ) AS SELECT p.PAT_ID, p.PAT_FirstName, ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName, p.PAT_LastName, p.PAT_Gender, CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB, p.PAT_AddressStreet1, ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2, p.PAT_AddressCity, p.PAT_AddressStateProvince, p.PAT_AddressPostalCode, ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress, p.PAT_PhoneNumber, ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name, ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName, ISNULL(p.PAT_Comment,'') AS PAT_Comment, ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID, ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification, ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName, ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail, ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission, p.PAT_LastEandMCodingDateTime, p.PAT_Desceased, p.PAT_PCP_ID, p.PAT_LastUpdatedDateTime, ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType, ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing, ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal, ISNULL(p.PAT_PortalID,0) AS PAT_PortalID, ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name, ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName, pcp.PRIMCP_ID, ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID, ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName, ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname, pcp.PRIMCP_DateDeactivated, ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax, ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice, ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1, ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2, ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City, ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State, ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip, ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1, ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2, ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City, ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State, ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip, ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax, pati.PATINS_InsuranceTypeID, ISNULL(pati.PATINS_Account,'') AS PATINS_Account, ISNULL(pati.PATINS_Group,'') AS PATINS_Group, ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType, ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount, ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount, ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays, ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay, ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay, ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar, ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan, ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description, ISNULL(ip.INSP_Name,'') AS INSP_Name, ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice, ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies, CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate, ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive, CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate, CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate FROM dbo.patient AS p LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1 LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,',')) AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,',')) AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,',')) Is there a faster / more efficient way to accomplish the above? Any insight would be appreciated!!
View Replies !
Quick SSIS Expression Builder Question
This works: len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate()) This doesn't: "dataware/"+ len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate()) I tried a couple of different things at this point, where am I missing the cast? Before len? Cast to what?
View Replies !
Reading File As One String, Then Parsing - How To Do This?
Hi, The suggestion to do this is buried deep in one of my posts, however I still do not have a clear idea of how to do this. I have a flat file which has several "bad rows" in it. Because file error redirection is buggy, I need a manual approach to get rid of these incomplete rows in my data file. Phil, you suggested I read the file as one long string, then parse out the bad rows (using a script?).... however I have no idea as to how to actually do this. I was wondering if it's possible to clarify the steps involved in doing this, or perhaps point me to an example I can look at, as I cannot seem to get around this problem on my own. Thanks much!!
View Replies !
Using Expression Builder To Access A Dialy File Minus One Day
Hello, I am using the following expression to access a daily file. "D:\importdata\peregrinedata\ACD_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt" output: D:importdataperegrinedataACD_DATA_2008123.txt I need to have this file by current day -1. I've tried dateadd and can't figure out how to get it to work. Thanks
View Replies !
Both Foreground And Background Color Appear To Be White In Expression Builder...
I have a co-worker who applied build 3161 to SQL Server 2005. The original problem was that the print preview was showing up as black.This is a documented issue with Microsoft here: http://support.microsoft.com/kb/935436 Now, when she goes into expression builder, the foreground AND background color for what you type in there appears to be white... so everything shows up as invisible. You can highlight the text you type and it shows up but otherwise it is obviously invisible. Anyone know how to fix this?
View Replies !
Cannot Access Expression Builder Via Variable Properties Window (no Ellipsis)
Has anyone encountered this before? My colleague opens up an SSIS package with variables, many of which use expressions built with the Expression Builder. She cannot bring up the Expression Builder by clicking the ellipsis (...) in the "Expression" Property of the Property Window, regardless of whether "EvaluateAsExpression" is set to True or False. I can open up this same package and use the Expression Builder just fine. Any ideas? Apologies if this has been answered already, I did many searches to try to find the solution before posting here. Thanks much! Brian Pulliam
View Replies !
Lots Of Individual Insert Commands Or String Parsing In Sql?
Wondering what's the preferred method for this. I've got a scenario that a user is updating some content on a page and I need to update my word catalogs for my search feature. I have some code currently to filter out words that are too small, make sure there are no duplicates and to count how many occurrences there are of each. What I'm wondering is, does it make more sense to do a loop in my code to run all the insert commands to place the new words in the database, should I try sticking them together in one string and parse them when they get up there or is there a better method someone can suggest?
View Replies !
Enabling Expression Builder For Custom SSIS DataFlow Source Component
Hi, I have implemented a custom source component that can be used as the data source in the Data Flow task. I have also created a custom UI for this component by using the IDtsComponentUI . But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder. I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI. My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at. Thank you, Jameel.
View Replies !
Evaluate A String Expression
Hi, I have a table which has expression as a column and the data looks similar to the following . ID (int) Expression (nvarchar data type) 1 8*(1/2)-6 2 278*(1/4)-2 3 81*(3/5) +4 I now have the expression as an nvarchar. (Ex: 8*(1/2)-6) Now I need to evaluate this expression and output the result (which is -2 in above Ex) .Can someone give me idea on how do I evaluate the expression which is available as a string. I need to write a procedure for this. I am unable to cast/convert nvarchar to float/int Any sample code would be greatly appreciated. This a very urgent requirement for me.Please get back Thanks Swapna
View Replies !
Expression In Connection String
Hi, I use expressions in connection strings ..... ="Data Source=" & Parameters!resultServer.Value & ";Initial Catalog=" & Parameters!database.Value Problem is when i change the parameter server, before i am able to change the parameter database, the report errors out. How can i enforce the report server to wait till i am able to give it valid values for both server and database. Thanks.
View Replies !
String Formatting Expression Problem
I have what should be a simple string formatting issue -- removing all alpha characters from a phone number. T-SQL below is simple and works just fine: declare @home_phone as char(14); set @home_phone = '(123)979-3206'; set @home_phone = REPLACE(@home_phone,'-',''); set @home_phone = REPLACE(@home_phone,'(',''); set @home_phone = REPLACE(@home_phone,')',''); The condensed version below works equally well: set @home_phone = REPLACE((REPLACE((REPLACE(@home_phone,'(','')),')','')),'-',''); Either script above returns correct result: select @home_phone; Result = '1239793206' HOWEVER, within SSIS Derived Column expressions, this function fails to remove parentheses. First of all, Expression Builder doesn't like outer single quotes (turns red) which I resolved by using double quotes resulting in the following expression: REPLACE(home_phone,"-","") + REPLACE(home_phone,"'('","") + REPLACE(home_phone,"')'","") Unfortunately, this expression fails to remove parentheses and instead returns: '(123)9793206' I've tried several different permutations--no inner single quotes; adding more inner single quotes; etc., but so far no success. Any suggestions would be much appreciated!
View Replies !
Expression In DataSource Conenction String
I'm not exactly sure how to phrase this problem. If I'm going about this the wrong way, any pointers would be appreciated. I am using an expression for a data source connection so I can decide which server/database to use at runtime. I also need to pass credentials to use for that connection. The only way I'm able to get this to work is to specify "No Credentials" for the data source and then pass a DataSourceCredentials to the report throught the report viewer. This works, for the most part, but puts a "Change Credentials" link in the report viewer. What I think would be much better is if I could specify the credentials in the connection string that I pass. However, everything I've tried so far has not worked. I get errors complaining that the compination of options is invalid or that the report server cannot find the credentials of "unknown keyword: User Name". All by trying various combinations of things. How do other people go about this? I've seen blogs implying that this is not such a big deal (that credentials CAN be passed over in the connection string). Hopefully I'm missing something simple here. If I have no choice, the "Change Credentials" link will not be the end of the world, but that's very sloppy. The people viewing this report will not know anything about the database credentials and I'd rather not show them somthing as inviting as a link that can get them off track. Thanks for your help! -C
View Replies !
Converting A String Expression To Proper Case
How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :) Just a couple of typical examples of how the data should appear ~ payment, credit card ==> Payment, Credit Card butcher & singer ==> Butcher & Singer
View Replies !
Filters && Expression With String Values, Should They Work??
Any idea why this expression would not work in SSRS? Based on a expression on a group textbox I get 0 records that match: =Iif(Fields!ACCRUAL_CODE.Value <> "A", CountDistinct(Fields!LOAN_NBR.Value), 0) I've evaluated in the proc & there should be a count of 29 records. I found a work around by using this in the stored proc & I get my record count: ACCRUAL_CODE = CASE WHEN BASE.ACCRUAL_CODE = 'A' THEN 0 ELSE 1 END and changed the expression in SSRS to this & it works: =sum(Fields!ACCRUAL_CODE.Value) Is this a known issue with strings? From searching on this site I see that string evaluations are widely used so I do not see why it wouldn't work. I've also see this when filtering the dataset on anything that is a character. Any suggestions?
View Replies !
Problem With Expression Based Connection String
We're using RS2005 and have a report that we want to be able to dynamically select what database server we use at runtime. For this report we are using private OLE DB datasources to connect to Sybase ASA 9 databases. I have created a parameter for the database server name and modified the datasource connection string ="Provider=ASAProv.90;Data Source=" & Parameters!db.Value This works fine in the IDE but when we publish to the server we get the following error: An error has occurred during report processing. Cannot create a connection to data source 'rpASA_02'. Database server not found I can create expression based connection strings against MSSQL and everything works fine on both the IDE and RS server. Why doesn't this work with OLEDB? Am I doing something wrong or is this an MS bug? tia -bruce
View Replies !
Expression Based Connection String && Stored Procedures
I have been looking around on this forum and on other sources on the web about expression based connection strings in rs2005. From my own trials and from what others have reported it seems that you can not use expression based connection strings with stored procedures. Can anyone confirm that rs2005 does not have the ability to do this? Or alternatively has anybody been able to successfully use an expression based connection string with a stored procedure?
View Replies !
Frror: 4000 Max String Literal In Expression For Datareader Component
Hi, I have a datareader component of which i am dynamically setting its sqlcommand statement with expression (click the background of dataflow > properties > expressions). Now my sql select statement has about 600 fields so that makes my expression statment "select field1, field2, .....from table1 where field2 >=" + @[User::dateforfield2] but when i evalute the expresssion (which is right), i get the error: A string literal in the expression exceeds the maximum allowed length of 4000 character and i think its because of the fields in my select statment causing my string literal to grow more than 4000 characters. Is there any way to increase the max string literal for expressions. Please help.
View Replies !
How To Find Terms In A File Name: Regular Expression OR A String Function
hi, i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files. the file are named as: ff_inbound_20071008_1.csv ff_inbound_20071008_2.csv where for file ff_inbound_20071008_1.csv: ff => flat file inbound => dataset of this csv 20071008 => date 1=> file count having in mind that they are store in the variable as a full path: z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv i need to extract the dataset and the date for each file. how can i extract these terms from the file name? many thanks, nicolas
View Replies !
How To Convert A Returning String From Custom Code To A Function In Expression?
I have a custom code function that return string like: "SUM(Fields(Parameters!dept.Value + ""_1HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_2HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_3HeadCount"").Value) " How do I convert this string to a function inside the expression (in this case "SUM", "Fields", "Parameters!", etc.)? The expression of my TextBox show the string as a string instead of converting them to proper function and display the corrent value. The reason I need to build the string is because the Fields number is dynamic and I need a loop to increase the number in the field name. Any suggest is highly appreciated! Thanks, Tabbey
View Replies !
URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 "C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls" Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls as connectionString works However - I'm trying to deploy the package - and trying to use expression: @[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls" which causes the same error to occur (Same error with other Excel source also: Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 )
View Replies !
Report Wizard Doesn't Take A Simple Query In The Query String - Query Works In Query Builder When I Supply The Parameters
The following query in the query string: execute p_rpt_cli_v_index_reg_adj_exp_by_bkt2 @as_of_date='06/06/2007', @client_type=3, @index_as_of_date='05/31/2007' produces following error: There is an error in the query, invalid object name '#CLI_1', Invalid object name '#index'. When I open up the query Builder, and provide the same query and run, it asks for those 3 parameters values and after I provide those parameters, the query runs, but, clicking on Ok, produces same error as above in the 'Microsoft Report Designer' information window. The above query works in the Query Analyzer fine. What's so different in the Reporting Services env?
View Replies !
&&"Failed To Load Expression Host Assembly. Details: StrongName Cannot Have An Empty String For The Assembly Name. &&"
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process." First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report: "Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name." Please help.
View Replies !
Xml Parsing
DECLARE @idoc int DECLARE @doc1 varchar(8000) DECLARE @doc2 varchar(8000) SELECT @doc1 = SUBSTRING(name, ( 0*8000 ) + 1, 8000 ) ,--from aaaa where bbbb = 'dhsghg' @doc2 = SUBSTRING(name, ( 1*8000 ) + 1, 8000 ) from aaaa where bbbb = 'dhsghg' EXEC sp_xml_preparedocument @idoc OUTPUT, @doc1 /* Step1 */ SELECT * FROM OPENXML (@idoc, '/aaaa/bbbb/cccc/dddd/eee',1) With ([name] varchar(50) '../@name', number varchar(50) '../@number') EXEC sp_xml_preparedocument @idoc OUTPUT, @doc2 /* Step2 */ SELECT * FROM OPENXML (@idoc, '/aaaa/bbbb/cccc/dddd/eee',1) With ([name] varchar(50) '../@name', number varchar(50) '../@number') I am parsing an xml file which is having more than 8000 characters. So I am selecting the file into two variables of varchar datatype each with 8kb size. And doing the abobe two steps,step1 and step2. How can i combine these two steps into a single step. I am expecting something like following. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc1+@doc2 Thanks.
View Replies !
Parsing
What is parsing?? can someone give me an example please?? this what I got from BOL Returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name.
View Replies !
Parsing
I need to figure out how to parse a comma separated value. Lets say I have a variable equal to a comma separated list. SET @Variable = '045, 032, 025, 653' I need to create a dynamic sql string to look like: SET @Variable = ''' + '045' + ''' + ',' + ''' + '032' + ''' + ',' etc... Can someone teach me a optimized query to do this? Thanks Greg
View Replies !
XML Parsing
I am trying to process an XML document that contains the attribute 'from_x'. However an openxml query can't seem to find any column with a '_x' suffix. For example if I were to execute the following fragment: declare @hDoc int, @Message varchar(200) select @Message = '<BACK_FM from_x="12"></BACK_FM>' exec sp_xml_preparedocument @hDoc OUTPUT, @Message select from_x from openxml(@hDoc, 'BACK_FM',1) with (from_x int) I get back a null value from the openxml query. Attribute names 'fromx' and 'from_y' work ok but nothing I have tried with a trailing '_x' will work. Does anyone know if this is a known SQL Server bug? Is it a bug at all or something about XML that I don't know about? Thanks, Wayne King
View Replies !
Help With Parsing
Hi - I am new to SQL server and was wondering if someone can help me with this one. Thanks My table holds 2 columns (SECTOR and TERM) with following example values SECTOR TERM Hybrid 6/18 Hybrid 9/19 Hybrid 10/17 Hybrid 3/13 I would like to find out the rows where my values from SECTOR before '/' does not equal TERM i.e. Row 1 where 6<>8 and row 3 where 10<>7 Thanks.
View Replies !
Report Builder: How To Provide Parameter In The Report Generated Report Builder
Hi All, I have used Report Builder in one of the project for client. Is it possible to provide parameter in the report that is generated by Report Builder? It's quite the same with the parameter in usual Report/RDL (by Visual Studio). And is it possible to pass the UserID that is running the report to the Data Source View or directly to the query? Currently, I need to get the UserID of the user that runs the report to do some filtering of the data (in SQL Query). I can do this easily in the RDL file developed by Visual Studio, which I can use global parameter User!UserID and pass to query. I find difficulty to use SQL Execution Context (select USER_NAME(),SUSER_NAME()), because report needs to be run as certain account, so that the report can be subscribed by user (e.g. email subscription). How to pass this UserID to the query/data without end-user intervention in Report Builder? Thanks.
View Replies !
Report Builder: How Can I Append Two Table Fields To Report In Report Builder
Hi, When i select datasource in Report Builder, i am able to see all the available DataSources. Eg: I have selected one datasource from the list and which has 3 tables(table1, table2, table3) associated to that datasource. when i drag and drop table1 fields to report, i am not able to see the other 2 tables(table2 & table3) Is there any property or relationship do i need to maintain? Thanks, SR.
View Replies !
Parsing A Sql Field
Can you parse a SQL field? Let's say, FULLNAME field got a TEXT datatype with the following data: <firstname>Norm</firstname><lastname>bercasio</lastname><Color>blue</color>then using a select statement, parse the field to find the lastname then write it to another field called LASTNAME on the same table, same rowID. Can you send a select statement how it can be done? I am using SQL 2003 or 2005. thank you so much.
View Replies !
Need Help Parsing Out Info
Hi All, I'm using a SQL selection to fill a DataGrid. One of the fields I have is called diagnosis. This field in the database can contain multiple diagnosis. But I use a set of characters to divide each diagnosis. Example : Sick!@#$%Hurt!@#$%Ill!@#$% My problem is this is how it looks in my Data Grid. Can someone tell me how to parse out each diagnosis. Thanks
View Replies !
Formula Parsing
Hi,I have three tables in the following structure (simplified):Table 1: Containing the customers-------------------------------------------------create table Customers([cusID] int identity(1, 1) not null,[cusName] varchar(25) not null)Table 2: Containing the customer data fields---------------------------------------------------------------create table Data([datID] int identity(1, 1) not null,[datName] varchar(25) not null,[datFormula] varchar(1500))Table 3: Containing the customer data values-----------------------------------------------------------------create table Values([cusID] int not null,[datID] int not null,[valValue] sql_variant)In this structure the user can add as many data fields to a customer ashe wants (e.g. Country, City, Email, Phone, ...). I have added triggerswhich create a view similar to a pivot (I am working in SQL 2000) andadd triggers to the view so it is insertable, deletable and updateable.What I would like to do, is allow the user to create new fields wherethe values are based upon a calculation. This calculation would be donethrough a formula similar to what he would do e.g. in excel (thisformula is stored in the dimFormula field then).An example might help. Let's assume the user created a field 'Sales'(containing last year's sales) and 'Invoices' (containing the number ofinvoices that were created for him last year). Now, he wants to createa field 'AvgSales' with the formula '[Sales]/[Invoices]'.(Note that through adding these data fields, the above view was created(let's assume it is called vw_Customers and contains the columns [ID],[Name], [Sales], [Invoices], [AvgSales]).What I am looking for is a function which can parse this formula into at_sql query which runs the calculation. So, the formula'[Sales]/[Invoices]' would be translated into (let's assume there areno records with NULL or zero invoices):update vw_Customersset [AvgSales] = [Sales]/[Invoices]from vw_CustomersI am able to do the above with simple calculations (where you can evenuse sql functions e.g. year, len, ...). Now I would like to take thisone step forward into the possibility of using functions with morevariables.For example. Let's assume, the user wants to add a rating (field called'Rating') to his customers based upon the result of 'AvgSales. Heenters the formula 'if([AvgSales] > 2500, 'A', 'B')'.If anyone could help me on this, I would be very grateful. Thanks.M
View Replies !
|
TRACKER
