AHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
Thanks Rob
It is NOW working
One HUGE dope slap to me
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Wednesday, September 26, 2018 1:06 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: [EXTERNAL] Re: Problem with creating a file with a different field name
AS seems to be missing
Create table qtemp.allcusts
as (Select ccust as Customer_nbr
from erplxf.rcm
where cad1 = '611 E 1000 N')
With data
If you're going to add OR REPLACE, and keep your WITH DATA clause, you'll also need to add an ON REPLACE clause as the default does not play well with OR REPLACE
Create or replace table qtemp.allcusts
as (Select ccust as Customer_nbr
from erplxf.rcm
where cad1 = '611 E 1000 N')
With data
on replace delete rows
Without the ON REPLACE you will get:
Message ID . . . . . . : SQ20038
Message . . . . : Clauses not valid in same definition.
Cause . . . . . : PRESERVE and WITH DATA cannot be specified at the same
time.
-- The WITH DATA option cannot be specified with ON REPLACE PRESERVE ROWS.
-- A field procedure cannot be defined for a column that has a generated
expression.
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
http://www.dekko.com
From: "Alan Shore" <ashore@xxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 09/26/2018 12:04 PM
Subject: Problem with creating a file with a different field name
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Hi everyone
Before I forget - we are on V7r3
When I use STRSQl with the following command
Select Cust# as Customer_nbr from File01 where FieldB = 'SOMEVALUE'
Data is displayed, with the column heading Customer_Nbr
When I run the same query AFTER pressing F13, choose 1. Change session
attributes
Change SELECT output to 3 (for File) and define a file name
The file is created, with the field Customer_Nbr
However, when I change the query to the following
Create or replace table qtemp.allcusts
(Select Cust# as Customer_nbr from File01 where FieldB = 'SOMEVALUE')
With data
I get the error
Token CUSTOMER_NBR was not valid. Valid tokens: ( ROW IDENTITY TRANS
And the cursor is under the C of Customer_nbr
Anyone any idea what I am doing wrong?
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
midrange.com
