[IQUG] [iqug] Col headers in TEMP EXTRACTs

Mumy, Mark mark.mumy at sap.com
Sat May 25 11:42:24 MST 2019


Here’s the code:

-- use sp_iqcolumn to get the ordered column list (same as specifying ‘*’)
-- must be put into an IQ temp table so that we can extract the column list
-- use the delimiter parameter to list to change the delimiter from the default comma
-- this code uses a + to illustrate the point
select table_name, table_owner, cols=list( column_name, '+' ) into #xxx from sp_iqcolumn( 'mark' )
group by table_owner, table_name
go
-- set the output file and column delimiter for the data output
-- make sure to use the same delimiter here as you did above in the LIST function so everything matches
set temporary option temp_extract_name1='/tmp/mark.out'
set temporary option temp_extract_column_delimiter='+';
go
-- get the column row and dump it into the output file
select cols from #xxx
go
-- change the type of extraction to append the data.  Only append after the first extract
-- so that you can guarantee that the file has 1 and only 1 row, the column list
set temporary option temp_extract_append='on';
go
select * from mark
go

My table has just 8 rows, here’s the output from running the SQL:
1> select table_name, table_owner, cols=list( column_name, '+' ) into #xxx from sp_iqcolumn( 'mark' )
2> group by table_owner, table_name
(1 row affected)
1> set temporary option temp_extract_name1='/tmp/mark.out'
2> set temporary option temp_extract_column_delimiter='+';
1> select cols from #xxx
cols
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 rows affected)
1> set temporary option temp_extract_append='on';
1> select * from mark
a1          a2          a3
----------- ----------- -----------

(0 rows affected)

And the output file has this:
[sap at iqhost tmp]$ cat /tmp/mark.out
a1+a2+a3+
1+1+1+
2+2+2+
3+3+3+
4+4+4+
5+5+5+
6+6+6+
7+7+7+
8+8+8+


Mark

Mark Mumy
SAP Platform and Technologies Global Center of Excellence
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: "iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of Pankaj Pathak <pathak1611 at gmail.com>
Date: Saturday, May 25, 2019 at 11:34 AM
To: "markdmumy at gmail.com" <markdmumy at gmail.com>
Cc: IQ Group <iqug at dssolutions.com>
Subject: Re: [IQUG] [iqug] Col headers in TEMP EXTRACTs

Sounds good. 😊

Thank you!!

With warm regards,
Pankaj

On Sat 25 May, 2019, 20:38 Mark Mumy, <markdmumy at gmail.com<mailto:markdmumy at gmail.com>> wrote:
The other option is to write a proc that outputs the column names as an IQ string, then it goes to a file.  Then use the append option and run the real select to push output to the same file. It after line 1.

I had code for this once.  Let me see if I can dig it up.
Mark

========================

Sent from my mobile device

On May 25, 2019, at 09:01, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
Thanks Pathak.

Will try this.

David


From: Pankaj Pathak [mailto:pathak1611 at gmail.com]
Sent: Saturday, May 25, 2019 8:58 AM
To: Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>
Cc: iqug at dssolutions.com<mailto:iqug at dssolutions.com>
Subject: Re: [IQUG] [iqug] Col headers in TEMP EXTRACTs


External Email: Use caution with links and attachments
Hi David,


There is an alternative, if your table width is not too large.

Select * from table_name where 1=2;( this will yield no output, except the column names itself)

Run above query in **interactive SQL** SAP IQ native client.

And in output window click anywhere and do Ctrl+C (copy) and paste in a file abc.csv

Now extract data using temp_extract options, by adding Temp_Extract_Append=ON and check, if it works.


Note: make sure file name must be
Temp_Extract_Name1='abc.csv'
Hth

Thank you!!

With warm regards,
Pankaj

On Sat 25 May, 2019, 17:24 Louie, David, <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:
I think the answer is no but is there a way to add columns header before the data (as the first line)
when using temp_extract options?  Don’t see an option available.

Thanks
David


This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/compliance/email-disclaimers for further information.  Please refer to http://www.blackrock.com/corporate/compliance/privacy-policy for more information about BlackRock’s Privacy Policy.
For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/about-us/contacts-locations.

© 2019 BlackRock, Inc. All rights reserved.
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug<https://urldefense.proofpoint.com/v2/url?u=http-3A__iqug.org_mailman_listinfo_iqug&d=DwMFaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=VjSVZUiXUOL638sRz1cab_FIIgzYfjeB6s4mEyUYLgk&s=BbRqLMxcyq3Bj7Ni5-nwvQOAjsfPJYMJUFVeNhqc48A&e=>
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20190525/dfbf349d/attachment-0001.html>


More information about the IQUG mailing list