scottklement.com

Hi,

1) Suppose if my field name and record format name is too long then how can i fit it into Functions under DDS for below case( I am referring this link :-When to specify REF and REFFLD keywords for DDS files - IBM Documentation)

5: FIELD6 has the same attributes as FLD6 in record format RECORDB in FILE4 in LIB1.

currently what happening is that just for example for my case number 5:-

FIELD6 R REFFLD(RECORDB/FLD6 LIB1/FILE4) (5) --> here due to combination of all this it's not fitting in single line and going to next line so i can i complete remaing file name in next line here ?

Any example to overcome this issue here please?

2) Suppose if i have to create a new file with below conditions for it's fields:-

field1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,else concatenate to FIELD3+FIELD4 of this same file1.

Any example to fulfil this condition for this case please?

Thanks.

You can use + to continue data on the next line

thanks much and what about second case here please?

Thanks much...

I've never done anything like that in DDS, so I don't know the answer to #2.

Is there a reason you're using DDS for this instead of SQL?

Or for that matter, just do it in a program?

Ok, If i try SQL table for the same my tjis SQL query does not work:-
"CREATE TABLE NewTable AS
SELECT
Field1 AS NewField1,
Field2 AS NewField2,
Field3 AS NewField3,
CASE
WHEN Field2 <> '' THEN Field1 || Field2
ELSE Field3 || Field4
END AS ConcatenatedField
FROM File1"

any corrected version of the same please?

thanks..

Hi,

Apart from that concatenation of the fields i want some fields to be referred from fiel1 some from fiel2 and some from file 3 likewise and these files could be sometime in same library and may be in different library plus i want to add some custom fields with my choice of data type and length in this table's structure.

but currently unable to form a table :

tried below SQL query but it's not getting executed getting this error:-"Keyword FROM not expected. Valid tokens: FOR USE SKIP WAIT WITH FETC "

CREATE TABLE l1/t1 AS (
SELECT
f1.field1 AS NAME1,
f1.field2 AS NAME2,
f1.field3 AS NAME3 ,
f2.field4 AS NAME4 ,
f4.field5 AS NAME5 ,
f1.field6 AS NAME6 ,
f1.field7 AS NAME7 ,
f1.field8 AS NAME8 ,
f1.field9 AS NAME9

FROM LIB1.FILE1 as f1
FROM LIB1.FILE2 as f2
FROM LIB2.FILE3 as f3
) WITH NO DATA

Awaiting experts advice here for the same to be able to create desire table(file) structure here.

Thanks.

Are your questions in this thread related?

You were asking previously about concatenating fields... You would never be able to do that in a create table statement.