Hi,
I manage range in a database:
CREATE TABLE range(level INTEGER,
roomIdMin INTEGER,
roomIdMax INTEGER,
CHECK (roomIdMin<=roomIdMax) );
INSERT INTO range VALUES (1,100,200);
INSERT INTO range VALUES (2,300,400);
| level | roomIdMin | roomIdMax |
+------------+------------+------------+
| 1 | 100 | 200 |
| 2 | 300 | 400 |
I manage range insertion through a Trigger to have automatique range merging
(if level are =). For instance :
INSERT INTO range VALUES (1,150,250);
| level | roomIdMin | roomIdMax |
+------------+------------+------------+
| 1 | 100 | 250 |
| 2 | 300 | 400 |
Idem for all insertion case...
For range deletion, I would like to do be able to delete for instance
[150,200] and get:
| level | roomIdMin | roomIdMax |
+------------+------------+------------+
| 1 | 100 | 149 |
| 1 | 201 | 250 |
| 2 | 300 | 400 |
So I cannot use Trigger because range [150,200] is not an entry in range
table.
So I write a "generic" TRANSACTION where I process all deletation case but I
have a SQL syntaxe problem with one case (the "split" case):
If I have in my database:
| level | roomIdMin | roomIdMax |
+------------+------------+------------+
| 1 | 100 | 400 |
And I delete [200,300], I would like to have
| level | roomIdMin | roomIdMax |
+------------+------------+------------+
| 1 | 100 | 199 |
| 1 | 301 | 400 |
SO I can:
Case 1:
- delete [100,400]
- insert [100,199]
- insert [301,400]
Case 2:
- Update [100,400] to [100,199]
- insert [301,400]
So I would like to write something like that:
CASE WHEN
EXISTS( SELECT @max=suid_max, @min=roomIdMin FROM range
WHERE 200 BETWEEN roomIdMin AND roomIdMax
AND 400 BETWEEN roomIdMin AND roomIdMax)
THEN
DELETE FROM range WHERE roomidm...@min AND roomidm...@max;
INSERT INTO range VALUES (1, @min, 199 );
INSERT INTO range VALUES (1, 301 , @max );
END
or
CASE WHEN
EXISTS( SELECT @max=suid_max, @min=roomIdMin FROM range
WHERE 200 BETWEEN roomIdMin AND roomIdMax
AND 400 BETWEEN roomIdMin AND roomIdMax)
THEN
UPDATE range SET roomIdMax=199 WHERE roomidm...@min AND roomidm...@max;
INSERT INTO range VALUES (1, 301 , @max );
END
But SQLite generate a syntaxe error... Because I think we cannot use CASE
WHEN THEN END outside and instruction...
I tried lots of syntax without success...
How can I write that in SQL for SQLite?
Regards,
Sylvain
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
