I am trying to achieve loading a fact table using my stage and dimesion data , I was planning to use a Lookup transformation to do this, however I am supposed to use a substring task to because the data in the stage table is in adifferent format from that in the dimensions, how do I incorporate this substring task within the data flow, any help is appreciated.
it's early in the morning (well it is here anyway) and i need a shot of logic...
in the data base i'm currently working with i have a varchar field that holds either a number or a collection of numbers in square brackets. I need to extract the number or the first number in square brackets if it's a list. i know it's gonna be a simple one but my head just won't do it?? i'm trying with substring and charindex to determine the position of the '[' but just not getting it this morning
quick sample of what the data in this column may look like...
declare @t table (col varchar(30)) insert into @t select '2' union all select ' ' union all select '  '
so i need to get...
col ------- 2 5 12
EDIT- OK, so i get this to work but only if there is actually square brackets
declare @t table (col varchar(30)) insert into @t --select '2'union all select ' 'union all select '  '
select col ,substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1) from @t
I have a SQL query in Visual Studio (SSRS). I have a GL Account field that is formatted such as 100-400-123-1234. I wanted to use the substring function to pull out the second set of numbers which I can assign a location:
CASE WHEN substring(GlAccount,5,3)= '400' THEN 'Gainesville' CASE WHEN substring(GlAccount,5,3)= '401' THEN 'Aledo'
I tried this and it comes back with syntax errors. Can anyone tell me how to approach this as I have a list of about 35 locations that I need to do like this. Thanks.
I have a problem in Substring function. There is a Text data type field in my Table and it has large no of characters. I want to get the whole text as the result. The problem is its just giving me 300 characters from a row, as the result.
I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?
I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', â€¦)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 containt approx. 6000 row and Table2 containt only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".
When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.
When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1 like in the following query : select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', â€¦)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905'))) the query take an execution time of ~15 secondes!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.
Itâ€™s weird because, Sql Express 2005 is supposed to be more performant than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!
I'm trying to create a function that splits up a column by spaces, andI thought creating a function that finds the spaces with CHARINDEX andthen SUBSTRING on those values would an approach. I get an errorsaying that the I have an Invalid column 'Course_Number'. Not surewhy but I am very new to User Defined Functions. Here is what I haveso far:CREATE FUNCTION CourseEvalBreakdown(@fskey int)RETURNS @CourseTable TABLE(Col CHAR(2),Area CHAR(4),Number CHAR(4),Section CHAR(4),Term CHAR(3))ASBEGINDECLARE@Ind1 tinyint,@Ind2 tinyint,@Rows intDECLARE @crstbl TABLE (FStaffKey int,Course_Number char(20),Term char(3),Col char(2),Area char(4),Number char(4),Section char(3))INSERT INTO @crstbl (FStaffKey, Course_Number, Term)SELECT FStaffKey, Course_Number, TermFROM EvalWHERE FStaffKey = @fskeySET @Rows = @@rowcountWHILE @Rows 0BEGINSET @Ind1 = CHARINDEX(' ', Course_Number, 4)SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',Course_Number, 4)+1))UPDATE @crstblSET Col = SUBSTRING(Course_Number, 1, 2)WHERE FStaffKey = @fskeyUPDATE @crstblSET Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))WHERE FStaffKey = @fskeyUPDATE @crstblSET Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, (@Ind2-@Ind1)-1))WHERE FStaffKey = @fskeyUPDATE @crstblSET Section = SUBSTRING(Course_Number, @Ind2+1, 3)WHERE FStaffKey = @fskeyENDINSERT @CourseTableSELECT Col, Area, Number, Section, Term FROM @crstblRETURNENDGO
Hi, I am new at sql 2000 and 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. Now it is giving me this error, €œSQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. €œ Through research I have no clue as what I need to do, or where to look. The package runs without error when I execute the package itself. Any help is greatly appreciated. Thanks, Lori
I am using a simple procedure to pivot results (found in another forum and adapted it). It is done on SQL Server 2005 server with all service packs. Procedure: ************** ALTER Procedure [dbo].[EthnicityPivot] @StDate as Datetime, @EndDate as Datetime as begin DECLARE @Teams varchar(2000)
truncate table ForEthnicPivot
INSERT INTO ForEthnicPivot SELECT DISTINCT COUNT(ID), Team, Ethnicity FROM dbo._EthnicityByTeamEpisode where Startdate between @StDate and @EndDate GROUP BY Ethnicity, Team
SET @Teams = '' --// Get a list of the pivot columns that are important to you.
SELECT @Teams = @Teams + '[' + Team + '],' FROM (SELECT Distinct Team FROM ForEthnicPivot) Team --// Remove the trailing comma
SET @Teams = LEFT(@Teams, LEN(@Teams)-1) --// Now execute the Select with the PIVOT and dynamically add the list --// of dates for the columns EXEC( 'SELECT * FROM ForEthnicPivot PIVOT (SUM(countID) FOR Team IN (' + @Teams + ')) AS X' ) end ************
I can call the function: exec EthnicityPivot '01/01/2007','09/09/2007'
and it works fine in SQL analyzer, but when I want to use it in Visual Studio in a new report I am getting this error message:
There is an error in the query. Invalid length parameter passed to the SUBSTRING function. Incorrect syntax near ')'.
Hi i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose  it works, i dont use  in the code :) colums: VLF_InfectionDestination is nvarchar 254
SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number FROM tb_AVVirusLog INNER JOIN __CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (__CustomerMachines002.folder_id = @folderId) [OR (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')] GROUP BY tb_AVVirusLog.VLF_VirusName HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie')) ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESC
An application I developed normally works great, but it seems that when processing a certian record (and none of the others so far), SQL Server throws this error: "Invalid length parameter passed to the substring function."
I am running into trouble performing a second join on a column while using aggregate functions over that table.
What I think is happening is that the aggregate values (sum, avg, count) are being multiplied because I am joining on the same table a second time, but I don't know how to fix or avoid this problem.
Here's the query:
SELECT CAST(ro.xing_tc_no AS INT) AS loc_id, SUM(ro.total_fatal) AS tot_fatal, SUM(ro.total_serious) AS tot_serious, SUM(ro.total_minor) AS tot_minor, COUNT(ro.report_no) AS tot_accidents, AVG(ro.NO_OCCUPANTS) AS num_occupants
FROM UserHotspots uh
LEFT JOIN rods_k_crossing AS rkc ON rkc.location_id = uh.CrossingID
LEFT JOIN rods_occurrence AS ro ON rkc.tc_location_nbr = ro.xing_tc_no
GROUP BY ro.xing_tc_no
The second join is also performed on rods_k_crossing and that is when the sums, avgs, and counts get multiplied. If I take that second join out the numbers come out fine.
I want to join a table valued function but function parameter should left joined table's primary key .... this is posible in oracle by pipeline method .. eg.. SELECT A.Col1,A.Col2,B.Col1,B.Col2 FROM Tab As A LEFT OUTER JOIN TblFunction(A.Pkey) B ON A.Col1 = B.Col1
A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:
Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2 where dato > '2006-08-11' Group By DatePart(dw,dato)
Trying to join two or more tables in all sorts of variants of this:
Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1], datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2] FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato) Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11' Group By datePart(dw,a1.dato), datePart(dw,a2.dato)
Hi there. I've hit some gap in my SQL fundementals. I'm playing with table-valued functions but I can't figure out how to join those results with another table. I found another way to hit my immediate need with a scalar function, but ultimately I'm going to need to use some approach like this. What am I misunderstanding here?
The Given Objects: function Split(stringToSplit, delimiter) returns table (column: token) table Words (column: Words.word) -- table of predefined words table Sentences (column: Sentences.sentence) -- table of sentences; tokens may not be in Words table, etc
The Problems: 1) how do I query a set of Sentences and their Tokens? (using Split) 2) how do I join tables Sentences and Words using the Split function?
The Attempts: A) select word, sentence, token from Words, Sentences, dbo.Split(sentence, ' ') -- implicitly joins Split result with Sentences? where word = token
resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."
B) select word, sentence from Words, Sentences where word in (select token from dbo.Split(sentence, ' ')) -- correlated subquery?
resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."