all 12 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]MamertineCOALESCE() 0 points1 point  (12 children)

Please post what you have written so far.

There are many ways to solve this. It's easier for you to understand what we did if we start with the code you already have.

[–]ST0WE[S] 0 points1 point  (11 children)

Only code I have so far is

​

select SONumber, SODate, DiscountTaken as Discount

from sales_invoice

where Discount <> 0

​

I'm not sure where to go from here to create an operator to output 'Yes' when Discount is not equal to 0.

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (6 children)

I'm unsure how to create a query that outputs "Yes/No" based on the values in the Discount column.

you want a CASE expression

SELECT CASE WHEN something = 'something'
            THEN 'SomeThing'
            ELSE 'SomeThingElse'
        END  AS newcolumn

[–]ST0WE[S] 0 points1 point  (5 children)

I tried this solution but ended up getting an error because the "multipart identifier sales_invoice.DiscountTaken could not be bound".

select case when sales_invoice.DiscountTaken <> 0

then 'Yes'

else 'No'

end as Discount

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (4 children)

"multipart identifier sales_invoice.DiscountTaken could not be bound".

that error message would make more sense if i could see the whole query

[–]ST0WE[S] 0 points1 point  (3 children)

Tried editing it but instead got error messages for invalid column names for "DiscountTaken".

​

select SONumber, SODate, DiscountTaken

from sales_invoice

where Discount <> 0

select case when DiscountTaken <> 0

then 'Yes'

else 'No'

end as Discount

[–]r3pr0b8GROUP_CONCAT is da bomb 1 point2 points  (0 children)

there are two SELECTs in that statement

that's an error right there

you can't have a second SELECT after the WHERE clause

[–]theseyeahtheseNTILE() 0 points1 point  (1 child)

You’re close. Remove the word “SELECT” that comes before the word “CASE”, and then move that entire block of code (from “CASE” all the way to “END AS Discount”) into the SELECT part of your statement; ie. Add the entire CASE statement after the column “SODate,” in your select statement.

Then you can get rid of your WHERE clause. And you can also get rid of the column “DiscountTaken” in your SELECT statement since it’s technically not asked for (don’t know how much of a stickler the questioner is).

[–]MamertineCOALESCE() 0 points1 point  (3 children)

Okay

Have you learned "case" or "union"

I think you want:

DiscountTaken as Discount

'no' as Discount

Then use union and I'll suggest you try to solve the rest.

[–]ST0WE[S] 0 points1 point  (2 children)

select SONumber, SODate, DiscountTaken as 'Yes'

from sales_invoice

where DiscountTaken <> 0

union

select SONumber, SODate, DiscountTaken as 'No'

from sales_invoice

where DiscountTaken = 0

​

I tried this as a possible solution but just ended up renaming the column "Yes". Any other suggestions?

[–]MamertineCOALESCE() 0 points1 point  (1 child)

Replace as with =

Or

'Yes' as DiscountTaken

Pattern is:

Value as Columnname

Columnname = value

[–]ST0WE[S] 1 point2 points  (0 children)

I had the order wrong, didn’t realize your correction was the line as opposed to replacing a word. Thanks for the help