[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Case statement inside of SQL string

I everyone...

I have the following sql string being built.  I need to insert a case statement within the string.  Please take a look and see if you see anything wrong?


strSQL = "SELECT brch_oh, ordernum_ol, ord_dt_oh, bonum_ol, custnum_oh, name_oh, sup_ol, item_num_ol, ty_ol, " &_
            " description001_ol, price_ol, qtyor_ol, cost_ol, pc_ol  " &_
            " FROM OAUSER.ORDLIN, OAUSER.ORDHDR WHERE " &_
            " ((ORDERNUM_OH = ORDERNUM_OL) and " &_
            " (cusclas_oh <> 'W') and (qtyor_ol > 0) and " &_
            " (flg_ol = 1) and (sup_ol <> 'ZZZ') and item_num_ol  <> 'NONE'  and "
      SELECT CASE pc_ol
      WHEN  E0  THEN
            strSQL = strSQL & " (((price_ol - cost_ol)/(price_ol + 0.0001)) < .10) and g_ol = 2) or  "
      Else
            strSQL = strSQL & " (((price_ol - cost_ol)/(price_ol + 0.0001)) < .15) and g_ol = 2) or  "
      End
            strSQL = strSQL & " ((ORDERNUM_OH = ORDERNUM_OL) and " &_
            " (cusclas_oh <> 'W') and (qtyor_ol > 0) and " &_
            " (flg_ol = 1) and (sup_ol <> 'ZZZ') and item_num_ol  <> 'NONE'  and " &_
            " (((price_ol - cost_ol)/(price_ol + 0.0001)) < .30) and g_ol = 1)"

Thanks!

Muligan
0
muligan
Asked:
muligan
  • 4
  • 3
1 Solution
 
rafranciscoCommented:
Try this one:

strSQL = "SELECT brch_oh, ordernum_ol, ord_dt_oh, bonum_ol, custnum_oh, name_oh, sup_ol, item_num_ol, ty_ol, " &_
          " description001_ol, price_ol, qtyor_ol, cost_ol, pc_ol  " &_
          " FROM OAUSER.ORDLIN, OAUSER.ORDHDR WHERE " &_
          " ((ORDERNUM_OH = ORDERNUM_OL) and " &_
          " (cusclas_oh <> 'W') and (qtyor_ol > 0) and " &_
          " (flg_ol = 1) and (sup_ol <> 'ZZZ') and item_num_ol  <> 'NONE'  and " &_
          " (((price_ol - cost_ol)/(price_ol + 0.0001)) < 
                     CASE WHEN pc_ol = 'E0' THEN .10 ELSE .15 END) and g_ol = 2) or  " &_
          strSQL = strSQL & " ((ORDERNUM_OH = ORDERNUM_OL) and " &_
          " (cusclas_oh <> 'W') and (qtyor_ol > 0) and " &_
          " (flg_ol = 1) and (sup_ol <> 'ZZZ') and item_num_ol  <> 'NONE'  and " &_
          " (((price_ol - cost_ol)/(price_ol + 0.0001)) < .30) and g_ol = 1)"
0
 
muliganAuthor Commented:
Sorry... I just cut and paste and it didn't work.
0
 
rafranciscoCommented:
I am not familar with VB but basically I just changed this part:

     SELECT CASE pc_ol
     WHEN  E0  THEN
          strSQL = strSQL & " (((price_ol - cost_ol)/(price_ol + 0.0001)) < .10) and g_ol = 2) or  "
     Else
          strSQL = strSQL & " (((price_ol - cost_ol)/(price_ol + 0.0001)) < .15) and g_ol = 2) or  "
     End

to this:

" (((price_ol - cost_ol)/(price_ol + 0.0001)) < 
                     CASE WHEN pc_ol = 'E0' THEN .10 ELSE .15 END) and g_ol = 2) or  " &_
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
muliganAuthor Commented:
Rafrancisco,

I tried that as well, and it didn't work.
0
 
rafranciscoCommented:
When you say "it didn't work", was there a syntax error or were there no output received?

Please try running this using Query Analyzer and see if you get the desired output:

SELECT brch_oh, ordernum_ol, ord_dt_oh, bonum_ol, custnum_oh, name_oh, sup_ol, item_num_ol, ty_ol,
       description001_ol, price_ol, qtyor_ol, cost_ol, pc_ol
FROM OAUSER.ORDLIN, OAUSER.ORDHDR WHERE
((ORDERNUM_OH = ORDERNUM_OL) and
(cusclas_oh <> 'W') and (qtyor_ol > 0) and
(flg_ol = 1) and (sup_ol <> 'ZZZ') and item_num_ol  <> 'NONE'  and
(((price_ol - cost_ol)/(price_ol + 0.0001)) < 
                     CASE WHEN pc_ol = 'E0' THEN .10 ELSE .15 END) and g_ol = 2) or
((ORDERNUM_OH = ORDERNUM_OL) and
(cusclas_oh <> 'W') and (qtyor_ol > 0) and
(flg_ol = 1) and (sup_ol <> 'ZZZ') and item_num_ol  <> 'NONE'  and
(((price_ol - cost_ol)/(price_ol + 0.0001)) < .30) and g_ol = 1)

If not, then something needs to be done with the query.  I checked the syntax after I added the CASE and there was no syntax error.
0
 
muliganAuthor Commented:
I ended up going a totally different avenue.  I just added another or statement into the string...and it works just fine now.

I am going to give you the credit for your attempts.

Thanks for the input!

M
0
 
rafranciscoCommented:
Thank you for the points.  Have a good day.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now