Help! Need assistance!

I have a table with following columns:
Tutorial Number
Lesson Number
Order Number
where each tutorial has many lessons and there is a order number for
each lesson within that tutorial.
In the UI, once the user re-sorts the lessons in any manner which they
like, I am to store the new order as is the order in the datagrid.
How can I do that?
what should the Stored Procedure do?
thanks in advance,On 30 Sep 2005 14:15:08 -0700, nashak@.hotmail.com wrote:

>I have a table with following columns:
>Tutorial Number
>Lesson Number
>Order Number
>where each tutorial has many lessons and there is a order number for
>each lesson within that tutorial.
>In the UI, once the user re-sorts the lessons in any manner which they
>like, I am to store the new order as is the order in the datagrid.
>How can I do that?
>what should the Stored Procedure do?
>thanks in advance,
Hi nashak,
Homework assignment for school?
I think you need one stored procedure, to move one lesson to a new
location. Input parameters would be the tutorial number, the lesson to
be moved and the new location (either "before" or "after" another
lessen; in both cases you'll need a special value for "end of list" or
"start of list").
Draw some examples, think about possible cases (move forward - move
backward - "move", but new location = old location - etc), and work out
how in each of these cases the assigned Order Numbers should change.
Then translate those rules into SQL, hand in the assignment to your
teacher, and hope he or she doesn't read this group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Wish it were just as simple as homework assigment.. :-)
The actual columns are different. I thought of these just as a sample.
What I am trying to do is as follows:
My table stores the Tutorial, lessons in that tutorial and the order of
the lessons.
The datagrid in my UI has been bound to the dataset that I created with
the above table. This grid has buttons to move the lessons up or
down(and thereby changing the order) as well as a checkbox to delete
the lesson from the tutorial. I need to first delete all the deleted
lessons and then capture the order of lessons and store this new order
in the "OrderOfLesson" column.
One way I think is to put a client side script that would change order
at every button click and store that order as well as change status of
rows that have been deleted. Then just update the database by calling
update() of the datatable. Is there some example code of the above that
i could look at?|||Some more stuff:
The table stores Order of lessons. However the Datagrid does not
display the order number. Is there anyway for the dataset to contain
order number column but for the Datagrid to not display that column?
In the above case, I could then at the Save event
(1) Go thru dataset and re-order all the rows and then call Update() of
dataset to update the rows in the database.
Does this sound possible?|||On 1 Oct 2005 08:06:35 -0700, nashak@.hotmail.com wrote:

>Hugo,
>Wish it were just as simple as homework assigment.. :-)
>The actual columns are different. I thought of these just as a sample.
>What I am trying to do is as follows:
>My table stores the Tutorial, lessons in that tutorial and the order of
>the lessons.
>The datagrid in my UI has been bound to the dataset that I created with
>the above table. This grid has buttons to move the lessons up or
>down(and thereby changing the order) as well as a checkbox to delete
>the lesson from the tutorial. I need to first delete all the deleted
>lessons and then capture the order of lessons and store this new order
>in the "OrderOfLesson" column.
>One way I think is to put a client side script that would change order
>at every button click and store that order as well as change status of
>rows that have been deleted. Then just update the database by calling
>update() of the datatable. Is there some example code of the above that
>i could look at?
Hi nashak,
Not a homework assignment, then. Okay. Sorry for being suspicious.
Anyway, that doesn't change the rest of my answer: you should have a
stored procedure that you can call to move a lesson around in the order.
The body of the stored procedure would probably consist of some IF's to
determine which of the various cases applies (giving one order a higher
place in the list, giving one order a lower place in the list, maybe
some more as well), and one UPDATE statement for each of the cases.
Here's the outline of an UPDATE statement that will move the lesson that
held the 6th place to the 3rd place (and simulteneously move the lessons
that were at 3rd, 4th and 5th place one place down):
UPDATE Lessons
SET Place = CASE WHEN Place = 6 THEN 3 ELSE Place + 1 END
WHERE Place BETWEEN 3 AND 6
AND other criteria
The real procedure would use variables, of course :-)
If you need more help, then please provide some more information about
your tables (as CREATE TABLE statements) and sample data (as INSERT
statements) that I can use to build and test the procedure. For tips on
how to assemble this information: www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello,
I did think of that. However the concern that I have is that this SP is
called everytime the user moves a lesson up and down. i.e if lesson
goes up I do order-- for that and order++ for the previous one. Too
many db calls.
Instead, I would like to see if I can let the user do whatever he wants
and then try to store the complete data from grid/dataset with a new
order starting from the first row.
Thanks,|||A general SP for re-ordering a sequential column has been posted
before.|||On 1 Oct 2005 15:13:03 -0700, nashak@.hotmail.com wrote:

>Hello,
>I did think of that. However the concern that I have is that this SP is
>called everytime the user moves a lesson up and down. i.e if lesson
>goes up I do order-- for that and order++ for the previous one. Too
>many db calls.
>Instead, I would like to see if I can let the user do whatever he wants
>and then try to store the complete data from grid/dataset with a new
>order starting from the first row.
>Thanks,
Hi nashak,
The advantage of using "too many db calls" is that changes are persisted
directly. No data loss if the client crashes. Of course, that's a down
side as well, as you'll either have to settle for no "abort all changes"
button, or use a long running transaction that will cause blocking and
hurt concurrency.
If you prefer using less db calls, build an app roughly like this:
- Get lessons and their relative positions from DB; store into array.
- Allow user to change order. Note changes in array.
- If user decides to abort changes, do nothing.
- If user decides to accept changes, call stored procedure that updates
order column for all lessons in one UPDATE statement. Do check if
someone else made changes in the meantime (you can use a rowversion
column for this - google "optimistic locking" for more details).
- If stored procedure reports changes by other users, go back to step 1
(or allow user to choose to override changes by other, or ...)
Getting the changed information back to the stored procedure requires an
array parameter. Unfortunately, there is no such thing in SQL Server.
See http://www.sommarskog.se/arrays-in-sql.html for some alternatives.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

Sorry, wrong format in last post. Here's the question:

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

|||Check out maxrecursion here http://msdn2.microsoft.com/en-us/library/ms175972.aspx|||Thanks. I tried it but got some error:

CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 1)
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
OPTION (MAXRECURSION 1000)
)

It says Incorrect syntax near the keyword 'OPTION'. What's wrong with it?
Thanks!
|||

You need to use maxrecursion in the projection out of the CTE, the below works for me in SP1;

use tempdb

go

CREATE TABLE Table1 ( fr int, t int)

go

INSERT Table1 VALUES (1, 2)

INSERT Table1 VALUES (2, 3)

INSERT Table1 VALUES (3, 4)

INSERT Table1 VALUES (1, 3)

INSERT Table1 VALUES (1, 4)

INSERT Table1 VALUES (4, 1)

go

WITH CTE_Sample (fr, t, level) AS

(

SELECT Table1.fr, Table1.t, 1 AS level

FROM Table1

WHERE fr=1

UNION ALL

SELECT Table1.fr, Table1.t, level+1

FROM Table1

INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t

)

select *

from CTE_Sample

OPTION (MAXRECURSION 2);

go

|||

Thank you Euan! It works!

btw. Do you know if I can join CTE_Sample with other tables or other CTEs? I tried but got some errors.

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
)

WITH CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

It says "Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
" Then I added a semicolon at the end of CTE_Sample, then it says "Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ';'.
".

What should I do?

|||

I'm not 100% clear on what yuo are actually trying to do but in principle you can use CTEs very flexibly, I suggest reviewing Boks On Line and doing some online searching for them, there are lots of examples.

You can also try looking in the T-SQL forum for examples in there.

|||

You should separate the With statements with a comma and remove the second "with".

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
) ,

CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

You should be able to with a where clause on some accumulator. For example, take the level column in the following query:

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID
)
SELECT *
FROM Employee_CTE
WHERE level <= 2
ORDER BY level desc

Just vary the value in the where clause to vary the number of times it recurses.

|||

Thank you Louis. But that denpends on the recursion would end properly, and it'll calculate all the levels. If the number of the levels is too large, or there's a loop, there would be a problem. I tried to use the MAXRECURSION hint, but it gave me a syntax error. What I have is:

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID

option (MAXRECURSION 10)
)

Then it told me the syntax for option (MAXRECURSION 10) is wrong. You have any ideas what the right way is?

Thanks!

|||

Yeah, it goes on the query, not the CTE (kind of wierd syntax, but anyhow)

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID

)

SELECT *
FROM Employee_CTE
Option (MAXRECURSION 10)

Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

Sorry, wrong format in last post. Here's the question:

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

|||Check out maxrecursion here http://msdn2.microsoft.com/en-us/library/ms175972.aspx|||Thanks. I tried it but got some error:

CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 1)
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
OPTION (MAXRECURSION 1000)
)

It says Incorrect syntax near the keyword 'OPTION'. What's wrong with it?
Thanks!|||

You need to use maxrecursion in the projection out of the CTE, the below works for me in SP1;

use tempdb

go

CREATE TABLE Table1 ( fr int, t int)

go

INSERT Table1 VALUES (1, 2)

INSERT Table1 VALUES (2, 3)

INSERT Table1 VALUES (3, 4)

INSERT Table1 VALUES (1, 3)

INSERT Table1 VALUES (1, 4)

INSERT Table1 VALUES (4, 1)

go

WITH CTE_Sample (fr, t, level) AS

