creating a file using DDS

[c21]

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.

[Scott Klement]

You can use + to continue data on the next line

Code: Select all

      A            FIELD6    R               REFFLD(RECORDB/FLD6 +
      A                                      LIB1/FILE4)          

[c21]

thanks much and what about second case here please?

Thanks much...

[Scott Klement]

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?

[c21]

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..

[c21]

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.

[Scott Klement]

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.