Re sort

Hi all
I Have Table Num As Int , Name As NvarChar(20) I Need Trigger to Resort The
Field Num When I Change The num
Num Name
1 aaaaa
2 bbbbb
3 cccccc
4 ddddd
I Want when I Change the Num 1 To 2 Came Like
Num Name
1 bbbbb
2 aaaaaa
3 cccccc
4 ddddd
Re sort The Num In Trigger
Thankswhat are you talking about? Sorting is done at query time, by specifying a
sort order.
"Taha" <taha105@.hotmail.com> wrote in message
news:%23%23nlsb9jGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Hi all
>
> I Have Table Num As Int , Name As NvarChar(20) I Need Trigger to Resort
> The Field Num When I Change The num
>
> Num Name
> 1 aaaaa
> 2 bbbbb
> 3 cccccc
> 4 ddddd
>
> I Want when I Change the Num 1 To 2 Came Like
>
> Num Name
> 1 bbbbb
> 2 aaaaaa
> 3 cccccc
> 4 ddddd
>
> Re sort The Num In Trigger
>
> Thanks
>|||Let's make this problem more concrete. You want to put automobiles
into numbered parking spaces and move them around.
CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(170) NOT NULL);
Re-arrange the display order based on the parking_space column:
CREATE PROCEDURE SwapVehicles (@.old_parking_space INTEGER,
@.new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space
WHEN @.old_parking_space
THEN @.new_parking_space
ELSE parking_space + SIGN(@.old_parking_space - @.new_pos)
END
WHERE parking_space BETWEEN @.old_parking_space AND @.new_parking_space
OR parking_space BETWEEN @.new_parking_space AND @.old_parking_space;
When you want to drop a few rows, remember to close the gaps with this:
CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);|||Thank you CELKO
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150310619.762497.25420@.p79g2000cwp.googlegroups.com...
> Let's make this problem more concrete. You want to put automobiles
> into numbered parking spaces and move them around.
> CREATE TABLE Motorpool
> (parking_space INTEGER NOT NULL PRIMARY KEY
> CHECK (parking_space > 0),
> vin CHAR(170) NOT NULL);
> Re-arrange the display order based on the parking_space column:
> CREATE PROCEDURE SwapVehicles (@.old_parking_space INTEGER,
> @.new_parking_space INTEGER)
> AS
> UPDATE Motorpool
> SET parking_space
> = CASE parking_space
> WHEN @.old_parking_space
> THEN @.new_parking_space
> ELSE parking_space + SIGN(@.old_parking_space - @.new_pos)
> END
> WHERE parking_space BETWEEN @.old_parking_space AND @.new_parking_space
> OR parking_space BETWEEN @.new_parking_space AND @.old_parking_space;
> When you want to drop a few rows, remember to close the gaps with this:
> CREATE PROCEDURE CloseMotorpoolGaps()
> AS
> UPDATE Motorpool
> SET parking_space
> = (SELECT COUNT (M1.parking_space)
> FROM Motorpool AS M1
> WHERE M1.parking_space <= Motorpool.parking_space);
>

Re Order table

Imagine you have a subset of data which effectively looks like
id - int
sortNumber int
testData varchar
id sortNumber testData
1 1 test1
2 2 test2
3 3 test3
4 4 test4
5 5 test5
6 6 test6
What I want to do is to be able to move a record up and down within the
list.
Therefore, if I want to move record 4 to position 2 ie;
id sortNumber testData
1 1 test1
4 4 test4
2 2 test2
3 3 test4
5 5 test5
6 6 test6
But I want to keep the column 'sortNumber' in ascending order:
id sortNumber testData
1 1 test1
4 2 test4
2 3 test2
3 4 test4
5 5 test5
6 6 test6
The id column is effectively redundent and only used for selecting the
record for update purposes.
Hope someone out there understands what it is I'm trying to do and can
help.
Thanks in advance
Dave.
Whats the best way of approaching this without using a cursor?Try this one
CREATE TABLE T1
(
testData VARCHAR(10) NOT NULL PRIMARY KEY,
pos INT NOT NULL UNIQUE,
id INT NOT NULL UNIQUE
)
INSERT INTO T1 VALUES('A', 1,1)
INSERT INTO T1 VALUES('B', 2,2)
INSERT INTO T1 VALUES('C', 3,3)
INSERT INTO T1 VALUES('D', 4,4)
INSERT INTO T1 VALUES('E', 5,5)
INSERT INTO T1 VALUES('F', 6,6)
DECLARE
@.id AS VARCHAR(10),
@.newpos AS INT,
@.oldpos AS INT
SET @.id = 'D'
SET @.newpos = 2
SET @.oldpos = 4
SELECT T1.*
FROM T1, (SELECT pos AS idpos FROM T1 WHERE testData = @.id) AS P
ORDER BY
CASE
WHEN testData = @.id THEN @.newpos
WHEN pos BETWEEN idpos AND @.newpos THEN pos - 1
WHEN pos BETWEEN @.newpos AND idpos THEN pos + 1
ELSE pos
END
--
UPDATE T1 SET pos =CASE
WHEN testData = @.id THEN @.newpos
WHEN pos BETWEEN @.oldpos AND @.newpos THEN pos - 1
WHEN pos BETWEEN @.newpos AND @.oldpos THEN pos + 1
ELSE pos
END
SELECT * FROM T1
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1166435646.480710.14850@.79g2000cws.googlegroups.com...
> Imagine you have a subset of data which effectively looks like
> id - int
> sortNumber int
> testData varchar
> id sortNumber testData
> 1 1 test1
> 2 2 test2
> 3 3 test3
> 4 4 test4
> 5 5 test5
> 6 6 test6
> What I want to do is to be able to move a record up and down within the
> list.
> Therefore, if I want to move record 4 to position 2 ie;
> id sortNumber testData
> 1 1 test1
> 4 4 test4
> 2 2 test2
> 3 3 test4
> 5 5 test5
> 6 6 test6
> But I want to keep the column 'sortNumber' in ascending order:
> id sortNumber testData
> 1 1 test1
> 4 2 test4
> 2 3 test2
> 3 4 test4
> 5 5 test5
> 6 6 test6
> The id column is effectively redundent and only used for selecting the
> record for update purposes.
> Hope someone out there understands what it is I'm trying to do and can
> help.
> Thanks in advance
> Dave.
> Whats the best way of approaching this without using a cursor?
>

RDA Push Command Failing ...

Hi,

I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-

The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.

Cheers,

Jiggy!

Hi All,

I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?

Cheers very much,

Jiggy!

|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...

This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.

Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.

-Joshua

RDA Push Command Failing ...

Hi,

I am using the Pull command to pull two fields, on is the primary ID (int) non identity and the other is Description which comes down as an ntext type. This works fine but if I change the description and use the push command I get the following error:-

The Query processor could not produce a query from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

I am really stuck with this one so if anyone can shed some light on it I would be much appreciated.

Cheers,

Jiggy!

Hi All,

I have found the problem. Basically I am intergrating with another desktop / web based application making a PDA version so the SQL Server Database is already in place. I have found that each table has a cluster index on the primary key for performance issues. Can someone please advice if there is a fix for this or is the Push command not compatible with Clustered Index's?

Cheers very much,

Jiggy!

|||Just so you do not feel alone in the world... and MAYBE point you in a useful direction...

This has to do with the parameters being set by the connection, most likely. The way to test this is to run the same SQL statement in Query Analyzer and see if it works, if it does, then you need to attempt to set different paramters on your connection and/or command.

Hope this helps a little, if you find the solution, and/or paramters to pass, I'd love to hear about it.

-Joshua