IF THEN in SQL Select Statement

Question

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:

Journal detail view = pa_journal_detail
Vendor detail view = ap_vendor

Match on Vendor Number:

The vendor number is contained in the following fields:

ap_vendor.a_vendor_number
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

What specific error? For one thing, that subquery looks like it should be returning more than one row (no correlation between the inside and outside data), which would cause its own problems. Also, is there some specific reason you didn't use a LEFT JOIN? Generally, using CASE ... (<subquery>) should be avoided (although there are specific situations it might help), because in most cases it's going to be obscuring data relationships. — Clockwork-Muse
– Clockwork-Muse, Commented Apr 16, 2019 at 16:43

Zaynul Abadin Tuhin · Accepted Answer · 2019-04-16 17:24:31Z

1

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

edited Apr 16, 2019 at 17:24

answered Apr 16, 2019 at 16:32

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rebecca Christian Over a year ago

I've tried this way and the example below and am getting this error on both "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Zaynul Abadin Tuhin Over a year ago

@RebeccaChristian use limit inside subquery btw what is your dbms name

Rebecca Christian Over a year ago

Its a SQL Server database from my software provider

Zaynul Abadin Tuhin Over a year ago

@RebeccaChristian then use top 1

Gen Wan Over a year ago

It is better to add 'order by' after top clause

JBJ · Accepted Answer · 2019-04-16 18:30:42Z

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

Mureinik · Accepted Answer · 2019-04-16 16:36:18Z

0

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')

answered Apr 16, 2019 at 16:36

Mureinik

316k54 gold badges402 silver badges405 bronze badges

6 Comments

Rebecca Christian Over a year ago

When I attempt this way I get the following error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"

Mureinik Over a year ago

@RebeccaChristian There is no subquery here. Are you using this snippet as part of some larger query?

Rebecca Christian Over a year ago

Yes, I was attempting to.

Mureinik Over a year ago

@RebeccaChristian I may not have been clear enough. This does not replace the case expression in the query you shared, but the entire query. If this isn't the issue, and you're this query as part of a larger query, please share it, I can't really help without seeing what produced the error.

Rebecca Christian Over a year ago

I updated the question with the full query. Thanks!

|

Collectives™ on Stack Overflow

IF THEN in SQL Select Statement

3 Answers 3

5 Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related