Oninit cdi / Archive
Talk to an expert →

NEW EXTERNAL TABLE QUESTION

Posted in 2017

Topics: Data Types & Schema Design

JOHN HENRY — — source: IIUG Forums & Mailing Lists

Hi All, I am seeing a strange behavior with an external table. if I do a select * from the table I get all the rows and columns returned. If I try to use a where clause on the table and say "where ISSUER_CUSIP is not null" I get the -217 error that the column is not in the table. If I do "select ISSUER_CUSIP from the external table I get the -217 error. I also did a select star into a temp table and tried the same selects on the temp table I get the same results, a -27 error. here is my table definition, any ideas? CREATE EXTERNAL TABLE gwk_trm_rslist_usccb_ext( ISSUER_NAME VARCHAR(100), ISSUERID VARCHAR(25), ISSUER_TICKER VARCHAR(12), ISSUER_CUSIP VARCHAR(12), ISSUER_SEDOL VARCHAR(9), ISSUER_ISIN VARCHAR(12), ISSUER_CNTRY_DOMICILE VARCHAR(16), AS_OF_DATE CHAR(8), USCCB_EXCLUSION_TIE CHAR(1), USCCB_ABORTION CHAR(1), USCCB_ADULT_ENTERTAINMENT CHAR(1), USCCB_CONTRACEPTIVES CHAR(1), USCCB_DEFENSE_AND_WEAPONS CHAR(1), USCCB_DISCRIMINATION CHAR(1), USCCB_LANDMINES CHAR(1), USCCB_PREDATORY_LENDING CHAR(1), USCCB_STEM_CELLS CHAR(1)) USING(DATAFILES("DISK:/opt/gim2usr/scripts/usccb_feed/gwk_trm_rslist_usccb.dat") , REJECTFILE "/opt/gim2usr/scripts/usccb_feed/reject_trm_rslist_usccb.out");

↪ replying to JOHN HENRY
david@smooth1.co.uk — — source: IIUG Forums & Mailing Lists

Use lowercase for the column names. Otherwise you need to set the DELIMIDENT environment variable https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlr.doc/id s_sqr_233.htm Regards, David. > On 07 September 2017 at 19:04 JOHN HENRY <jhenry@gwkinvest.com> wrote: > > > Hi All, > I am seeing a strange behavior with an external table. if I do a select * from > the table I get all the rows and columns returned. If I try to use a where > clause on the table and say "where ISSUER_CUSIP is not null" I get the -217 > error that the column is not in the table. If I do "select ISSUER_CUSIP from > the external table I get the -217 error. I also did a select star into a temp > table and tried the same selects on the temp table I get the same results, a > -27 error. > > here is my table definition, any ideas? > > CREATE EXTERNAL TABLE gwk_trm_rslist_usccb_ext( > ISSUER_NAME VARCHAR(100), > ISSUERID VARCHAR(25), > ISSUER_TICKER VARCHAR(12), > ISSUER_CUSIP VARCHAR(12), > ISSUER_SEDOL VARCHAR(9), > ISSUER_ISIN VARCHAR(12), > ISSUER_CNTRY_DOMICILE VARCHAR(16), > AS_OF_DATE CHAR(8), > USCCB_EXCLUSION_TIE CHAR(1), > USCCB_ABORTION CHAR(1), > USCCB_ADULT_ENTERTAINMENT CHAR(1), > USCCB_CONTRACEPTIVES CHAR(1), > USCCB_DEFENSE_AND_WEAPONS CHAR(1), > USCCB_DISCRIMINATION CHAR(1), > USCCB_LANDMINES CHAR(1), > USCCB_PREDATORY_LENDING CHAR(1), > USCCB_STEM_CELLS CHAR(1)) > > USING(DATAFILES("DISK:/opt/gim2usr/scripts/usccb_feed/gwk_trm_rslist_usccb.dat") , > REJECTFILE "/opt/gim2usr/scripts/usccb_feed/reject_trm_rslist_usccb.out"); > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to JOHN HENRY
Mike Walker — — source: IIUG Forums & Mailing Lists

Weird... Do you have the same error when selecting other columns, or just ISSUER_CUSIP? Do you happen to have DELIMIDENT set in your environment? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JOHN HENRY Sent: Thursday, September 07, 2017 12:05 PM To: ids@iiug.org Subject: NEW EXTERNAL TABLE QUESTION [39813] Hi All, I am seeing a strange behavior with an external table. if I do a select * from the table I get all the rows and columns returned. If I try to use a where clause on the table and say "where ISSUER_CUSIP is not null" I get the -217 error that the column is not in the table. If I do "select ISSUER_CUSIP from the external table I get the -217 error. I also did a select star into a temp table and tried the same selects on the temp table I get the same results, a -27 error. here is my table definition, any ideas? CREATE EXTERNAL TABLE gwk_trm_rslist_usccb_ext( ISSUER_NAME VARCHAR(100), ISSUERID VARCHAR(25), ISSUER_TICKER VARCHAR(12), ISSUER_CUSIP VARCHAR(12), ISSUER_SEDOL VARCHAR(9), ISSUER_ISIN VARCHAR(12), ISSUER_CNTRY_DOMICILE VARCHAR(16), AS_OF_DATE CHAR(8), USCCB_EXCLUSION_TIE CHAR(1), USCCB_ABORTION CHAR(1), USCCB_ADULT_ENTERTAINMENT CHAR(1), USCCB_CONTRACEPTIVES CHAR(1), USCCB_DEFENSE_AND_WEAPONS CHAR(1), USCCB_DISCRIMINATION CHAR(1), USCCB_LANDMINES CHAR(1), USCCB_PREDATORY_LENDING CHAR(1), USCCB_STEM_CELLS CHAR(1)) USING(DATAFILES("DISK:/opt/gim2usr/scripts/usccb_feed/gwk_trm_rslist_usccb.d at"), REJECTFILE "/opt/gim2usr/scripts/usccb_feed/reject_trm_rslist_usccb.out"); **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mike Walker
JOHN HENRY — — source: IIUG Forums & Mailing Lists

Many thanks to David, I changed the column names to lower case and it all plays nicely now. And to answer Mikes questions, it was happening with all the column names but I don't know off hand what the DELIMIDENT environment variable is set to. I will have to look. Thanks so much guys.

Related threads