× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. September 2018

RE: sql to output row as csv string

So adding this is way too complicated?

Create table LIB/TABLENAME as (select [*|f,f,f,f,f,] from atable where 1=1) with data;
Call qcmdexc('CPYTOSTMF LIB/TABLENAME ssss etc.');// from at least 7.2 forward no need to use the length portion of QCMDEXC() SP.
Call qcmdexc('CHGAUT ssss ...');

No need for a SP unless you want that.

This can even be run from a command line using the RSS plugin of ACS, if its installed on the IBM server and up to date.

9.1.31 RSS
/PLUGIN=rss /SYSTEM=<system> /DATABASE=<database> [/FILE=<file>] [/SQL=<file>] [/AUTORUN=<0,1>]

/FILE=<filename> - Open the specified file
/SQL=<filename> - Open the specified file
/AUTORUN=<1,0> - Automatically run the script

-Matt


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jay Vaughn
Sent: Wednesday, September 19, 2018 2:40 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: sql to output row as csv string

you can call an external rpg from the sql stored procedure, that can then execute the cl command... but wayyyy too much engineering for what I'm trying to accomplish...
but thx for the ideas.

On Wed, Sep 19, 2018 at 4:34 PM Rob Berendt <rob@xxxxxxxxx> wrote:

Can you call a CL command, like CPYTOIMPF, from a stored procedure?
If so, then use the first suggested SQL statement to subset and order
the data, then call the command.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.dekko.com&d=Dw
IGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2
RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7i
qI&s=K8fKxLmJ7_PFHRbGxpHc_gqKpGDA-1Evp2Djn4ubcFs&e=





From: "Jay Vaughn" <jeffersonvaughn@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 09/19/2018 03:47 PM
Subject: Re: sql to output row as csv string
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



i understand there are many ways to arrive at the data but I should
have been specific and say it must be in the form of an sql select statement.
I want to keep this confined to an sql procedure.

On Wed, Sep 19, 2018 at 3:41 PM Diego E. KESSELMAN
<diegokesselman@xxxxxxxxx>
wrote:

Have you tried with something like:

CREATE TABLE MYLIB1/MYFILE1 AS (SELECT * FROM XYZ/ABC123)

and then

CPYTOIMPF FROMFILE(MYLIB1/MYFILE1 )
TOSTMF('/home/USER1/MYFILE1.csv' )
MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF) DATFMT(*YYMD)
TIMFMT(*JIS) ADDCOLNAM(*SQL)

??


Regards

Diego E. KESSELMAN

El 19/9/18 a las 14:32, Jeff Crosby escribió:
I note that you did the search including the term "sql2csv"
meaning
you
knew sql2csv existed. If Jay didn't know that, he would not
necessarily
come up with it as a search term.

Just sayin'

On Wed, Sep 19, 2018 at 3:25 PM, Rob Berendt <rob@xxxxxxxxx> wrote:

Something like SQL2CSV?
https://urldefense.proofpoint.com/v2/url?u=http-3A__lmgtfy.com_-3
Fq-3Dsql2csv-2Band-2B-2522IBM-2Bi-2522&d=DwIGaQ&c=2S-2xx8Cum_thMf
Ws-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-V
hnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=82PQVFO
NH-EreU51o8jBxpBa1-ek1Jufg-_p3qOE3ig&e=


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept
1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.dekko.com
&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1
Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQ
hEPgpAxUNPo7n7iqI&s=K8fKxLmJ7_PFHRbGxpHc_gqKpGDA-1Evp2Djn4ubcFs&e
=





From: "Jay Vaughn" <jeffersonvaughn@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <
midrange-l@xxxxxxxxxxxx>
Date: 09/19/2018 03:21 PM
Subject: sql to output row as csv string
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Been searching for a way to output a csv string from an sql
select statement and have found nothing good.

i simply want to take the row selected and output the column
values
such
as...
"data1", "data2", "data3", etc...

I do NOT want to have to implicitly name and concat each column
together
in
the statement... I'll need a function to do that for me.

Is there anything avail to do this?

Jay
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midran
ge.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs
-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-Vhn
RH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=02DKqLjhW
hV7sg9CnQ33CwVZQQGjjtkOh2FVYyZaBxo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=MnPBibFFubAdU1SXgXLTPdbjKcmDKUUwEamaXeJjSZM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEad
iD&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2K
O1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklb
gQhEPgpAxUNPo7n7iqI&s=0fn1b7xQlU7DSW-jgMXjDRecEuLito5a3QjtucPyzb8
&e=


--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midran
ge.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs
-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-Vhn
RH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=02DKqLjhW
hV7sg9CnQ33CwVZQQGjjtkOh2FVYyZaBxo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=MnPBibFFubAdU1SXgXLTPdbjKcmDKUUwEamaXeJjSZM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEad
iD&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2K
O1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklb
gQhEPgpAxUNPo7n7iqI&s=0fn1b7xQlU7DSW-jgMXjDRecEuLito5a3QjtucPyzb8
&e=



--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.
com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQ
TwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m
=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=02DKqLjhWhV7sg9CnQ33C
wVZQQGjjtkOh2FVYyZaBxo&e= or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=MnPBibFFubAdU1SXgXLTPdbjKcmDKUUwEamaXeJjSZM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEadiD&;
d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8H
swJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAx
UNPo7n7iqI&s=0fn1b7xQlU7DSW-jgMXjDRecEuLito5a3QjtucPyzb8&e=

--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.co
m_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwol
PvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2
SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=02DKqLjhWhV7sg9CnQ33CwVZQQGjj
tkOh2FVYyZaBxo&e= or email: MIDRANGE-L-request@xxxxxxxxxxxx Before
posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=MnPBibFFubAdU1SXgXLTPdbjKcmDKUUwEamaXeJjSZM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEadiD&d=
DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJh
t2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n
7iqI&s=0fn1b7xQlU7DSW-jgMXjDRecEuLito5a3QjtucPyzb8&e=


--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.co
m_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwol
PvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2
SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=02DKqLjhWhV7sg9CnQ33CwVZQQGjj
tkOh2FVYyZaBxo&e= or email: MIDRANGE-L-request@xxxxxxxxxxxx Before
posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=MnPBibFFubAdU1SXgXLTPdbjKcmDKUUwEamaXeJjSZM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEadiD&d=
DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJh
t2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n
7iqI&s=0fn1b7xQlU7DSW-jgMXjDRecEuLito5a3QjtucPyzb8&e=

--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=02DKqLjhWhV7sg9CnQ33CwVZQQGjjtkOh2FVYyZaBxo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=MnPBibFFubAdU1SXgXLTPdbjKcmDKUUwEamaXeJjSZM&e=.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEadiD&d=DwIGaQ&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=BKpd2SsWAHumcMe9IM5PUklbgQhEPgpAxUNPo7n7iqI&s=0fn1b7xQlU7DSW-jgMXjDRecEuLito5a3QjtucPyzb8&e=

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.