Thread: ColdFusion sez this is valid SQL

ColdFusion sez this is valid SQL

From
"D. Duccini"
Date:
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!"
-----------------------------------------------------------------------------


Re: ColdFusion sez this is valid SQL

From
The Hermit Hacker
Date:
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


Re: ColdFusion sez this is valid SQL

From
"D. Duccini"
Date:
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!"
-----------------------------------------------------------------------------


and the monkey pushes the button

From
"D. Duccini"
Date:
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!"
-----------------------------------------------------------------------------


amateur nite out

From
"D. Duccini"
Date:
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!"
-----------------------------------------------------------------------------



Re: ColdFusion sez this is valid SQL

From
"Brett W. McCoy"
Date:
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.



Re: ColdFusion sez this is valid SQL

From
Chris
Date:
> > 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


Re: amateur nite out

From
The Hermit Hacker
Date:
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;



Re: ColdFusion sez this is valid SQL

From
David Merrill
Date:
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

constraint question

From
mwaples@optusnet.com.au
Date:
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.

Re: constraint question

From
mwaples@optusnet.com.au
Date:
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.

Re: ColdFusion sez this is valid SQL

From
"Poul L. Christiansen"
Date:
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!"
> -----------------------------------------------------------------------------


Re: ColdFusion sez this is valid SQL

From
"D. Duccini"
Date:
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!"
-----------------------------------------------------------------------------



Re: ColdFusion sez this is valid SQL

From
"Poul L. Christiansen"
Date:
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!"
> -----------------------------------------------------------------------------


Re: ColdFusion sez this is valid SQL

From
"D. Duccini"
Date:
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


Re: amateur nite out

From
George Young
Date:
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

Re: amateur nite out

From
"D. Duccini"
Date:
> 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!"
-----------------------------------------------------------------------------