Thread: ColdFusion sez this is valid SQL
select * from speakers where left(speaker_last_name,1) >= 'N'; which psql doesn't like is this really SQL standard or really an oracle thing? i know you can do a better job with select * from speakers where speaker_last_name ~* '^N'; the only reason I'm concerned is that our webmaster is working to integrate ColdFusion apps with PSQL (which work great) however, she's a real SQL novice (had to show her how to setup a sequence to map over to MS Access 'auto' datatype) et al ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------
haven't seen use of 'left()', but you could use substr() ...
On Tue, 2 Jan 2001, D. Duccini wrote:
>
> select * from speakers where left(speaker_last_name,1) >= 'N';
>
> which psql doesn't like
>
> is this really SQL standard or really an oracle thing?
>
> i know you can do a better job with
>
> select * from speakers where speaker_last_name ~* '^N';
>
> the only reason I'm concerned is that our webmaster is working to
> integrate ColdFusion apps with PSQL (which work great) however, she's a
> real SQL novice (had to show her how to setup a sequence to map over to MS
> Access 'auto' datatype) et al
>
>
>
>
> -----------------------------------------------------------------------------
> david@backpack.com BackPack Software, Inc. www.backpack.com
> +1 651.645.7550 voice "Life is an Adventure.
> +1 651.645.9798 fax Don't forget your BackPack!"
> -----------------------------------------------------------------------------
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
well, i just tried it on oracle 8i as well and it doesn't work there
either, so i'm going with the theory that the CF people are just on crack
"go back to your homes citizens, nothing to see here"
-duck
> haven't seen use of 'left()', but you could use substr() ...
>
> On Tue, 2 Jan 2001, D. Duccini wrote:
>
> >
> > select * from speakers where left(speaker_last_name,1) >= 'N';
> >
> > which psql doesn't like
> >
> > is this really SQL standard or really an oracle thing?
> >
> > i know you can do a better job with
> >
> > select * from speakers where speaker_last_name ~* '^N';
> >
> > the only reason I'm concerned is that our webmaster is working to
> > integrate ColdFusion apps with PSQL (which work great) however, she's a
> > real SQL novice (had to show her how to setup a sequence to map over to MS
> > Access 'auto' datatype) et al
> >
> >
> >
> >
> > -----------------------------------------------------------------------------
> > david@backpack.com BackPack Software, Inc. www.backpack.com
> > +1 651.645.7550 voice "Life is an Adventure.
> > +1 651.645.9798 fax Don't forget your BackPack!"
> > -----------------------------------------------------------------------------
> >
> >
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
>
-----------------------------------------------------------------------------
david@backpack.com BackPack Software, Inc. www.backpack.com
+1 651.645.7550 voice "Life is an Adventure.
+1 651.645.9798 fax Don't forget your BackPack!"
-----------------------------------------------------------------------------
select (speaker_last_name || ', ' || speaker_first_name) as fullname from speakers; man, i think its break time ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------
i think its time for afternoon coffee how do you select multiple columns but return them as a single column? ie, select lastname+', '+firstname as fullname from authors; of course it chokes on the + op ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------
On Tue, 2 Jan 2001, D. Duccini wrote:
> well, i just tried it on oracle 8i as well and it doesn't work there
> either, so i'm going with the theory that the CF people are just on crack
>
> "go back to your homes citizens, nothing to see here"
Isn't left() a Visual Basic thing?
-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
When you are in it up to your ears, keep your mouth shut.
> > well, i just tried it on oracle 8i as well and it doesn't work there > > either, so i'm going with the theory that the CF people are just on crack > > > > "go back to your homes citizens, nothing to see here" > >Isn't left() a Visual Basic thing? It's also a mysql thing.. don't know about VB.. ------------------------ Chris Smith http://www.squiz.net
On Tue, 2 Jan 2001, D. Duccini wrote: > > i think its time for afternoon coffee > > how do you select multiple columns but return them as a single column? > > ie, select lastname+', '+firstname as fullname from authors; > > of course it chokes on the + op SELECT lastname || ', ' || firstname AS fullname FROM authors;
On Wed, Jan 03, 2001 at 11:06:23AM +1100, Chris wrote: > > > > well, i just tried it on oracle 8i as well and it doesn't work there > > > either, so i'm going with the theory that the CF people are just on crack > > > > > > "go back to your homes citizens, nothing to see here" > > > >Isn't left() a Visual Basic thing? > > It's also a mysql thing.. don't know about VB.. It is a VB thing also. That would work in Acce Oh, I can't bear to finish. <choke> <gasp> <wheeze> -- Dr. David C. Merrill http://www.lupercalia.net Linux Documentation Project dmerrill@lupercalia.net Collection Editor & Coordinator http://www.linuxdoc.org Finger me for my public key Whenever you have need of anything, Once in the month, and better when the moon is full, You shall assemble in some secret place And adore the spirit of Me Who is Queen of all the Wise. -- from The Charge of the Goddess, Doreen Valiente
I have a table category with columns - category_id , main_id I would like to check that the main_id value exists in category_id in any row of the table category. Is it possible to have a foreign key referring to the current table ? Or should I be using a trigger to do it ? I'm thinking I have to use a trigger to do it - but want to make sure.
ok im dumb - but this is the novice list - references category(category_id) worked after Id done everything correct mwaples@optusnet.com.au wrote: > > I have a table category with columns - > category_id , main_id > > I would like to check that the main_id value exists in category_id in > any row of the table category. > > Is it possible to have a foreign key referring to the current table ? > Or should I be using a trigger to do it ? > > I'm thinking I have to use a trigger to do it - but want to make sure.
Try: "select * from speakers where #left(speaker_last_name,1)# >= 'N'" You forgot the # signs ;-) Poul L. Christiansen "D. Duccini" wrote: > select * from speakers where left(speaker_last_name,1) >= 'N'; > > which psql doesn't like > > is this really SQL standard or really an oracle thing? > > i know you can do a better job with > > select * from speakers where speaker_last_name ~* '^N'; > > the only reason I'm concerned is that our webmaster is working to > integrate ColdFusion apps with PSQL (which work great) however, she's a > real SQL novice (had to show her how to setup a sequence to map over to MS > Access 'auto' datatype) et al > > ----------------------------------------------------------------------------- > david@backpack.com BackPack Software, Inc. www.backpack.com > +1 651.645.7550 voice "Life is an Adventure. > +1 651.645.9798 fax Don't forget your BackPack!" > -----------------------------------------------------------------------------
doesn't work either ERROR: parser: parse error at or near "left" at least not under 6.5.3 On Wed, 3 Jan 2001, Poul L. Christiansen wrote: > Try: > "select * from speakers where #left(speaker_last_name,1)# >= 'N'" > > You forgot the # signs ;-) > > Poul L. Christiansen > > "D. Duccini" wrote: > > > select * from speakers where left(speaker_last_name,1) >= 'N'; > > > > which psql doesn't like > > > > is this really SQL standard or really an oracle thing? > > > > i know you can do a better job with > > > > select * from speakers where speaker_last_name ~* '^N'; > > > > the only reason I'm concerned is that our webmaster is working to > > integrate ColdFusion apps with PSQL (which work great) however, she's a > > real SQL novice (had to show her how to setup a sequence to map over to MS > > Access 'auto' datatype) et al > > > > ----------------------------------------------------------------------------- > > david@backpack.com BackPack Software, Inc. www.backpack.com > > +1 651.645.7550 voice "Life is an Adventure. > > +1 651.645.9798 fax Don't forget your BackPack!" > > ----------------------------------------------------------------------------- > ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------
Hmmmm, I don't know excatcly what you are trying to do, but I don't think you can use the Cold Fusion function Left. Try using the PostgreSQL function substr() instead, as someone else suggested. Poul L. Christiansen "D. Duccini" wrote: > doesn't work either > > ERROR: parser: parse error at or near "left" > > at least not under 6.5.3 > > On Wed, 3 Jan 2001, Poul L. Christiansen wrote: > > > Try: > > "select * from speakers where #left(speaker_last_name,1)# >= 'N'" > > > > You forgot the # signs ;-) > > > > Poul L. Christiansen > > > > "D. Duccini" wrote: > > > > > select * from speakers where left(speaker_last_name,1) >= 'N'; > > > > > > which psql doesn't like > > > > > > is this really SQL standard or really an oracle thing? > > > > > > i know you can do a better job with > > > > > > select * from speakers where speaker_last_name ~* '^N'; > > > > > > the only reason I'm concerned is that our webmaster is working to > > > integrate ColdFusion apps with PSQL (which work great) however, she's a > > > real SQL novice (had to show her how to setup a sequence to map over to MS > > > Access 'auto' datatype) et al > > > > > > ----------------------------------------------------------------------------- > > > david@backpack.com BackPack Software, Inc. www.backpack.com > > > +1 651.645.7550 voice "Life is an Adventure. > > > +1 651.645.9798 fax Don't forget your BackPack!" > > > ----------------------------------------------------------------------------- > > > > ----------------------------------------------------------------------------- > david@backpack.com BackPack Software, Inc. www.backpack.com > +1 651.645.7550 voice "Life is an Adventure. > +1 651.645.9798 fax Don't forget your BackPack!" > -----------------------------------------------------------------------------
yeah we solved it...i was just intrigued by the purported standard "SQL" example in the CF tutorial > Hmmmm, I don't know excatcly what you are trying to do, but I don't think you can use > the Cold Fusion function Left. > > Try using the PostgreSQL function substr() instead, as someone else suggested. > > Poul L. Christiansen
On Tue, 02 Jan 2001, D. Duccini wrote:
> how do you select multiple columns but return them as a single column?
>
> ie, select lastname+', '+firstname as fullname from authors;
>
> of course it chokes on the + op
This needs to be double vertical bars, not plus signs:
select lastname || ', ' || firstname as fullname from authors;
In psql, the "\do" command (list operators), shows:
op |left_arg |right_arg |result |description
---+----------+----------+---------+-----------------------------------------
...
|| |text |text |text |concatenate
--
George Young, Rm. L-204 gry@ll.mit.edu
MIT Lincoln Laboratory
244 Wood St.
Lexington, Massachusetts 02420-9108 (781) 981-2756
> In psql, the "\do" command (list operators), shows: > > op |left_arg |right_arg |result |description > ---+----------+----------+---------+----------------------------------------- > ... > || |text |text |text |concatenate slick. doesn't work on solaris 6.5.3 => \do || ERROR: regcomp failed with error empty (sub)expression but our days with 6.5.3 are numbered ;) ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------