Re: sql to output row as csv string

You guys must have missed my earlier post where i said this file has 306 columns. :)

I ended up using cpytoimpf and cpyfrmimpf back to a flat file to handle the heavy format lifting.
Then processed the string adding additional formatting as needed. Worked well. Generic and will work for other files without getting into implicitly mucking with columns.

But thank you for the suggestions.

Jay

On Sep 20, 2018, at 6:54 PM, Roger Harman <roger.harman@xxxxxxxxxxx> wrote:

I used that technique several years ago to easily build delimited output.

Sample:
d hdr ds template
d SomeField 10a inz
d 1a inz('|')
d Another 25a inz
d 1a inz('|')

Just use RESET <dsName> before building each record. Do NOT use CLEAR.

Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power





From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Sent: Thursday, September 20, 2018 12:17 PM
To: Midrange Systems Technical Discussion
Subject: Re: sql to output row as csv string
Jay

I have a crazy idea!

Make a data structure that has the fields, it can be a dcl-ds with
extname() kind of thing.

Let that be a host structure for reading a row into, maybe using the SET
or VALUES function.

Have a second DS that has all the fields of the first - you'll have to
build this manually - and have separator subfields, whatever is needed
according to data type - commas and full quotes where needed.

When you execute the SET and have the 1st DS filled, do an EVAL-CORR to
get the CSV formatting.

Hey, it's been a downpour here, and I was worried my umbrella might turn
into a lightning rod! So go figure, right?

Cheers
Vern

On 9/20/2018 1:00 PM, Jay Vaughn wrote:
John,

because our non-iseries people used a open source data replication tool
that placed sql triggers on our files that run incredibly long.
I'm refactoring that trigger but the end product is to output a string of
the before/after row in a csv string format.
that row is to be inserted into another table that is polled by the
off-platform process and pull the data off platform...

so before we go off re-designing the process... this is what we are stuck
with so I'm simply looking for a method to extract the row data into a
string like below. (because I only care about the before/after row that I
have access via my trigger buffer) :)..

"12345","XYZ"," ", etc...

last call before I write my own procedure....???

Jay



On Thu, Sep 20, 2018 at 11:05 AM John Yeung <gallium.arsenide@xxxxxxxxx>
wrote:

On Thu, Sep 20, 2018 at 10:22 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

I can't believe there is not a function to convert an entire row (mixed
data type columns) into
a comma separate string...

As I was saying before, your use case is on the unusual side. Most
people are not really looking for a CSV string, ultimately. Most
people want to build a CSV file[1]. As such, I don't think it's
surprising at all that this function doesn't exist (in any readily
accessible way).

Why do you want a string? What are you going to do with it? Maybe if
you provide more context, an even better approach may present itself.

John Y.

[1]Actually, a large portion of people who think they want a CSV file
*really* want an Excel file, but they'll settle for CSV because they
perceive that to be easier.
--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD
--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD