Multiple Options Into One SQL Field
Apr 13, 2007
Morning all,
I'm writing an Insert form which will write records to a few tables. What I want to know is how do I write multiple answers to one question in different rows in the table but keeping the ID?
For example.
The form has the following fields:
HotelID
HotelFacilities (CheckBoxList)
Now each hotel (in this case) will only have one ID but more than one HotelFacility .
How do I get my table to read...
HotelID
HotelFacility
1
Bar
1
Restaurant
1
Cafe
1
Wi-Fi Access
I presume
INSERT INTO tblHotelFacilities(HotelID, HotelFacility)
VALUES(@HotelID, @HotelFacility)
won't write more than one selected facility?
Thanks,
Brett
View 5 Replies
ADVERTISEMENT
Apr 22, 2008
I need to create a series of reports, which will have multiple parameters. I will have 2 parameters, which I need to make available for both to be used, none to be used or one or the other.
I can achive this in the 'data' area of VS2005, the parameters are listed in the 'layout' area, however I can not get the report to run with out selecting both the parameters in the 'preview' area.
I think it may be to do with the Allow Null check box but I can not get it to work! with data type string.
He is my where clause, which works: -
WHERE (NewMainintro = @Intro) AND (NewStatus = @Status)
OR
(@Intro = '') AND (@Status = '')
OR
(NewMainintro = @Intro) AND (@Status = '')
OR
(NewStatus = @Status) AND (@Intro = '')
Any help would be appreciated.
Thanks
View 1 Replies
View Related
Mar 3, 2008
Hi,
The variable @Item can contain multiple values separated by a comma.
e.g. ItemA, ItemB
But the STORED PROCEDURE query sees it as one string instead of 2 separate ones.
How do I solve this?
Thanks.
Code SnippetWHILE(@StartDate < @EndDate)
BEGIN
IF LEFT(DATENAME(dw,@StartDate),3) NOT IN ('SAT','SUN')
INSERT INTO @temp(myDate) VALUES(@StartDate);
SET @StartDate = DATEADD(d,1,@StartDate);
END
Code SnippetSELECT LEFT(DATENAME(dw, te.myDate), 3) + ' ' + CONVERT(char(11), te.myDate, 106) AS 'NewsDate'
FROM @temp AS te
LEFT OUTER JOIN
NewsItemTable AS t
ON t.NewsDate = te.myDate
AND t.Item IN(@Item)
WHERE t.NewsDate IS NULL
View 1 Replies
View Related
Apr 13, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I have a question about how to have multiple chart options for a report? Like when I view a report, I want to see the report in different charts formats. It is possible in SQL Server 2005 Reporting Services (designer) to change from different chart options? Hope my question is clear for your advices.
Thank you very much in advance for your help and advices. And I am looking forward to hearing from you.
With best regards,
Yours sincerely,
View 7 Replies
View Related
Jul 20, 2005
I need to set multiple values for some SQL statements, for exampleSET NUMERIC_ROUNDABORT OFFGOSETANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ONGOin a .sql file, but would like to reset them to their previous settingsagain for other SQL statements. I didn't find anything in books online. Ithought just calling "set" was enough, but apparently not ....Thanks.
View 3 Replies
View Related
Nov 1, 2007
I inherited a system and am moving our SQL2000 input data into a new 2005 database for web searching by clients. The conversion went pretty well, but I'm looking for suggestions on how best to index and query the data.
Details: We have almost 500,000 records in a pretty wide history table, and will be adding more records daily. The original table design has numerous sets of dates(first/last of different types), some small fields, and one large text field that is used for full-text catalog. The input dates are all stored as datetimes set to midnight by an insert trigger.
...
FirstPub as datetime
LastPub as datetime
FirstSale as datetime
LastSale as datetime
Name as varchar(50)
Addr as varchar(50)
DetailText as text....
I need to create a new asp.net page that can accept any specific date, or a range of dates (first/last publish date or range, first/last sale date or range) along with any of the optional 10 text fields. All input is optional, and any combination of fields can be entered. Result set has to include the primary key, key dates, and the large text field.
I've read many posts and articles, but find many different opinions about what works best. I found a similar solution that used a series of case statements with text operators indicating if the date was input or not. Other guys say no - just pass the dates as null without any additional control fields.
I found solutions saying it's best to pass dates as strings, and others that say to use datetime everywhere and just format the presentation. Some say convert dates to formatted dates and then compare them. Others say that causes table scans and should be avoided.
Questions:
1) What is the best way to get a compare dates that are entered through an asp.net page and stored procedures?
2) How to query either a specific date, as well as a range between the first and last date specified? Can it be done efficiently without needing additional screen fields for the range specification? If tried checking nulls, but am having trouble selecting multiple sets of date ranges and/or specific dates with one proc. There is always the old method of storing the selected keys in temp table, and then returning the distinct rows. Most of my experience has been in SQL 7.0 and some 2000, so I'm wondering if there are new ways to tackle the problem.
3) Better to create an index for each date type that might be selected, or one index that has all the main dates in one place?
4) Should we split the large text field to another table in our search database? It would seem to make sense to split them as we add records from the input system. Two tables linked with the same primary key seems like a good improvement from original table design. How would that affect the performance of returning all selected rows and including the large text field?
View 3 Replies
View Related
Jul 23, 2005
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies
View Related
Sep 30, 2015
I need formulating a view through which I can create a an output like below image -
Monthly Table
NUM STATUS ACTIVITYCODE
HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69
Season Table
NUM STARTMONTH STARTDAY ENDMONTH EMDDAY
HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28
[Code] ...
Final OutputÂ
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + Â startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + Â endmonth)AS Season from seasons;
View 10 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
Jul 20, 2005
I have a book cataloge where I have an author id linked up to the book.However, I'm finding that some books have mulitple authors, and I'mwondering if there is a way to add more than one id to the author idfield. Or do I have to have many author id fields? Any way around that?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies
View Related
Jul 23, 2005
Hi all,I need to put multiple results in one field, but not sure how. Here'ssome sample code:select a.name,a.accountnum,a.ordernum,(select itemid from items where items_ordernum = a.ordernum) asItems[color=blue]>From clients a[/color]There's multiple instances of 'itemid' in 'items' with the criteria Igave, so this generates an error. I want to instead have the itemnumbers listed as just a list seperated by commas, so the results mightbe this:Name AccountNum OrderNum ItemsJohn Smith 12345 0000234 1233, 1333, 4322Mike Jones 43223 0000023 4322, 543And so forth. Is there someway to loop through this sub-query and makethe results become a list?Thanks --rlangly
View 2 Replies
View Related
Jul 24, 2006
i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date. i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this? what i currently have doesn't blow up, but neither does it actually insert anything. here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)"> <InsertParameters> <asp:Parameter Name="Major" Type="Int32" /> <asp:Parameter Name="Minor" Type="Int32" /> <asp:Parameter Name="Sub" Type="Int32" /> <asp:Parameter Name="Rev" Type="Int32" /> <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" /> </InsertParameters></asp:SqlDataSource>
View 7 Replies
View Related
May 9, 2000
Can some one please tell me how to update a field in a table with multiple
values for each of the values in the other fields?
Thanks in advance.
View 1 Replies
View Related
Oct 18, 2006
I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like:
Title - Author - Edition
The Amazing Pixies - A. N. Author - Hdbk
The Amazing Pixies - A. N. Author - Pbk
Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.
View 2 Replies
View Related
Nov 18, 2004
I have a SQL query that returns several fields from several tables, eg. Title, Subtitle, Author, Binding and Imprint. When these are returned everything seems rosy until there are two authors linked to one title. When this happens Title, Subtitle, Binding and Imprint are repeated which is not required. Is there a way to concatenate the authors from multple records to return a single title with the concatenated authors, instead of repeating titles due to multiple authors?.
Example:
A query may currently return:
Title1 - Subtitle1 - Author 1a - etc
Title1 - Subtitle1 - Author 1b - etc
Title1 - Subtitle1 - Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc
When I would like
Title1 - Subtitle1 - Author 1a, Author 1b, Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc
My actual SQL code, if you are interested, is:
SELECT dbo.edition.ISBN, dbo.edition.title, dbo.party.first_name+' '+dbo.party.surname as name, dbo.edition.reviews, dbo.edition.long_blurb, dbo.series.series_id, dbo.series.series_number,
dbo.series.series_title, dbo.edition.sub_title, dbo.edition.about_author, dbo.edition.short_blurb,
dbo.series.editors_affiliations, dbo.title.contents, dbo.title.affiliations, dbo.series.series_editors
FROM dbo.edition INNER JOIN
dbo.series ON dbo.edition.series_id = dbo.series.series_id INNER JOIN
dbo.title ON dbo.edition.title_id = dbo.title.title_id INNER JOIN
dbo.agreement ON dbo.edition.edition_id = dbo.agreement.edition_id INNER JOIN
dbo.role ON dbo.agreement.role_id = dbo.role.role_id INNER JOIN
dbo.party ON dbo.role.party_id = dbo.party.party_id
View 1 Replies
View Related
Feb 7, 2007
Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.
TABLE
ColumnAColumnB
1.......A
2.......B
3.......C
4.......A
5.......A
6.......B
7.......C
8.......D
9.......C
10......E
EXPECTED OUTPUT
ColumnA ColumnB
1.......4,5
2.......6
3.......7
4.......1,5
5.......1, 4
6.......2
7.......3
8.......
9.......3,7
10......
View 2 Replies
View Related
May 30, 2008
Quick question. What I'm trying to do is concatenate a field for multiple records (hope that is worded in an understandable manner). Here's an example:
ID.....Code
5......33
5......23
ID.....Code...Result
5......33.....33 23
5......23.....33 23
I need the code to get the Result field. I know the code if you were to find the sum, min, max, etc...
(SELECT SUM(CODE)
FROM Table
WHERE ID = Table.ID) AS Result
But I don't know how to write it so it will combine strings.
View 5 Replies
View Related
Oct 12, 2005
Hi
I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..
CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner
(
@Crime_No varchar(20),
@Unit_ID int
)
as
begin
DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int
DECLARE @Defect_ID varchar(100)
select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID
SELECT
TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1,
dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id,
dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone,
dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age,
dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type,
TBL_TR_ACCIDENT_VEHICLE.Registration_Number,
TBL_TR_ACCIDENT_VEHICLE.Crime_No,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre,
TBL_TR_ACCIDENT_VEHICLE.vehicle_Make,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model,
TBL_TR_ACCIDENT_VEHICLE.Unit_ID,
TBL_TR_ACCIDENT_VEHICLE.RowID,
TBL_TR_ACCIDENT_VEHICLE.UserID,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color,
TBL_TR_ACCIDENT_VEHICLE.HP,
TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity,
TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle,
TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight,
TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight,
TBL_TR_ACCIDENT_VEHICLE.Skid_Length,
(select TBL_TR_Person_OutsideDetails.OutSide_state from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],
(select TBL_TR_Person_OutsideDetails.OutSide_City from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]
---here I faced the problem-
/*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/
(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5))
FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE
WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number)
select @Defect_ID
FROM
tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE
where
tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID
AND
TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No
AND
TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner
end
GO
View 2 Replies
View Related
Feb 15, 2006
can we insert multiple values into the same field. as we do for the mailing list. that is can we use commas to enter multiple values into the same field
View 10 Replies
View Related
Jul 23, 2005
Dear all,I need to update one field in a table for a given record and visit number.Example below is how the table looks -SID VISIT DLCO101 0 12101 1 16102 0 18102 2 10103 1 12103 2 14Here is how I would like it to look. The changes are the starred items.SID VISIT DLCO101 0 14*101 1 16102 0 18102 2 16*103 1 12*103 2 14I know it is an UPDATE statement, but I am not sure how to use it when Ineed to update more than one record.Thanks for the help in advance.Jeff
View 3 Replies
View Related
May 4, 2006
Is there a way to create one field from multiple records using sql.For exampleTable 1John 18Peter 18David 18Now I want an sql query that when executed will return a field thatlooks like thisQuery1John Peter DavidSo basically it will return one record with all the name in one field
View 4 Replies
View Related
Jun 27, 2007
Hello,I am loading data from our MS Active Directory into our datawarehouse. (check out Mircosofts's Logparser, it can pull data fromADS, server event logs and more. It can also create text files or loaddirectly to SQL. Its free and a pretty useful tool)There is a field that contains the direct reports of a manager. Thedirect report users are delimited by a pipe symbol.I want to breakup the field into multple rows. There can be none, oneor many direct report users in this field.<disclaimer>This is a snippet of an example. This is only an example. I know thatI have not defined PK nor indexes. My focus is how to solve a problemof parsing a field that has multple values into multple rows.</disclaimer>Thanks for any help in advance.RobCREATE TABLE "dbo"."F_ADS_MANAGERS"("MANAGER_KEY" VARCHAR(255) NULL,"DIRECT_REPORTS_CN" VARCHAR(255) NULL);INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)VALUES ('CN=Marilette, 'CN=RobertD,OU=TechnologyGroup,DC=strayer,DC=edu|CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Mi chelleC,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Lee K,OU=TechnologyGroup')I want to end up with 5 rows, 1 row for each user that is seprated bythe PIPE symbol.CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Lee K,OU=TechnologyGroup
View 3 Replies
View Related
Jul 20, 2005
Hi,I hope someone here can help me.We have a product table which has a many-to-many relationto a category table (joined through a third "ProductCategory" table):[product] ---< [productCategory] >--- [category]--------- ---------------- ----------productID productCategoryID categoryIDproductName productID categoryNamecategoryIDWe want to get a view where each product occupies just one row, andany multiple category values are combined into a single value, eg(concatenating with commas):Product Category-------------------cheese dairycheese solidmilk dairymilk liquidbeer liquidwill become:Product Category-------------------cheese dairy, solidmilk dairy, liquidbeer liquidWhat is the best way to do it in SQL?Thanks and regards,Dmitri
View 4 Replies
View Related
Apr 4, 2008
I'm new to SQL Reporting Services but have made some decent headway. I'm stumped by one issue though: How do I repeat a field over and over on a single line?
To elaborate:
I have a table of backup job failures, with a reason and the time it failed.
ID | Reason | Time
1 | No tape | 3/13/3008
2 | Bad drive | 3/14/2008
I'm trying to create a summary, and I want to list the information as follows:
There were 2 failed backup jobs on 3/13/2008, 3/14/2008
My SQL query is as follows:
SELECT Time, COUNT(*) AS NumFailures FROM FailedBackups GROUP BY Time ORDER BY Time ASC
How do I "loop" the Time field on a single line in Reporting Services?
View 6 Replies
View Related
Sep 5, 2006
hello guys,
I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?
thank you.
View 1 Replies
View Related
Aug 3, 2006
i want to write a code that can split the addresses into multiple fields. anyone can point me out where to start?
lets say i have "12 north plaza boulevard apt.16" and i want it to become:
address_number
12
suffixA
North
street_name
plaza blvd
suffixb
apt16
View 1 Replies
View Related
Nov 27, 2007
HiHo,
just a beginners question:
I have the following row with 2 fields:
Field 1: Task A
Field 2:´ 1;2;3;4
The number of semicolon divided elements in Field 2 is variabel.
I would like to create new rows like:
Row 1 Field 1: A Field 2: 1
Row 2 Field 1: A Field 2: 2
Row 3 Field 1: A Field 2: 3
Row 4 Field 1: A Field 2: 4
I think I should use a Foreach Loop.
But I don't exactly how to do it?
best regards
Chris
View 5 Replies
View Related
Apr 24, 2007
Hello,
I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below:
Input field: Product Description
So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned.
SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill"
I guess there'll need to be a nested Select and loop to parse the space separated input field.
Any pointers would be appreciated.
Thank you
Lee
View 2 Replies
View Related
Oct 17, 2005
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
View 2 Replies
View Related
Jun 6, 2008
I'm a total newb to SQL so I apologize if I butcher the description of what I'm looking to do :) Hopefully the example makes more sense. Is it possible for a single record in 1 table to contain relationships to several records in a different table?
What I've got so far is 2 tables - Hardware and Software, with ID Specifying primary keys HardwareID and SoftwareID respectively. the hardware records are for computer details and the software obviously are for programs. What I need to be able to do is pull up a hardware record and be able to see what software is installed on it, and vice versa pull up a software record to see which computers it's installed on. The problem I'm running into is that a computer can have multiple programs installed on it and a program can be installed on multiple computers, so I don't know how to create that relationship to account for that.
What I'd like to see is in the Hardware table a column for SoftwareID that has a foreign key relationship to the SoftwareID field in the Software Table, and vice versa....My question though is is that possible to do and have potentially multiple separate records it links to from that same column field? I might have a computer with say Windows XP Pro, Office 2003 Standard, Adobe Acrobat 8 and a proprietary rate calculator program that i need each to be displayed with their details when I open that computer's record. Or on the software side if I need to see which computers a license is already installed on then I want to make sure I can pull up the full list of computers.
And finally if what I'd like to do isn't possible as I described it...any recommendations for a better way?
Thanks all for the help!
View 3 Replies
View Related
May 3, 2006
Hi All
I'm really new to SQL and would appreciate a solution to the following problem.
I have a table that contains 1 record. in that record there is a column called CODES which contains multiple values that are seperated by a comma.
C200,C201,C202,C204,C200,C203,C200,C202,C203,C205,C200,C202,C203,C204,C205,C206,C200,C201,C202
I need a solution to select all values from CODES that are distinct so I end up with the following.
C200,C201,C202,C203,C204,C205,C206
Any help would be really appreciated. Please bear in mind I'm a complet novice.
View 4 Replies
View Related
Nov 6, 2007
Hi all,
I want to retrieve the datas from the table with condition if DetailID is not null and DetailID is not guid.empty then it return only the DetailID = @DetailID
can any one help on this.
Thanks,
Sajith
View 4 Replies
View Related
Jan 4, 2008
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
View 6 Replies
View Related
