다중 조인 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 () 변수 값을 동적으로 삽입합니다.다음과 같은 한 줄 주석에 유의하십시오.
-- 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>
