Does anyone know why I would this error or more important, what does mean?

(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: LOCATION

Here is my code, I don't see a problem --- is there a limitation for using join fields with format focus? If I only use one key(which would make my data wrong) I don't get an error. I would really hate to have to cfreate a temp field just for the join.

  
SQL
SELECT SLSRGN AS REGION, SLSVND AS VENDOR, SLSSTR AS STORE, SLSSTN AS STOREN,
SLSSTY AS STYLE,  SLSDSC AS STYLED, SLSSKU AS SKU, MAX(SLSCLR) AS COLOR,
SUM(SLSQTY) AS QUANTITY,
SUM(SLSRET) AS RETAIL,
SUM(SLSCST) AS COST
FROM SLSSKU
-IF &TYPE NE 'LTD'  GOTO SKIP_LTD;
-GOTO SKIPP
-SKIP_LTD
-IF &TYPE NE 'YTD'  GOTO SKIP_YTD;
WHERE SLSFYR = &YEAR
-GOTO SKIPP;
-SKIP_YTD
-IF &TYPE NE 'MTD'   GOTO SKIP_MTD;
WHERE SLSFYR = &YEAR AND SLSFPD = &MONTH
-GOTO SKIPP;
-SKIP_MTD
-SKIPP
GROUP BY SLSRGN, SLSVND, SLSSTR, 
SLSSTN, SLSSTY, SLSDSC, SLSSKU;
TABLE ON TABLE HOLD AS SLSTOP20 
FORMAT FOCUS INDEX  SLSSTR SLSSKU 
SLSRGN SLSVND  SLSSTN   SLSSTY SLSCLR
END
SQL
SELECT LOCNUM AS STORE, LOCSKU AS SKU, SUM(LO****) AS ONHAND
FROM LOCINV
GROUP BY LOCNUM, LOCSKU;
TABLE ON TABLE HOLD AS LOCATION 
FORMAT FOCUS INDEX  LOCNUM LOCSKU
END
JOIN
SLSTOP20.SEG01.SKU AND SLSTOP20.SEG01.STORE IN SLSTOP20 TO ALL
LOCATION.SEG01.SKU AND LOCATION.SEG01.STORE IN LOCATION AS J1
END

This message has been edited. Last edited by: <Mabel>,

When joining Focus files you can only use one field. So you would have to make temporary fields to join on.

That is so lame!!!!!

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

Lloyd, you are using SQL to retrieve data... so, in this case, it's not better change your SQL code in order to join the data?

Also you can use MATCH FILE.

Mikel

Not really. If you look at the sql, it's a many-to-one join file that I want summarize and filter before I join the files.

I am not that comfortable with match files. I use SQL mostly because it's second nature to me since I've used it for so long and work with it in many different languages. Match file is only good for webfocus so picking it up hasn't really been high on my to do list.

I have my hands in so many projects It's easier to go with what you know when you can.

n.selph, its just in the target file that a single key field is required. the source can specify several fields in the order they exist in the target key field.
so, in this case, in the creation of the LOCATION file, a single key needs to be made from the locnum and locsku fields and the join would be fine;

Lloyd,

We can all relate to being frustrated with certain aspects of any programing language or tool within our prfession. However, please refrain from posting objectionable comments which do not add value to this form or the question at hand (JOINing files).

Thanks.

Lloyd,

I must say that I agree with Mickey.

As SQL is so second nature then why have you not thought about achieving the whole thing in SQL?

Something along the lines of -

ENGINE SQLMSS SET SERVER data-adapter-id
SET SQLENGINE = SQLMSS (or what ever yours is)
SQL
SELECT SLSRGN AS REGION
     , SLSVND AS VENDOR
     , SLSSTR AS STORE
     , SLSSTN AS STOREN
     , SLSSTY AS STYLE
     , SLSDSC AS STYLED
     , SLSSKU AS SKU
     , MAX(SLSCLR) AS COLOR
     , SUM(SLSQTY) AS QUANTITY
     , SUM(SLSRET) AS RETAIL
     , SUM(SLSCST) AS COST
     , (SELECT SUM (LO****)
          FROM LOCINV S1
         WHERE T1.SLSSTR = S1.LOCNUM
           AND T1.SLSSKU = S1.LOCSKU
         GROUP BY LOCNUM, LOCSKU) AS ONHAND
  FROM SLSSKU T1
-IF &TYPE NE 'LTD'  GOTO SKIP_LTD;
-GOTO SKIPP
-SKIP_LTD
-IF &TYPE NE 'YTD'  GOTO SKIP_YTD;
WHERE SLSFYR = &YEAR
-GOTO SKIPP;
-SKIP_YTD
-IF &TYPE NE 'MTD'   GOTO SKIP_MTD;
WHERE SLSFYR = &YEAR AND SLSFPD = &MONTH
-GOTO SKIPP;
-SKIP_MTD
-SKIPP
GROUP BY SLSRGN
       , SLSVND
       , SLSSTR
       , SLSSTN
       , SLSSTY
       , SLSDSC
       , SLSSKU
;

should do the trick.

To all:
I am sorry if my comment offended anyone, but I have seen other members voice thier displeasure at certain answers that they disagree with, so I have no idea why I am being taken to task.???

Tony A: you are 100% correct, I normally shy away from sub-selects, but in this case it would fit bill perfectly. I just had it my mind to summarize then join.....

Lloyd,

You are free to express frustration and/or disagreement. I have done this myself on occasion when I disagree with a post. Your choice of words is what we are taking exception to. I cannot recall ever seeing any other post on Focal Point which used words that necessitated the use of * asterisks.

Ironically I experienced the same error this week which you began the discussion about. Although it was not in the context of using SQL. I found it frustrating as well being a little rusty with my JOIN syntax. We all can agree that some things are frustrating. However, when we express that frustration or disagreement on Focal Point we need to choose our words wisely.

Thanks for your understanding.

Mickey, point taken.

Tony A:
I get this error when I try the subselect, only thing I did differntly was I omitted:

ENGINE SQLMSS SET SERVER data-adapter-id
SET SQLENGINE = SQLMSS (or what ever yours is)

  
(FOC14069) SYNTAX ERROR ON LINE 12 AT 'SUM'  -- Expected ')'
 0 ERROR AT OR NEAR LINE     71  IN PROCEDURE MEMFEX  FOCEXEC *
 (FOC003) THE FIELDNAME IS NOT RECOGNIZED: ONHAND
 BYPASSING TO END OF COMMAND
 (FOC009) INCOMPLETE REQUEST STATEMENT

I've never used ENGINE or SQLENGINE, we have all of our tables on an AS/400 which we access thru EDA on a WINNT machine. When I tried those commands I got errors. What Do they do? how can I determine what values should be used?

Lloyd,

The method you are using (e.g. without the ENGINE etc.) is still being parsed first using the synonym. The SQL using this method cannot include subselects etc.

When you include the ENGINE etc. then the SQL will be passed (and not parsed) direct to the data adapter and therefore can include SQL syntax that the data adapter can handle.

I realise you have AS400 but without knowing what database you are targetting (e.g DB2, Oracle, MS SQL etc.) cannot help you with the ENGINE settings you require (I am also at home - told you I was sad). I'm sure that there is someone out there that can help we just need to know what database on your platform you are using.

T

Tony A:
We are DB2 on the AS/400.
My DB Name is MK400A

I don't think we have the adapters set up properly, we had a consulting group do our install and configuration. So Iam not sure what was setup and what wasn't. We took courses 351 and 354 which didn't cover this at all. I looked at some of the manual but they seem pretty daunting. What course would you suggest to take to get a handle on the setup and configuring of the adapters? There are techniques that I think would benefit our shop (such as Stored Procedures), But I don't know how to implement them.

Any suggestions?

Lloyd,

Sorry, can't advise on courses as they've changed somewhat since I did either of the two that I have done .

However, identifying your data adapter name and ENGINE type is easier than I made out (sorry). Just open one of your ACX files in text editor and note the CONNECTION value. Now open the equivalent MAS file and note the SUFFIX value.

You settings should be -

ENGINE [suffix value] SET SERVER [connection value]
SET SQLENGINE = [suffix value]

Hopefully that should get you going.

Good luck.

T

Tony:
OK, my Conection is MK400A, but my suffix value was set to EDA.

I just realized that the AS/400 was setup as a remote server. Will that make a difference?