Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ConCatenating Fields

8 posts in Delphi Last posting was on 2003-09-08 14:26:20.0Z
Brian Paiine Posted on 2003-09-07 16:27:10.0Z
From: "Brian Paiine" <bpaine@direcway.com>
Newsgroups: advantage.delphi
Subject: ConCatenating Fields
Date: Sun, 7 Sep 2003 11:27:10 -0500
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 66.82.48.1
Message-ID: <3f5b5b99@solutions.advantagedatabase.com>
X-Trace: 7 Sep 2003 10:23:53 -0700, 66.82.48.1
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.82.48.1
Xref: solutions.advantagedatabase.com Advantage.Delphi:13900
Article PK: 1107336

Help,

I am trying to get the Name of an individual to show up in a Quick report. I
use the code below and
I get the Last Name and firstname with no space. Any suggestions? It must be
something simple I
am overlooking.

Thanks,

Brian

Select LName,', ',FName, address, zip from Clients
Order by Lname,Fname

I get:

SMITHTOM 10 ANYSTREET 13110

I want:

SMITH, TOM 10 ANYSTREET 13110


john blackburn Posted on 2003-09-07 16:47:52.0Z
From: john blackburn <john.blackburn@lintonhealy.co.uk>
Subject: Re: ConCatenating Fields
Newsgroups: advantage.delphi
Date: Sun, 07 Sep 2003 17:47:52 +0100
References: <3f5b5b99@solutions.advantagedatabase.com>
Lines: 30
User-Agent: KNode/0.7.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
NNTP-Posting-Host: 82.68.81.230
Message-ID: <3f5b6194@solutions.advantagedatabase.com>
X-Trace: 7 Sep 2003 10:49:24 -0700, 82.68.81.230
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!82.68.81.230
Xref: solutions.advantagedatabase.com Advantage.Delphi:13901
Article PK: 1107337


Brian Paiine wrote:

> Help,
>
> I am trying to get the Name of an individual to show up in a Quick report.
> I use the code below and
> I get the Last Name and firstname with no space. Any suggestions? It must
> be something simple I
> am overlooking.
>
> Thanks,
>
> Brian
>
> Select LName,', ',FName, address, zip from Clients
> Order by Lname,Fname
>
> I get:
>
> SMITHTOM 10 ANYSTREET 13110
>
> I want:
>
> SMITH, TOM 10 ANYSTREET 13110

Just put a normal QLABEL text item between the two fields on the quick
report sheet; there is no need to do anything in the selection.

John


"Jeff Kreider" Posted on 2003-09-07 18:46:25.0Z
From: "Jeff Kreider" <jeff -at- pctournaments REMOVE ME.com>
Newsgroups: advantage.delphi
References: <3f5b5b99@solutions.advantagedatabase.com>
Subject: Re: ConCatenating Fields
Date: Sun, 7 Sep 2003 13:46:25 -0500
Lines: 37
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 66.188.90.85
Message-ID: <3f5b7d72@solutions.advantagedatabase.com>
X-Trace: 7 Sep 2003 12:48:18 -0700, 66.188.90.85
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.188.90.85
Xref: solutions.advantagedatabase.com Advantage.Delphi:13902
Article PK: 1107338

I use the following to add fields together.

SELECT RTRIM( RTRIM( LName )+', '+FName) as WholeName

Jeff Kreider

"Brian Paiine" <bpaine@direcway.com> wrote in message
news:3f5b5b99@solutions.advantagedatabase.com...
> Help,
>
> I am trying to get the Name of an individual to show up in a Quick report.
I
> use the code below and
> I get the Last Name and firstname with no space. Any suggestions? It must
be
> something simple I
> am overlooking.
>
> Thanks,
>
> Brian
>
> Select LName,', ',FName, address, zip from Clients
> Order by Lname,Fname
>
> I get:
>
> SMITHTOM 10 ANYSTREET 13110
>
> I want:
>
> SMITH, TOM 10 ANYSTREET 13110
>
>


Brian Paiine Posted on 2003-09-07 23:51:05.0Z
From: "Brian Paiine" <bpaine@direcway.com>
Newsgroups: advantage.delphi
References: <3f5b5b99@solutions.advantagedatabase.com> <3f5b7d72@solutions.advantagedatabase.com>
Subject: Re: ConCatenating Fields
Date: Sun, 7 Sep 2003 18:51:05 -0500
Lines: 48
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 66.82.48.1
Message-ID: <3f5bc3b8@solutions.advantagedatabase.com>
X-Trace: 7 Sep 2003 17:48:08 -0700, 66.82.48.1
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.82.48.1
Xref: solutions.advantagedatabase.com Advantage.Delphi:13903
Article PK: 1107339

What I mean is how do I do this in code? It is easy within the SQL
of ARC.

Brian

"Jeff Kreider" <jeff -at- pctournaments REMOVE ME.com> wrote in message
news:3f5b7d72@solutions.advantagedatabase.com...
> I use the following to add fields together.
>
> SELECT RTRIM( RTRIM( LName )+', '+FName) as WholeName
>
> Jeff Kreider
>
>
> "Brian Paiine" <bpaine@direcway.com> wrote in message
> news:3f5b5b99@solutions.advantagedatabase.com...
> > Help,
> >
> > I am trying to get the Name of an individual to show up in a Quick
report.
> I
> > use the code below and
> > I get the Last Name and firstname with no space. Any suggestions? It
must
> be
> > something simple I
> > am overlooking.
> >
> > Thanks,
> >
> > Brian
> >
> > Select LName,', ',FName, address, zip from Clients
> > Order by Lname,Fname
> >
> > I get:
> >
> > SMITHTOM 10 ANYSTREET 13110
> >
> > I want:
> >
> > SMITH, TOM 10 ANYSTREET 13110
> >
> >
>
>


Karl Perry Posted on 2003-09-08 03:22:34.0Z
From: "Karl Perry" <kaperry@NOSPAMcablespeed.com>
Newsgroups: advantage.delphi
References: <3f5b5b99@solutions.advantagedatabase.com> <3f5b7d72@solutions.advantagedatabase.com> <3f5bc3b8@solutions.advantagedatabase.com>
Subject: Re: ConCatenating Fields
Date: Sun, 7 Sep 2003 20:22:34 -0700
Lines: 21
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 66.235.30.168
Message-ID: <3f5bf6cc@solutions.advantagedatabase.com>
X-Trace: 7 Sep 2003 21:26:04 -0700, 66.235.30.168
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.235.30.168
Xref: solutions.advantagedatabase.com Advantage.Delphi:13905
Article PK: 1107341


"Brian Paiine" <bpaine@direcway.com> wrote in message
news:3f5bc3b8@solutions.advantagedatabase.com...
> What I mean is how do I do this in code? It is easy within the SQL
> of ARC.

Um... you didn't say anything about that in your original post.

Do you mean you want to create an SQL statement on-the-fly, such as:

with MyAdsQuery do begin
SQL.Clear;
SQL.Add('SELECT * FROM MyTable');
Open;
end;

Is that what you mean? Or ... what exactly do you mean by "in code?"

Karl Perry


Brian Paiine Posted on 2003-09-08 10:58:58.0Z
From: "Brian Paiine" <bpaine@direcway.com>
Newsgroups: advantage.delphi
References: <3f5b5b99@solutions.advantagedatabase.com> <3f5b7d72@solutions.advantagedatabase.com> <3f5bc3b8@solutions.advantagedatabase.com> <3f5bf6cc@solutions.advantagedatabase.com>
Subject: Re: ConCatenating Fields
Date: Mon, 8 Sep 2003 05:58:58 -0500
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 66.82.48.1
Message-ID: <3f5c604e@solutions.advantagedatabase.com>
X-Trace: 8 Sep 2003 04:56:14 -0700, 66.82.48.1
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.82.48.1
Xref: solutions.advantagedatabase.com Advantage.Delphi:13909
Article PK: 1107345

That is exactly what I mean. I am having trouble with quotes and
parenthesis.
Also, can I use a field alias in an order by clause? Like:

Select Lastname+', '+firsname as Name from customers
order by Name.

I seem to get errors when I try this.

Thanks,

Brian

