Thread: combining columns in select
Hello, I have a table used in a java application that I have been working fine with ODBC & dbase that I want to move to pgsql. One of my select statements is as follows select lname + ', ' + fname as NAME from table ... If I try to enter this into psql, I get an error. This same statement works fine in the ODBC/dbase. Why doesn't this work? Is there a work around so I can get columns of concatenated text strings? Thanks, Doug
Hi, On Sat, 2 Oct 1999, Doug Thistlethwaite wrote: > select lname + ', ' + fname as NAME from table ... I think this is a parser bug. I recall there is a simple workaround: select (lname + ', ') + fname ... (use braces) regards, -- Jens Glaser Am Holderstrauch 13, 36041 Fulda, 0661/9429507 jens@jens.de
Thus spake Jens Glaser
> On Sat, 2 Oct 1999, Doug Thistlethwaite wrote:
> > select lname + ', ' + fname as NAME from table ...
>
> I think this is a parser bug. I recall there is a simple workaround:
>
> select (lname + ', ') + fname ... (use braces)
While you are correct in your solution, it isn't a parser bug. It just
follows the spec. There has been talk of allowing the first form as
an extension.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
At 03:15 +0200 on 03/10/1999, "D'Arcy" "J.M." Cain wrote: > While you are correct in your solution, it isn't a parser bug. It just > follows the spec. There has been talk of allowing the first form as > an extension. Since when has "+" become the string concatenation operator? It used to be "||" last time I checked. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
What I was trying to do is develop a java application using the ODBC and then move it to postgres & a linux database when I had everything working. Unfortunately, I have discovered that there are many differences in the "standard SQL" that each driver uses. I could not find any ODBC drivers that us local data that allow the "||" concatenation symbol. Most of them use the "+" symbol. I have found the same problem with "Memo" vs "text" fields as well. The ODBC driver for dbase (the one which seems to work best in my case) also uses the # symbol for dates while postgres likes the ' symbol. I guess I have to live with this and write separate query statements for each implementation. Doug Herouth Maoz wrote: > At 03:15 +0200 on 03/10/1999, "D'Arcy" "J.M." Cain wrote: > > > While you are correct in your solution, it isn't a parser bug. It just > > follows the spec. There has been talk of allowing the first form as > > an extension. > > Since when has "+" become the string concatenation operator? It used to be > "||" last time I checked. > > Herouth > > -- > Herouth Maoz, Internet developer. > Open University of Israel - Telem project > http://telem.openu.ac.il/~herutma
At 07:04 +0200 on 10/10/1999, Doug Thistlethwaite wrote: > What I was trying to do is develop a java application using the ODBC and then > move it to postgres & a linux database when I had everything working. > Unfortunately, I have discovered that there are many differences in the > "standard SQL" that each driver uses. > > I could not find any ODBC drivers that us local data that allow the "||" > concatenation symbol. Most of them use the "+" symbol. I have found the >same > problem with "Memo" vs "text" fields as well. The ODBC driver for dbase (the > one which seems to work best in my case) also uses the # symbol for dates > while postgres likes the ' symbol. > > I guess I have to live with this and write separate query statements for each > implementation. If you want to write a cross-platform JDBC application, you must avoid non-standard SQL. The SQL92 standard operator for string concatenation is just a single pipe char |. So neither "+" nor "||" are standard. In this case, I don't think either of the databases supports the actual standard. You can probably overcome this difficulty by setting a variable to either "||" or "+", according to the type of driver you get, and build the SQL queries using this variable. As for the other things, you really have to conform to the standard. Neither Memo nor Text are standard types. If you want standard, stick to CHARACTER VARYING. As for dates, there are escape sequences in JDBC which are supposed to convert dates from a common format to the format acceptable to your particular database. Use these, and you are "standard". Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma