Linking different field types

[BryanLane]

I am using Crystal 11.5.8.826.

The Purchasing portion [PurHdr] of our system uses a PO Number [PurHdr.Ponum] that is an 8-character string field and the Sales Order [OrdLin] system from the same vendor records the PO Number as a number field [OrdLin.Lpono].

I need to link the two table on PO Number. I created a formula in OrdLin that zero-pads the PO "number" field to convert it to an 8-character string field, but the formula is not available for linking the tables.

I assume I will need to create a Command to link the tables but I am not sure how to write the command formula to convert the number to a string.

Any suggestions or alternative methods?
Thanks!

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[Turkbear]

Hi,
Each database system has its own syntax for conversions ( text to number, number to text) so, even though most are very similar, knowing your database would help.

In Oracle it would be

Select ... from table
where
PurHdr.Ponum = To_Char(OrdLin,09999999)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Or you could do it in the from clause in the command for Oracle (Turkbear, shouldn't the numbers all be zeros?):

Select "PurHdr"."field1", "OrdLin"."field2"
from "PurHdr" inner join "OrdLin" on
"PurHdr"."Ponum" = to_char("OrdLin"."Lpono",'00000000')
where etc.

Another solution would be to use a subreport for one of the tables, and do the conversion in a formula that you can then use to link the sub to the main table. This becomes complicated though if you need to do calculations using the sub fields in the main report.

-LB

 

[Turkbear]

Hi,
The formating of text to numbers uses 9s as place holders/indicators with a leading 0 ( or trailing 0) to indicate whether to pad with leading/trailing zeros :

http://www.nlm.cz/cachedoc/v508/rsql/rsql_tochar.html[/URL]]
Number-to-String Conversion
The second use of TO_CHAR is to convert a number to a string.
The following table lists the valid format codes for the format parameter for this version of TO_CHAR.
Format Code Example Description
9 9999 Return value with the specified number of digits, with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
0 09999990 Return leading zeros. Return trailing zeros.
$ $9999 Return value with a leading dollar sign.
B B9999 Return blanks for the integer part of a fixed-point number when the integer part is zero (regardless of 0's in the format model).
S S9999


9999S Return negative value with a leading minus sign "-". Return positive value with a leading plus sign "+" .

Return negative value with a trailing minus sign "-". Return positive value with a trailing plus sign "+".
D 99D99 Return a decimal character (that is, a period "." ) in the specified position. Only one "D" is allowed in the format parameter.
G 9G999 Return a group separator in the position specified. The group separator used is the same as the one defined for the default locale. The default is ",". No group separators may appear to the right of the decimal.
FM FM90.9 Return a value with no leading or trailing blanks.
, 9,999 Return a comma in the specified position. No comma may appear to the right of the decimal. The format model may not begin with a comma.
. 99.99 Return a decimal point (that is, a period “.”) in the specified position. Only one "." is allowed in the format parameter.

More detailed examples here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Thanks, Turkbear. My mistake--I thought maybe it was a typo on your part, but wasn't sure. I've never had occasion to use this. So what would my formatting have returned? Anything? All zeros? I tried testing this in a command, but while it didn't error out, it was taking too long, so I didn't find out.

-LB

 

[Turkbear]

Hi LB,
Not sure what would have been returned, but I expect all 0s
or a syntax error. I no longer have access to a running Oracle instance or I would test it.

If you have SqlPlus try it in a simple select like:

Select To_Char(1234,'00000000' from Dual;

then

Select To_Char(1234,'09999999' from Dual;


It should be very fast with the result.








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Thanks, I'll try that.

-LB