I have two fields in two master files. One field is D20.2 (real) and the other is P11 (real). When I drap the field from one master file to the other, they will not connect. The data within these two fields is the same. Any ideas? I believe they need to be the same type. If so, what is a P11, and how can I change it to a D12.2. I cann't change the master file as that may affect others. Thanks

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

Tracie,

Look up define-based join. You can use a define to set the format of the join key in one file to match the format of the join key in the other file.

Tracie,

P and D are both numeric formats. Keep in mind that not only do they need to be in the same format, they need to be the same size. All you have to do to convert the P11 to a D20.2 is:
DEFINE yourfile
NEWFIELD/D20.2=OLDFIELD;
END


As Ginny suggested, you may be able to use a define-based join.

And be aware of the fact that joining D-fiels may not yield the desired results.
D-fields are binary fields, and the chance that the values in the fields are indeed 100% the same in two instances is quite slim. On screen they may look the same, but it is quite possible that the values may differ somewhere in the 20th decimal position. If that's the case, the join will not be successful, and you'll be wondering why.
My advice would be to convert both fields in both data files using a define to a A20.2 field and then MATCH on these calculated values.

Hope this helps...

quote:

a A20.2 field

???
hmmm.

quote:

a A20.2 field

yes, I hmmmm too...

quote:

a A20.2 field

Yeah, Wow

Well now, c'mon, y'all know what I mean.
Or don't you?

No...

GamP,

Well... if I quote you:

quote:

And be aware of the fact that joining D-fiels may not yield the desired results.
D-fields are binary fields, and the chance that the values in the fields are indeed 100% the same in two instances is quite slim. On screen they may look the same, but it is quite possible that the values may differ somewhere in the 20th decimal position. If that's the case, the join will not be successful, and you'll be wondering why.

then I wouldn't use anything of type 'D' for JOIN or for MATCH. Since the other field is a P, the probably the content of the D20.2 is also a whole number. I would try to convert the 'D' to a 'P', or if possible, both to a 'I'.
What do you say?

Are these FOCUS files? Bear in mind two caveats:

a. On platforms lacking packed-decimal arithmetic instructions, P format was (back in PC Focus days) stored internally as D, so the defined-P-to-real-P join would still be D-to-D. On Windows P is nowadays stored as packed (I just verified), so this may not be an issue.

b. In principle, P format has separate representations for +0 and -0. Not sure if -0 can actually occur, and if it would be treated as "equal" regardless. (Anyone know for sure?)

You can only obtain -0 if you program in assembler and if you are a bit crooked...

There was also the problem of distinguishing between +0 and 0 (signless 0) - same as above.

Brings back vague memories of Univac 1100 architecture (36 bit word, 6 and 9 bit character representations), whose fixed-point integer format had equal-size ranges of positive and negative integers (unlike the 360), but hence had two zeros: all binary 0's, and all 1's.

And Wang, based on 360, which allowd low-values in a packed field as a valid representation of zero (usefull for file compression): so 000c, 000d, and 0000 were all valid in a signed packed field.

What is type of files are the input files? If they are Oracle or DB2 create a view that does the join for you.

These are Oracle master files. We are trying to define a new variable on both master files and will see if those new elements will join. I will let you know. Thanks

remember, JOINs clear DEFINEs...
you might want to do a SET KEEPDEFINES ON
before your defines if you're going to try to set up a defined field on your guest file.
otherwise,
a defined-based join lets you transform the field on your host file to the same format as the field on the guest file.
That's how we do it here.
i'm sure i'm repeating something someone else has already told you...

Consider creating a view in Oracle that does the join for you. Oracle can join 2 numeric fields even when they are not the same length. Then create a master for the view. If you are using only a few of the fields from each table the data extraction is also faster with a view.

This one hasn't been solved quite yet. We did create a view that does the join and I still cannot display the field from the view.

 PO Order 
PO Note ID 
Document Text Note ID Document Text 
Note Text Field 
426308 22,630,118.00 22,630,118.00 . 
426308 22,630,119.00 22,630,119.00 . 
 

It does match the two note fields but the note_text pulled from the new view will not display.

 TABLE FILE IFS_PURCHASE_ORDER_LINE_ALL
PRINT 
     ORDER_NO AS 'PO Order'
     NOTE_ID AS 'PO Note ID'
     NOTE_ID AS 'Document Text Note ID'
     NOTE_TEXT AS 'Document Text,Note Text Field'
HEADING
""
FOOTING
""
WHERE ( ORDER_NO EQ '426308' ); 

The two are joined on Order number

This one has not been solved and I still need some assistance. Can someone removed the SOLVED from the text? thanks

tracie, you are the only one who can do that, as the thread owner

Can you post your MFD

Tracie,

Now Oracle has the join, and it still does not show.
Are you sure that the master file for IFS_PURCHASE_ORDER_LINE_ALL is the master file that has been created for the new Oracle view? For if it is and if the data is indeed in the separate physical tables, then this all of a sudden becomes an Oracle problem.
Please check again. And, as Pat suggests, please post your master file(s) and exact fex-code that you're using.

Here is the MFD.

FILENAME=IFS_WI_NOTE_ID_TEXT, SUFFIX=SQLORA  , $
  SEGMENT=IFS_WI_NOTE_ID_TEXT, SEGTYPE=S0, $
    FIELDNAME=ORDER_NO, ALIAS=ORDER_NO, USAGE=A12V, ACTUAL=A12V, $
    FIELDNAME=NOTE_ID, ALIAS=NOTE_ID, USAGE=D20.2, ACTUAL=D8, $
    FIELDNAME=NOTE_TEXT, ALIAS=NOTE_TEXT, USAGE=A2000V, ACTUAL=A2000V, $
  

Tracie,

This is a different master file as the one you mentioned earlier.
Are you able to just use this master file and get the text for the order?
If not, are you able to get the text by using the oracle software (sqlplus for instance)?
If you do see the text in this way, then what exactly is the join and table command where it fails to get the text?

The DBA made a type in the file name and recreated it. It has the same elements as the first one. I can just use the IFS_WI_NOTE_ID_TEXT master file and display the note text for the same order. When I join it to the purchase order line all master file I loose the note text.

In that case, the problem lies either with the join or with the other master file.
Does this other master file by accident also have a field called NOTE_TEXT?
If so, you need to qualify your field name to instruct webfocus that is has to come from the joined file (ie. print the IFS_WI_NOTE_ID_TEXT.NOTE_TEXT field).
If this is not the case or if it still does not print, then we'll defenitely have to see your master file(s) and code to try and find the cause of the problem.

Yes, both files have a NOTE_TEXT and the one on the purchase order line all is empty. How to I change the field in Webfocus to look at the note_text from the WI_IFS_NOTE_ID_TEXT master instead of the one? I changed the source, but it reverted back to the original

  ORDER_NO AS 'PO No'
     NOTE_ID AS 'PO Note ID'
     NOTE_ID AS 'Document Text,Note ID'
     NOTE_TEXT 

Thanks

Using DevStudio, are you?
In the object inspecter, right click and check Qualified FIeldnames (or some text to that effect). I think it's the first option in the menu.
The select your note_text field from the object inspector to show on the report.
That should do it.

That worked. Thank you so very much. This one is SOLVED.

One last question. How do I change the heading for this post adding , or does someone else do that? This forum has been extremely helpful to me and I want to ensure I know how to properly update by posts. Thanks