AW: Embedded SQL issue.

Hi,

Just a warning! Be carefull when using double pipes (||) because it is not
international.
To be sure, not to get those errors just use concat instead:

Select Substr(digits(MyDatNum), 1, 4) concat '-' concat
       Substr(digits(MyDatNum), 5, 2) concat '-' concat
       Substr(digits(MyDatNum), 7, 2)

From MyTable

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Alan Shore
Gesendet: Monday, April 02, 2007 18:39
An: RPG programming on the AS400 / iSeries
Betreff: Re: Embedded SQL issue.


Jake - just realized that what you are using as concatenate ("||") may in
fact NOT be the correct representation. On my keyboard the concatenate (||)
is in fact shift with backslash (\) - NO spaces in  between each figure.
Just a suggestion


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

rpg400-l-bounces@xxxxxxxxxxxx wrote on 04/02/2007 12:25:32 PM:

I am just trying to concatenate two fields first there is a third 
field

also

customer_year. Wanted to keep it simple..:).

cheers,
-Jake.


On 4/2/07, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx>
wrote:

Doesn't a date require a year? Or am I missing something in your

statment?

Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
Big Farm Salads - Hurry in to enjoy the newest farm-fresh taste from

Bob

Evans, the "Home of Homestyle."


rpg400-l-bounces+michael_schutte=bobevans.com@xxxxxxxxxxxx wrote on
04/02/2007 12:17:40 PM:

Thanks for the suggestion, Alan. I tried and it failed with the 
same

reason.

I tried to use the 'cast' also and that failed too. Will keep 
trying

a

few

more options.

cheers,
-Jake.


On 4/2/07, Alan Shore <AlanShore@xxxxxxxx> wrote:

You can only concatenate ("||") character fields. The two fields

are

both

numeric.
Try the following (even though it looks NOT like what I 
described) select dec(customer_month) || '-' || 
dec(customer_day) as "Date"

from

iso.customer;



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill 
rpg400-l-bounces@xxxxxxxxxxxx wrote on 04/02/2007 11:50:25 AM:

Hello Guru's,
I am trying to execute a SQL statement in my RPG program but I 
am

getting

this error. I was wondering if anybody ran into this before.

SQL0440N  No authorized routine named "||" of type "FUNCTION"

having

compatible arguments was found.


my SQL statement is as follows.
select customer_month || '-' || customer_day as "Date" from

iso.customer

;

The data types of both customer_month and customer_day are

decimal(2).

I did a search on SQL0440N and it turned up as a bug. If so, 
has

it

been

fixed and I just need a 'patch' maybe?

Any help will be much appreciated. Thanks in advance.

cheers,

-Jake.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L)

mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L)

mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) 
mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) 
mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at 
http://archive.midrange.com/rpg400-l.