숫자인지 문자인지 확인 하는 방법
함수 명 : isnumeric
Determined whether an expression is a valid numeric type.
반환 값 :
문자 일 때 0
숫자 일 때 1 을 반환한다.
SQL OBJECT BROWSER 에서 FUNCTION 을 열어보면 미리 정의된 함수를 참고해서 사용할 수 있도록 보여 주고 있습니다.
문제 : UNION 으로 합친 자료의 한개 정보가 문자와, 숫자가 섞여 있고 한줄은 TEXT 정보, 한줄은 생산 계획 수량을 보여주고, 일별, 라인별 합계를 표시하고자 할때
예제 : SELECT SUM(iif(isnumeric( '99')=0, 0,CONVERT(decimal(20,2), '99')))
SELECT FACTORY , LINE_ID AS LINE
, SUM(iif(isnumeric(ORDERINFO)=0, 0,CONVERT(decimal(10,0), ORDERINFO)))AS [TOTAL]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '01' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [01]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '02' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [02]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '03' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [03]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '04' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [04]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '05' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [05]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '06' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [06]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '07' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [07]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '08' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [08]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '09' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [09]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '10' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [10]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '11' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [11]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '12' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [12]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '13' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [13]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '14' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [14]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '15' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [15]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '16' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [16]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '17' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [17]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '18' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [18]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '19' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [19]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '20' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [20]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '21' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [21]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '22' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [22]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '23' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [23]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '24' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [24]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '25' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [25]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '26' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [26]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '27' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [27]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '28' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [28]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '29' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [29]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '30' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [30]
,MIN(CASE WHEN SUBSTRING(DDDD,1,2) = '31' THEN CASE WHEN RIGHT(DDDD,1) <> '-' THEN ORDERINFO ELSE '-' END END ) AS [31]
FROM (
SELECT 'A' AS SORT,FACTORY, LINE_ID, THISMMSTART, THISMMEND, DDDD, [DAYS], HOLICNT,
STUFF(( SELECT ORDER_CODE+char(10)
FROM #TEMP_SCHEDULE
WHERE FACTORY = A.FACTORY
AND LINE_ID = A.LINE_ID
AND THISMMSTART = A.THISMMSTART
AND THISMMEND = A.THISMMEND
GROUP BY ORDER_CODE+char(10)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(100)'),1,0,'') +
MIN(ORDERINFO1) +
STUFF(( SELECT ORDERINFO2+char(10)
FROM #TEMP_SCHEDULE
WHERE FACTORY = A.FACTORY
AND LINE_ID = A.LINE_ID
AND THISMMSTART = A.THISMMSTART
AND THISMMEND = A.THISMMEND
GROUP BY ORDERINFO2+char(10)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(200)'),1,0,'')
+ PLAN_NO AS ORDERINFO
, SORT_SEQ
FROM #TEMP_SCHEDULE AS A
GROUP BY SORT_SEQ, FACTORY, LINE_ID, THISMMSTART, THISMMEND, DDDD, [DAYS], HOLICNT, PLAN_NO
UNION ALL
SELECT 'B' AS SORT, FACTORY, LINE_ID, THISMMSTART, THISMMEND, DDDD, [DAYS], HOLICNT, CONVERT(NVARCHAR,CONVERT(INTEGER,SUM(DAYQTY))) AS ORDERINFO
, SORT_SEQ
FROM #TEMP_SCHEDULE AS A
GROUP BY SORT_SEQ, FACTORY, LINE_ID, THISMMSTART, THISMMEND, DDDD, [DAYS], HOLICNT
UNION ALL
SELECT 'A' AS SORT, A.FACTORY, A.LINE_ID, NULL, NULL, A.DDDD, NULL, NULL,NULL, A.SORT_SEQ
FROM
(
SELECT FAC_SHORT AS FACTORY, C.DETAIL_CODE AS LINE_ID, A.SORT_SEQ
, CASE WHEN DAY_SECTION <> '10' THEN REPLICATE('0', 2 - LEN(CAST( DATEPART(DAY, C_DATE) AS VARCHAR))) + CAST( DATEPART(DAY, C_DATE) AS VARCHAR) + '-' ELSE REPLICATE('0', 2 - LEN(CAST( DATEPART(DAY, C_DATE) AS VARCHAR))) + CAST( DATEPART(DAY, C_DATE) AS VARCHAR) END AS DDDD
FROM BASIC_COMFAC A
INNER JOIN DBO.DL_CALENDAR D ON D.WORK_CODE = A.COM_PROD
-- INNER JOIN DBO.SYSTEM_CODE_MANAGEMENT_DETAIL B ON B.MAIN_CODE ='RUNLINE' AND B.DETAIL_CODE = A.COM_PROD+A.FAC_SHORT AND B.DELETE_CHECK <> 'T'
INNER JOIN DBO.SYSTEM_CODE_MANAGEMENT_DETAIL C ON C.MAIN_CODE ='FACLINE' AND C.DELETE_CHECK <> 'T'
WHERE C.DETAIL_CODE BETWEEN '01' AND A.LINES
AND C_DATE BETWEEN CONVERT(DATETIME, @YYYY + '-'+@MM +'-01', 121) AND DATEADD(MONTH, 1, CONVERT(DATETIME, @YYYY + '-'+@MM +'-01', 121))
AND A.COM_PROD = @COMPANY_CODE
AND A.FACTORY LIKE @FACTORY_CODE + '%' ) A
) SCHEDULE
GROUP BY SORT_SEQ, FACTORY , LINE_ID, SORT
ORDER BY SORT_SEQ, FACTORY, LINE_ID, SORT
DROP TABLE #TEMP_SCHEDULE
'VB.NET&MSSQL' 카테고리의 다른 글
HRESULT: 0x800A03EC (1) | 2022.07.20 |
---|---|
MSSQL DATAFILE 위치변경 (0) | 2021.12.29 |
IIS LOG FILE 삭제 방법 (0) | 2021.08.14 |
VB.NET 콤보박스 CHECKBOX 로 다중선택구현하기 (0) | 2020.07.17 |
VB.NET 관리자 권한으로 실행하기 (0) | 2020.06.26 |
댓글