Help with an SQL SELECT statement in ASP page

I have built this SQL statement which should
create the RS I need.

strSQL1 = "SELECT [INVENTRY MASTER].BOX_NO FROM [INVENTRY MASTER] WHERE
Left([INVENTRY MASTER].BOX_NO, PatIndex('%821%', [INVENTRY MASTER].BOX_NO) -
1) NOT LIKE '%[1-9]%' AND [INVENTRY MASTER].BOX_NO LIKE '%821%';"

This Line:

objRS1.Open strSQL1, objConn

Causes this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
passed to the substring function.

I have searched google, and found reference to the error meaning it found a
space in the first position. I tried adding LTRIM into my statement to cure
it but it made no difference, I may be barking up the wrong tree so to speak
;) but I cant find any other information on it.

If anyone has any ideas why this statement does not work I'd be very
grateful, the project has to be completed today, and this is the last thing
to get working now!

Ta

Paul McGuireThe length parameter in your LEFT(String, Length) function is

PatIndex('%821%', [INVENTRY MASTER].BOX_NO) - 1

PatIndex can return 0, if your string '821' isn't part of your BOX_NO, which makes the length = -1, which isn't allowed as a valid length. Your additional condition does not help, since the expression as a whole will be evaluated.

Consider to use a view to put your conditon
[INVENTRY MASTER].BOX_NO LIKE '%821%'|||Let me expand on what I am trying to achieve to see if you can help further.

imagine a table collumn

BOX_NO
--------
JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821
JHIS 00821a
JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

A user on the internet page I am writing will enter 821 because he wants a list of the boxes:

JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821

The JHIS is the users account number the leading 0's can vary which is what has caused the need to use PatIndex in the way which I have? Is there another way?

All the user knows is box number JHIS 0000821 is box number 821 if he wanted JHIS 0001821 he would enter 1821 and expect to get these boxes returned:

JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

Any ideas on the best way to do this? Am i on the right lines?|||Why not just

WHERE BOX_NO LIKE '%0821%'

?|||erm I am not sure :) I will put this in now... it seems like this may work if I just add a proceeding '0' to what has been inputted and do a LIKE

If this works I'm going to be very embarrised but pleased to because its been bugging me for hours!

I will let you know if this solves the issue|||Thank You very much! I obviously could not see the wood for the trees!

I changed it slightly because as it stood if 821 was entered 0008210 returned aswell which was not wanted.

by changing the last % to a [a-z] and adding an OR and just looking for %0821 I get back exactly what I need. And its a nice simple select statement!

So I offer you a big thanks and a :) this should keep the managment happy!|||You are welcome.sql

Help w/ a SQL Query