(

SELECT Table1.fr, Table1.t, 1 AS level

FROM Table1

WHERE fr=1

UNION ALL

SELECT Table1.fr, Table1.t, level+1

FROM Table1

INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t

)

select *

from CTE_Sample

OPTION (MAXRECURSION 2);

go

|||

Thank you Euan! It works!

btw. Do you know if I can join CTE_Sample with other tables or other CTEs? I tried but got some errors.

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
)

WITH CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

It says "Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
" Then I added a semicolon at the end of CTE_Sample, then it says "Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ';'.
".

What should I do?

|||

I'm not 100% clear on what yuo are actually trying to do but in principle you can use CTEs very flexibly, I suggest reviewing Boks On Line and doing some online searching for them, there are lots of examples.

You can also try looking in the T-SQL forum for examples in there.

|||

You should separate the With statements with a comma and remove the second "with".

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
) ,

CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

Help writing SQL

I want to select records where a column is null, is not null or is equal to a specific number. How do I do this in one sql statement. My application is an asp.net web site with a business logic layer, a data access layer and sql server. Thank you in advance.select *
from tb
where isnull(col,'*')=isnull(@.search,'*')

Help with Where

I help with my where statment, I need the Asset ID to return all valuse that are matching with the ESN Number or ID.

SELECT Asset.AssetId,
A.Description,
B.Barcode,
C.AssetDescription,
B.SKU,
J.EsnNumber,
G.WarehouseDescription,
I.TraceTime,
I.PreviousTraceTime,
I.HasMoved,
I.DistanceMiles,
I.Direction,
I.Landmark,
I.FemaLocation,
I.ReportTime,
I.ReplaceByDate,
I.CurrLocStreet,
I.CurrLocCity,
I.CurrLocState,
I.CurrLocZip,
I.CurrLocCounty,
I.CurrMileFromStratix,
I.PrevMileFromStratix
FROM AssetType As A
INNER JOIN Asset As B ON (A.AssetTypeId = B.AssetTypeId)
INNER JOIN AssetAttribute As C ON (A.AssetTypeId = C.AssetTypeId AND B.AssetAttributeId = C.AssetAttributeId)
INNER JOIN AssetVehicle As D ON (B.AssetId = D.AssetId)
INNER JOIN AssetCustomAttribute As E ON (B.AssetId = E.AssetId)
INNER JOIN AssetCustomAttributeDef As F ON (A.AssetTypeId = F.AssetTypeId AND E.AssetCustomAttributeDefId = F.AssetCustomAttributeDefId AND E.AssetTypeId = F.AssetTypeId)
INNER JOIN InventoryOrigin As G ON (B.WarehouseId = G.WarehouseId)
INNER JOIN EsnAsset As H ON (B.AssetId = H.AssetId)
INNER JOIN ESNTracking As I On (H.EsnID = I.EsnID)
INNER JOIN ESN As J ON (I.EsnId = J.EsnId)
WHERE B.AssetId = 'EsnNumber'

Hi,

I guess this is not enough information to help you, could you explain that in more detail ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

help with update query!

I have two tables... BillD and NewBillD

BillD has columns [order number], [price], [cost] etc.

NewBillD has just columns [order number], [price]

the order numbers in both tables are the same. I want to update billd with price from NewBillD.

Why will this query not work:

Update billd
set price = newbilld.price
where account = newbilld.account

Thanks!

Kenduh, forgot to join... Is it Friday yet?

help with update query

Hello all,

I have a database with table "A1" of fields
1. Id (Auto number)
2. Server(this matches the name field of A2)
3.Vendor
4.Model
5.ttyport
This table has 200 rows in it

and 2nd table A2 of fields

1.name(this matches the server field of A1)
2.ttyport
This has 100 rows in it.

I want to accomplish something like this,

Update A1 set A1.ttyport=A2.ttyport,A1.ttyport=A2.ttyport, where A1.Server=A2.name

if A1.Server IS NOT EQUAL A2.name then create another row in A1 with A2.name, A2.ttyport and leaving the rest of the fields with "null"

is it possible? can some one help me with the modification of the query ?

please help,
ThanksIf you want to do it using only one query, I'd say you can't do that - as far as I know, there's no way to do UPDATE and INSERT in the same statement.

You'll have to do the UPDATE with the one you already have (why do you have double "A1.ttyport=A2.ttyport" in it?) and write another one to INSERT data into A1 table.|||First update, than insert

update A1
set ttyport = (
select A2.ttyport
from A2
where A1.Server=A2.name
)
where exists (
select A2.ttyport
from A2
where A1.Server=A2.name
)

You can ommit where clause from update if you know that every record in A1 has 1 record in A2.

insert into A1 (servername, ttyport)
select name, ttyport
from A2
where not exists (
select A2.ttyport
from A2
where A1.Server=A2.name
)

I do not know what db and version you are on, but DB2 and Oracle have MERGE command which allows to do both operations in a single SQL.