Salut à tous,
J'apprends toujours JMP et je suis un peu plus à l'aise pour écrire du code SQL pour les extractions de données plus avancées. Existe-t-il un bon moyen d'importer des scripts SQL dans JMP sans avoir à revenir en arrière et à modifier toute la syntaxe? J'ai joint le codage SQL que je souhaite importer ci-dessous.
Merci!
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'
)
Vous pouvez mettre votre code SQL dans une chaîne, puis exécuter exécuter sql () dessus.J'utilise aussi evalinsert () pour insérer dynamiquement des valeurs de variable.Notez que les commentaires d'une seule ligne comme ceci:
-- This is a comment
peut parfois causer des problèmes.Mieux vaut utiliser:
/* This is a comment */
Enfin, si vous avez besoin de guillemets doubles dans votre code SQL, vous pouvez utiliser les constructions JSL \ [et] \.
a = "\[This string has " a double quote]\";
Voici votre code refait en 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>