"Karl Perry" <kaperry@NOSPAMcablespeed.com> wrote in message
news:3f5bf6cc@solutions.advantagedatabase.com...
>
> "Brian Paiine" <bpaine@direcway.com> wrote in message
> news:3f5bc3b8@solutions.advantagedatabase.com...
> > What I mean is how do I do this in code? It is easy within the SQL
> > of ARC.
>
> Um... you didn't say anything about that in your original post.
>
> Do you mean you want to create an SQL statement on-the-fly, such as:
>
> with MyAdsQuery do begin
> SQL.Clear;
> SQL.Add('SELECT * FROM MyTable');
> Open;
> end;
>
> Is that what you mean? Or ... what exactly do you mean by "in code?"
>
> Karl Perry
>
>


Joachim Duerr (ADS Support) Posted on 2003-09-08 12:08:18.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: ConCatenating Fields
Newsgroups: Advantage.Delphi
References: <3f5b5b99@solutions.advantagedatabase.com> <3f5b7d72@solutions.advantagedatabase.com> <3f5bc3b8@solutions.advantagedatabase.com> <3f5bf6cc@solutions.advantagedatabase.com> <3f5c604e@solutions.advantagedatabase.com>
Organization: Extended Systems GmbH
User-Agent: XanaNews/1.15.6.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 195.2.185.25
Message-ID: <3f5c6322@solutions.advantagedatabase.com>
Date: 8 Sep 2003 05:08:18 -0700
X-Trace: 8 Sep 2003 05:08:18 -0700, 195.2.185.25
Lines: 23
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Delphi:13910
Article PK: 1107346


Brian Paiine wrote in <3f5c604e@solutions.advantagedatabase.com> :

> That is exactly what I mean. I am having trouble with quotes and
> parenthesis.
> Also, can I use a field alias in an order by clause? Like:
>
> Select Lastname+', '+firsname as Name from customers
> order by Name.
>
> I seem to get errors when I try this.

use either the same expression or the column number for the order by:

ORDER BY lastname+', '+firstname
ORDER BY 1


--
Joachim Duerr
Lead ADS Support, Extended Systems GmbH, Germany
advantage[AT]extendsys.de
- posted with Xananews 1.15.6.2 -
!!! ADS 7 Beta now available !!!


Karl Perry Posted on 2003-09-08 14:26:20.0Z
From: "Karl Perry" <kaperry@NOSPAMcablespeed.com>
Newsgroups: advantage.delphi
References: <3f5b5b99@solutions.advantagedatabase.com> <3f5b7d72@solutions.advantagedatabase.com> <3f5bc3b8@solutions.advantagedatabase.com> <3f5bf6cc@solutions.advantagedatabase.com> <3f5c604e@solutions.advantagedatabase.com>
Subject: Re: ConCatenating Fields
Date: Mon, 8 Sep 2003 07:26:20 -0700
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 64.122.99.34
Message-ID: <3f5c9271@solutions.advantagedatabase.com>
X-Trace: 8 Sep 2003 08:30:09 -0700, 64.122.99.34
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!64.122.99.34
Xref: solutions.advantagedatabase.com Advantage.Delphi:13913
Article PK: 1107348


"Brian Paiine" <bpaine@direcway.com> wrote in message
news:3f5c604e@solutions.advantagedatabase.com...
> That is exactly what I mean. I am having trouble with quotes and
> parenthesis.
> Also, can I use a field alias in an order by clause? Like:
>
> Select Lastname+', '+firsname as Name from customers
> order by Name.

Joachim replied about the ORDER BY clause - I just use the position of the
field in the SELECT statement, in this case 1.

Regarding the concatenation, your original attempt:

SELECT LastName, ', ', FirstName ...

was not a concatenation. It was selection of two fields and "making up" a
comma/space column in the result set.

To concatenate in code, enclose the quoted stuff in their own sets of
quotes:

with MyAdsQuery do begin
SQL.Clear;
SQL.Add('SELECT LastName, ' + ''', ''' + 'FirstName AS Name FROM
Customers');
Open;
end;

''', ''' is single-quote, single-quote, single-quote, comma, space,
single-quote, single-quote, single-quote. Since in Delphi you must
"double-up" a single quote to put it inside a string, you must actually have
three single-quote marks in a row: open quote, quote, escape (another
quote).

Since you are building a quoted string in Delphi code, you can also use the
Delphi function QuotedString or AnsiQuotedString. Delphi Help has examples
of usage.

HTH,

Karl Perry