oracle出现缺少null关键字或者其他缺失关键字处理

原创于 2022-04-24 08:43:21 发布 · 4.1k 阅读

CC 4.0 BY-SA版权

文章标签：

这篇博客讨论了在SQL查询中遇到的问题，特别是关于`istrue`关键字在MySQL和Oracle数据库之间的不兼容性。错误的SQL语句导致了缺失`null`关键字的错误。通过修正SQL，将`first_vaccine_date IS true`更改为`first_vaccine_date IS NOT NULL`，问题得到了解决。博客内容涉及数据库查询优化和SQL语法差异。

一般来说出现这种情况，就有可能是你的sql语句出现问题，或者是你的语法出现问题，要多进行检查。
例如 is true在mysql中可以用在oracle中却不能会出现缺失null关键字的报错

报错sql

  <select id="getVaccinated"  resultType="Map">
        SELECT
        sum( CASE WHEN second_vaccine_date IS NULL AND streng_vaccine_date IS NULL AND first_vaccine_date IS true THEN 1
        ELSE 0 END ) AS onlyFirstVaccine,
        sum( CASE WHEN second_vaccine_date IS Not Null THEN 1 ELSE 0 END ) AS completeSecondVaccine,
        sum( CASE WHEN streng_vaccine_date IS Not Null THEN 1 ELSE 0 END ) AS completeStrongVaccine,
        sum( CASE WHEN not_vaccine_type = '1' THEN 1 ELSE 0 END ) AS taboos,
        sum( CASE WHEN not_vaccine_type = '2' THEN 1 ELSE 0 END ) AS notArrange,
        sum( CASE WHEN not_vaccine_type = '3' THEN 1 ELSE 0 END ) AS otherReason
        FROM
        sys_user_depart s,
        epidemic_vaccination_info e,
        sys_user u
        WHERE
        s.user_id = e.id
        AND e.id = u.username
        AND u.user_identity= #{userTypeBks,jdbcType=VARCHAR}
        AND s.dep_id in
        <foreach item="item" collection="subDepartId" index="index" open="(" separator="," close=")">
            #{item,jdbcType=VARCHAR}
        </foreach>

    </select>

正确sql

  <select id="getVaccinated"  resultType="Map">
        SELECT
        sum( CASE WHEN second_vaccine_date IS NULL AND streng_vaccine_date IS NULL AND first_vaccine_date IS NOT NULL THEN 1
        ELSE 0 END ) AS onlyFirstVaccine,
        sum( CASE WHEN second_vaccine_date IS Not Null THEN 1 ELSE 0 END ) AS completeSecondVaccine,
        sum( CASE WHEN streng_vaccine_date IS Not Null THEN 1 ELSE 0 END ) AS completeStrongVaccine,
        sum( CASE WHEN not_vaccine_type = '1' THEN 1 ELSE 0 END ) AS taboos,
        sum( CASE WHEN not_vaccine_type = '2' THEN 1 ELSE 0 END ) AS notArrange,
        sum( CASE WHEN not_vaccine_type = '3' THEN 1 ELSE 0 END ) AS otherReason
        FROM
        sys_user_depart s,
        epidemic_vaccination_info e,
        sys_user u
        WHERE
        s.user_id = e.id
        AND e.id = u.username
        AND u.user_identity= #{userTypeBks,jdbcType=VARCHAR}
        AND s.dep_id in
        <foreach item="item" collection="subDepartId" index="index" open="(" separator="," close=")">
            #{item,jdbcType=VARCHAR}
        </foreach>

    </select>