質問編集履歴
1
sqlを追加しました
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -11,9 +11,24 @@
|
|
|
11
11
|
既にSELECTで指定されている最後の項目の後に[,]は入れています。
|
|
12
12
|
|
|
13
13
|
元々実装されているSQLはPHPでは実行できますが、OSqlEditで実行すると
|
|
14
|
-
「
|
|
14
|
+
「＼」が「¥」に置き換えられ「式がありません」のエラーが出ます。
|
|
15
15
|
|
|
16
16
|
---
|
|
17
|
+
元々実装されているSQL
|
|
18
|
+
```SQL
|
|
19
|
+
SELECT MF_SISAKU_MAIN.SISKKNR_NO, MF_SISAKU_MAIN.TNT_CD, MF_SISAKU_MAIN.PAYMENT_CD, MF_SISAKU_MAIN.PAYMENT_TYPE,MF_SISAKU_MAIN.HSYCAL_KBN, MF_SISAKU_MAIN.HYKSIHYO_KBN,MF_SISAKU_MAIN.TNTBSY_CD, MF_SISAKU_MAIN.KOSIN_TIM, MF_SISAKU_MAIN.SISKDAT_SBT, MF_SISAKU_MAIN.PRIVATE_FLG, MF_SISAKU_MAIN.PAYAPLY_STAT,MF_SISAKU_MAIN.SISKAPLY_STAT,MF_SISAKU_MAIN.SIMU_STAT, HYKFROM_YM, HYKTO_YM, TKUISK_KNM, SISK_ID, SISK_KNM, HINGRP_KNM, HSY_KIN, PLN_KIN, JGN_KIN, SSKRYK_KNM, KNZSMEI_SEI_KNM, KNZSMEI_MEI_KNM,SISKJTI_KBN, SISKPTN_KBN, SISKOPE_STAT, TRKSAPLY_STAT, CATE_JTI.KNM AS CATE_JTI_KNM, CATE_SBT.KNM AS CATE_SBT_KNM, PAY.PAYMENT_KNM,SIMU_SBT.KNM AS SIMU_SBT_KNM,MKH_SBT.KNM AS MKH_SBT_KNM,CASE WHEN HYKTO_YM IS NOT NULL THEN TO_CHAR(ADD_MONTHS(SUBSTR(HYKTO_YM, 1, 4) || \'/\' || SUBSTR(HYKTO_YM, 5, 2) || \'/01\', 1), \'YYYYMM\') END AS HYKTO_NXT_YM,
|
|
20
|
+
FROM (SELECT MF_SISAKU.*, EGT0000P.EGT0102S(SISKKNR_NO, \'O\') || 01 AS SISKKIJUN_YMD FROM MF_SISAKU) MF_SISAKU_MAIN LEFT OUTER JOIN VW_TKUISK ON MF_SISAKU_MAIN.HYKHOJIN_CD = VW_TKUISK.TKUISK_CD AND SISKKIJUN_YMD BETWEEN VW_TKUISK.YUKOFROM_YMD AND VW_TKUISK.YUKOTO_YMD
|
|
21
|
+
LEFT OUTER JOIN MF_SISKHINGRP ON MF_SISAKU_MAIN.SISKKNR_NO = MF_SISKHINGRP.SISKKNR_NO AND SISK_SEQ = 1 AND HYKHSY_KBN = 1 AND HYOJI_SEQ = 1
|
|
22
|
+
LEFT OUTER JOIN MF_HINGRP ON MF_HINGRP.HINGRP_CD = MF_SISKHINGRP.HINGRP_CD AND SISKKIJUN_YMD BETWEEN MF_HINGRP.YUKOFROM_YMD AND MF_HINGRP.YUKOTO_YMD
|
|
23
|
+
LEFT OUTER JOIN VW_NEWSSK ON MF_SISAKU_MAIN.TNTBSY_CD = VW_NEWSSK.SSK_CD AND SISKKIJUN_YMD BETWEEN VW_NEWSSK.YUKOFROM_YMD AND VW_NEWSSK.YUKOTO_YMD
|
|
24
|
+
LEFT OUTER JOIN VW_KZNKHN ON MF_SISAKU_MAIN.TNT_CD = VW_KZNKHN.KZN_CD AND SISKKIJUN_YMD BETWEEN VW_KZNKHN.YUKOFROM_YMD AND VW_KZNKHN.YUKOTO_YMD
|
|
25
|
+
LEFT OUTER JOIN MF_CATEGORY CATE_JTI ON MF_SISAKU_MAIN.SISKJTI_KBN = CATE_JTI.KBN AND CATE_JTI.CTGRSBT_KBN = \'SISKJTI\'
|
|
26
|
+
LEFT OUTER JOIN MF_CATEGORY CATE_SBT ON MF_SISAKU_MAIN.SISKSBT_CD = CATE_SBT.KBN AND CATE_SBT.CTGRSBT_KBN = \'SISKSBT\'
|
|
27
|
+
LEFT OUTER JOIN MF_CATEGORY SIMU_SBT ON MF_SISAKU_MAIN.SIMU_STAT = SIMU_SBT.KBN AND SIMU_SBT.CTGRSBT_KBN = \'SIMUST\'
|
|
28
|
+
LEFT OUTER JOIN MF_CATEGORY MKH_SBT ON MF_SISAKU_MAIN.MKH_STAT = MKH_SBT.KBN AND MKH_SBT.CTGRSBT_KBN = \'MKHOST\'
|
|
29
|
+
LEFT OUTER JOIN VW_PAYMENT PAY ON MF_SISAKU_MAIN.PAYMENT_CD = PAY.PAYMENT_CD ;
|
|
30
|
+
```
|
|
31
|
+
|
|
17
32
|
追加するSQL
|
|
18
33
|
```SQL
|
|
19
34
|
select HYSCAL_KBN,HYKSIHYO_KBN
|
|
@@ -42,4 +57,19 @@
|
|
|
42
57
|
END AS KUBUN
|
|
43
58
|
|
|
44
59
|
from HOGE;
|
|
60
|
+
```
|
|
61
|
+
|
|
62
|
+
追加後のSQL
|
|
63
|
+
```SQL
|
|
64
|
+
SELECT MF_SISAKU_MAIN.SISKKNR_NO, MF_SISAKU_MAIN.TNT_CD, MF_SISAKU_MAIN.PAYMENT_CD, MF_SISAKU_MAIN.PAYMENT_TYPE,MF_SISAKU_MAIN.HSYCAL_KBN, MF_SISAKU_MAIN.HYKSIHYO_KBN,MF_SISAKU_MAIN.TNTBSY_CD, MF_SISAKU_MAIN.KOSIN_TIM, MF_SISAKU_MAIN.SISKDAT_SBT, MF_SISAKU_MAIN.PRIVATE_FLG, MF_SISAKU_MAIN.PAYAPLY_STAT,MF_SISAKU_MAIN.SISKAPLY_STAT,MF_SISAKU_MAIN.SIMU_STAT, HYKFROM_YM, HYKTO_YM, TKUISK_KNM, SISK_ID, SISK_KNM, HINGRP_KNM, HSY_KIN, PLN_KIN, JGN_KIN, SSKRYK_KNM, KNZSMEI_SEI_KNM, KNZSMEI_MEI_KNM,SISKJTI_KBN, SISKPTN_KBN, SISKOPE_STAT, TRKSAPLY_STAT, CATE_JTI.KNM AS CATE_JTI_KNM, CATE_SBT.KNM AS CATE_SBT_KNM, PAY.PAYMENT_KNM,SIMU_SBT.KNM AS SIMU_SBT_KNM,MKH_SBT.KNM AS MKH_SBT_KNM,CASE WHEN HYKTO_YM IS NOT NULL THEN TO_CHAR(ADD_MONTHS(SUBSTR(HYKTO_YM, 1, 4) || \'/\' || SUBSTR(HYKTO_YM, 5, 2) || \'/01\', 1), \'YYYYMM\') END AS HYKTO_NXT_YM, CASE HSYCAL_KBN WHEN 1 THEN CASE HYKSIHYO_KBN WHEN 01 THEN A WHEN 02 THEN A WHEN 03 THEN B WHEN 04 THEN B WHEN 05 THEN B WHEN 06 THEN B END WHEN 2 THEN CASE HYKSIHYO_KBN WHEN 01 THEN A WHEN 02 THEN A WHEN 03 THEN B WHEN 04 THEN B WHEN 05 THEN B WHEN 06 THEN B END WHEN 3 THEN C ELSE NULL END AS HOGE
|
|
65
|
+
FROM (SELECT MF_SISAKU.*, EGT0000P.EGT0102S(SISKKNR_NO, \'O\') || 01 AS SISKKIJUN_YMD FROM MF_SISAKU) MF_SISAKU_MAIN LEFT OUTER JOIN VW_TKUISK ON MF_SISAKU_MAIN.HYKHOJIN_CD = VW_TKUISK.TKUISK_CD AND SISKKIJUN_YMD BETWEEN VW_TKUISK.YUKOFROM_YMD AND VW_TKUISK.YUKOTO_YMD
|
|
66
|
+
LEFT OUTER JOIN MF_SISKHINGRP ON MF_SISAKU_MAIN.SISKKNR_NO = MF_SISKHINGRP.SISKKNR_NO AND SISK_SEQ = 1 AND HYKHSY_KBN = 1 AND HYOJI_SEQ = 1
|
|
67
|
+
LEFT OUTER JOIN MF_HINGRP ON MF_HINGRP.HINGRP_CD = MF_SISKHINGRP.HINGRP_CD AND SISKKIJUN_YMD BETWEEN MF_HINGRP.YUKOFROM_YMD AND MF_HINGRP.YUKOTO_YMD
|
|
68
|
+
LEFT OUTER JOIN VW_NEWSSK ON MF_SISAKU_MAIN.TNTBSY_CD = VW_NEWSSK.SSK_CD AND SISKKIJUN_YMD BETWEEN VW_NEWSSK.YUKOFROM_YMD AND VW_NEWSSK.YUKOTO_YMD
|
|
69
|
+
LEFT OUTER JOIN VW_KZNKHN ON MF_SISAKU_MAIN.TNT_CD = VW_KZNKHN.KZN_CD AND SISKKIJUN_YMD BETWEEN VW_KZNKHN.YUKOFROM_YMD AND VW_KZNKHN.YUKOTO_YMD
|
|
70
|
+
LEFT OUTER JOIN MF_CATEGORY CATE_JTI ON MF_SISAKU_MAIN.SISKJTI_KBN = CATE_JTI.KBN AND CATE_JTI.CTGRSBT_KBN = \'SISKJTI\'
|
|
71
|
+
LEFT OUTER JOIN MF_CATEGORY CATE_SBT ON MF_SISAKU_MAIN.SISKSBT_CD = CATE_SBT.KBN AND CATE_SBT.CTGRSBT_KBN = \'SISKSBT\'
|
|
72
|
+
LEFT OUTER JOIN MF_CATEGORY SIMU_SBT ON MF_SISAKU_MAIN.SIMU_STAT = SIMU_SBT.KBN AND SIMU_SBT.CTGRSBT_KBN = \'SIMUST\'
|
|
73
|
+
LEFT OUTER JOIN MF_CATEGORY MKH_SBT ON MF_SISAKU_MAIN.MKH_STAT = MKH_SBT.KBN AND MKH_SBT.CTGRSBT_KBN = \'MKHOST\'
|
|
74
|
+
LEFT OUTER JOIN VW_PAYMENT PAY ON MF_SISAKU_MAIN.PAYMENT_CD = PAY.PAYMENT_CD ;
|
|
45
75
|
```
|