I have a 300+ line sql passthru and named some of the columns as a_fg, b_promised for sorting, my thinking was I would be able to save the file as focus hold file then rename them, I've tried using define and compute with no luck and ideas?


Secongd question is how do you change the properties of a column in a heading?

This message has been edited. Last edited by: DaveZ,

Why would you want to rename a column? You can always use the AS phrase while reporting...
Changing the properties of a column in a heading ... I think you mean the way the column is presented? If that's indeed the case, then use DEFINE to create a new field with the desired properties (format and edit options) and use that field in the heading.

Maybe I didn't explain myself clear enough...it's data that I'm trying to change not a column name. In sql I saved the data as a_fg, b_promised and c_ and d_ and so on, so when I sort on the column the the data comes out the way the user wants it. I've tried to do a define with no luck, but a define should work correct?

The DEFINE should in that case take care of stripping the a_ b_ c_ aso.
But you do need to keep sorting in that way, so you'll need both fields in the request:
BY ORIG_FIELD NOPRINT
BY DEF_FIELD
or some such code.

I'M GETTING THE FOLLOWING ERROR FOR DESCRIPTION ON FIle
code follows

(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: LOB1

APP PATH MFGVIS
APP DELETEF MFGVIS LOB1 MASTER
DEFINE FILE LOB1
RECORD_TYPE_2/A35 = IF RECORD_TYPE EQ 'A.FG' THEN 'FG' ELSE '';
AVG_AR_TERM/D20.0 = IF AVG_AR_TERMS GE 0 THEN AVG_AR_TERMS ELSE 0;
END
APP HOLD MFGVIS
END
ENGINE SQLORA SET DEFAULT_CONNECTION mfgvis

// SQL HERE


END
TABLE FILE SQLOUT
BY PLANT_C
BY PART_X
BY RECORD_TYPE
PRINT BALANCE
RECORD_TYPE
END_PAYWK
DESCR_X
YIELD_6_MONTH_N
INDUSTRY_CODE
AVG_AR_TERMS
ON TABLE NOTOTAL
ON TABLE HOLD AS LOB1 FORMAT FOCUS INDEX PLANT_C PART_X
END
TABLE FILE LOB1
SUM
'LOB1.SEG01.BALANCE'
BY 'LOB1.SEG01.PART_X' NOPRINT
BY RECORD_TYPE NOPRINT
BY RECORD_TYPE_2
ACROSS 'LOB1.SEG01.END_PAYWK' AS ''
ON 'LOB1.SEG01.PART_X' PAGE-BREAK
ON TABLE SUBHEAD
"company name"
"&DATEMDYY <+0> LINE OF BALANCE SUMMARY BY PLANT"
HEADING
"ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

Are there any othere errors being reported.

What happens if you add a -RUN before the TABLE FILE LOB1.

Are there records being written to the FOCUS file ?

Stick in your code a ?FF SQLOUT I general place it between the End of the SQL ":" and the TABLE FILE SQLOUT

When I review the list of field names I am generally surprised by at least one name or the capitalization.

I got rid of the ERROR FOR DESCRIPTION ON FIle my removing the delete of the hold file

now it does not recognize the define file
THE FIELDNAME IS NOT RECOGNIZED: RECORD_TYPE_2


-*APP DELETEF MFGVIS LOB1 MASTER
DEFINE FILE LOB1
RECORD_TYPE_2/A35=IF RECORD_TYPE EQ 'A.FG' THEN 'FG' ELSE '';
AVG_AR_TERM/D20.0=IF AVG_AR_TERMS GE 0 THEN AVG_AR_TERMS ELSE 0;
TERMS/D12=IF LOB1.SEG01.AVG_AR_TERMS GE 0 THEN LOB1.SEG01.AVG_AR_TERMS ELSE 0;
END

You have put the define before the SQL coding.
I think that should be done after retrieving the SQL output

Your defines do not work on the SQL tables.

Thanks to everyone for your help.

it is helpfull for this forum if you tell us how it is solved