I am trying to parse through a string to see if values from another table appear in it in any position.
我试图解析一个字符串，以查看来自另一个表的值是否出现在任何位置。
select ROUTE,
case when ROUTE like '%' || b.AIRPORTCODE || '%' then 1
else 0 end as CLASS_B,
case when ROUTE like '%' || c.AIRPORTCODE || '%' then 1
else 0 end as CLASS_C
from FLIGHT_MESSAGE,
(select * from CLASS_B_C_AIRPORTS where CLASS_B_C = 'B') b,
(select * from CLASS_B_C_AIRPORTS where CLASS_B_C = 'C') c
The CLASS_B_C_AIRPORTS table will have an airport code (KDCA) and whether or not it is 'B' or 'C'.
CLASS_B_C_AIRPORTS表将包含机场代码（KDCA）以及它是否为“B”或“C”。
In my example below, ROUTE field will contain a string of text like:
在下面的示例中，ROUTE字段将包含一串文本，如：
KDCA..FLUKY.DCA246.PAUKI..MOL.FLCON6.KRIC/0127
For this string, I'd like to return the following, because KDCA is a Class B airport and KRIC is a Class C airport:
对于这个字符串，我想返回以下内容，因为KDCA是B级机场，而KRIC是C级机场：
| ROUTE | CLASS_B | CLASS_C |
----------------------------------------------------------------------
| KDCA..FLUKY.DCA246.PAUKI..MOL.FLCON6.KRIC/0127 | 1 | 1 |
This query currently returns 0's for Class B and Class C against this string.
此查询当前为此字符串返回Class B和Class C的0。
1 个解决方案
#1
1
I think this is what you want:
我想这就是你想要的：
SELECT ROUTE,
MAX(CASE WHEN CLASS_B_C = 'B' THEN 1 ELSE 0 END) as CLASS_B,
MAX(CASE WHEN CLASS_B_C = 'C' THEN 1 ELSE 0 END) as CLASS_C
FROM FLIGHT_MESSAGE fm JOIN
CLASS_B_C_AIRPORTS a
ON fm.ROUTE LIKE '%' || a.AIRPORTCODE || '%'
GROUP BY ROUTE;
#1
1
I think this is what you want:
我想这就是你想要的：
SELECT ROUTE,
MAX(CASE WHEN CLASS_B_C = 'B' THEN 1 ELSE 0 END) as CLASS_B,
MAX(CASE WHEN CLASS_B_C = 'C' THEN 1 ELSE 0 END) as CLASS_C
FROM FLIGHT_MESSAGE fm JOIN
CLASS_B_C_AIRPORTS a
ON fm.ROUTE LIKE '%' || a.AIRPORTCODE || '%'
GROUP BY ROUTE;