Multiple queries - final version of the template

After using the Template Wizard to edit the first draft of the template and to debug the edited copy, you should arrive at a final version of the template which correctly enters the defect data from the database tables into GainSeeker.  

A sample of a working template is displayed below, with the changes from the first draft highlighted.

Column
Heading

Cell

Formula

Connect

B1

NVar 81 = Q_CONNECT 1, "Defect_Data", "Y", "N":

NVar 82 = Q_CONNECT 2, "Defect_Data", "Y", "N":

NVar 83 = Q_CONNECT 3, "Defect_Data", "Y", "N":

NVar 89 = Q_CONNECT 9, "GainSeeker71_B", "Y", "N":

If NVar 81 = 0, GOTO [S1]

Outer Query

C1

SVar 81 =  "Select * from LOG_ALL where Processed = 0":

NVar 81 = Q_START 1, SVar 81:

If NVar 81 = 0, GOTO [S1]

Date/Time

D1

SVar 1 = Date_Format Q_COLUMN 1, 2, "", "MDY" :

SVar 11 = CONCAT "CONVERT (DATETIME, '",
(Format (Q_COLUMN 1, 2, ''), 'yyyy-mm-dd hh:nn:ss'), "', 102)":

SVar 1

Serial #

E1

SVar 3 = Q_COLUMN 1, 0, "" :

SVar 3

DMS Set

L1

DB_RESET 0:

DB_SET_VALUE "Part_Number", SVar 95:

DB_SET_VALUE "Process", SVar 94:

DB_SET_VALUE "Sample_Size", '1':

If (INSTR (Q_COLUMN 1, 3, ""), "P"),
(DB_SET_VALUE "NCU", "0") + GOTO [O1],
(DB_SET_VALUE "NCU", "1") + GOTO [M1]:

DB_SET_VALUE "DateTime", Prep_Q SVar 1:

DB_SET_VALUE "Trace_1", Prep_Q SVar 3:

DB_SET_VALUE "Trace_4", Prep_Q Q_COLUMN 1, 4, ""

Inner Query

M1

SVar 82 =  CONCAT "Select * from LOG_FAIL
where Processed = 0 and DateTime = ", SVar 11,
" and PartNo = '", SVar 95, "' and Serial = '", SVar 3, "'" :

NVar 82 = Q_START 2, SVar 82:

NVar 9 = 1

Inner Next

N1

SVar 9 = CONCAT "Defect_Description", CSTR NVar 9:

SVar 11 = CONCAT "Defect_Count", CSTR NVar 9:

DB_SET_VALUE SVar 9, (Prep_Q (Q_COLUMN 2, 2, "")) :

DB_SET_VALUE SVar 11, "1" :

If Q_NEXT 2 = 1,
(GOTO [N1]) + (NVar 9 = (NVar 9) + 1),
(NVar 9 = 0)

DMS Save

O1

SVar 93 = DB_DATA_SQL "DMS*":

NVar 99 = Q_EXECUTE 9, SVar 93 :

SVar 83 =  CONCAT "Update LOG_ALL set Processed = 1
where Processed = 0 and DateTime = ", SVar 11,
" and PartNo = '", SVar 95, "' and Serial = '", SVar 3, "'" :

NVar 83 = Q_START 3, SVar 83 :

SVar 83 =  CONCAT "Update LOG_FAIL set Processed = 1
where Processed = 0 and DateTime = ", SVar 11,
" and PartNo = '", SVar 95, "' and Serial = '", SVar 3, "'" :

NVar 83 = Q_START 3, SVar 83

Outer Next

Q1

If Q_NEXT 1 = 0, GOTO [S1], GOTO [D1]

Terminate

S1

Exit

 

More:

Working with multiple SQL statements