IBM i Global

Hi,

I have below queries with respect to creating a file:-

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.

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

Original Message:
Sent: Sun July 21, 2024 01:36 AM
From: Birgitta Hauser
Subject: Files - fields queries

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Sat July 20, 2024 12:29 PM
From: jerry ven
Subject: Files - fields queries

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Original Message:
Sent: Sat July 20, 2024 12:03 PM
From: Daniel Gross
Subject: Files - fields queries

You have to JOIN the tables - a SELECT statement can only have one (1) FROM clause. The other solution is the list all tables behind the FROM with "," (comma) separated - which means a CROSS JOIN.

HTH
Daniel


Original Message:
Sent: 7/20/2024 11:56:00 AM
From: jerry ven
Subject: RE: Files - fields queries

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

------------------------------
jerry ven

Original Message:
Sent: Sat July 20, 2024 04:14 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 11:19 AM
From: jerry ven
Subject: Files - fields queries

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Original Message:
Sent: Thu July 18, 2024 09:34 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Original Message:
Sent: Thu July 18, 2024 04:11 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 03:08 AM
From: jerry ven
Subject: Files - fields queries

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 02:00 AM
From: Birgitta Hauser
Subject: Files - fields queries

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Thu July 18, 2024 01:00 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Original Message:
Sent: Wed July 17, 2024 01:57 PM
From: jerry ven
Subject: Files - fields queries

Hi,

I have below queries with respect to creating a file:-

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.

Jerry,

there is no such thing as a REFFLD in SQL. So to get a field definitions for a new table, you have to cboose all the fields you like in a single SELECT statement.

And to get fields from different tables into a single SELECT statement, you have to use some sort of JOIN. The simplest JOIN - from the syntactic point of view - is the CROSS JOIN, where you simply write:

   ... FROM table1, table2, table3 ...

Every other JOIN needs conditions that you have to write up in the ON clause. 

...FROM table1JOIN table2 ON 2=1JOIN table3 ON 3=3...

In this case you use a "natural" JOIN with conditions that will never be met, like "2=1" - so there is definitely no data that is selected - and the query engine is quite smart.

You will get the table structure - but without data. And as  you will do your CREATE TABLE with the NO DATA clause - you should have the desired result.

But this would also work the that CROSS JOIN using an "WHERE 1=2" clause - also a condition that cannot be met. Even as I assume that the SQL engine is smart enough not to select any data, as you are using NO DATA .

So just try it out.

HTH

------------------------------
Daniel Gross

Original Message:
Sent: Sun July 21, 2024 04:10 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

Original Message:
Sent: Sun July 21, 2024 01:36 AM
From: Birgitta Hauser
Subject: Files - fields queries

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Sat July 20, 2024 12:29 PM
From: jerry ven
Subject: Files - fields queries

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Original Message:
Sent: Sat July 20, 2024 12:03 PM
From: Daniel Gross
Subject: Files - fields queries

You have to JOIN the tables - a SELECT statement can only have one (1) FROM clause. The other solution is the list all tables behind the FROM with "," (comma) separated - which means a CROSS JOIN.

HTH
Daniel


Original Message:
Sent: 7/20/2024 11:56:00 AM
From: jerry ven
Subject: RE: Files - fields queries

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

------------------------------
jerry ven

Original Message:
Sent: Sat July 20, 2024 04:14 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 11:19 AM
From: jerry ven
Subject: Files - fields queries

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Original Message:
Sent: Thu July 18, 2024 09:34 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Original Message:
Sent: Thu July 18, 2024 04:11 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 03:08 AM
From: jerry ven
Subject: Files - fields queries

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 02:00 AM
From: Birgitta Hauser
Subject: Files - fields queries

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Thu July 18, 2024 01:00 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Original Message:
Sent: Wed July 17, 2024 01:57 PM
From: jerry ven
Subject: Files - fields queries

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Thanks for the help provided so far. I am currently focused just only on preparing structure (Schema) of this table.

I tried my SQL query and it worked and later on was able to add columns in it as per my name and data type and length using alter table sql query.

SELECT                                                            
        f1.field1 AS NAME1,                                
        f1.field2 AS NAME2,                                           
        f1.field3 AS NAME3 ,                                 
        f2.field4 AS NAME4  ,                                    
        f3.field5 AS NAME5  ,                                   
        f1.field6 AS NAME6  ,                               
        f1.field7 AS NAME7  ,                               
        f1.field8 AS NAME8      ,                               
        f1.field9 AS NAME9                                   
                                                                      
    FROM LIB1.FILE1   as f1,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

*******************

1)

For the adding columns in this structure of the table i have further query here that however i am able to add it with my desired column name but what to do if some column name is like 'ABCD WXYZ(sometext)'

though using alter table l1/t1 add " 

"ABCD WXYZ(sometext)"

it got added with these double quotation marks ("") around with this kind of column name but i don't want these double quotation marks around in my field name(column name) so how to get rid off from this ?

2)

Also I am still not sure that how can i add that complex thing like "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" --How would i achieve it in this table's structure ? because my field1 should be structured based on this condition only.

So could someone please help for these points here?

Thanks much.....

Jerry,

there is no such thing as a REFFLD in SQL. So to get a field definitions for a new table, you have to cboose all the fields you like in a single SELECT statement.

And to get fields from different tables into a single SELECT statement, you have to use some sort of JOIN. The simplest JOIN - from the syntactic point of view - is the CROSS JOIN, where you simply write:

   ... FROM table1, table2, table3 ...

Every other JOIN needs conditions that you have to write up in the ON clause. 

...FROM table1JOIN table2 ON 2=1JOIN table3 ON 3=3...

In this case you use a "natural" JOIN with conditions that will never be met, like "2=1" - so there is definitely no data that is selected - and the query engine is quite smart.

You will get the table structure - but without data. And as  you will do your CREATE TABLE with the NO DATA clause - you should have the desired result.

But this would also work the that CROSS JOIN using an "WHERE 1=2" clause - also a condition that cannot be met. Even as I assume that the SQL engine is smart enough not to select any data, as you are using NO DATA .

So just try it out.

HTH

------------------------------
Daniel Gross

Original Message:
Sent: Sun July 21, 2024 04:10 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

Original Message:
Sent: Sun July 21, 2024 01:36 AM
From: Birgitta Hauser
Subject: Files - fields queries

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Sat July 20, 2024 12:29 PM
From: jerry ven
Subject: Files - fields queries

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Original Message:
Sent: Sat July 20, 2024 12:03 PM
From: Daniel Gross
Subject: Files - fields queries

You have to JOIN the tables - a SELECT statement can only have one (1) FROM clause. The other solution is the list all tables behind the FROM with "," (comma) separated - which means a CROSS JOIN.

HTH
Daniel


Original Message:
Sent: 7/20/2024 11:56:00 AM
From: jerry ven
Subject: RE: Files - fields queries

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

------------------------------
jerry ven

Original Message:
Sent: Sat July 20, 2024 04:14 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 11:19 AM
From: jerry ven
Subject: Files - fields queries

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Original Message:
Sent: Thu July 18, 2024 09:34 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Original Message:
Sent: Thu July 18, 2024 04:11 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 03:08 AM
From: jerry ven
Subject: Files - fields queries

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 02:00 AM
From: Birgitta Hauser
Subject: Files - fields queries

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Thu July 18, 2024 01:00 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Original Message:
Sent: Wed July 17, 2024 01:57 PM
From: jerry ven
Subject: Files - fields queries

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Could someone please respond to my queries here soon?

Thanks much..

Hi,

Thanks for the help provided so far. I am currently focused just only on preparing structure (Schema) of this table.

I tried my SQL query and it worked and later on was able to add columns in it as per my name and data type and length using alter table sql query.

SELECT                                                            
        f1.field1 AS NAME1,                                
        f1.field2 AS NAME2,                                           
        f1.field3 AS NAME3 ,                                 
        f2.field4 AS NAME4  ,                                    
        f3.field5 AS NAME5  ,                                   
        f1.field6 AS NAME6  ,                               
        f1.field7 AS NAME7  ,                               
        f1.field8 AS NAME8      ,                               
        f1.field9 AS NAME9                                   
                                                                      
    FROM LIB1.FILE1   as f1,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

*******************

1)

For the adding columns in this structure of the table i have further query here that however i am able to add it with my desired column name but what to do if some column name is like 'ABCD WXYZ(sometext)'

though using alter table l1/t1 add " 

"ABCD WXYZ(sometext)"

it got added with these double quotation marks ("") around with this kind of column name but i don't want these double quotation marks around in my field name(column name) so how to get rid off from this ?

2)

Also I am still not sure that how can i add that complex thing like "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" --How would i achieve it in this table's structure ? because my field1 should be structured based on this condition only.

So could someone please help for these points here?

Thanks much.....

Jerry,

there is no such thing as a REFFLD in SQL. So to get a field definitions for a new table, you have to cboose all the fields you like in a single SELECT statement.

And to get fields from different tables into a single SELECT statement, you have to use some sort of JOIN. The simplest JOIN - from the syntactic point of view - is the CROSS JOIN, where you simply write:

   ... FROM table1, table2, table3 ...

Every other JOIN needs conditions that you have to write up in the ON clause. 

...FROM table1JOIN table2 ON 2=1JOIN table3 ON 3=3...

In this case you use a "natural" JOIN with conditions that will never be met, like "2=1" - so there is definitely no data that is selected - and the query engine is quite smart.

You will get the table structure - but without data. And as  you will do your CREATE TABLE with the NO DATA clause - you should have the desired result.

But this would also work the that CROSS JOIN using an "WHERE 1=2" clause - also a condition that cannot be met. Even as I assume that the SQL engine is smart enough not to select any data, as you are using NO DATA .

So just try it out.

HTH

------------------------------
Daniel Gross

Original Message:
Sent: Sun July 21, 2024 04:10 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

Original Message:
Sent: Sun July 21, 2024 01:36 AM
From: Birgitta Hauser
Subject: Files - fields queries

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Sat July 20, 2024 12:29 PM
From: jerry ven
Subject: Files - fields queries

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Original Message:
Sent: Sat July 20, 2024 12:03 PM
From: Daniel Gross
Subject: Files - fields queries

You have to JOIN the tables - a SELECT statement can only have one (1) FROM clause. The other solution is the list all tables behind the FROM with "," (comma) separated - which means a CROSS JOIN.

HTH
Daniel


Original Message:
Sent: 7/20/2024 11:56:00 AM
From: jerry ven
Subject: RE: Files - fields queries

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

------------------------------
jerry ven

Original Message:
Sent: Sat July 20, 2024 04:14 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 11:19 AM
From: jerry ven
Subject: Files - fields queries

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Original Message:
Sent: Thu July 18, 2024 09:34 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Original Message:
Sent: Thu July 18, 2024 04:11 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 03:08 AM
From: jerry ven
Subject: Files - fields queries

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 02:00 AM
From: Birgitta Hauser
Subject: Files - fields queries

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Thu July 18, 2024 01:00 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Original Message:
Sent: Wed July 17, 2024 01:57 PM
From: jerry ven
Subject: Files - fields queries

Hi,

I have below queries with respect to creating a file:-

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.

Hi,

Thanks for the help provided so far. I am currently focused just only on preparing structure (Schema) of this table.

I tried my SQL query and it worked and later on was able to add columns in it as per my name and data type and length using alter table sql query.

SELECT                                                            
        f1.field1 AS NAME1,                                
        f1.field2 AS NAME2,                                           
        f1.field3 AS NAME3 ,                                 
        f2.field4 AS NAME4  ,                                    
        f3.field5 AS NAME5  ,                                   
        f1.field6 AS NAME6  ,                               
        f1.field7 AS NAME7  ,                               
        f1.field8 AS NAME8      ,                               
        f1.field9 AS NAME9                                   
                                                                      
    FROM LIB1.FILE1   as f1,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

*******************

1)

For the adding columns in this structure of the table i have further query here that however i am able to add it with my desired column name but what to do if some column name is like 'ABCD WXYZ(sometext)'

though using alter table l1/t1 add " 

"ABCD WXYZ(sometext)"

it got added with these double quotation marks ("") around with this kind of column name but i don't want these double quotation marks around in my field name(column name) so how to get rid off from this ?

2)

Also I am still not sure that how can i add that complex thing like "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" --How would i achieve it in this table's structure ? because my field1 should be structured based on this condition only.

So could someone please help for these points here?

Thanks much.....

Jerry,

there is no such thing as a REFFLD in SQL. So to get a field definitions for a new table, you have to cboose all the fields you like in a single SELECT statement.

And to get fields from different tables into a single SELECT statement, you have to use some sort of JOIN. The simplest JOIN - from the syntactic point of view - is the CROSS JOIN, where you simply write:

   ... FROM table1, table2, table3 ...

Every other JOIN needs conditions that you have to write up in the ON clause. 

...FROM table1JOIN table2 ON 2=1JOIN table3 ON 3=3...

In this case you use a "natural" JOIN with conditions that will never be met, like "2=1" - so there is definitely no data that is selected - and the query engine is quite smart.

You will get the table structure - but without data. And as  you will do your CREATE TABLE with the NO DATA clause - you should have the desired result.

But this would also work the that CROSS JOIN using an "WHERE 1=2" clause - also a condition that cannot be met. Even as I assume that the SQL engine is smart enough not to select any data, as you are using NO DATA .

So just try it out.

HTH

------------------------------
Daniel Gross

Original Message:
Sent: Sun July 21, 2024 04:10 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

Original Message:
Sent: Sun July 21, 2024 01:36 AM
From: Birgitta Hauser
Subject: Files - fields queries

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Sat July 20, 2024 12:29 PM
From: jerry ven
Subject: Files - fields queries

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Original Message:
Sent: Sat July 20, 2024 12:03 PM
From: Daniel Gross
Subject: Files - fields queries

You have to JOIN the tables - a SELECT statement can only have one (1) FROM clause. The other solution is the list all tables behind the FROM with "," (comma) separated - which means a CROSS JOIN.

HTH
Daniel


Original Message:
Sent: 7/20/2024 11:56:00 AM
From: jerry ven
Subject: RE: Files - fields queries

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

------------------------------
jerry ven

Original Message:
Sent: Sat July 20, 2024 04:14 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 11:19 AM
From: jerry ven
Subject: Files - fields queries

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Original Message:
Sent: Thu July 18, 2024 09:34 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Original Message:
Sent: Thu July 18, 2024 04:11 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 03:08 AM
From: jerry ven
Subject: Files - fields queries

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 02:00 AM
From: Birgitta Hauser
Subject: Files - fields queries

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Thu July 18, 2024 01:00 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Original Message:
Sent: Wed July 17, 2024 01:57 PM
From: jerry ven
Subject: Files - fields queries

Hi,

I have below queries with respect to creating a file:-

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.

So far you have had some great answers.  The only reason I am adding this is the one tidbit I found to use. The DEFINITION ONLY and INCLUDING COLUMN DEFAULTS key words helped me when I tried to use the reference file concept for a new file.  I normally do not use this design, but someone challenged me to not change everything about the current process with DDS.  

What I like about using the ACS Run scripts to develop new concepts is the detailed error messages that help me inch forward. 

CREATE or REPLACE TABLE Long_Table_Name
for system name MYFILEPF AS
  (SELECT FIELD1  as  FIELD1,
          FIELD2  as  FIELD2,
          CAST(' ' AS CHAR(50))    AS NEWFIELD3,
          CAST(' ' AS CHAR(10))    AS NEWFIELD4 
          CAST('0001-01-01-00.00.00.000000' AS TIMESTAMP) AS AUDITTIME,
          CAST(0 AS INTEGER  )     AS Record_ID
     FROM REFFILEPF)
          DEFINITION ONLY
          INCLUDING COLUMN DEFAULTS
 RCDFMT MYFILERF;

-- Using SQL, we can assign the identity field
ALTER TABLE MYFILEPF
  ALTER COLUMN RECORD_ID SET DATA TYPE INTEGER GENERATED ALWAYS AS IDENTITY;
ALTER TABLE MYFILEPF
  ALTER COLUMN AUDITTIME SET DEFAULT CURRENT_TIMESTAMP;

LABEL ON TABLE MYFILEPF IS 'SQL Table Based on Reference File';

-- The columns existing in the reference file pick up their descriptions from the reference file. 
-- You need to define the manually created columns here.
    LABEL ON COLUMN MYFILEPF (
        NEWFIELD3   IS 'New Field 3 description',
        NEWFIELD4   IS 'New Field 4 description',
        AUDITTIME IS 'AUDIT TIME',
        RECORD_ID IS 'RECORD ID'
        ) ;

    LABEL ON COLUMN MYFILEPF (
        NEWFIELD3 TEXT IS 'New Field 3 description',
        NEWFIELD4 TEXT IS 'New Field 4 description',
        AUDITTIME TEXT IS 'AUDIT TIME',
        RECORD_ID TEXT IS 'RECORD ID'
       ) ;                          

Hi,

Thanks for the help provided so far. I am currently focused just only on preparing structure (Schema) of this table.

I tried my SQL query and it worked and later on was able to add columns in it as per my name and data type and length using alter table sql query.

SELECT                                                            
        f1.field1 AS NAME1,                                
        f1.field2 AS NAME2,                                           
        f1.field3 AS NAME3 ,                                 
        f2.field4 AS NAME4  ,                                    
        f3.field5 AS NAME5  ,                                   
        f1.field6 AS NAME6  ,                               
        f1.field7 AS NAME7  ,                               
        f1.field8 AS NAME8      ,                               
        f1.field9 AS NAME9                                   
                                                                      
    FROM LIB1.FILE1   as f1,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

*******************

1)

For the adding columns in this structure of the table i have further query here that however i am able to add it with my desired column name but what to do if some column name is like 'ABCD WXYZ(sometext)'

though using alter table l1/t1 add " 

"ABCD WXYZ(sometext)"

it got added with these double quotation marks ("") around with this kind of column name but i don't want these double quotation marks around in my field name(column name) so how to get rid off from this ?

2)

Also I am still not sure that how can i add that complex thing like "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" --How would i achieve it in this table's structure ? because my field1 should be structured based on this condition only.

So could someone please help for these points here?

Thanks much.....

Jerry,

there is no such thing as a REFFLD in SQL. So to get a field definitions for a new table, you have to cboose all the fields you like in a single SELECT statement.

And to get fields from different tables into a single SELECT statement, you have to use some sort of JOIN. The simplest JOIN - from the syntactic point of view - is the CROSS JOIN, where you simply write:

   ... FROM table1, table2, table3 ...

Every other JOIN needs conditions that you have to write up in the ON clause. 

...FROM table1JOIN table2 ON 2=1JOIN table3 ON 3=3...

In this case you use a "natural" JOIN with conditions that will never be met, like "2=1" - so there is definitely no data that is selected - and the query engine is quite smart.

You will get the table structure - but without data. And as  you will do your CREATE TABLE with the NO DATA clause - you should have the desired result.

But this would also work the that CROSS JOIN using an "WHERE 1=2" clause - also a condition that cannot be met. Even as I assume that the SQL engine is smart enough not to select any data, as you are using NO DATA .

So just try it out.

HTH

------------------------------
Daniel Gross

Original Message:
Sent: Sun July 21, 2024 04:10 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

Original Message:
Sent: Sun July 21, 2024 01:36 AM
From: Birgitta Hauser
Subject: Files - fields queries

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Sat July 20, 2024 12:29 PM
From: jerry ven
Subject: Files - fields queries

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Original Message:
Sent: Sat July 20, 2024 12:03 PM
From: Daniel Gross
Subject: Files - fields queries

You have to JOIN the tables - a SELECT statement can only have one (1) FROM clause. The other solution is the list all tables behind the FROM with "," (comma) separated - which means a CROSS JOIN.

HTH
Daniel


Original Message:
Sent: 7/20/2024 11:56:00 AM
From: jerry ven
Subject: RE: Files - fields queries

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

------------------------------
jerry ven

Original Message:
Sent: Sat July 20, 2024 04:14 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 11:19 AM
From: jerry ven
Subject: Files - fields queries

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Original Message:
Sent: Thu July 18, 2024 09:34 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Original Message:
Sent: Thu July 18, 2024 04:11 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 03:08 AM
From: jerry ven
Subject: Files - fields queries

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 02:00 AM
From: Birgitta Hauser
Subject: Files - fields queries

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Thu July 18, 2024 01:00 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Original Message:
Sent: Wed July 17, 2024 01:57 PM
From: jerry ven
Subject: Files - fields queries

Hi,

I have below queries with respect to creating a file:-

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.

David, 

  That example is one of the few times anyone has given anything positive about SQL tables and field references, and is nearly identical to the style I've had my team use for a number of years.  It seems odd there are so many that say it cannot be done, when it is very doable.  We vary slightly, doing it like this, and I discourage the ad-hoc style of your CAST statements, preferring to add to the field reference if needed.   

fictional example of a DDL source member:

CREATE OR REPLACE TABLE WEBORDERS (

SESSIONID ,

DSERRMSG)

 As

(Select

CODE15,

TEXT100

 FROM MASTERREF)

WITH NO DATA INCLUDING COLUMN DEFAULTS

RCDFMT RWEBORD;

LABEL ON TABLE WEBORDERS IS 'WEB CART INFO';

LABEL ON COLUMN WEBORDERS (

SESSIONID IS 'SESSION ID                                              ' ,

DSERRMSG IS 'ERROR MESSAGE                                            ');

LABEL ON COLUMN WEBORDERS (

SESSIONID TEXT IS 'SESSION ID      ' ,

DSERRMSG TEXT IS 'ERROR MESSAGE ');

ALTER TABLE WEBORDERS

PRIMARY KEY (SESSIONID);    

In addition, we often copy file definitions, using DDS keyword FORMAT().  This too, is very possible in SQL DDL:

CREATE OR REPLACE TABLE WEBORDERSH

LIKE WEBORDERS

RCDFMT RWEBORD;

LABEL ON TABLE WEBORDERSH IS 'WEB CART INFO PURGED HISTORY';

ALTER TABLE WEBORDERSH

PRIMARY KEY (SESSIONID);

  Also, with the REPLACE function, we do not use alter to add fields, we code them in the DDL and rerun the statement and replace handles it fine.

So far you have had some great answers.  The only reason I am adding this is the one tidbit I found to use. The DEFINITION ONLY and INCLUDING COLUMN DEFAULTS key words helped me when I tried to use the reference file concept for a new file.  I normally do not use this design, but someone challenged me to not change everything about the current process with DDS.  

What I like about using the ACS Run scripts to develop new concepts is the detailed error messages that help me inch forward. 

CREATE or REPLACE TABLE Long_Table_Name
for system name MYFILEPF AS
  (SELECT FIELD1  as  FIELD1,
          FIELD2  as  FIELD2,
          CAST(' ' AS CHAR(50))    AS NEWFIELD3,
          CAST(' ' AS CHAR(10))    AS NEWFIELD4 
          CAST('0001-01-01-00.00.00.000000' AS TIMESTAMP) AS AUDITTIME,
          CAST(0 AS INTEGER  )     AS Record_ID
     FROM REFFILEPF)
          DEFINITION ONLY
          INCLUDING COLUMN DEFAULTS
 RCDFMT MYFILERF;

-- Using SQL, we can assign the identity field
ALTER TABLE MYFILEPF
  ALTER COLUMN RECORD_ID SET DATA TYPE INTEGER GENERATED ALWAYS AS IDENTITY;
ALTER TABLE MYFILEPF
  ALTER COLUMN AUDITTIME SET DEFAULT CURRENT_TIMESTAMP;

LABEL ON TABLE MYFILEPF IS 'SQL Table Based on Reference File';

-- The columns existing in the reference file pick up their descriptions from the reference file. 
-- You need to define the manually created columns here.
    LABEL ON COLUMN MYFILEPF (
        NEWFIELD3   IS 'New Field 3 description',
        NEWFIELD4   IS 'New Field 4 description',
        AUDITTIME IS 'AUDIT TIME',
        RECORD_ID IS 'RECORD ID'
        ) ;

    LABEL ON COLUMN MYFILEPF (
        NEWFIELD3 TEXT IS 'New Field 3 description',
        NEWFIELD4 TEXT IS 'New Field 4 description',
        AUDITTIME TEXT IS 'AUDIT TIME',
        RECORD_ID TEXT IS 'RECORD ID'
       ) ;                          

Hi,

Thanks for the help provided so far. I am currently focused just only on preparing structure (Schema) of this table.

I tried my SQL query and it worked and later on was able to add columns in it as per my name and data type and length using alter table sql query.

SELECT                                                            
        f1.field1 AS NAME1,                                
        f1.field2 AS NAME2,                                           
        f1.field3 AS NAME3 ,                                 
        f2.field4 AS NAME4  ,                                    
        f3.field5 AS NAME5  ,                                   
        f1.field6 AS NAME6  ,                               
        f1.field7 AS NAME7  ,                               
        f1.field8 AS NAME8      ,                               
        f1.field9 AS NAME9                                   
                                                                      
    FROM LIB1.FILE1   as f1,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

*******************

1)

For the adding columns in this structure of the table i have further query here that however i am able to add it with my desired column name but what to do if some column name is like 'ABCD WXYZ(sometext)'

though using alter table l1/t1 add " 

"ABCD WXYZ(sometext)"

it got added with these double quotation marks ("") around with this kind of column name but i don't want these double quotation marks around in my field name(column name) so how to get rid off from this ?

2)

Also I am still not sure that how can i add that complex thing like "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" --How would i achieve it in this table's structure ? because my field1 should be structured based on this condition only.

So could someone please help for these points here?

Thanks much.....

Jerry,

there is no such thing as a REFFLD in SQL. So to get a field definitions for a new table, you have to cboose all the fields you like in a single SELECT statement.

And to get fields from different tables into a single SELECT statement, you have to use some sort of JOIN. The simplest JOIN - from the syntactic point of view - is the CROSS JOIN, where you simply write:

   ... FROM table1, table2, table3 ...

Every other JOIN needs conditions that you have to write up in the ON clause. 

...FROM table1JOIN table2 ON 2=1JOIN table3 ON 3=3...

In this case you use a "natural" JOIN with conditions that will never be met, like "2=1" - so there is definitely no data that is selected - and the query engine is quite smart.

You will get the table structure - but without data. And as  you will do your CREATE TABLE with the NO DATA clause - you should have the desired result.

But this would also work the that CROSS JOIN using an "WHERE 1=2" clause - also a condition that cannot be met. Even as I assume that the SQL engine is smart enough not to select any data, as you are using NO DATA .

So just try it out.

HTH

------------------------------
Daniel Gross

Original Message:
Sent: Sun July 21, 2024 04:10 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Thanks for responding to my queries.

any SQL query example based on my requirements here to create such view here?

plus  i don't want to use cross join here i just want to create a structure of this table based on my requirements here.

so just to create a structure of such a table why do i need to use  cross join here?

neither i believe i need any kind of other joins etc. as i simply neeed to have a table created based on my requirements here.

i think once a table using this SQL query is created :- 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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

then in this table t1 i just need to use alter table add colmn sql query like these examples here:"SQL ADD COLUMN - Add One or More Columns To a Table (sqltutorial.org)" to add fields as per my choice of desired length and datatypes here.

only thing i am not sure currently that complex thing like "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" --How would i achieve it in this table ?

Any example for this desired table's(file) structure which could form my desired table using any single SQL query please?

Thanks.

so 

Original Message:
Sent: Sun July 21, 2024 01:36 AM
From: Birgitta Hauser
Subject: Files - fields queries

1. If you want to create something similar to a logical file, you need to create a VIEW and NOT a Table (the SQL table is the equivalent of a PF).
2. There are several JOIN methods, just listing tables separated by a comma is an CROSS JOIN which will generate a cartesian product of data, i.e. the 1st row of the 1st table is joined with all rows of the 2nd table, the 2nd row of the 1st is joined with all rows of the 2nd table ... and so on.
3. Joining tables is very basic SQL, you'll find a lot of examples in the internet, or even W3School will explain it:https://www.w3schools.com/sql/sql_join.asp 

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Sat July 20, 2024 12:29 PM
From: jerry ven
Subject: Files - fields queries

Thanks.

I think advice was here like either use below sql qry:-

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,                                     
    LIB1.FILE2   as f2,                                    
    LIB2.FILE3   as f3                                     
) WITH NO DATA       

