Обсуждение: SELECT returnig a constant
I used to be able to return a constant value in a SELECT statement in ORACLE. I need to populate a table for testing, and I was going to do so like this: SELECT employee.id , project.proj_no , work_type.type , 'rate' 1 FROM employee CROSS JOIN project CROSS JOIN work_type; This statement works correctly, till I add the last "'rate' 1 line, then it returns a syntax error. How can I do this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On Tue, Oct 15, 2019 at 09:28:51AM -0400, stan wrote: > I used to be able to return a constant value in a SELECT statement in > ORACLE. I need to populate a table for testing, and I was going to do so > like this: > > SELECT > employee.id , > project.proj_no , > work_type.type , > 'rate' 1 > FROM employee > CROSS JOIN project > CROSS JOIN work_type; > > This statement works correctly, till I add the last "'rate' 1 line, then it > returns a syntax error. > > How can I do this? > Turns out this works. SELECT employee.id , project.proj_no , work_type.type , 1 as rate FROM employee CROSS JOIN project CROSS JOIN work_type; ~ -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On 15/10/2019 14:28, stan wrote: > I used to be able to return a constant value in a SELECT statement in > ORACLE. I need to populate a table for testing, and I was going to do so > like this: > > SELECT > employee.id , > project.proj_no , > work_type.type , > 'rate' 1 > FROM employee > CROSS JOIN project > CROSS JOIN work_type; > > This statement works correctly, till I add the last "'rate' 1 line, then it > returns a syntax error. I don't think you can use a number as a column name. Give it a different name and it should work: SELECT .... , 'rate' my_constant_name FROM ... Ray. -- Raymond O'Donnell // Galway // Ireland ray@rodonnell.ie
On Tue, 15 Oct 2019 at 14:35, Ray O'Donnell <ray@rodonnell.ie> wrote: > > On 15/10/2019 14:28, stan wrote: > > I used to be able to return a constant value in a SELECT statement in > > ORACLE. I need to populate a table for testing, and I was going to do so > > like this: > > > > SELECT > > employee.id , > > project.proj_no , > > work_type.type , > > 'rate' 1 > > FROM employee > > CROSS JOIN project > > CROSS JOIN work_type; > > > > This statement works correctly, till I add the last " 'rate' 1 line, then it > > returns a syntax error. > > I don't think you can use a number as a column name. Give it a different > name and it should work: Well you can but only if you quote the column alias SELECT employee.id , project.proj_no , work_type.type , 'rate' "1" FROM employee CROSS JOIN project CROSS JOIN work_type; but I really wouldn't recommend it. That way madness lies. Geoff
On Tue, Oct 15, 2019 at 8:25 AM Geoff Winkless <pgsqladmin@geoff.dj> wrote:
On Tue, 15 Oct 2019 at 14:35, Ray O'Donnell <ray@rodonnell.ie> wrote:
>
> On 15/10/2019 14:28, stan wrote:
> > I used to be able to return a constant value in a SELECT statement in
> > ORACLE. I need to populate a table for testing, and I was going to do so
> > like this:
> >
> > SELECT
> > employee.id ,
> > project.proj_no ,
> > work_type.type ,
> > 'rate' 1
> > FROM employee
> > CROSS JOIN project
> > CROSS JOIN work_type;
> >
> > This statement works correctly, till I add the last " 'rate' 1 line, then it
> > returns a syntax error.
I would assume you have the value and the alias backwards and you want
SELECT 1 AS "rate"
Both the double quotes around the alias and the AS keyword are optional.
Both the double quotes around the alias and the AS keyword are optional.
On 15/10/2019 15:01, stan wrote: > Thanks, as you can see from my SOLVED reply, I go that part figured out. > Now I am trying to figure out how to complete this. The SELECT returns more > than 1 row, and when I put that in the VALUES clause this does not work. Please reply to the list, rather than to me - reply-all should do the trick. :-) You're using cross-joins, which returns the cartesian product of the tables concerned, without any WHERE clause.... so if the tables are big you'll return a LOT of data. Ray. -- Raymond O'Donnell // Galway // Ireland ray@rodonnell.ie