March 14, 2011 at 12:52 pm
Hello
Need some help with one problem, i have a small table with 10 columns, the first columns it's the name of the routes, and the rest of the columns are the points for the route (product line production). I have a set of 30 routes defined, but some products will pass only in only 1 point, and others will use the 9 points, the values for these cells will be the order of the points in the route.
I don't have the sample data where with me, but it's similar to the table above (when some product don't use some point the value in the cell will be NULL):
Name | Point1 | Point2 | Point3 | Point4 | Point5 ...
R1 | NULL | 1 | 3 | 2 | NULL
R2 | 2 | 3 | 1 | NULL | NULL
Now in the application that will use this table, the user needs to indicate what points the product will use, after this i need to get the route name and after this i need to get the order of the route.
For the first step, i only see the option to use
SELECT Name FROM Table WHERE Point1 IS NULL AND Point2 IS NOT NULL AND...
After this i need to get the correct order for the points, for example, looking to the first example line, i need to get something like Point2,Point4,Point3. I can do this in the front end, but i'd like to do all the job in the server.
The table design can be changed if that helps to do what i want...
Thanks
March 14, 2011 at 7:08 pm
Without knowing more about what you will be using this route data for, I can only suggest your NOT using NULLs. As you are well aware a NULL is NOT equal to a NULL.
Where you are considering using a NULL to indicate a point is NOT used in that route, I would suggest using a zero (0). And when creating the lookup table, set each point to be NOT NULL with a default value of zero (0).
Something like this:
CREATE TABLE [dbo].[Routes](
[Name] [varchar](5) NOT NULL,
[Route1] [int] NOT NULL,
[Route2] [int] NOT NULL,
[Route3] [int] NOT NULL,
[Route4] [int] NOT NULL,
[Route5] [int] NOT NULL,
CONSTRAINT [PK_Routes] PRIMARY KEY CLUSTERED
(
[Name] 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
GO
ALTER TABLE [dbo].[Routes] ADD CONSTRAINT [DF_Routes_Route1] DEFAULT ((0)) FOR [Route1]
GO
ALTER TABLE [dbo].[Routes] ADD CONSTRAINT [DF_Table_2_Roue2] DEFAULT ((0)) FOR [Route2]
GO
ALTER TABLE [dbo].[Routes] ADD CONSTRAINT [DF_Routes_Route3] DEFAULT ((0)) FOR [Route3]
GO
ALTER TABLE [dbo].[Routes] ADD CONSTRAINT [DF_Routes_Route4] DEFAULT ((0)) FOR [Route4]
GO
ALTER TABLE [dbo].[Routes] ADD CONSTRAINT [DF_Routes_Route5] DEFAULT ((0)) FOR [Route5]
GO
March 15, 2011 at 4:26 am
The routing line it's used to validate the selections of the user in the interface, to write a log file...
First i check if the points selected by the user is a valid route (Select Name FROM Routes WHERE Point1=0 AND Point2>0 AND..), then after checking if it's valid (IF i have a NAME), i need to write to a log file with every point in the route order from the lowest to the highest and the time that will be used in each point.
I can use the 0 instead of the NULL, but i don't see how to do what i need... 🙂
Sample of the log file with the info in my previous post (Order,Point Name,Duration) with R1:
1 Point2 1,5
2 Point4 3,0
3 Point3 0,5
March 15, 2011 at 10:07 am
Current valid data:
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
RouteName VARCHAR(3) PRIMARY KEY,
Point1 INT NOT NULL DEFAULT(0),
Point2 INT NOT NULL DEFAULT(0),
Point3 INT NOT NULL DEFAULT(0),
Point4 INT NOT NULL DEFAULT(0),
Point5 INT NOT NULL DEFAULT(0),
Point6 INT NOT NULL DEFAULT(0),
Point7 INT NOT NULL DEFAULT(0),
Point8 INT NOT NULL DEFAULT(0),
Point9 INT NOT NULL DEFAULT(0)
)
INSERT INTO #mytable (RouteName,Point1,Point2,Point3,Point4,Point5,Point6,Point7,Point8,Point9)
SELECT 'R1 ','10','20','30','40','0','0','0','0','0' UNION ALL
SELECT 'R10','0','0','0','10','0','0','0','0','0' UNION ALL
SELECT 'R2 ','10','20','0','30','0','0','0','0','0' UNION ALL
SELECT 'R20','0','0','0','0','10','20','30','40','50' UNION ALL
SELECT 'R21','0','0','0','0','10','0','0','0','0' UNION ALL
SELECT 'R22','0','0','0','0','0','10','0','0','0' UNION ALL
SELECT 'R23','0','0','0','0','0','0','10','0','0' UNION ALL
SELECT 'R24','0','0','0','0','0','0','0','0','10' UNION ALL
SELECT 'R25','0','0','0','0','10','0','0','0','20' UNION ALL
SELECT 'R26','0','0','0','0','10','20','0','30','0' UNION ALL
SELECT 'R27','0','0','0','0','10','20','0','30','40' UNION ALL
SELECT 'R28','0','0','0','0','0','10','0','20','0' UNION ALL
SELECT 'R29','0','0','0','0','0','10','20','30','0' UNION ALL
SELECT 'R3 ','10','0','20','30','0','0','0','0','0' UNION ALL
SELECT 'R30','0','0','0','0','0','10','20','30','40' UNION ALL
SELECT 'R4 ','10','0','0','20','0','0','0','0','0' UNION ALL
SELECT 'R40','10','20','30','0','40','50','60','70','80' UNION ALL
SELECT 'R41','10','0','0','0','20','30','40','50','60' UNION ALL
SELECT 'R42','10','0','0','0','0','20','30','40','50' UNION ALL
SELECT 'R5 ','10','20','0','0','0','0','0','0','0' UNION ALL
SELECT 'R6 ','10','0','20','0','0','0','0','0','0' UNION ALL
SELECT 'R60','40','0','0','0','10','20','30','0','0' UNION ALL
SELECT 'R61','40','0','30','0','0','10','0','20','0' UNION ALL
SELECT 'R62','0','0','30','0','0','0','0','20','0' UNION ALL
SELECT 'R7 ','10','0','0','0','0','0','0','0','0' UNION ALL
SELECT 'R70','40','50','0','0','10','20','0','30','0' UNION ALL
SELECT 'R71','30','40','0','0','0','10','0','20','0' UNION ALL
SELECT 'R8 ','0','10','0','0','0','0','0','0','0' UNION ALL
SELECT 'R9 ','0','0','10','0','0','0','0','0','0'
Thanks
March 15, 2011 at 12:17 pm
I though in another solution, but in this one i can't get the route name correctly, according to the user selections...
IF OBJECT_ID('TempDB..#mytemptable2','U') IS NOT NULL
DROP TABLE #mytemptable2
CREATE TABLE #mytemptable2(
Name VARCHAR(10) NOT NULL,
OrderRoute INT NOT NULL,
PointName VARCHAR(10) NOT NULL,
)
INSERT INTO #mytemptable2 ( Name, OrderRoute, PointName )
SELECT 'R1','10','Point1' UNION ALL
SELECT 'R1','20','Point2' UNION ALL
SELECT 'R1','30','Point3' UNION ALL
SELECT 'R1','40','Point4' UNION ALL
SELECT 'R10','10','Point4' UNION ALL
SELECT 'R2','10','Point1' UNION ALL
SELECT 'R2','20','Point2' UNION ALL
SELECT 'R2','30','Point4' UNION ALL
SELECT 'R20','10','Point5' UNION ALL
SELECT 'R20','20','Point6' UNION ALL
SELECT 'R20','30','Point7' UNION ALL
SELECT 'R20','40','Point8' UNION ALL
SELECT 'R20','50','Point9' UNION ALL
SELECT 'R21','10','Point5' UNION ALL
SELECT 'R22','10','Point6' UNION ALL
SELECT 'R23','10','Point7' UNION ALL
SELECT 'R24','10','Point9' UNION ALL
SELECT 'R25','10','Point5' UNION ALL
SELECT 'R25','20','Point9' UNION ALL
SELECT 'R26','10','Point5' UNION ALL
SELECT 'R26','20','Point6' UNION ALL
SELECT 'R26','30','Point8' UNION ALL
SELECT 'R27','10','Point5' UNION ALL
SELECT 'R27','20','Point6' UNION ALL
SELECT 'R27','30','Point8' UNION ALL
SELECT 'R27','40','Point9' UNION ALL
SELECT 'R28','10','Point6' UNION ALL
SELECT 'R28','20','Point8' UNION ALL
SELECT 'R29','10','Point6' UNION ALL
SELECT 'R29','20','Point7' UNION ALL
SELECT 'R29','30','Point8' UNION ALL
SELECT 'R3','10','Point1' UNION ALL
SELECT 'R3','20','Point3' UNION ALL
SELECT 'R3','30','Point4' UNION ALL
SELECT 'R30','10','Point6' UNION ALL
SELECT 'R30','20','Point7' UNION ALL
SELECT 'R30','30','Point8' UNION ALL
SELECT 'R30','40','Point9' UNION ALL
SELECT 'R4','10','Point1' UNION ALL
SELECT 'R4','20','Point4' UNION ALL
SELECT 'R40','10','Point1' UNION ALL
SELECT 'R40','20','Point2' UNION ALL
SELECT 'R40','30','Point3' UNION ALL
SELECT 'R40','40','Point5' UNION ALL
SELECT 'R40','50','Point6' UNION ALL
SELECT 'R40','60','Point7' UNION ALL
SELECT 'R40','70','Point8' UNION ALL
SELECT 'R40','80','Point9' UNION ALL
SELECT 'R41','10','Point1' UNION ALL
SELECT 'R41','20','Point5' UNION ALL
SELECT 'R41','30','Point6' UNION ALL
SELECT 'R41','40','Point7' UNION ALL
SELECT 'R41','50','Point8' UNION ALL
SELECT 'R41','60','Point9' UNION ALL
SELECT 'R42','10','Point1' UNION ALL
SELECT 'R42','20','Point6' UNION ALL
SELECT 'R42','30','Point7' UNION ALL
SELECT 'R42','40','Point8' UNION ALL
SELECT 'R42','50','Point9' UNION ALL
SELECT 'R5','10','Point1' UNION ALL
SELECT 'R5','20','Point2' UNION ALL
SELECT 'R6','10','Point1' UNION ALL
SELECT 'R6','20','Point3' UNION ALL
SELECT 'R60','10','Point5' UNION ALL
SELECT 'R60','20','Point6' UNION ALL
SELECT 'R60','30','Point7' UNION ALL
SELECT 'R60','40','Point1' UNION ALL
SELECT 'R61','10','Point6' UNION ALL
SELECT 'R61','20','Point8' UNION ALL
SELECT 'R61','30','Point3' UNION ALL
SELECT 'R61','40','Point1' UNION ALL
SELECT 'R62','10','Point6' UNION ALL
SELECT 'R62','20','Point8' UNION ALL
SELECT 'R62','30','Point3' UNION ALL
SELECT 'R7','10','Point1' UNION ALL
SELECT 'R70','10','Point5' UNION ALL
SELECT 'R70','20','Point6' UNION ALL
SELECT 'R70','30','Point8' UNION ALL
SELECT 'R70','40','Point1' UNION ALL
SELECT 'R70','50','Point2' UNION ALL
SELECT 'R71','10','Point6' UNION ALL
SELECT 'R71','20','Point8' UNION ALL
SELECT 'R71','30','Point1' UNION ALL
SELECT 'R71','40','Point2' UNION ALL
SELECT 'R8','10','Point2' UNION ALL
SELECT 'R9','10','Point3'
I need something like a IN and NOT IN, for the values of the points. I already tried sub queries but with no luck...
March 15, 2011 at 7:31 pm
now you have me completely confused as to what it is you need ..
Sample of the log file with the info in my previous post
(Order,Point Name,Duration) with R1:
1 Point2 1,5
2 Point4 3,0
3 Point3 0,5
From the sample data you posted:
SELECT * FROM mytemptable2 WHERE Name = 'R1'
Results in:
Name OrderRoute PointName
---------- ----------- ----------
R1 10 Point1
R1 20 Point2
R1 30 Point3
R1 40 Point4
(4 row(s) affected)
a. Is Order and your "OrderRoute" just 2 different names for the same field?
b. Your data did not include any duration data.
c. What is/are the rule(s) invoked to obtain your answer?
Result: I am confused and can not attempt to assist your further with my lack of understanding exactly what you require and the rule(s) you applied to generate the required results.
March 16, 2011 at 3:59 am
Hehehe sorry about that... Forget the log file structure. :ermm:
This's what happen when trying to do several things at the same time.
1. The OrderRoute it's the same of the Order, this time i used the real data, so instead of 1 i have 10, instead of 2 i have 20, it's a internal requirement
2. The duration isn't saved in this table, i just wrote it value to aid to understand why do i need the correct order of the values...
3. I try to explain again, i have several possible routes (production line, for example cut, bend, etc) for different products. When the user "submits" a new product to the production line, he or she needs to select the points that the product will pass in the production line and indicate the duration of each operation. I need to save this data to the "log" file, where i wrote only one route, order by the OrderRoute (looking to the second version of the table).
So the steps are these ones (using the second version of the table):
1 - Need to identify the route that has the points selected by the user in the interface, something like,
SELECT Name FROM #mytemptable2 WHERE PointName IN ('Point1','Point2') AND PointName NOT IN ('Point3','and the rest of the Points that arent in the IN clause')
I know that this query doesn't work, and this is where i have my problem...
2 - After getting the Route Name, i need to get the Points order by the OrderRoute, this one is easy, just need to put an Order BY
SELECT OrderRoute, PointName FROM #mytemptable2 WHERE Name = 'RouteNameReturnedInThePreviousQuery' ORDER BY OrderROute
If i use the first version of the table, i can easily get the name of the route, but i don't see how to get the correct order of the route because the order depends of the columns...
Thanks
March 23, 2011 at 11:31 am
No one 🙁
March 24, 2011 at 7:20 pm
As much as I dislike doing what is given below, it is the only quick and dirty method I could come up with in SQL 2000.
1. Revised your table which contains the Route data
2. Inserted your data as shown in my INSERT INTO statements
3. Came up with a quick and dirty, simple pattern matching T-SQL procedure.
Reason for not considering this any where near the best solution.
a. Do not know your front end code as to wether it can pass the required information.
b. Note that if the first point in the path is a 0 the procedure must be invoked as: Dbo.FindRoute,0,10 other examples FindRoute 0,0,0,10 or Dbo.FindRoute 0,0,0,0,0,0,0,0,10
Hopefully by posting this answer, it will encourage others to come up with a better solution.
CREATE TABLE mytable
(RouteName VARCHAR(3) PRIMARY KEY,POINTS VARCHAR(30))
INSERT INTO mytable (RouteName,Points)
SELECT 'R1' ,'10,20,30,40,0,0,0,0,0' UNION ALL
SELECT 'R10','0,0,0,10,0,0,0,0,0' UNION ALL
SELECT 'R2 ','10,20,0,30,0,0,0,0,0' UNION ALL
SELECT 'R20','0,0,0,0,10,20,30,40,50' UNION ALL
SELECT 'R21','0,0,0,0,10,0,0,0,0' UNION ALL
SELECT 'R22','0,0,0,0,0,10,0,0,0' UNION ALL
SELECT 'R23','0,0,0,0,0,0,10,0,0' UNION ALL
SELECT 'R24','0,0,0,0,0,0,0,0,10' UNION ALL
SELECT 'R25','0,0,0,0,10,0,0,0,20' UNION ALL
SELECT 'R26','0,0,0,0,10,20,0,30,0' UNION ALL
SELECT 'R27','0,0,0,0,10,20,0,30,40' UNION ALL
SELECT 'R28','0,0,0,0,0,10,0,20,0' UNION ALL
SELECT 'R29','0,0,0,0,0,10,20,30,0' UNION ALL
SELECT 'R3 ','10,0,20,30,0,0,0,0,0' UNION ALL
SELECT 'R30','0,0,0,0,0,10,20,30,40' UNION ALL
SELECT 'R4 ','10,0,0,20,0,0,0,0,0' UNION ALL
SELECT 'R40','10,20,30,0,40,50,60,70,80' UNION ALL
SELECT 'R41','10,0,0,0,20,30,40,50,60' UNION ALL
SELECT 'R42','10,0,0,0,0,20,30,40,50' UNION ALL
SELECT 'R5 ','10,20,0,0,0,0,0,0,0' UNION ALL
SELECT 'R6 ','10,0,20,0,0,0,0,0,0' UNION ALL
SELECT 'R60','40,0,0,0,10,20,30,0,0' UNION ALL
SELECT 'R61','40,0,30,0,0,10,0,20,0' UNION ALL
SELECT 'R62','0,0,30,0,0,0,0,20,0' UNION ALL
SELECT 'R7 ','10,0,0,0,0,0,0,0,0' UNION ALL
SELECT 'R70','40,50,0,0,10,20,0,30,0' UNION ALL
SELECT 'R71','30,40,0,0,0,10,0,20,0' UNION ALL
SELECT 'R8 ','0,10,0,0,0,0,0,0,0' UNION ALL
SELECT 'R9 ','0,0,10,0,0,0,0,0,0'
CREATE PROC Dbo.FindRoute
@Point1 VARCHAR(3) = 0
,@Point2 VARCHAR(3) = 0
,@Point3 VARCHAR(3) = 0
,@Point4 VARCHAR(3) = 0
,@Point5 VARCHAR(3) = 0
,@Point6 VARCHAR(3) = 0
,@Point7 VARCHAR(3) = 0
,@Point8 VARCHAR(3) = 0
,@Point9 VARCHAR(3) = 0
AS
DECLARE @Path VARCHAR(30)
SET @Path = @Point1+','+@Point2+','+@Point3+','+@Point4+','+@Point5+','+@Point6+','+@Point7+','+@Point8+','+@Point9
PRINT @Path -- Remove after testing
SELECT RouteName,Points FROM mytable WHERE POINTS = @Path
Running as Dbo.FindRoute 0,10 produced the following -
Name Path
R8 0,10,0,0,0,0,0,0,0
March 25, 2011 at 3:34 am
Thanks once again.
But the code that you posted doesn't solve my problem, because i need to know the correct order of the columns, if you look to the R60 how do i know what's the correct order... the first item in the string it's the 40 but it's the last point in this route...
In the front end the user will have to select where the product will stop in the production line without knowing the correct order, if the user select Point1, Point2 , Point4, and the user doesn't know the correct order (normal situation), the order could be one of the following (10,20,0,30... or 20,10,0,30.... or 30,10,0,20... or....).
I could handle the problem in the front end, using the first version the points as column names, after getting the route name, just get the complete row, and with a loop in the code, just go through the cells and find the values that i want and get the column name.
For example if the user select in the UI the Point1, Point3 , Point7, i get the route name with a simple select checking for Point1 > 0 and point2 > and point3 > 0 and the rest of the points = 0, after this i get a row like this (20,0,10,0,0,0,30,0,0), then with a loop, i know that i have 3 points so the max value will be 30, i just check for 10, when i get the cell, i get the name of the column, and so on for the rest of the values...
This is fine, but i was looking to do this all in the db without putting this kind of code in the UI.
Thanks for replying to this bitbucket. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply