Tweet
Rahul, The quickest and easiest solution would be to outfile your SQL results and use that file in your program. The file can be created in QTEMP and will be deleted when the program ends. The opnqryf statement that you posted looks like it is only joining files 1 and 2 together. Also in your SQL statement you selected fields from all 3 files. Do you want all of the fields in the SQL to be included in the opnqryf? If yes, you cannot specify format(file1) in the opnqryf since some of the fields are not in File1. You will need to create DDS for the format that you want and specify that file name as the format. Vilena
-
-
Open query on 3 Files
Vilena, You have rightly proposed the easy solution to move the result into a file in QTEMP. Also we can convert the RPGLE program to SQLRPGLE program and embedd the query which i have given it in the previous post. I do not want fields from FILE 2 and FILE3. I just want the records in FILE 1 to be ordered as was shown in sample data. This was the reason why i have specified FILE 1 in format name. I wanted to convert the query i have mentioned in SQL format to OPNQRYF. Is that possible through OPNQRYF or it is impossible ? How can i join FILE1 and FILE3 as shown in the query ? Thanks in advance. Thanks Rahul -
Open query on 3 Files
Rahul, Try setting the JDFTVAL to *NO, the JORDER value to *ANY and Take out the JFLD parameter. This should cause the query to join all files based on the QRYSLT parameters. Are the dates in the files stored in date data type or zoned? If they are defined as zoned then on the MAPFLD parameter try redining the 6 digit dates as follows: MAPFLD((ANTD6 HADT *ZONED 6) (ANTDT6 ANTD6 *CHAR 6)). There is no need to substring if you are using the entire length of the field. Have you tried using the %RANGE in your QRYSLT parameter? It might make it easier to read. VilenaComment
-
Open query on 3 Files
Rahul, I was looking through my notes on OPNQRYF and found that the fields used in the KEYFLD parameter must be in the file specified on the FORMAT parameter. If you do not specify a format then all fields from all files will be included in the opnqryf. It looks like you may need to use one the suggestions from my first post to accomplish what you want. VilenaComment
-
Open query on 3 Files
Vilena, Here are my clarifications to your replies. Reply for Join field etc: If we remove the join field option and join order then the query retrieves the same number of records as it has retrieved in SQL but the ordering is not maintainted. In this if we mention the key fields of different file's other than that of mentioned in Format then they will be ignored. For eg if i mention SZON,HDIV,HDPT,DYR# only HDIV and HDPT will be considered. Also the date field HADT in FILE 1 is 6 and Zoned but the Date fields(DBDY,DEDY) in FILE 3 are of length 8 and zoned. Suppose HADT is 040525 and DBDY is 20040525 how would you compare these two? That's y i have taken the substring of both the date's. We cannot take substring of numeric data hence taken substring of char data of DBDY and DEDY. I feel we cannot select records using %RANGE for this problem as we have to mention the values in %RANGE field. We are checking whether this date falls in that range or not but we are not selecting records falling in that range. Please correct me if i'm wrong. For your OPNQRYF with KEYFLD parameter issue: If we specify join field condition then we can use the key fields of two files. I have tried it and it was working. I shall give the example of that also below. I am trying to join FILE 1 and FILE 2 and arrange the records in FILE 1 by Zone,Division,Department. Open query : OPNQRYF FILE((FILE1) (FILE2)) FORMAT(FILE1) QRYSLT('(HDIV *EQ 04) ') KEYFLD( (SZON) (HDIV) (HDPT) ) JFLD((HSTR SST R *EQ)) ) SQL Query for the same is: SELECT B.SZON,A.* FROM FILE1 A, FILE2 B WHERE (A.HDIV = 04 AND A.HSTR = B.SSTR ) ORDER BY B.SZON,A.HDIV, A.HDPT The open query mentioned above worked and retrieved the values in order which was retrieved in SQL query. Your advice in first post is perfectly correct but i want to achieve the same using open query. Please correct me if i'm wrong somewhere. Thanks in advance. thanks rahulComment
-
Comment