Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
Hello!
Can anyone help me to use connectby() with my structure?
I cannot change the name of tables. It is a import!
--
Thomas Wegner
"Thomas Wegner" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
> Hello,
>
> i have a table like this:
>
> CREATE TABLE "public"."WINUSER" (
> "ID_WINUSER" INTEGER NOT NULL,
> "STATUS" INTEGER NOT NULL,
> "CUSTOMERID" VARCHAR(8) NOT NULL,
> "CUSTOMERPW" VARCHAR(100) NOT NULL,
> "EMAIL" VARCHAR(100) NOT NULL,
> "REF_ID_WINUSER" INTEGER,
> PRIMARY KEY("ID_WINUSER"),
> ) WITH OIDS;
>
> and will get the tree from this to fields:
>
> "ID_WINUSER" INTEGER NOT NULL,
> "REF_ID_WINUSER" INTEGER,
>
> i write this sql:
>
> SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
> '"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
>
> and get this error:
>
> ERROR: Query-specified return tuple not valid for Connectby: wrong number
> of columns
>
> How is the correct use of connectby() for me?
> --
> Thomas Wegner
>
> "Joe Conway" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> news:[EMAIL PROTECTED]
> > Merrall, Graeme wrote:
> > > Am I right in thinking that recursive procedures and procs returning
row
> > > sets would allow us to better emulate this behaviour? As anyone looked
> > > at it yet?
> > >
> >
> > See connectby() in contrib/tablefunc. Someone was working on SQL99
> > recursive queries but it didn't get done for 7.4 -- perhaps it will be
> > in 7.5. In the meantime, connectby() is in 7.3 and might work for you.
> >
> > HTH,
> >
> > Joe
> >
> >
> > ---(end of broadcast)---
> > TIP 8: explain analyze is your friend
> >
>
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
Hello,
i have a table like this:
CREATE TABLE "public"."WINUSER" (
"ID_WINUSER" INTEGER NOT NULL,
"STATUS" INTEGER NOT NULL,
"CUSTOMERID" VARCHAR(8) NOT NULL,
"CUSTOMERPW" VARCHAR(100) NOT NULL,
"EMAIL" VARCHAR(100) NOT NULL,
"REF_ID_WINUSER" INTEGER,
PRIMARY KEY("ID_WINUSER"),
) WITH OIDS;
and will get the tree from this to fields:
"ID_WINUSER" INTEGER NOT NULL,
"REF_ID_WINUSER" INTEGER,
i write this sql:
SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
'"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
and get this error:
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
How is the correct use of connectby() for me?
--
Thomas Wegner
"Joe Conway" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
> Merrall, Graeme wrote:
> > Am I right in thinking that recursive procedures and procs returning row
> > sets would allow us to better emulate this behaviour? As anyone looked
> > at it yet?
> >
>
> See connectby() in contrib/tablefunc. Someone was working on SQL99
> recursive queries but it didn't get done for 7.4 -- perhaps it will be
> in 7.5. In the meantime, connectby() is in 7.3 and might work for you.
>
> HTH,
>
> Joe
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem with LEFT JOIN
Hello, whats wrong with this SQL?: SELECT L.*, A."lastname" AS firma_value, T."string_val1" AS type_value FROM "lists" L, "typecode" T LEFT JOIN "adressen" A ON A."id_adressen"=L."firma" WHERE T."id_typecode"=L."lists_type" ORDER BY L."id_lists" I get this: ERROR: relation "l" does not exist This version work, but i need a LEFT JOIN (L."firma" can have NULL): SELECT L.*, A."lastname" AS firma_value, T."string_val1" AS type_value FROM "lists" L, "typecode" T, "adressen" A WHERE T."id_typecode"=L."lists_type" AND A."id_adressen"=L."firma" ORDER BY L."id_lists" -- Thomas Wegner ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Make a column case insensitive
I create every time i need this, a copy of this field filled out by a
trigger
on insert and update that holds the upper (or lower for you) value of
the orginal field like this:
create table "users" (
"email" varchar(255),
...
"u_email" varchar(255)
...
);
CREATE OR REPLACE FUNCTION "public"."user_function" () RETURNS trigger AS'
BEGIN
NEW."u_email" = UPPER(NEW."email");
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
CREATE TRIGGER "user_insert" BEFORE INSERT
ON "public"."user" FOR EACH ROW
EXECUTE PROCEDURE "public"."user_function"();
CREATE TRIGGER "user_update" BEFORE UPDATE
ON "public"."user" FOR EACH ROW
EXECUTE PROCEDURE "public"."user_function"();
and do a simple
select from blabla where u_email=:email
Param email = Upper(searchvalue).
This speed up any case insensitive searches.
-
Thomas Wegner
Cabrio Meter - The Weather Plugin for Trillian
http://trillian.wegner24.de/cabriometer
"Rod Taylor" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
> On Fri, 2004-08-06 at 11:29, Gordon Ross wrote:
> > Is it possible to make a column case insensitive, without having to
pepper your SELECTs with lots of lower() function calls (and forgetting to
do it at times !)
>
> You could make yourself a set returning function to do this job -- but
> that is probably just as difficult to remember when selecting data out
> of the table.
>
> create function abc(text) returns set of tab as 'select * from tab where
> col = lower($1);' language sql;
>
> select * from abc('sEaRcH_VaLuE');
>
>
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] tablefunc's in 8.0 Beta Win32 - where?
Sorry, i found it. I'm Win32 user and use installation from pgInstaller. I compile it and it run fine :-) ! - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer "Thomas Wegner" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Whera are tablefunc's like connectby in 8.0? > ----- > Thomas Wegner > Cabrio Meter - The Weather Plugin for Trillian > http://trillian.wegner24.de/cabriometer > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] tablefunc's in 8.0 Beta Win32 - where?
Whera are tablefunc's like connectby in 8.0? - Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SQL99 "with recursive" question
Hello, i think i am to stupid to use the "with recursive" SQL! I have a table: create table tree ( id_tree integer, parent_id integer ... ); In PostgreSQL i use the great connectby() function and in Oracle i simple use select * from tree start with id_tree = 0 connect by parent_id=prior id_tree But how must i use this with "with recursive" function? Every example i found uses two tables. I have only one! Please help me! I need this to use my oracle tables with the same speed in sybase 9.0. --------- Thomas Wegner Cabrio Meter - The Weather Plugin for Trillian http://trillian.wegner24.de/cabriometer ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
