Hi Fellows,

As you can see, I'm giving you a bit of work.
Definitely I could not find a way to do this on the web, I'd googleit a lot.
I have a query on multiple tables, there is a factor multiplied by the quantity that will return a final value.
This factor could be in the standard table or can be negotiated by the seller.
If it is negotiated, has precedence over the value on standard table.
I tried something like this above but does not work.


select codProduct,
quantity,
if negociatedValue > 0 then (or is not null)
   negociatedValue * quantity
else
   standardValue * quantity
from sales ...

asked 22 Jun '12, 16:26

RDPSispetro's gravatar image

RDPSispetro
895610
accept rate: 0%

I just found a way to do it, but with case and using alias, like ...

select codProduct, quantity, case when negociatedValueis not null then negociatedValue * quantity else standardValue * quantity end from sales ...

(22 Jun '12, 16:36) RDPSispetro

I would prefer the isnull() function - or coalesce(), which works the same here but is standard SQL:

select codProduct, quantity, isnull(negociatedValue, standardValue) * quantity
from sales ...
permanent link

answered 26 Jun '12, 03:19

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654
accept rate: 32%

This might work:

select codProduct,
quantity,
if negociatedValue > 0 then (or is not null)
   negociatedValue * quantity
else
   standardValue * quantity
endif
from sales ...
permanent link

answered 22 Jun '12, 17:29

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13
×8

question asked: 22 Jun '12, 16:26

question was seen: 1,411 times

last updated: 26 Jun '12, 03:19