マルチ結合SQLスクリプトのインポートこんにちは、みんな、
私はまだJMPを学んでおり、より高度なデータプル用のSQLコードを書くのが少し快適です。 戻ってすべての構文を変更せずにSQLスクリプトをJMPにインポートする良い方法はありますか？ インポートしたいSQLコーディングを以下に添付しました。
ありがとう！
ダビデ
--[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'
)
データ表スクリプティングRe：マルチジョインSQLスクリプトのインポートSQLコードを文字列に入れて、実行することができますsql（）を実行しますその上に。私も使っていますevalinsert（）変数値を動的に挿入します。次のような1行のコメントに注意してください。
-- This is a comment
トラブルの原因となる場合があります。使用する方が良い：
/* This is a comment */
最後に、SQLコードで二重引用符が必要な場合は、JSL構造体\ [および] \を使用できます。
a = "\[This string has " a double quote]\";
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>
