Beginner SQL help

The Sandbox Software/Technology
162

Something like this?

SELECT
...
      ,CASE
       WHEN [LOB] IN ('A','B','C','D') THEN 'WHATEVER'
       WHEN [LOB] IN ('E','F','G','H') THEN 'OKAYFINE'
       ELSE 'YEAHSURE'
       end AS [CLS]
...

Also…

If I wanted to delete all of the 'YEAHSURE’s I’d have to do a WHERE statement after the SELECT statement and probably do something like…

...
WHERE [LOB] IN ('A','B','C','D','E','F','G','H')
...

Yes?

163

That should work. The where section is performed before the select section so you can’t refer to case statement results in the where section (unless you’re in an outer query)

1 Like
164

Could use ‘having’ instead of ‘where’ to execute after the ‘select’?? SQL newb myself and hardly ever use it so not sure.

165

Having is used for aggregated values whereas Where is for row level. For example,

Select
ColA,
ColB,
sum(ColC)
From
Table1
Where
ColA <> ‘NotInterestimg’
and ColB <> ‘Boring’
group by ColA, ColB
having sum(ColC) > 1000000

166

It is possible that you could use
WHERE CLS <> ‘YEAHSURE’
It might depend on the specific SQL, though.

Only one way to find out.

167

HAVING statements act on the final table, so I do believe it could be used in a “where” type situation on the output.

Although, I do believe that the query execution is more efficient when “filtering” can be done on the input tables; but it may not be as clear to a human reader as to what’s going on.

168

Yeah, as a SAS guy, that’s one of the first things I tried when I was starting out with SQL.
You get this:

Invalid column name 'CLS'.
169

Bummer.

170

But the following should work like you’re wanting:

HAVING CLS <> ‘YEAHSURE’

The "WHERE CLS . . . " statement fails because CLS isn’t a field on the source table.

1 Like
171

Yeah that’s what I was getting at. VA just actually knows what he’s talking about.

1 Like
172

lol

FWIW, my background (think 80’s computer nerd) creates the situation where I understand source documentation better than most 3rd party materials.

173

Before investing any time in this post, SEE THE UPDATE A FEW POSTS BELOW

New problem:

I’m not sure why this isn’t working. According to Case Statement using SQL (examples included) - Data to Fish I believe I have it set up correctly.

Here’s the code:

SELECT
 ...
CONCAT('123',
 CASE
  WHEN [PRODUCT] IN ('AAA','BBB','CCC') THEN 'ABC'
  WHEN LEFT([PRODUCT],2) = 'PO' AND [LINE_OF_BUSINESS] = 'Q' THEN 'ABC'
  WHEN [PRODUCT] IN ('XXX','YYY','ZZZ') THEN 'XYZ'
  ELSE '???'
 END
) AS KOODI

It gets hung up on the " WHEN LEFT([PRODUCT],2) = ‘PO’ AND [LINE_OF_BUSINESS] = ‘Q’ THEN ‘ABC’" line telling me “Incorrect syntax near the keyword ‘THEN’.”

I’ve also tried to use OR but get the same error message:

SELECT
 ...
CONCAT('123',
 CASE
  WHEN [PRODUCT] IN ('AAA','BBB','CCC')
    OR LEFT([PRODUCT],2) = 'PO' AND [LINE_OF_BUSINESS] = 'Q' THEN 'ABC'
  WHEN [PRODUCT] IN ('XXX','YYY','ZZZ') THEN 'XYZ'
  ELSE '???'
 END
) AS KOODI

What I’m really trying to accomplish: I have a whole bunch of "PRODUCT"s that are mapped to KOODI. There are some PRODUCTs, however, that are mapped based on the combination of partial-PRODUCT and their LOB. They are mapped to other KOODIs that already exist for other things.

Even this doesn’t work:

SELECT
 ...
CONCAT('123',
 CASE
  WHEN LEFT([PRODUCT],2) = 'PO' AND [LINE_OF_BUSINESS] = 'Q' THEN 'ABC'
  ELSE '???'
 END
) AS KOODI
174

Try this instead:

CASE
  WHEN [PRODUCT] IN ('AAA','BBB','CCC') THEN 'ABC'
  WHEN LEFT([PRODUCT],2) = 'PO' AND [LINE_OF_BUSINESS] = 'Q' THEN 'ABC'
  WHEN [PRODUCT] IN ('XXX','YYY','ZZZ') THEN 'XYZ'
  ELSE '???'
 END
) AS KOODI

That “OR” combined with “AND” in the same statement is bad syntax, to me.

1 Like
175

Before investing any time in this post, SEE THE UPDATE in the POST BELOW

Yeah, that’s what I tried in the “Even this doesn’t work” part of my post. It’snot working either.

It definitely doesn’t like the AND logic in my WHEN statement. Is this a SQL-version issue? I believe I’m using v17.9.1.

176

UPDATE
I’m pretty sure it was a parenthesis problem…it wasn’t in my sample code but in my actual code.
If I clean that up I should be good.
Thanks for reading & thanks for your input, ideas, and help.

177

So I have records that have a date field and several other fields that can create a unique key. The dates are business dates, and I want to match records with 1 date to the business date immediately prior.

Because of the business date thing, i can’t use simple formulas like currdate -1 = prior date

I have considered building a dates table that has curr date and prior date on the same record and matching across that table.

Another way would be to join on priordate < curr date and select records with the max of priordate, but that seems like a bunch of matches for no purpose than to discard them.

Anyone have solutions they have used in the past and would like to share?

178

Define “business date.”

I mean, for this purpose.

179

You could build a table with two columns - business date and the rank of the business date (from earliest to latest). The previous business day will just be the row where the rank = x - 1 (where x = the current days rank).

180

US Equity market dates.

181

I thought of this, but didn’t know if there was a SQL function that assigned the rank. That left me thinking, build a table with the date and rank (somehow). I realized if I did this, I could then build a table with curr date and prior date, and skip the rank entirely.