Re: SQL RTRIM function -- MIDRANGE-L

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
midrange-l-request@xxxxxxxxxxxx
Sent: Tuesday, April 08, 2014 12:00 PM
To: midrange-l@xxxxxxxxxxxx
Subject: MIDRANGE-L Digest, Vol 13, Issue 531

Send MIDRANGE-L mailing list submissions to
midrange-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/midrange-l
or, via email, send a message with subject or body 'help' to
midrange-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
midrange-l-owner@xxxxxxxxxxxx

On Tue, 08 Apr 2014 11:47:43, Vernon Hamberg wrote:

More than one way to skin a cat - er, trim a string, right?

Glad you got it working.

It gets better. Had a related problem with some pesky leading zeros in an
alphanumeric field. Here's examples of how some of the data looked in a
char(10) field, with b=blank:

bbbb123456 (good)
0000123456 (not good)
bb00123456 (not good)

Basically using the same approach that Tom & Eric posted up, I did two
iterations through the data. In the first, changing the leading blanks to
0's, and second, concatenated blanks to the trimmed-down significant digits.
(Actually concatenated to a substring result, using the strip function to
establish my beginning position and length parameters).

Thanks again to everyone!

--
Buzz

On 4/8/2014 8:34 AM, Buzz Fenner wrote:

Guys,

After reading through Eric's initial post stating that trim would not work
if the field was not defined as
varying, I sort of dismissed the offered solutions out of hand since the
field in question was char(20) (from
DDS).

But then I saw Jeff's success with Tom's suggestion so I went back, tried
it, and it worked like a charm. Eric's did too, once I fixed a missing

')'.

Guess I got a little confused after being informed that the trim function
wouldn't work on a non-varying field but being presented with solutions
using Rtrim (or should I say wouldn't get me the results I was looking

for).

Obviously, I mis-read what I was reading! So, like Jeff, I'm putting these
in the old toolbox!

Thanks again!

--
Buzz

------------------------------

message: 4
date: Tue, 8 Apr 2014 16:48:29 +0000
from: "Koester, Michael" <mkoester@xxxxxxxxxxxxx>
subject: RE: Creating a WSDL for someone else's web service

Thanks for that Charles. I agree with:

You don't appear to have either. :) Instead, you've got a "home grown"
web service that appears to exchange XML. Those are fun.

And I believe I will be sending xml as POSTs.
When I point the browser to the URL, I get:
<status>
<message>Invalid data.</message>
<code>040</code>
</status>

My previous experience with WSDL2RPG (ala Thomas Raddatz) was most
successful on another project so I was hoping to follow that path. All the
communications, XLS parsing, CCSID conversion, and other "plumbing" was
generated and ready to put data into and execute. But it required the WSDL
to generate the stub programs.

So you're suggesting that is not an option?
-- Michael
~~~~~~~~~~~~~~~~

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, April 08, 2014 12:09 PM
To: Midrange Systems Technical Discussion
Subject: Re: Creating a WSDL for someone else's web service

Every web service "works" the same since they all use HTTP. You've got
a URL that you use HTTP POST/GET/PUT/DELETE...methods against.

SOAP is simply standardized way to publish/consume a WS that relies on
exchanging XML messages.

REST is a term used to define certain non-SOAP defined web services
that relies mainly on arguments passed on the URL.

You don't appear to have either. :) Instead, you've got a "home grown"
web service that appears to exchange XML. Those are fun.

What you need to find out is what HTTP method needs to be used to send
the XML you've been given. Likely, it's a POST. So you'd build your
XML and use the appropriate HTTP_POST_xxxx() method of Scott's HTTP
API.

Did you get anything more from the vendor about using the service? For
example, one vendor I'm working with gave an example that used cURL (a
common HTTP API like command line utility)

Another other is to open the WS URL in a browser and see what's there.

Charles

On Tue, Apr 8, 2014 at 10:37 AM, Koester, Michael <mkoester@data-
east.com>wrote:

Thanks Charles.
I think I may have a problem though, in that I don't know the details
that I may (or may not?) need. For starters, I don't yet know if I

am

dealing with a SOAP or REST service. Or can I declare that in my

WSDL

and expect the service to play along with whichever I've chosen?
The reference you pointed me to says:
"A WSDL description contains all the details of a Web service,

including:

The service's URL. [Got that]
The communication mechanisms it understands. [No clue - is that
needed?]
What operations it can perform. [Got that]
The structure of its messages." [Apparently most are strings, so

i

can probably fake that]

...and it also says:
"There are four child elements of description that together
encapsulate all of the details about a Web service:
types
interface
binding
service"
Not sure what I need for those.

I'm not there yet. I do appreciate the help though.
-- Michael
~~~~~~~~~~~~~~~~~~

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, April 08, 2014 9:47 AM
To: Midrange Systems Technical Discussion
Subject: Re: Creating a WSDL for someone else's web service

Michael,

Personally, I'd just build the XML manually. It's pretty basic and
not SOAP based; SOAP services are where a WSDL really helps.

If you really want to look at creating a WSDL manually, this might
help:
https://www.ibm.com/developerworks/webservices/library/ws-restwsdl/

Charles

On Tue, Apr 8, 2014 at 9:33 AM, Koester, Michael <mkoester@data-
east.com>wrote:

New project: Communicate through a web service to some other
outfit to set up, modify, enable/disable, etc., email and other

services.

This will be integrated with our Service Order maintenance

application

running on our IBM Power, in ILE-RPG. I've consumed web services
before. I can handle this.

First obstacle: I am told that this is to be done through their
"web service" (quotes intentional), but when I requested access

to

the WSDL, I was referred to the care and feeding document. Said
document is a PDF containing descriptions of how to perform
various operations through various snippets of XML. Cool. I
asked again where to find the WSDL, and was told "haven't got

one." Oh.

Perhaps I can construct one, and paste in the XML pieces? This
web service (my contact refers to the "app") is not publicly
available, which may explain why they don't have a WSDL. Perhaps
this has never been requested by other clients they've invited to
share the app

with.

Question: How do I fudge up a WSDL that will include all the
necessary pieces to satisfy WSDL2RPG stub generators?

Second obstacle: I'm still waiting for SysOp to get the updates
installed to allow my access to "Web Administration for i". I

suspect

there is a wizard or two there that would help, but I can't get
there yet. We are on
7.1 (still waiting for TR7 too).

One of the operations from the care and feeding manual looks like

this:

[I do have the URL of where the XML gets sent to, as well as the
user and password referenced in the authenticate tag]

To suspend the user, issue the following command:

<?xml version="1.0"?>
<authenticate>
<user>[*****]</user>
<pass>[********]</pass>
<domain>gsinet.net</domain>
</authenticate>
<archive_user>
<user>
<userid>bob</userid>
<archive_code>NOPAY</archive_code>
</user>
</archive_user>

So given that I may be rolling my own WSDL by hand, can someone
point me to some useful details? My goal is to have a suitable
WSDL to be accessed by WSDL2RPG.

Many thanks,
Michael Koester
Programmer/Analyst

DataEast

------------------------------

message: 5
date: Tue, 8 Apr 2014 12:50:23 -0400
from: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
subject: Re: getting error on create view

ah yes sorry bout tht.

On Tue, Apr 8, 2014 at 12:40 PM, Steve Richter
<stephenrichter@xxxxxxxxx>wrote:

on the WHERE clause IHORDT is not qualified:

create view mklib.CUST5000A AS
select a.IHDOCD, a.IHENT#, a.IHSFX#, a.IHINV#,
a.ADINTA,
b.IHRIDC, b.IHCOM#, b.IHIDC#, b.IHENT#,
b.IHSFX#,
b.IHINV#, b.IHDOCT, b.IHORD#, b.IHREL#,
b.IHORDT,
b.IHFL50, b.IHDOCD, b.IHFL51, b.IHORDD
FROM mklib.CUST5000 a
join ASTDTA.OEINHDIH b
ON a.IHENT# = b.IHENT#
WHERE b.IHORDT IN ('COR', 'COE')

On Tue, Apr 8, 2014 at 12:16 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

Am getting error, 'Column List required'. When i run this plainly for
viewing in SQL it is fine.

create view mklib.CUST5000A AS SELECT
ALL T01.IHDOCD, T01.IHENT#, T01.IHSFX#, T01.IHINV#,
T01.ADINTA,
T02.IHRIDC, T02.IHCOM#, T02.IHIDC#, T02.IHENT#,
T02.IHSFX#,
T02.IHINV#, T02.IHDOCT, T02.IHORD#, T02.IHREL#,
T02.IHORDT,
T02.IHFL50, T02.IHDOCD, T02.IHFL51, T02.IHORDD

