SELECT t1.contract_code, t1.contract_name, t1.specialty_code, t1.specialty_name, t1.end_date, t1.send_date, t1.sheet_state, t1.actual_free, t1.actual_works, CASE when t1.region_code =t2.area_code then t2.dim2_code ELSE NULL END AS cityCode, CASE WHEN t1.county_code = t2.area_code THEN t2.dim2_code ELSE NULL END AS countryCode FROM flow_designer_gz.flow_paypertime_view AS t1 join flow_designer_gz.gz_dim_area_mapping AS t2 ON (t1.county_code = t2.area_code OR t1.region_code = t2.area_code)

您的查询语句看起来是正确的，将 flow_paypertime_view 表和 gz_dim_area_mapping 表连接起来，并根据条件判断来获取 cityCode 和 countryCode 的值。请注意，您在 SELECT 语句中使用了 t2.dim2_code，这是根据您的要求来获取对应的 dim2_code。

如果您有任何其他问题或需要进一步帮助，请随时告诉我。

相关问题

表1flow_paypertime_view 字段contract_code, contract_name,specialty_code,specialty_name,end_date,send_date,sheet_state,actual_free,actual_works,county_code,region_code 表2gz_dim_area_mapping 字段 dim2_code,area_code 现在要查询表一中 county_code或者region_code等于表2area_code的数据对应的dim2_code,结果字段为contract_code, contract_name,specialty_code,specialty_name,end_date,send_date,sheet_state,actual_free,actual_works,region_code 如果region_code等于dim2_code,则结果字段为cityCode,如果county_code等于dim2_code,则结果字段显示为countryCode

您可以使用以下 SQL 查询语句来实现您的需求：

SELECT
    t1.contract_code,
    t1.contract_name,
    t1.specialty_code,
    t1.specialty_name,
    t1.end_date,
    t1.send_date,
    t1.sheet_state,
    t1.actual_free,
    t1.actual_works,
    CASE
        WHEN t1.region_code = t2.dim2_code THEN t2.area_code
        ELSE NULL
    END AS cityCode,
    CASE
        WHEN t1.county_code = t2.dim2_code THEN t2.area_code
        ELSE NULL
    END AS countryCode
FROM
    flow_paypertime_view AS t1
LEFT JOIN
    gz_dim_area_mapping AS t2 ON (t1.county_code = t2.area_code OR t1.region_code = t2.area_code)

这个查询语句使用了 LEFT JOIN 来连接两个表，并通过 CASE WHEN 语句来判断条件。如果 region_code 等于 dim2_code，则结果字段为 cityCode，如果 county_code 等于 dim2_code，则结果字段为 countryCode。

SELECT '155427848241370' || SEQ_KD_INDEX.nextval || 'GJ', a.PROBABLECAUSE, ( SELECT zhFUNc_GETNEWIDFORCIR ( id ) FROM gistar.tdn_switch ch WHERE ch.ipaddress = a.IP AND ROWNUM = 1 ), '1028200001', ( SELECT name FROM gistar.tdn_switch ch WHERE ch.ipaddress = a.IP AND ROWNUM = 1 ), ( SELECT code FROM gistar.tdn_switch ch WHERE ch.ipaddress = a.IP AND ROWNUM = 1 ), a.IP, a.ALARMTIME, a.alarmtext, SYSDATE, NULL, a.alarmid, a.createtime, a.perceivedseverity, a.moname FROM ( SELECT x.*, ( SELECT COUNT( DISTINCT re.coveraddressid ) FROM gistar.jr_customer_record @yjyx_zy re WHERE re.project_type IN ( 'FTTH宽带' ) AND re.oltip = x.IP ) AS oltcount FROM gistar.t_trouble_alarminfo x ) a WHERE a.specialty = 'PON' AND a.motype = 'OLT' AND IP IS NOT NULL AND a.PROBABLECAUSE = 'OLT网管脱网' AND ( SELECT substr( b.tt_number, 0, 2 ) FROM fwbz.t_trouble_ticket_new @GDOSSDB_SGDD b WHERE a.tt_id = b.tt_id ) = 'NM' AND ( SELECT title FROM fwbz.t_trouble_ticket_new @GDOSSDB_SGDD b WHERE a.tt_id = b.tt_id ) = 'OLT网管脱网' AND ( ( SYSDATE < '2021-05-01' AND a.oltcount >= 600 ) OR a.oltcount >= 400 ) UNION ALL SELECT '155427848241370' || SEQ_KD_INDEX.nextval || 'GJ', a.PROBABLECAUSE, ( SELECT zhFUNc_GETNEWIDFORCIR ( id ) FROM gistar.tdn_switch ch WHERE ch.ipaddress = a.IP AND ROWNUM = 1 ), '1028200001', ( SELECT name FROM gistar.tdn_switch ch WHERE ch.ipaddress = a.IP AND ROWNUM = 1 ), ( SELECT code FROM gistar.tdn_switch ch WHERE ch.ipaddress = a.IP AND ROWNUM = 1 ), a.IP, a.ALARMTIME, a.alarmtext, SYSDATE, a.cleartime, a.alarmid, a.createtime, a.perceivedseverity, a.moname FROM ( SELECT x.*, ( SELECT COUNT( DISTINCT re.coveraddressid ) FROM gistar.jr_customer_record @yjyx_zy re WHERE re.project_type IN ( 'FTTH宽带' ) AND re.oltip = x.IP ) AS oltcount FROM gistar.t_trouble_alarminfo x ) a WHERE a.specialty = 'PON' AND a.motype = 'OLT' AND IP IS NOT NULL AND a.PROBABLECAUSE = 'OLT网管脱网' AND ( SELECT substr( b.tt_number, 0, 2 ) FROM fwbz.t_trouble_ticket_new @GDOSSDB_SGDD b WHERE a.tt_id = b.tt_id ) = 'NM' AND ( SELECT title FROM fwbz.t_trouble_ticket_new @GDOSSDB_SGDD b WHERE a.tt_id = b.tt_id ) = 'OLT网管脱网' AND ( ( SYSDATE < '2021-05-01' AND a.oltcount >= 600 ) OR a.oltcount >= 400 ) AND a.cleartime IS NOT NULL AND a.alarmid IN ( SELECT alarm_id FROM olt_ALARM_BILL GROUP BY alarm_id, device_ip, alarm_time, create_time HAVING count( * ) = 1 );报错ORA-02287: sequence number not allowed here

这个 SQL 语句中使用了 SEQ_KD_INDEX.nextval，而在 SELECT 语句中不能直接使用序列的 nextval() 方法，因为 nextval() 方法不是一个常量或字面值，而是一个函数，因此不能在字符串中直接使用。要解决这个问题，可以将 nextval() 放在一个子查询中，然后在 SELECT 语句中引用该子查询的结果。例如，可以将第一个 SELECT 语句修改如下：

SELECT '155427848241370' || (SELECT SEQ_KD_INDEX.nextval FROM dual) || 'GJ', a.PROBABLECAUSE, ...

这样就可以在字符串中引用 nextval() 方法的返回值了。注意，这里使用了 Oracle 中的 dual 表来执行子查询，因为在 Oracle 中，必须使用 FROM 子句来指定一个表，即使这个表只包含一行数据也是如此。