Hi ALL,

My master file join tables,i checked the join condition are linked correctly, but the records are not retrieved.

i checked database use the same condition and it return records, don't know why the webfocus is not retrieved records. Is it because the 2 fields column type format is different so that cannot use EQ to compare? CSCMA.CODE_VALUE is 'A10V', CSSDD.AREA_CODE is 'A4V'.

select CSCMA.code_full_desc
from CSSDD, CSCMA
where
CSCMA.CODE_VALUE = CSSDD.AREA_CODE
and CSCMA.CATEGORY_NAME='CAT2'
and CSSDD.ID = '0000002121212121'

all the 3 conditions are added in master file,
CSSDD.AREA_CODE EQ Field CSCMA.CODE_VALUE
CSCMA.CATEGORY_NAME EQ value 'CAT2'
CSSDD.ID EQ value '0000002121212121'


I have just tried change my oracle database table column size, after i changed table column size for CSSDD from varchar(4) to varachar(10) and import as master file again, there are records found. so that it means that it is the format different cause the issue. but how can we solve it? i should not change my database size.

Can help to advise? thanks.

This message has been edited. Last edited by: FP Mod Chuck,

quote:

Is it because the 2 fields column type format is different so that cannot use EQ to compare

Yes, should be same format. It's a better practice to have both fields with the same format.

What DBMS are your tables in?

quote:

Originally posted by BabakNYC:
What DBMS are your tables in?

Oracle 12c database

quote:

Originally posted by MartinY:

quote:

Is it because the 2 fields column type format is different so that cannot use EQ to compare

Yes, should be same format. It's a better practice to have both fields with the same format.

But my A10V table is a code table, it host codes that for other tables which exceeds length of 4 varchar, so it must be A10V.

Any other suggestion? thanks.

quote:

But my A10V table is a code table, it host codes that for other tables which exceeds length of 4 varchar, so it must be A10V.

You probably have something in CSSDD that identify which rows/code are for CSCMA table ?
So extract and Hold the code list specific for CSCMA table, reformat as a A4V and then JOIN the Hold table and CSCMA.

Add this to the top of your code:

-SET &ECHO=ALL;
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
SET TRACEON    = STMTRACE//CLIENT
SET TRACEON    = STMTRACE/2/CLIENT
SET TRACEUSER  = ON

Then run it and show us what you get. I'm not sure I quite understand what's happening. Do you get 0 Records found? Or do you get some records but not all? If we look at the SQL trace that is generated out of WF, and attempt to run that SQL in SQL*Plus what happens?

quote:

Originally posted by BabakNYC:
Add this to the top of your code:

-SET &ECHO=ALL;
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
SET TRACEON    = STMTRACE//CLIENT
SET TRACEON    = STMTRACE/2/CLIENT
SET TRACEUSER  = ON

Then run it and show us what you get. I'm not sure I quite understand what's happening. Do you get 0 Records found? Or do you get some records but not all? If we look at the SQL trace that is generated out of WF, and attempt to run that SQL in SQL*Plus what happens?

It is get 0 records found. after i changed my oracle database table column size for CSSDD to from varchar(4) to varachar(10) and import as master file again, there are records found. so that it means that it is the format different cause the issue. but how can we solve it? i should not change my database size.

That's most likely just because of:

SET XRETRIEVAL = OFF

The above group of statements shows a trace of your request, including what SQL gets sent to the RDBMS.

The reason Babak asked you to add that is so that the results are not masked by your table output.

I don't have access to Oracle so I tried this in MSS. I created two sql server tables. One is a copy of the car file with COUNTRY A10 and another with CNTRY A7V. I entered a few countries into CNTRY and joined it to the first table and wrote a simple request to bring back the matching rows between the two tables. I tried the join both in a TABLE request as well as a master file with the same outcome. Even though the format of the two fields in the join is different, the output contained the expected outcome.

I doubt if this behavior is any different in Oracle. Your findings based on changing the table structure are coincidental not causal. Here's what I tried:

FILENAME=JOINED_MSS, $
  SEGMENT=SEG01, CRFILE=IBISAMP/CAR_MSS, CRINCLUDE=ALL, $
  SEGMENT=SEG01, SEGTYPE=KU, PARENT=SEG01, CRFILE=IBISAMP/CNTRY, CRINCLUDE=ALL,
    JOIN_WHERE=COUNTRY EQ CNTRY;, $


FILENAME=CAR_MSS , SUFFIX=SQLMSS  , $
  SEGMENT=SEG01, SEGTYPE=S0, $
    FIELDNAME=FOCLIST, ALIAS=FOCLIST, USAGE=I5, ACTUAL=I4, $
    FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=CAR, ALIAS=CARS, USAGE=A16, ACTUAL=A16, $
    FIELDNAME=MODEL, ALIAS=MODEL, USAGE=A24, ACTUAL=A24, $
    FIELDNAME=BODYTYPE, ALIAS=TYPE, USAGE=A12, ACTUAL=A12, $
    FIELDNAME=SEATS, ALIAS=SEAT, USAGE=I3, ACTUAL=I4, $
    FIELDNAME=DEALER_COST, ALIAS=DCOST, USAGE=D7, ACTUAL=D8, $
    FIELDNAME=RETAIL_COST, ALIAS=RCOST, USAGE=D7, ACTUAL=D8, $
    FIELDNAME=SALES, ALIAS=UNITS, USAGE=I6, ACTUAL=I4, $
    FIELDNAME=LENGTH, ALIAS=LEN, USAGE=D5, ACTUAL=D8, $
    FIELDNAME=WIDTH, ALIAS=WIDTH, USAGE=D5, ACTUAL=D8, $
    FIELDNAME=HEIGHT, ALIAS=HEIGHT, USAGE=D5, ACTUAL=D8, $
    FIELDNAME=WEIGHT, ALIAS=WEIGHT, USAGE=D6, ACTUAL=D8, $
    FIELDNAME=WHEELBASE, ALIAS=BASE, USAGE=D6.1, ACTUAL=D8, $
    FIELDNAME=FUEL_CAP, ALIAS=FUEL, USAGE=D6.1, ACTUAL=D8, $
    FIELDNAME=BHP, ALIAS=POWER, USAGE=D6, ACTUAL=D8, $
    FIELDNAME=RPM, ALIAS=RPM, USAGE=I5, ACTUAL=I4, $
    FIELDNAME=MPG, ALIAS=MILES, USAGE=D6, ACTUAL=D8, $
    FIELDNAME=ACCEL, ALIAS=SECONDS, USAGE=D6, ACTUAL=D8, $



FILENAME=CNTRY, SUFFIX=SQLMSS  , $
  SEGMENT=SEG01, SEGTYPE=S0, $
    FIELDNAME=CNTRY, ALIAS=CNTRY, USAGE=A7V, ACTUAL=A7V, $


-SET &ECHO=ALL;
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
SET TRACESTAMP = OFF
SET TRACEON    = STMTRACE//CLIENT
SET TRACEON    = STMTRACE/2/CLIENT
SET TRACEUSER  = ON

TABLE FILE ibisamp/joined_mss
BY JOINED_MSS.SEG01.COUNTRY
BY JOINED_MSS.SEG01.CAR
BY JOINED_MSS.SEG01.CNTRY
ON TABLE PCHOLD FORMAT HTML

END

Here's the Trace:

SET XRETRIEVAL=OFF
 SET EMPTYREPORT=OFF
 SET TRACESTAMP = OFF
 SET TRACEON    = STMTRACE//CLIENT
 SET TRACEON    = STMTRACE/2/CLIENT
 SET TRACEUSER  = ON
 TABLE FILE ibisamp/joined_mss
 BY JOINED_MSS.SEG01.COUNTRY
 BY JOINED_MSS.SEG01.CAR
 BY JOINED_MSS.SEG01.CNTRY
 ON TABLE PCHOLD FORMAT HTML
 END
 -RUN
 AGGREGATION DONE ...
 SELECT
 T1."COUNTRY",
 T1."CARS",
 T2."CNTRY"
 FROM
 CAR_MSS T1,
 cntry T2
 WHERE
 (T2."CNTRY" = T1."COUNTRY")
 GROUP BY
 T1."COUNTRY",
 T1."CARS",
 T2."CNTRY"
 ORDER BY
 T1."COUNTRY",
 T1."CARS",
 T2."CNTRY";

Now if you run that SQL in the bottom of the trace in the native DBMS query tool you will find that you'll get a bunch of rows back. If you don't, then you should focus on the join behavior in Oracle. Unless you are joining tables from different DBMS's (e.g. Oracle --> FOCUS or HOLD FILE --> SQL Server) where the join is no longer executed inside the DBMS, you do not have to have the same field length. This might also happen when your code causes Optimization to be turned off. Things like DEFINE BASED JOINs could cause that. The SQL trace is where I think you should start. If the trace is not showing a single SELECT with a WHERE T1.TABLE = T2.TABLE you'll have to see why that's happening.