FROM mklib.CUST5000 T01 INNER JOIN
ASTDTA.OEINHDIH T02
ON T01.IHENT# = T02.IHENT#

WHERE IHORDT IN ('COR', 'COE')
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

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

------------------------------

message: 6
date: Tue, 8 Apr 2014 12:56:53 -0400
from: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
subject: Re: getting error on create view

still not working. i have the qualfier now

FROM mklib.CUST5000 T01 INNER JOIN
ASTDTA.OEINHDIH T02
ON T01.IHENT# = T02.IHENT#

WHERE T02.IHORDT IN ('COR', 'COE')

On Tue, Apr 8, 2014 at 12:50 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

ah yes sorry bout tht.

On Tue, Apr 8, 2014 at 12:40 PM, Steve Richter

<stephenrichter@xxxxxxxxx>wrote:

on the WHERE clause IHORDT is not qualified:

create view mklib.CUST5000A AS
select a.IHDOCD, a.IHENT#, a.IHSFX#, a.IHINV#,
a.ADINTA,
b.IHRIDC, b.IHCOM#, b.IHIDC#, b.IHENT#,
b.IHSFX#,
b.IHINV#, b.IHDOCT, b.IHORD#, b.IHREL#,
b.IHORDT,
b.IHFL50, b.IHDOCD, b.IHFL51, b.IHORDD
FROM mklib.CUST5000 a
join ASTDTA.OEINHDIH b
ON a.IHENT# = b.IHENT#
WHERE b.IHORDT IN ('COR', 'COE')

On Tue, Apr 8, 2014 at 12:16 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

Am getting error, 'Column List required'. When i run this plainly for
viewing in SQL it is fine.

create view mklib.CUST5000A AS SELECT
ALL T01.IHDOCD, T01.IHENT#, T01.IHSFX#, T01.IHINV#,
T01.ADINTA,
T02.IHRIDC, T02.IHCOM#, T02.IHIDC#, T02.IHENT#,
T02.IHSFX#,
T02.IHINV#, T02.IHDOCT, T02.IHORD#, T02.IHREL#,
T02.IHORDT,
T02.IHFL50, T02.IHDOCD, T02.IHFL51, T02.IHORDD

FROM mklib.CUST5000 T01 INNER JOIN
ASTDTA.OEINHDIH T02
ON T01.IHENT# = T02.IHENT#

WHERE IHORDT IN ('COR', 'COE')
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

------------------------------

message: 7
date: Tue, 08 Apr 2014 11:59:45 -0500
from: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
subject: Re: getting error on create view

I think I got it!

When you put your cursor on the message and pressed F1 - you DID do
that, right? What is the explanatory text - 2nd-level text?

I took a guess and went to the docs to find out about column lists.
According to the docs -

"You must specify a list of column names if the result table of the
subselect has duplicate column names"

The column names normally inherit the names from the result table,
unless there are certain issues. You have one of those - the one I cited.

The qualified names are not used in the view - so they don't make the
names unique.

You have T01.IHDOCD and T02.IHDOCD -- those need to be given distinct
names in the view.

HTH
Vern

On 4/8/2014 11:26 AM, Hoteltravelfundotcom wrote:

All removed but same error

On Tue, Apr 8, 2014 at 12:18 PM, Jeff Young <jyoung0950@xxxxxxxxx> wrote:

try without the ALL

Jeff Young
Sr. Programmer Analyst

On Tue, Apr 8, 2014 at 12:16 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

Am getting error, 'Column List required'. When i run this plainly for
viewing in SQL it is fine.

create view mklib.CUST5000A AS SELECT
ALL T01.IHDOCD, T01.IHENT#, T01.IHSFX#, T01.IHINV#,
T01.ADINTA,
T02.IHRIDC, T02.IHCOM#, T02.IHIDC#, T02.IHENT#,
T02.IHSFX#,
T02.IHINV#, T02.IHDOCT, T02.IHORD#, T02.IHREL#,
T02.IHORDT,
T02.IHFL50, T02.IHDOCD, T02.IHFL51, T02.IHORDD

FROM mklib.CUST5000 T01 INNER JOIN
ASTDTA.OEINHDIH T02
ON T01.IHENT# = T02.IHENT#

WHERE IHORDT IN ('COR', 'COE')
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

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

------------------------------