Ciao a tutti,
Sto ancora imparando JMP e sono un po 'più a mio agio nello scrivere codice SQL per i data pull più avanzati. Esiste un buon modo per importare script SQL in JMP senza dover tornare indietro e modificare tutta la sintassi? Ho allegato la codifica SQL che voglio importare di seguito.
Grazie!
David
--[11/05/2020] David Peyer
-- Discover which COMP Test Stations produced the highest percentage of COMPs that passed Final Test
/****** General Data flow
Get the COMP TS from Production_Test_Data
Link COMP [Serial] to CB [ParentSerial] in Syspro******/
/*Get the test data*/
SELECT
COMP.[Serial] AS COMP_Serial
,COMP.[StartDateTime] AS COMP_Start
,COMP.[StationID] AS COMP_Station
,COMP.[PressureTestPressureExpected]
,COMP.[PressureTestPressureHighLimit]
,COMP.[PressureTestPressureLowLimit]
,COMP.[PressureTestPressureMeasurement]
,COMP.[PressureTestPressureResult]
,COMP.[PressureTestCurrentExpected]
,COMP.[PressureTestCurrentHighLimit]
,COMP.[PressureTestCurrentLowLimit]
,COMP.[PressureTestCurrentMeasurement]
,COMP.[PressureTestCurrentResult]
,COMP.[PressureTestVoltageExpected]
,COMP.[PressureTestVoltageHighLimit]
,COMP.[PressureTestVoltageLowLimit]
,COMP.[PressureTestVoltageMeasurement]
,COMP.[PressureTestVoltageResult]
,COMP.[PressureTestFlowExpected]
,COMP.[PressureTestFlowHighLimit]
,COMP.[PressureTestFlowLowLimit]
,COMP.[PressureTestFlowMeasurement]
,COMP.[PressureTestFlowResult]
,COMP.[PressureTestTemperatureHighLimit]
,COMP.[PressureTestTemperatureMeasurement]
,COMP.[PressureTestTemperatureResult]
,COMP.[VacuumTestPressureExpected]
,COMP.[VacuumTestPressureHighLimit]
,COMP.[VacuumTestPressureLowLimit]
,COMP.[VacuumTestPressureMeasurement]
,COMP.[VacuumTestPressureResult]
,COMP.[VacuumTestCurrentExpected]
,COMP.[VacuumTestCurrentHighLimit]
,COMP.[VacuumTestCurrentLowLimit]
,COMP.[VacuumTestCurrentMeasurement]
,COMP.[VacuumTestCurrentResult]
,COMP.[VacuumTestVoltageExpected]
,COMP.[VacuumTestVoltageHighLimit]
,COMP.[VacuumTestVoltageLowLimit]
,COMP.[VacuumTestVoltageMeasurement]
,COMP.[VacuumTestVoltageResult]
,COMP.[VacuumTestFlowExpected]
,COMP.[VacuumTestFlowHighLimit]
,COMP.[VacuumTestFlowLowLimit]
,COMP.[VacuumTestFlowMeasurement]
,COMP.[VacuumTestFlowResult]
,COMP.[VacuumTestTemperatureHighLimit]
,COMP.[VacuumTestTemperatureMeasurement]
,COMP.[VacuumTestTemperatureResult]
,COMP.[LeakTestPressureExpected]
,COMP.[LeakTestPressureHighLimit]
,COMP.[LeakTestPressureLowLimit]
,COMP.[LeakTestPressureMeasurement]
,COMP.[LeakTestPressureResult]
,COMP.[LeakTestFlowExpected]
,COMP.[LeakTestFlowHighLimit]
,COMP.[LeakTestFlowLowLimit]
,COMP.[LeakTestFlowMeasurement]
,COMP.[LeakTestFlowResult]
, FT.Serial AS FT_Serial
, FT.StartDateTime AS FT_Start
, FT.StationID AS FT_Station
, FT.TestName AS FT_TestName
, FT.TestResult AS FT_PassFail
, FT.TestValue AS FT_TestValue
, FT.TestLowLimit
, FT.TestHighLimit
FROM [Production_Test_Data].[dbo].[Production_Test_Compressor] COMP
/*Get the COMP Serial Number from Syspro*/
JOIN [SYSPROPROD].[dbo].[InvSerialTrack] COMP_SYS
on COMP_SYS.Serial = COMP.[Serial] COLLATE database_default
/*Self Join COMP Parent to MBM Serial from Syspro*/
JOIN [SYSPROPROD].[dbo].[InvSerialTrack] MBM
on COMP_SYS.[ParentSerial] = MBM.[Serial] COLLATE database_default
/*Self Join MBM Serial to CB Serial in Syspro*/
JOIN [SYSPROPROD].[dbo].[InvSerialTrack] CB
on MBM.[ParentSerial] = CB.[Serial] COLLATE database_default
/*Join FT Results to Vent Serial*/
JOIN [Production_Test_Data].[dbo].[Production_Test_Final_Test] FT
on CB.[ParentSerial] = FT.Serial COLLATE database_default
/*Filter out non-COMP data*/
where(COMP.StartDateTime > '2020-09-01 00:00:00.000'
AND COMP_SYS.[StockCode] LIKE 'PRT-00100-000'
)
Puoi inserire il tuo codice SQL in una stringa e quindi eseguire eseguire sql () su di essa.Uso anche evalinsert () per inserire dinamicamente i valori delle variabili.Nota che commenti su una riga come questo:
-- This is a comment
a volte può causare problemi.Meglio usare:
/* This is a comment */
Infine, se hai bisogno di virgolette doppie nel tuo codice SQL, puoi usare i costrutti JSL \ [e] \.
a = "\[This string has " a double quote]\";
Ecco il tuo codice rifatto in JSL:
//[11/05/2020] David Peyer
// Discover which COMP Test Stations produced the highest percentage of COMPs that passed Final Test
/****** General Data flow
Get the COMP TS from Production_Test_Data
Link COMP [Serial] to CB [ParentSerial] in Syspro******/
start_dt = "2020-09-01 00:00:00.000";
stock_code = "PRT-00100-000";
/*Get the test data*/
sql_statement = evalinsert(
"SELECT
COMP.[Serial] AS COMP_Serial
,COMP.[StartDateTime] AS COMP_Start
,COMP.[StationID] AS COMP_Station
,COMP.[PressureTestPressureExpected]
,COMP.[PressureTestPressureHighLimit]
,COMP.[PressureTestPressureLowLimit]
,COMP.[PressureTestPressureMeasurement]
,COMP.[PressureTestPressureResult]
,COMP.[PressureTestCurrentExpected]
,COMP.[PressureTestCurrentHighLimit]
,COMP.[PressureTestCurrentLowLimit]
,COMP.[PressureTestCurrentMeasurement]
,COMP.[PressureTestCurrentResult]
,COMP.[PressureTestVoltageExpected]
,COMP.[PressureTestVoltageHighLimit]
,COMP.[PressureTestVoltageLowLimit]
,COMP.[PressureTestVoltageMeasurement]
,COMP.[PressureTestVoltageResult]
,COMP.[PressureTestFlowExpected]
,COMP.[PressureTestFlowHighLimit]
,COMP.[PressureTestFlowLowLimit]
,COMP.[PressureTestFlowMeasurement]
,COMP.[PressureTestFlowResult]
,COMP.[PressureTestTemperatureHighLimit]
,COMP.[PressureTestTemperatureMeasurement]
,COMP.[PressureTestTemperatureResult]
,COMP.[VacuumTestPressureExpected]
,COMP.[VacuumTestPressureHighLimit]
,COMP.[VacuumTestPressureLowLimit]
,COMP.[VacuumTestPressureMeasurement]
,COMP.[VacuumTestPressureResult]
,COMP.[VacuumTestCurrentExpected]
,COMP.[VacuumTestCurrentHighLimit]
,COMP.[VacuumTestCurrentLowLimit]
,COMP.[VacuumTestCurrentMeasurement]
,COMP.[VacuumTestCurrentResult]
,COMP.[VacuumTestVoltageExpected]
,COMP.[VacuumTestVoltageHighLimit]
,COMP.[VacuumTestVoltageLowLimit]
,COMP.[VacuumTestVoltageMeasurement]
,COMP.[VacuumTestVoltageResult]
,COMP.[VacuumTestFlowExpected]
,COMP.[VacuumTestFlowHighLimit]
,COMP.[VacuumTestFlowLowLimit]
,COMP.[VacuumTestFlowMeasurement]
,COMP.[VacuumTestFlowResult]
,COMP.[VacuumTestTemperatureHighLimit]
,COMP.[VacuumTestTemperatureMeasurement]
,COMP.[VacuumTestTemperatureResult]
,COMP.[LeakTestPressureExpected]
,COMP.[LeakTestPressureHighLimit]
,COMP.[LeakTestPressureLowLimit]
,COMP.[LeakTestPressureMeasurement]
,COMP.[LeakTestPressureResult]
,COMP.[LeakTestFlowExpected]
,COMP.[LeakTestFlowHighLimit]
,COMP.[LeakTestFlowLowLimit]
,COMP.[LeakTestFlowMeasurement]
,COMP.[LeakTestFlowResult]
, FT.Serial AS FT_Serial
, FT.StartDateTime AS FT_Start
, FT.StationID AS FT_Station
, FT.TestName AS FT_TestName
, FT.TestResult AS FT_PassFail
, FT.TestValue AS FT_TestValue
, FT.TestLowLimit
, FT.TestHighLimit
FROM [Production_Test_Data].[dbo].[Production_Test_Compressor] COMP
/*Get the COMP Serial Number from Syspro*/
JOIN [SYSPROPROD].[dbo].[InvSerialTrack] COMP_SYS
on COMP_SYS.Serial = COMP.[Serial] COLLATE database_default
/*Self Join COMP Parent to MBM Serial from Syspro*/
JOIN [SYSPROPROD].[dbo].[InvSerialTrack] MBM
on COMP_SYS.[ParentSerial] = MBM.[Serial] COLLATE database_default
/*Self Join MBM Serial to CB Serial in Syspro*/
JOIN [SYSPROPROD].[dbo].[InvSerialTrack] CB
on MBM.[ParentSerial] = CB.[Serial] COLLATE database_default
/*Join FT Results to Vent Serial*/
JOIN [Production_Test_Data].[dbo].[Production_Test_Final_Test] FT
on CB.[ParentSerial] = FT.Serial COLLATE database_default
/*Filter out non-COMP data*/
where(COMP.StartDateTime > '^start_dt^'
AND COMP_SYS.[StockCode] LIKE '^stock_code^'
)");
dbc = create database connection("<YOUR-CONNECTION-STRING></YOUR-CONNECTION-STRING>
