Diving The Text Into Multiple Col Values
Feb 1, 2008
Hi,
I have a fulladdress column which holds the complete address with commas.
i want to divide that into add1,add2,add3.....
For eg: FullAddress is Primrose Cottage, Featherbed Lane, Wilmcote, Stratford-Upon-Avon, Warwickshire, CV37 0ER
Then
Add1 should be Primrose Cottage
Add2 should be Featherbed Lane
Add3 should be Wilmcote
Add4 should be Stratford-Upon-Avon
Please let me know the solution for this
Thanks in advance
View 5 Replies
ADVERTISEMENT
May 30, 2007
I'd like to create a report with the folloiwng format:
DATE1 DATE2 DATE3 DATE4 DATE5 [fixed 5 dates across the top, from today to T+5]
THING1 x x x x
THING2 x x x x
THING3 x x x x
THING4 x x x x
my raw data looks like this:
THING1, DATE1, TEXT VALUE 1
THING1, DATE2, TEXT VALUE 2
&c&c.
Now: there may be 0, 1 or several (by which I mean 2-5 max) text values to display at each intersection. If there are zero I'd like it to be blank, if there are one or several, i'd like to display them in a little list within the cell.
Is this possible?
View 1 Replies
View Related
Oct 9, 2015
I am using SSRS 2008 R2 Report Builder 3.0 (10.50.4276.0) . I have simple set of data which has a persons Title and Name e.g. Mr Smith, Miss Jones, Doctor Foster
The report has a parameter where the user can select which records to show based on the matching titles (Mr, Miss, Doctor)
The Query for the report uses Title in (@Title) where @Title is the only parameter which can take multiple values. The report works correctly for any 1 value selected, but as soon as 2 or more values are ticked in the drop down, it fails.
I believe the parameter value is being passed into the query with a comma separating the values e.g. Mr,Miss which causes the IN statement to give an error, as the statement would be where Title IN ('Mr,Miss') which does not match any of the data values.
The parameter value passed needs to be 'Mr','Miss' for the IN statement to work. What statement do I have to put in the report query to get it to select any of the data rows where the title matches any 1 of the selected values?
View 5 Replies
View Related
Feb 23, 2007
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
Sep 7, 2007
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
View 2 Replies
View Related
Aug 15, 2007
Hi
I'm using SSIS to export views & tables in SQL 2005 to csv files. My problem is that when I select that I want to use " as a text qualifier, it also uses them to surround non-text fields such as time/date and integer fields.
In SQL 2000 I used DTS packages and they handled the data without any issues.
Can anyone help?
Ta
Phil
View 3 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Jun 6, 2007
If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?
View 2 Replies
View Related
Sep 1, 2007
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
View 3 Replies
View Related
Mar 29, 2007
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
View 1 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Jan 25, 2008
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
View 7 Replies
View Related
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
Feb 18, 2007
Hi,
I have a parameter that get his available values from a query.
I want that user will have the option to select from the values list OR to type in his own value.
Is there a way to do that?
Thanks,
View 3 Replies
View Related
Jul 25, 2006
Hello,
How can I get all the dataset value in textbox.
e.g in dataset I jave field call "CustomerName".
I would like to get in the textbox all the cutomer name seperated by ",".
Is the same as I can use join(Parameters!CustomersName.Value,",") but I need to do that from the data set and not from the parameters since I don't have parameters for my customer name
Thanks
View 4 Replies
View Related
Oct 10, 2007
Hi all,
I have an existing database, which is used to store complaints data and have one field, which stores notes in a varchar(8000) column.
My users are now wanting to store information, which exceed the number of available characters. What options do I have in facilitating this?
Many thanks
View 5 Replies
View Related
Jan 15, 2007
Hi everybody,
I need to replicate text values from MS SQL Server 2005 to Sybase ASE 12.5.3 using replication of SQL Server 2005. The connection to the Sybase server is opened through a linked server using 'Sybase ASE OLE DB Provider' version 2.70.00.24.
As Sybase ASE 12.5.3 doesn't support parameter of type text for stored procedures I used INSERT-, UPDATE- and DELETE-statements for 'statement delivery' in arcticle properties.
This works fine as long as the text attributes contains less then 16 K characters. Any longer text is truncated after 16384 characters.
The OLE DB connection itself is configured to use 1 MB for 'DefaultLength for Long Data'. So the provider should transfer more then 16384 characters. And direct update through the 'linked server' prove that.
Did anybody else recognize this limitation ?
Is there a workaround which allows to replicate more then 16384 characteres ?
Wolfgang Kunk
View 6 Replies
View Related
Apr 11, 2006
When loading a table in a data flow from a text file that contains non-null float values, I am seeing erratic and inconsistent results. I am presently using SQL Server Destination in a data flow.
- With low volumnes of data, less that 50,000 rows, no problems
- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package. If I run is directly (right-click and click on Execute), I get the expected result.
But if I use SQL Server Agent to run the package, half of the values are lost and nulls are loaded instead. I have inspected the into text file and there are few rows with null for the column.
Any help would be appreciated!
Greg
View 1 Replies
View Related
Jan 18, 2002
PO table has
itemnum - over 16000 records
closedate - can be 2001, 2002
location - is 1, 2, 3 etc
Like to find itemnum where location is 1 and itemnum has closedate of 2001, if it does have close date of 2001 than check if another record of same item with closedate of 2002. If yes, than diplay that item.
The result would be itemnum which have closedate of 2001 as well as 2002. Each item may have more than one record for each year.
View 1 Replies
View Related
Jun 17, 2008
Hi.
I;m trying to sort out some values.
Ok so for sort. We have a contract and a renewal column.
A contract can have many renewals.
I'm trying to get the latest renewal for each contract (latest renewal is the one with the higher number.
so im trying this..
select distinct c.policyno,c.renewalno
from CONTRACT AS c inner join multicontract as d ON c.multicontractID = d.ID
where
c.renewalno in (select max(crn.renewalno) from contract crn
where
c.id= crn.id
But i keep getting multiple results (p.e. contra 12 ,renewal 13,14,15 and i want contract 12, renewal 15)
Any help?
View 16 Replies
View Related
May 27, 2014
I am working on SSIS Packages. I have raw files in which data is in text qualifier.
When I am trying to load files into database it's throwing me error when there is no values in text qualifier.
For Example. "Others","0","0"," "," "
If I have the value above in raw files. SSIS Package throwing me error in " " this column.
Saying Data Conversion failed.
View 5 Replies
View Related
Jul 20, 2005
Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -
View 3 Replies
View Related
Oct 25, 2007
hi everyone
how do i insert multiple rows in a database ?
i came up with something like this after googling but it does not work
INSERT INTO tblSold (LID, BuyerID,Date)select ('759','2106','2441') UNION ALLselect ('0','0','0') UNION ALLselect ('10/25/2007','10/25/2007','10/25/2007')
View 8 Replies
View Related
Jan 22, 2008
Hi there
I have an exel spreadsheet with a very long list of towns. How can I import/insert that into my "Towns" table in sql express? I can't seem to find any way to import it and I'm not sure how to do multiple inserts.
Thanks
View 1 Replies
View Related
Jun 2, 2004
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
Thanks in advance for your comments.
View 2 Replies
View Related
Jan 5, 2000
i have about 2,000 record and i need to insert them in my table.
how do i insert these informations using this syntax??
au_id au_name au_fname
1003 vivian latin
1005 cecy mani
1004 bili david
insert into autors (au_id, au_name,au_fname)
Values ('1101', 'Rabit','jesicca')
thanks for your time and help.
View 1 Replies
View Related
May 23, 2006
Hi,
The values I need to store in the table are
Student ID
Student Name
Subjects
The "Student ID" is the primary key.
A student can take more than 1 subject.
For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math
How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?
My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.
Or I can create a new field called "RowNumber" and keep that the primary key..
For example:
Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math
If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?
View 5 Replies
View Related
Mar 19, 2007
hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...so far i created a table 'UserAccount' with column names as UserName as varchar(50)Password as varchar(50)EmailID as varchar(100)DateOfJoining as datetimeUserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..so now every user must have a list of other userid's.. as contact list..Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..THANK YOU !if it helps..iam using sql server express edition..and iam accessing using asp.net/C#
View 1 Replies
View Related
Apr 22, 2008
I have a table say #temp1 with coulmn Id and Description
and I do have another table #temp2 with column id and Projectdescription
but in #temp2 there could be more then one value against one id
like the data in #temp2 is look like
id Projectdescription
1 Computer project
1 update in computer project
1 another update in computer
2 Physics project
2 another update
but #temp1 has only one accurance of id
and data initially looks like
id Description
1 NUll
2 NUll
and I would like to update this table description from #temp2 Projectdescription column so that
data in #temp1 table look like after update
id Description
1 Computer project,update in computer project, another update in computer
2 Physics project, another update
I mean I would like to have concatination form of Projectdescription in description column against specific ID
I can achieve this by using UDF but i dont want to use that I just want to do it by update statement not even by using cursor
View 8 Replies
View Related
May 24, 2008
Hello,
I have a web form with a check box list with 5 values. Each of them is an int value, and the user can select multiple values from the check box list. For example:
Status:
1) Single
2) Married
3) In Relationship
4) Divorced
5) Any
These values correspond to a column called "RelationshipStatus" which holds an int value of NULL or 0-5.
So the value passed to my SQL query would be any combination of 1, 2, 3, 4, or 5 (Which denote any of the above). It could look like this:
@Status = '132'
Now my question is how would I do a select statement that finds any row with one of those value (Any row with 1, or 3, or 2)? If there is a '5' in the varible then the select statement should return any row regardless of the value.
Table Name: "USER_TABLE"
Column Name: "Relationship_Status"
Value Type: INT
Thank you experts!
View 1 Replies
View Related
Apr 14, 2014
this is my data
AM1WSJZ1241
AM1WLSU7162
AM1SXBI5100
AM1TWXX0477
AM1MSMQ6167
AM1WRQP1810
AM1HNME1411
i want a query to insert a data in table
View 1 Replies
View Related
