SQL Utility Freeform Mode Example

This topic contains information about the legacy SQL Utility, which is being phased out from GainSeeker Suite. It is still available for use in version 8.3, but is being replaced by the new GainSeeker SQL Utility function in the GainSeeker Utility module.

Using SQL Utility to Separate Compound Traceability – Using Freeform Mode

Problem Definition:

Say for example that Customer X has four separate traceability fields merged into traceability field one. Because up to 48 traceability fields are available, it is optimal to split out each value in traceability field one into its own column in the database tables, leaving one of the values in the current column. The customer is currently using 14 of the 30 available characters in traceability field one.

Current Layout of Traceability Field 1
Trace values	1 though 4
No. of characters	12341212341234
Sample Data	CHICPALNO1FLO1

Project Goal:

The project goal is three-fold:

Move trace value #1 into trace field 7
Leave trace value #2 as it is in trace field 1, but move it to the front of the field to eliminate the leading spaces
Move trace value #3 into UDL8
Move trace value #4 into UDL9
In addition, customer X wants to expand CHIC into Chicago, PA into Packaging, LN01 into Line 01, and FL01 into Filler 01.

Microsoft SQL Server Solution:

1. Select File, Freeform SQL

2. Enter the following SQL statements:

A. INSERT INTO VDat_Aux ( PartNoAux, DateTimeAux, UDL7, UDL8, UDL9, UDL10, UDL11, UDL12, UDL13, UDL14, UDL15, UDL16, UDL17, UDL18, UDL19, UDL20, UDL21, UDL22, UDL23, UDL24, UDL25, UDL26, UDL27, UDL28, UDL29, UDL30, UDL31, UDL32, UDL33, UDL34, UDL35, UDL36, UDL37, UDL38, UDL39, UDL40, UDL41, UDL42, UDL43, UDL44, UDL45, UDL46, UDL47, UDL48, Str1, Str2, Data9, Data10, Data11, Data12, Data13, Data14, Data15, Data16, Data17, Data18, Data19, Data20, Data21, Data22, Data23, Data24, Data25, Data26, Data27, Data28, Data29, Data30, Data31, Data32, Data33, Data34, Data35, Data36, Data37, Data38, Data39, Data40, Data41, Data42, Data43, Data44, Data45, Data46, Data47, Data48, Data49, Data50, Data51, Data52, Data53, Data54, Data55, Data56, Data57, Data58, Data59, Data60, Data61, Data62, Data63, Data64, Data65, Data66, Data67, Data68, Data69, Data70, Data71, Data72, Int1, Dbl1, Dbl2, Dbl3, Dbl4, Dbl5, Dbl6, Dbl7, Dbl8, Dbl9, Lng1, Lng2, Int2) SELECT PartNo, DateTime, Substring(UDL1, 1, 4), Substring(UDL1, 7, 4), Substring(UDL1, 11, 4), ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, 0, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, -1.6E+98, 0, 0, 0 FROM VData

When typing in this SQL statement, be careful not to press the Enter key to advance to the next line until you are finished with the entire statement. The SQL parser views the end of line caused by pressing the Enter key as the end of the SQL statement. Simply type in the entire statement and let the computer automatically scroll to the next line for you. Similarly, if you cut and paste the SQL statement into the SQL Utility, you may need to move to the end of each line and press the delete key. This will remove the Enter keys. If you don't do this, the above SQL statement may be treated as 29 different statements!

The SQL Utility will ask you if you want to execute the SQL statement after you press the Run SQL button. In freeform mode, it also numbers each SQL statement so you can be sure that you entered the text correctly. In this case, there should only be one SQL statement.

UPDATE VData SET Trace Field 1 = Substring (UDL1, 5, 2)

Statement A creates the data records in the auxiliary database. Statement B sets the value to just the fifth and sixth characters of the current trace value 1 (start at position five, use the next two characters). To set trace value 1 to the first four characters, use this command: SubString(UDL1, 1, 4)

Statement A creates the data records in the auxiliary database. Statement B sets the trace value 1 to just the fifth and sixth characters of the current value 1 (start at position five, use the next two characters). To set value 1 to the first four characters, use this command: SubString(Trace values 1, 1, 4)

3. Run the SQL Utility to expand the database values, e.g. to expand CHIC into Chicago.

4. If the customer forgets to move some of the traceability fields in the initial insert statement (2. A.), the VDat_Aux table can be updated at a later point using this SQL command:

UPDATE VDat_Aux SET UDL10 = Substring(UDL2, 13, 2) FROM VDat_Aux, VData WHERE PartNoAux = PartNo and DateTimeAux = DateTime

This command updates UDL10 using the 13th and 14th characters from UDL2.

Note: UDL1 - UDL6 are stored in the VData table. UDL7 - UDL48 are stored in the VDat_Aux table.

Oracle Solution:

Same as Microsoft SQL Server, except for:

- SQL statement 2A (replace the three occurrences of Substring with Substr).

- SQL statement 2B (replace Substring with Substr).

- SQL statement 4 (store the following text to a file, e.g. Ora_SPC.SQL, then run the PL/SQL script. To run the script, enter @ path\ora_spc.sql; at the > prompt in PL/SQL).

Ora_SPC.SQL contents:

--SET SERVEROUTPUT ON

--SET SERVEROUTPUT ON SIZE 1000000

DECLARE

-- psMsg VarChar(100);

-------------------------------------

Update VDat_Aux from VData 2

-------------------------------------

PROCEDURE lsubUpd2

( rsPN IN VARCHAR, rsDT IN VARCHAR)

CURSOR curVData IS SELECT UDL2 FROM VData WHERE PartNo = rsPN AND DateTime

DECLARE

-- psMsg VarChar(100);

-------------------------------------

Update VDat_Aux from VData 2

------------------------------------

PROCEDURE lsubUpd2

( rsPN IN VARCHAR, rsDT IN VARCHAR)

CURSOR curVData IS SELECT UDL2 FROM VData WHERE PartNo = rsPN AND DateTime = rsDT;

recVData curVData%ROWTYPE;

BEGIN

Open curVData;

Fetch curVData INTO recVData;

If curVData%FOUND then

UPDATE VDat_Aux SET UDL10 = Substr(recVData.UDL2, 13, 2) WHERE PartNoAux

= rsPN AND DateTimeAux = rsDT;

End If;

Close curVData;

END lsubUpd2;

---------------------------------

-- Update VDat_Aux from VData --

---------------------------------

PROCEDURE lsubUpd

plX INTEGER := 0;

CURSOR curVDat_Aux IS SELECT * FROM VDat_Aux; recVDat_Aux curVDat_Aux%ROWTYPE;

BEGIN
FOR recVDat_Aux IN curVDat_Aux
LOOP
plX := plX + 1;
lsubUpd2(recVDat_Aux.PartNoAux, recVDat_Aux.DateTimeAux);
If plX > 50 then
COMMIT;
plX := 0;
End If;
END LOOP;
COMMIT;
END lsubUpd;
-----------
-- Main --
-----------
BEGIN
lsubUpd();
END;
/

Pervasive ( Btrieve) Solution:

The Pervasive SQL syntax doesn't allow us to perform the above commands. Thus, you must e-mail the data files to us. We will load the data into MS SQL Server, convert it, re-load the data into Pervasive, and then e-mail the data files back to you. There may be an hourly charge associated with this service.