or

use Join but how can i use it here ?

meeting this requirements :-

"

1) this new table( file) should have some fields reffered from existing files which are there in different libraries ,however some files could be in same library also.

plus that concatenation codtion which i mentioned in my starting posts here "ield1 from file1 should be concatenated with field2 of this same file1 where FIELD2 NE BLANK ,
else concatenate to FIELD3+FIELD4 of this same file1." plus i should have liberty to add my own custom fields in this file with my own choice of datatype and length.

"

So Could someone please suggest a SQL query meeting these requirements here?

Thanks much...

Original Message:
Sent: Sat July 20, 2024 12:03 PM
From: Daniel Gross
Subject: Files - fields queries

You have to JOIN the tables - a SELECT statement can only have one (1) FROM clause. The other solution is the list all tables behind the FROM with "," (comma) separated - which means a CROSS JOIN.

HTH
Daniel


Original Message:
Sent: 7/20/2024 11:56:00 AM
From: jerry ven
Subject: RE: Files - fields queries

Hi,

Apar from that concatenation of the fields i want some fields to be reffered 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 custome 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 expert's advise here for the same to be able to create desire table(file) structure here.

Thanks.           

------------------------------
jerry ven

Original Message:
Sent: Sat July 20, 2024 04:14 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

Unable to do it in DDS of PF so tried SQL but it's also not working:-

"

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

"

So could someone please advise correct SQL query here to create desired file here? 

Also once it's created will it have record format name like PF which could be used to SETLL, READE,(inside RPGLE program) and RCVF command etc. in CL program etc. 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 11:19 AM
From: jerry ven
Subject: Files - fields queries

Hi,

1) For my first query it's resolved by putting '+' sign at end and continued remaining characters in next line so this issuse is fixed now.

2) for 2nd issue could someone please advise here?

Thanks.

Original Message:
Sent: Thu July 18, 2024 09:34 AM
From: jerry ven
Subject: Files - fields queries

Hi,

Could someone please advise here soon? Appreciate prompt response from experts in DDS for physical files here.

Thanks.

Original Message:
Sent: Thu July 18, 2024 04:11 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

I appreciate idea of SQL table here. 

But if it has to be done on physical file's DDS then Could someone please help at DDS level for the same caes here? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 03:08 AM
From: jerry ven
Subject: Files - fields queries

Thanks ,but even though if we have to create a new physical file  (or any new file) with these cases then how can we resolve these issues  in DDS here ? 

Thanks. 

Original Message:
Sent: Thu July 18, 2024 02:00 AM
From: Birgitta Hauser
Subject: Files - fields queries

For me the biggest question is: WHY do you want to create physical and logical files with DDS instead of using SQL?

With SQL (DDL=Data Definition Language) you can create tables, views and indexes as well as columns with long SQL names and short system names (when using long names without having specified a system name, a system name - First 5 characters followed by a 5 digit running no - is automatically generated.

SQL view never have a key but can include everything that can be used in an SELECT statement (except ORDER BY - since a View has no key), so it is possible to join tables and create new columns if neccessary

SQL Indexes include the keys.

Derived indexes allow to generate new key fields based on mathematical operators (e.g. Quantity * Price) or scalar functions (for examples YEAR(SalesDate), Upper(Name), Left(Text, 5))

Additionally it is possible to add WHERE conditions to an index (which can be much more powerful than SELECT/OMIT clauses).

Even though an index cannot be specified in a SQL Statement (SELECT, UPDATE, INSERT, DELETE) an SQL index can be used in composition with native I/O like any keyed logical file.

Since an index is built over a single file, it cannot include join information, but also in a DDS described logical join file all key columns must be from the same table.

So if you create a view and an index with the appropriate key fields and use embedded SQL (instead of native I/O) your program will run correctly.

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/  

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Thu July 18, 2024 01:00 AM
From: jerry ven
Subject: Files - fields queries

Hi, 

If anyone knows answers of these queries then could someone please resply soon? 

Thanks. 

Original Message:
Sent: Wed July 17, 2024 01:57 PM
From: jerry ven
Subject: Files - fields queries

Hi,

I have below queries with respect to creating a file:-

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.