sql - SQL Select 语句中的 IF THEN

Question

我正在编写一份报告以从日记账交易中返回供应商名称。以下是查询的基础知识。

我正在使用来自两个 View 的数据:

• 期刊详细信息 View = pa_journal_detail
• 供应商详情 View = ap_vendor

供应商编号匹配:

供应商编号包含在以下字段中:

1. ap_vendor.a_vendor_number
2. pa_journal_detail.jl_ref1 [在下面显示的某些条件下] 仅当期刊源代码是“API”或“APM”时

源代码存储在字段pa_journal_detail 中。 jl_source_code

供应商名称存储在字段 ap_vendor.a_vendor_name

这是我开始的查询。当我尝试运行时，它返回不正确的语法错误。

SELECT
CASE
          WHEN pa_journal_detail. jl_source_code = ‘API’
               OR pa_journal_detail. jl_source_code = ‘APM’
          THEN(
              SELECT  a_vendor_name
              FROM ap_vendor
              INNER JOIN pa_journal_detail 
              ON pa_journal_detail.jl_ref1 = ap_vendor.a_vendor_number)
          ELSE 0
  END as a_vendor_name, *
  FROM  pa_journal_detail

下面是完整的查询。我也尝试过包含“TOP 1”。我现在收到错误消息，指出关键字 AS 附近的语法不正确。

SELECT
   pa_journal_detail.a_project
  ,pa_journal_detail.jl_seg2
  ,pa_journal_detail.jl_seg3
  ,pa_journal_detail.jl_seg4
  ,pa_journal_detail.jl_source_code
  ,pa_journal_detail.jl_ref1
  ,pa_journal_detail.jl_gl_org
  ,pa_journal_detail.jl_gl_obj
  ,pa_journal_detail.jl_line_num
  ,pa_journal_detail.jl_journal_num
  ,pa_journal_detail.jl_jnl_year_period
  ,pa_journal_detail.jl_journal_number
  ,pa_journal_detail.jl_journal_seq
  ,(SELECT(CASE
              WHEN pa_journal_detail. jl_source_code = 'API'
                   OR pa_journal_detail. jl_source_code = 'APM'
              THEN(
                  SELECT TOP 1 a_vendor_name 
                  FROM ap_vendor
                  RIGHT JOIN pa_journal_detail 
                  ON pa_journal_detail.jl_ref1 = ap_vendor.a_vendor_number
                 )
              ELSE 0
      END as 'a_vendor_name', *
      FROM  pa_journal_detail))
FROM pa_journal_detail

这就是我最终得到的结果，现在可以正常工作了!谢谢大家!

SELECT
   pa_journal_detail.a_project
  ,pa_journal_detail.jl_seg2
  ,pa_journal_detail.jl_seg3
  ,pa_journal_detail.jl_seg4
  ,pa_journal_detail.jl_source_code
  ,pa_journal_detail.jl_ref1
  ,pa_journal_detail.jl_gl_org
  ,pa_journal_detail.jl_gl_obj
  ,pa_journal_detail.jl_line_num
  ,pa_journal_detail.jl_journal_num
  ,pa_journal_detail.jl_jnl_year_period
  ,pa_journal_detail.jl_journal_number
  ,pa_journal_detail.jl_journal_seq
  ,iif((pa_journal_detail.jl_source_code = 'API' 
        OR pa_journal_detail.jl_source_code = 'APM')
        ,(SELECT TOP 1 a_vendor_name 
                  FROM ap_vendor
                  RIGHT JOIN pa_journal_detail 
                  ON pa_journal_detail.jl_ref1 = ap_vendor.a_vendor_number)
         ,0) as  'a_vendor_name'
FROM pa_journal_detail

Answer 1

对于字符串比较你需要使用单引号

 CASE
              WHEN pa_journal_detail. jl_source_code = 'API'
                   OR pa_journal_detail. jl_source_code = 'APM'
              THEN(
                  SELECT top 1  a_vendor_name -- here you need limit or top 1
                  FROM ap_vendor
                  INNER JOIN pa_journal_detail 
                  ON pa_journal_detail.jl_ref1 = ap_vendor.a_vendor_number
                 )
              ELSE 0
      END as a_vendor_name, *
      FROM  pa_journal_detail