[SOLVED] Printing Only Columns From One File In Dynamic Join

Hey,

I have a procedure which will create two hold files. The first file will have a dynamic number of columns depending on what the user selects. The second file just has a sorting column and the column by which it will join to the first file. I am doing this join so that I can sort by the sorting column in the second file. Right now I am having to do a print * because I do not know exactly which columns will be in the first file because the user selects them. When I do the print * after the join, the report is showing all the columns from both the first and the second file. I only want to show the columns from the first file after the join. Is there any way to do this? Any help is appreciated.

Thank you.

This message has been edited. Last edited by: Prince Joseph,

Webfocus 8, All Outputs

Posts: 47 | Location: Atlanta, GA | Registered: June 09, 2011

Doug

Expert

posted

Hide Post

Step 1: Ensure that BOTH files are in the same sort order...
Step 2: Let us know how that works out for you.

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted

Show Post

j.gross

Virtuoso

posted

Hide Post

What prevents you from including everything in a single HOLD file?

You could sort on the ordering field with NOPRINT, if you want to omit the columns from the HOLD file.

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

Prince Joseph

Silver Member

posted

Hide Post

I have to do this join because there is a column in the second file that I am joining to that I have to sort by.

This is the only way I can get the final result to be in the right order.

Maybe this will explain this better:

  
TABLE FILE &FILETOLOAD
PRINT
COL1
COMPUTE RW/I10 = LAST RW + 1;
ON TABLE HOLD AS TEMP_JOIN_FILE
END

TABLE FILE TEMP_JOIN_FILE
PRINT
RW
COL1
BY COL1 NOPRINT
ON TABLE HOLD AS FINAL_JOIN_FILE
END

JOIN COL1 IN TEMP_SQL TO COL1 IN FINAL_JOIN_FILE

TABLE FILE TEMP_SQL
PRINT *
BY RW NOPRINT
END

The TEMP_SQL file will have the dynamic number of columns that are being generated by sql passthru. The only column guaranteed to be there in TEMP_SQL is COL1. TEMP_SQL will not be in the same order as FINAL_JOIN_FILE without doing the join because all the sql that is being run will change the order of the rows in TEMP_SQL. As you can see I am doing a PRINT * at the end to print all my columns, but this will obviously print the columns from FINAL_JOIN_FILE as well which is what I don't want. What I want is to show all the columns from TEMP_SQL after I do the join and sort by RW. I can do a NOPRINT on RW to make sure that that doesn't show. Hopefully that clarifies my issue.

Thanks again.

Webfocus 8, All Outputs

Posts: 47 | Location: Atlanta, GA | Registered: June 09, 2011

Ignored post by Prince Joseph posted

Show Post

Tewy

Platinum Member

posted

Hide Post

You could try something like this before your join

TABLE FILE SYSCOLUM
PRINT
NAME
WHERE TBNAME EQ 'TEMP_SQL'
-* if TEMP_SQL is focus format you may want the next line
WHERE NAME NE 'FOCLIST'
ON TABLE HOLD AS FLD FORMAT ALPHA
END

and then your final table request becomes

TABLE FILE TEMP_SQL
PRINT
-INCLUDE FLD
BY RW NOPRINT
END

WF 7.6.11
Output: HTML, PDF, Excel

Posts: 123 | Location: UK | Registered: October 09, 2003

Ignored post by Tewy posted

Show Post

Prince Joseph

Silver Member

posted

Hide Post

Thanks so much for your help!

Webfocus 8, All Outputs

Posts: 47 | Location: Atlanta, GA | Registered: June 09, 2011

Ignored post by Prince Joseph posted