Hello,

I am building an Accordian Report and am trying to represent a fraction in this form: "Numerator" "/" "Denominator" (example: 8/8, 3/10, 1/2 etc).
I am not trying to do an operation. How do I display both numerator and denominator in the same field?
I am working with extratcs from a SQLOUT query.


Here is an example:

 
ENGINE SQLMSS SET DEFAULT_CONNECTION DATABASE
SQL SQLMSS PREPARE SQLOUT FOR
................SQL Code..................
END
 
DEFINE FILE SQLOUT
a/A3=Numerator;
b/A3=Denominator;
c/A1='/';
abc/A7= a | c | b;
END

TABLE FILE SQLOUT
SUM
     Total
BY
     Country
Print
     Sub-Total
     abc AS 'ABC'

BY  City
END

HEADING
" "
"Accordian Report"
" "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET EXPANDABLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

Are the numerator and denominator decimal values or alphanumeric? If they are decimal values then your code will not work. You will need convert from decimal to alphanumeric. I tried a sample using the CAR file and it worked. The code is below.

 
DEFINE FILE CAR 
NUM/A3='3';
DNUM/A3='4';
SYMBOL/A1='/';
END
TABLE FILE CAR
PRINT 
     'CAR.COMP.CAR'
     'CAR.CARREC.MODEL'
     COMPUTE WHOLEFRAC/A7 = NUM | SYMBOL | DNUM;
BY 'CAR.ORIGIN.COUNTRY'
END

If the numerator and denominator values are decimal you might want to try the following.

 
DEFINE FILE SQLOUT
NUM/A3=EDIT(NUMERATOR,'999');
DNUM/A3=EDIT(DENOMINATOR,'999');
SYMBOL/A1='/';
END

I hope this helps.

Dave

H8K,

When working with multi-set requests as your example does, you'll need to repeat all previous sort phrases. Your example should read:

TABLE FILE SQLOUT
SUM     Total
BY      Country
Print   Sub-Total
        abc AS 'ABC'
BY      Country
BY      City

HEADING
" "
"Accordian Report"
" "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET EXPANDABLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

Maybe that helps ...

Thank you to both! Both tips were very helpful.

dburton, I used your second example because the fields are decimals:

quote:

DEFINE FILE SQLOUT
NUM/A3=EDIT(NUMERATOR,'999');
DNUM/A3=EDIT(DENOMINATOR,'999');
SYMBOL/A1='/';
END

But the problem is I get "000/000" as outputs for the computed "fraction" field. I get the SUM of the NUMERATORfield and the SUM of the DENOMINATORfield, but I get "000/000" outputs for the "fraction" field.
Any idea how I can get the SUM of the "NUMERATORfield" + "/" + "the SUM of the DENOMINATORfield" in the likes of (9/10, 2/8, 6/7 etc)?
Here is Code I used:

  
DEFINE FILE SQLOUT
a/A3=EDIT(NUMERATORfield,'999');
b/A3=EDIT(DENOMINATORfield,'999');
c/A1='/';
END
TABLE FILE SQLOUT
SUM 
     NUMERATORfield  AS 'field1'
     DENOMINATORfield AS 'field2'
     COMPUTE fraction/A7 = a|c|b;	 
     
BY  LOWEST COUNTRY

SUM
      NUMERATORfield  AS 'field1'
      DENOMINATORfield AS 'field2'
	  COMPUTE fraction/A7 = a | c | b;	 
BY  LOWEST COUNTRY
BY  LOWEST CITY
PRINT

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

quote:

I get "000/000" as outputs for the computed "fraction" field

That leads to the assumption that both fields have a numeric format that is more than 3 positions.
If you use EDIT for an integer field, it will produce output with leading zeroes.
But, from your code it seems that both fields already are alphanumeric. However, you do a SUM for them which apparently produces correct output. Hmmm. Something is defenitely not correct here.
I'd first check to see what format the NUMERATORfield and DENOMINATORfield have.
Then, use the edit statement as you already have it if the formats are A, but be sure to copy the correct positions; or if the formats are I, use EDIT(EDIT(field),'mask'); but if the formats are D use EDIT(FTOA(...),'mask').

Hi,

This should help you out:

  
TABLE FILE CAR
PRINT 
     COUNTRY
     CAR
     BODYTYPE
     MPG
     SALES
     COMPUTE FRAC/A13 = FTOA(MPG,'(D6c)','A6') || '/' || LJUST(6,FTOA(SALES,'(D6c)', 'A6'), 'A6');
END

The reason that you are getting 0 is because you are only picking up the first 3 digits of the amount fields, which are ussualy 0. You should change your defines to pick up the last 3 digits. This can be done several ways. The easiest is to put '$' in front of the define for each digit you are bypassing. There are many other ways of doing this.

quote:

Originally posted by RSquared:
The reason that you are getting 0 is because you are only picking up the first 3 digits of the amount fields, which are ussualy 0. You should change your defines to pick up the last 3 digits. This can be done several ways. The easiest is to put '$' in front of the define for each digit you are bypassing. There are many other ways of doing this.

This is correct. You need to adjust the length of your fields depending on the length of the NUMERATOR and DENOMINATOR. If each of the decimal fields are D12 then your alpha fields need to be the same. Sample below.

  
DEFINE FILE SQLOUT
NUM/A12=EDIT(NUMERATOR,'$$$$$$$$$999');
DNUM/A12=EDIT(DENOMINATOR,'$$$$$$$$$999');
SYMBOL/A1='/';
END

Thank you both Again! I'm getting the correct output!
This ticket is Solved [SOLVED] !!!