CODEWITHSUNDEEP
sqlsql-servert-sql
Rebecca Christian
Rebecca Christian

Reputation: 33

IF THEN in SQL Select Statement

I'm writing a report to return vendor names from journal transactions. Here are the basics of the query below.

I am using data from two views:

Match on Vendor Number:

The vendor number is contained in the following fields:

  1. ap_vendor.a_vendor_number
  2. pa_journal_detail.jl_ref1 [under certain criteria shown below] ONLY WHEN the journal source code is "API" or "APM"

The source code is stored in the field pa_journal_detail. jl_source_code

The vendor name is stored in the field ap_vendor.a_vendor_name

This is the query I had started with. It is returning incorrect syntax errors when I attempt to run. 

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

Here is the full query below. I have also tried it with the "TOP 1" included as well. I am now getting the error that there is incorrect syntax near the keyword 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

This is what I ended up with and its working now! Thank you all!

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

Upvotes: 3

Views: 116

Answers (3)

JBJ
JBJ

Reputation: 403

There is also iif(). I use it far more often than I should, I just like have a tiny little if for simple conditional work instead of big ole' Case statement.

select iif(1 = 1,'True','False') 
-- any series that results in a boolean
select iif((1 = 1 and 0 = 0) and (5 / 1 = 5 and 5 % 10 = 5),'True','False')

for your query

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)
              'a_vendor_name', *
      FROM  pa_journal_detail))
FROM pa_journal_detail

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

for string compare you need to use single quote

 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

Upvotes: 1

Mureinik
Mureinik

Reputation: 311143

I think a case expression may be the wrong tool for the job. If you want to join a table sometimes, a left join may be easier:

SELECT    p.*, a.a_vendor_name
FROM      pa_journal_detail p
LEFT JOIN ap_vendor a ON p.jl_ref1 = a.a_vendor_number AND
          p.jl_source_code IN ('API', 'APM')

Upvotes: 0

Related Questions