Hi! I am working with a sqlite file that has two tables (bas...

[Mathijs .]

Hi!

I am working with a sqlite file that has two tables (base and varieties).

I want to include the field EVENT_ID from table VARIETIES into table BASE and save the result into a new table EVENTS in the db file that would have the following fields:

INT_ID

PANEL_ID_NEW

EVENT_ID

Here are the masters (I have shown only the fields of interest):

FILENAME=BASE, SUFFIX=SQLODBC , $

SEGMENT=BASE, SEGTYPE=S0, $

FIELDNAME=INT_ID, ALIAS=INT_ID, USAGE=A25, ACTUAL=A25,

MISSING=ON, $

FIELDNAME=PANEL_ID_NEW, ALIAS=PANEL_ID_NEW, USAGE=A25, ACTUAL=A25,

MISSING=ON, $

SEGMENT=VARIETIES, SEGTYPE=KM, PARENT=BASE, CRFILE=FARMTRAK/VARIETIES, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,

JOIN_WHERE=BASE.INT_ID EQ VARIETIES.INT_ID AND BASE.PANEL_ID_NEW EQ VARIETIES.PANEL_ID_NEW;, $

FILENAME=VARIETIES, SUFFIX=SQLODBC , $

SEGMENT=VARIETIES, SEGTYPE=S0, $

FIELDNAME=INT_ID, ALIAS=INT_ID, USAGE=A25, ACTUAL=A25,

MISSING=ON, $

FIELDNAME=PANEL_ID_NEW, ALIAS=PANEL_ID_NEW, USAGE=A25, ACTUAL=A25,

MISSING=ON, $

FIELDNAME=EVENT_ID, ALIAS=EVENT_ID, USAGE=A10, ACTUAL=A10,

MISSING=ON, $

The problem I am facing is that varieties does not have 100% matching entries to base. Hence using this method I am loosing entries from table base (left outer join).

The workaround I have found is to match the tables in a report in order to be able to add dummy values to the entries that do not match but I did not manage to save the request as a table in the same database (ON TABLE HOLD AS EVENTS FORMAT SAME_DB) which is essential as I am working with huge datafiles that I did not manage to import into focus tables.

Does anyone know how I can proceed to add EVENT_ID from varieties to base, add dummy values for non-matching entries and save the table as a new table to the sqlite.db file

[Martin Yergeau]

You could probably use the MATCH feature with AFTER MATCH HOLD OLD-OR-NEW and then hold the match file as an sql table

As per example for a MS-SQL table

MATCH FILE BASE

BY INT_ID

BY PANEL_ID_NEW

RUN

FILE VARIETIES

BY INT_ID

BY PANEL_ID_NEW

BY EVENT_ID

AFTER MATCH HOLD AS TMP OLD-OR-NEW

END

-RUN

 

ENGINE SQLMSS SET DEFAULT_CONNECTION MyConStringName

TABLE FILE TMP

BY INT_ID

BY PANEL_ID_NEW

BY EVENT_ID

ON TABLE HOLD AS dbo.MyTableName FORMAT SQLMSS DROP

END

-RUN

[David Beagan]

It looks like the FOCUS language SQL supports full outer join (webfocus 8.2.06). The example below is on two ibisamp data files. I tried this on two SQL Server master files, it did appear that the SQL passed to the database did have the FULL OUTER JOIN in it.

-* https://www.w3schools.com/sql/sql_join_full.asp

SQL

SELECT COURSE_CODE

, COURSE_NAME

, COURSECODE

, DURATION

FROM EDUCFILE

FULL OUTER JOIN COURSE

ON EDUCFILE.COURSE_CODE = COURSE.DURATION

END

 

 

 

 

 

COURSE_CODE

COURSE_NAME

COURSECODE

DURATION

 

 

 

 

.

.

AMA130

3

 

 

.

.

AMA680

4

 

 

.

.

AMA800

3

 

 

.

.

BIT420

6

 

 

.

.

BIT640

6

 

 

.

.

BIT650

3

 

 

.

.

EDP090

3

 

 

.

.

EDP130

5

 

 

.

.

EDP390

3

 

 

.

.

EDP690

5

 

 

.

.

EDP750

5

 

 

.

.

MC230

4

 

 

.

.

MC90

3

 

 

.

.

NAMA40

4

 

 

.

.

NAMA730

3

 

 

.

.

NAMA930

5

 

 

.

.

PDR330

3

 

 

.

.

PDR740

3

 

 

.

.

PDR870

5

 

 

.

.

PU168

4

 

 

.

.

PU440

4

 

 

.

.

SFC280

2

 

 

.

.

SSI220

4

 

 

.

.

SSI670

5

 

 

.

.

SU620

2

 

 

.

.

UMI710

3

 

 

.

.

UMI720

5

 

 

.

.

UNI620

4

 

 

.

.

UNI780

5

 

 

101

FILE DESCRPT & MAINT

.

.

 

 

102

BASIC REPORT PREP NON-PROG

.

.

 

 

103

BASIC REPORT PREP FOR PROG

.

.

 

 

104

FILE DESC & MAINT NON-PROG

.

.

 

 

106

TIMESHARING WORKSHOP

.

.

 

 

107

BASIC REPORT PREP DP MGRS

.

.

 

 

108

BASIC RPT NON-DP MGRS

.

.

 

 

201

ADVANCED TECHNIQUES

.

.

 

 

202

WHATS NEW IN FOCUS

.

.

 

 

203

FOCUS INTERNALS

.

.

 

 

301

DECISION SUPPORT WORKSHOP

.

.

 

 

302

HOST LANGUAGE INTERFACE

.

.

[Sarah Buccellato]

Mathijs, were the responses from MartinY and David helpful

[Mathijs .]

Hi,

Sorry I was on vacation last week and have not had much time to test the suggestions.

Unfortunately I am with webfocus 8105 so Davids solution does not work.

I will try and test Martins solution.

Actually I may not have been precise enough.

As table varieties is not complete I get the following result when it is joined to table base:

 

 

 

 

INT_ID

EVENT_ID

EVENT

 

 

 

 

158

2000

No Event

 

 

159

.

.

 

 

 

And here is what I wish to get:

 

 

 

 

INT_ID

EVENT_ID

EVENT

 

 

 

 

158

2000

No Event

 

 

159

998

no answer

[David Beagan]

If you want to fill in null values (MISSING) then you can:

DEFINE FILE something

EVENT_ID/A10 = IF EVENT_ID IS MISSING THEN '998' ELSE EVENT_ID;

EVENT/A25 = IF EVENT IS MISSING THEN 'no answer' ELSE EVENT;

END

[Mathijs .]

For some reason I never managed to get this kind of define to work.

I finally made it with a match and saved as sqlodbc