Re: insert from a select

Is the query stated in the mail a working on.
Can we give a query for a column name in the SELECT statement.

"
Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
cl.classseq,*(select facility from esloc where esloc.pkid = cl.locationid)
as
facility*, cl.schedule from esclass cl where cl.pkid in (14507,14508)

"

On Thu, Nov 25, 2010 at 7:03 PM, John Fabiani <johnf(at)jfcomputer(dot)com> wrote:

> On Thursday, November 25, 2010 04:32:57 am Carla wrote:
> > Sorry. I forgot some columns:
> >
> > insert into tempclass (pkid, depart, sessionid, instrid, *classeq,
> > facility, schedule*)
> > Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> > cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
> > as facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
> >
> > 2010/11/25 Carla <cgourofino(at)hotmail(dot)com>
> >
> > > Try to explicit the column names. Something like:
> > >
> > > insert into tempclass (pkid, depart, sessionid, instrid)
> > >
> > > Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
> > > cl.classseq,(select facility from esloc where esloc.pkid =
> cl.locationid)
> > > as
> > > facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
> > >
> > > 2010/11/24 John Fabiani <johnf(at)jfcomputer(dot)com>
> > >
> > > Hi,
> > >
> > >> I have a strange issue that is mostly likely me not understanding
> > >>
> > >> something.
> > >> I always thought that an insert statement would accept any select
> > >> statement.
> > >> I'm guessing I am wrong.
> > >>
> > >> I have created a temporary table ("tempclass") that is exact match to
> an
> > >> existing table ('esclass').
> > >>
> > >> When I attempt to do the following
> > >> insert into tempclass Select cl.pkid, cl.depart, cl.sessionid,
> > >> cl.instrid, cl.classseq,(select facility from esloc where esloc.pkid =
> > >> cl.locationid) as
> > >> facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
> > >>
> > >> I get the following error:
> > >>
> > >> ERROR: column "schedule" is of type date but expression is of type
> > >> character
> > >> varying
> > >> LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select
> > >> fa...
> > >>
> > >> ^
> > >>
> > >> HINT: You will need to rewrite or cast the expression.
> > >>
> > >> The error makes no sense to me. But most important if I just run the
> > >> select
> > >> statement it works perfectly.
> > >>
> > >> Like I said the table "tempclass" (a temporary) is a dup of table
> > >> "esclass" so
> > >> none of it makes sense. Of course I did say I'm missing something.
> > >>
> > >> So why isn't the select statement working with the insert?
> > >>
> > >> Johnf
>
> Thanks to all - after reading everyone responses I slept on it. Today I
> realized that the order of the fields mattered as you all suggested.
>
> Thanks to all,
> Johnf
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

--
Regards,
----------------------------------------------
Trinath Somanchi,