In a sales software system that I built, a sales person belongs to a
department within the company. However, during their employment with the
company, they might work in several departments. So I implemented department
logging in the system.
CREATE TABLE [USER_DepartmentLog] (
[UserId] [int] NOT NULL ,
[DepartmentId] [int] NOT NULL ,
[DateTimeStarted] [datetime] NOT NULL ,
[DateTimeEnded] [datetime] NULL
)
GO
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded) VALUES (33, 13, '06/24/2005', '05/01/2005')
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
In this example, User 33 worked in Department 13 from 06/24/2005 to
05/01/2005
On 05/01/2005, they were transferred to Department 12
Each sales person books Jobs:
CREATE TABLE [Job] (
[Id] [int] NOT NULL,
[UserId] [int] NOT NULL,
[DateCreated] [datetime] NOT NULL
)
GO
INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(1, 33, '05/12/2005')
INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(2, 33, '05/01/2005')
INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(3, 33, '04/28/2005')
INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(4, 33, '04/05/2005')
I'd like to run a query that would return a list of jobs while at the same
time displaying the department that the sales person was in at the time they
entered the jobs. Here's what I would like my returned data to look like:
UserId JobId DepartmentId DateCreated
33 4 13 04/05/2005
33 3 13 04/28/2005
33 2 12 05/01/2005
33 1 12 05/12/2005
If a job was created on a date that the user transitioned from one
department to another, e.g. Job 2 was created on 05/01/2005 when the user
left Department 12 for Department 12, I'd like the job to appear under the
new Department.
Thank YouCORRECTION.
I meant '06/24/2002', not '06/24/2005'
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded) VALUES (33, 13, '06/24/2002', '05/01/2005')
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
In this example, User 33 worked in Department 13 from 06/24/2002 to
05/01/2005
On 05/01/2005, they were transferred to Department 12
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:%23wnB$nIXFHA.3340@.TK2MSFTNGP15.phx.gbl...
> In a sales software system that I built, a sales person belongs to a
> department within the company. However, during their employment with the
> company, they might work in several departments. So I implemented
> department logging in the system.
> CREATE TABLE [USER_DepartmentLog] (
> [UserId] [int] NOT NULL ,
> [DepartmentId] [int] NOT NULL ,
> [DateTimeStarted] [datetime] NOT NULL ,
> [DateTimeEnded] [datetime] NULL
> )
> GO
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 13, '06/24/2005', '05/01/2005')
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
> In this example, User 33 worked in Department 13 from 06/24/2005 to
> 05/01/2005
> On 05/01/2005, they were transferred to Department 12
> Each sales person books Jobs:
> CREATE TABLE [Job] (
> [Id] [int] NOT NULL,
> [UserId] [int] NOT NULL,
> [DateCreated] [datetime] NOT NULL
> )
> GO
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(1, 33,
> '05/12/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(2, 33,
> '05/01/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(3, 33,
> '04/28/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(4, 33,
> '04/05/2005')
> I'd like to run a query that would return a list of jobs while at the same
> time displaying the department that the sales person was in at the time
> they entered the jobs. Here's what I would like my returned data to look
> like:
> UserId JobId DepartmentId DateCreated
> 33 4 13 04/05/2005
> 33 3 13 04/28/2005
> 33 2 12 05/01/2005
> 33 1 12 05/12/2005
>
> If a job was created on a date that the user transitioned from one
> department to another, e.g. Job 2 was created on 05/01/2005 when the user
> left Department 12 for Department 12, I'd like the job to appear under the
> new Department.
> Thank You
>|||Hi, George
Thank you for providing DDL, sample data and expected results. Try this
query:
SELECT Job.UserID, Job.ID as JobID, DepartmentID, DateCreated
FROM Job INNER JOIN USER_DepartmentLog
ON Job.UserID=USER_DepartmentLog.UserID
AND DateCreated>=DateTimeStarted
AND (DateCreated<DateTimeEnded OR DateTimeEnded IS NULL)
Razvan|||Try,
select
j.UserId,
j.[Id] as job_id,
u.DepartmentId,
j.DateCreated
from
job as j
inner join
USER_DepartmentLog as u
on j.userid = u.userid
and j.datecreated between u.DateTimeStarted and isnull(u.DateTimeEnded,
j.datecreated)
and j.datecreated != isnull(u.DateTimeEnded, dateadd(day, -1, j.datecreated)
)
order by
j.userid, j.datecreated
go
But better if you add a constraint to not allow a user to start working in
another department at the same time it is finishing working for another one
,
so it will force us to enter something like:
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded)
VALUES (33, 13, '06/24/2002', '2005-05-01T08:00:00')
INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
DateTimeEnded)
VALUES (33, 12, '2005-05-01T08:00:01', NULL)
and the select statement will not need the expression:
...
and j.datecreated != isnull(u.DateTimeEnded, dateadd(day, -1, j.datecreated)
)
...
AMB
"George Durzi" wrote:

> CORRECTION.
> I meant '06/24/2002', not '06/24/2005'
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 13, '06/24/2002', '05/01/2005')
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
> In this example, User 33 worked in Department 13 from 06/24/2002 to
> 05/01/2005
> On 05/01/2005, they were transferred to Department 12
>
>
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:%23wnB$nIXFHA.3340@.TK2MSFTNGP15.phx.gbl...
>
>|||Thank you both for your help
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:%23wnB$nIXFHA.3340@.TK2MSFTNGP15.phx.gbl...
> In a sales software system that I built, a sales person belongs to a
> department within the company. However, during their employment with the
> company, they might work in several departments. So I implemented
> department logging in the system.
> CREATE TABLE [USER_DepartmentLog] (
> [UserId] [int] NOT NULL ,
> [DepartmentId] [int] NOT NULL ,
> [DateTimeStarted] [datetime] NOT NULL ,
> [DateTimeEnded] [datetime] NULL
> )
> GO
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 13, '06/24/2005', '05/01/2005')
> INSERT INTO USER_DepartmentLog (UserId, DepartmentId, DateTimeStarted,
> DateTimeEnded) VALUES (33, 12, '05/01/2005', NULL)
> In this example, User 33 worked in Department 13 from 06/24/2005 to
> 05/01/2005
> On 05/01/2005, they were transferred to Department 12
> Each sales person books Jobs:
> CREATE TABLE [Job] (
> [Id] [int] NOT NULL,
> [UserId] [int] NOT NULL,
> [DateCreated] [datetime] NOT NULL
> )
> GO
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(1, 33,
> '05/12/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(2, 33,
> '05/01/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(3, 33,
> '04/28/2005')
> INSERT INTO [Job]([Id], [UserId], [DateCreated]) VALUES(4, 33,
> '04/05/2005')
> I'd like to run a query that would return a list of jobs while at the same
> time displaying the department that the sales person was in at the time
> they entered the jobs. Here's what I would like my returned data to look
> like:
> UserId JobId DepartmentId DateCreated
> 33 4 13 04/05/2005
> 33 3 13 04/28/2005
> 33 2 12 05/01/2005
> 33 1 12 05/12/2005
>
> If a job was created on a date that the user transitioned from one
> department to another, e.g. Job 2 was created on 05/01/2005 when the user
> left Department 12 for Department 12, I'd like the job to appear under the
> new Department.
> Thank You
>