Horton

Thread

  1. ColdFusion sez this is valid SQL

    D. Duccini <duccini@backpack.com> — 2001-01-02T22:08:12Z

    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!"   
-----------------------------------------------------------------------------

  2. Re: ColdFusion sez this is valid SQL

    Marc G. Fournier <scrappy@hub.org> — 2001-01-02T22:17:49Z

    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

  3. Re: ColdFusion sez this is valid SQL

    D. Duccini <duccini@backpack.com> — 2001-01-02T22:21:35Z

    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!"   
-----------------------------------------------------------------------------

  4. amateur nite out

    D. Duccini <duccini@backpack.com> — 2001-01-02T22:45:48Z

    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!"   
-----------------------------------------------------------------------------

  5. and the monkey pushes the button

    D. Duccini <duccini@backpack.com> — 2001-01-02T22:52:31Z

    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!"   
-----------------------------------------------------------------------------

  6. Re: ColdFusion sez this is valid SQL

    Brett W. McCoy <bmccoy@chapelperilous.net> — 2001-01-02T23:31:21Z

    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.

  7. Re: amateur nite out

    Marc G. Fournier <scrappy@hub.org> — 2001-01-03T00:04:41Z

    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;

  8. Re: ColdFusion sez this is valid SQL

    Chris <csmith@squiz.net> — 2001-01-03T00:06:45Z

    > > 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

  9. Re: ColdFusion sez this is valid SQL

    David Merrill <dmerrill@lupercalia.net> — 2001-01-03T03:32:21Z

    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

  10. constraint question

    mwaples@optusnet.com.au — 2001-01-03T10:37:27Z

    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.

  11. Re: constraint question

    mwaples@optusnet.com.au — 2001-01-03T11:25:32Z

    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.

  12. Re: ColdFusion sez this is valid SQL

    Poul L. Christiansen <poulc@cs.auc.dk> — 2001-01-03T12:00:50Z

    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!"
> -----------------------------------------------------------------------------

  13. Re: ColdFusion sez this is valid SQL

    D. Duccini <duccini@backpack.com> — 2001-01-03T13:59:56Z

    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!"   
-----------------------------------------------------------------------------

  14. Re: ColdFusion sez this is valid SQL

    D. Duccini <duccini@backpack.com> — 2001-01-03T14:25:27Z

    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

  15. Re: ColdFusion sez this is valid SQL

    Poul L. Christiansen <poulc@cs.auc.dk> — 2001-01-03T14:25:33Z

    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!"
> -----------------------------------------------------------------------------

  16. Re: amateur nite out

    George Young <gry@ll.mit.edu> — 2001-01-03T14:29:42Z

    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

  17. Re: amateur nite out

    D. Duccini <duccini@backpack.com> — 2001-01-03T14:48:44Z

    > 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!"   
-----------------------------------------------------------------------------