Build specific SQL query

Question

please- help. I can't handle to build the specific query.

This is the query (slighty simplified)

$id in this case = id_user

SELECT
   ARTIKELNR, ARTIKELTEXT, B_DATA,
   margin = (SELECT TOP 1 SUM(BRNTZ / WERT*100 ) FROM BEW 
             WHERE b.ARTIKELNR = a.ARTIKELNR AND USR_NR = $id )
FROM BEW b 
LEFT JOIN arb a ON b.ARTIKELNR = a.ARTIKELNR
WHERE b.USR_NR = $id 
ORDER BY B_DATA DESC

I have a problem with margin - it has to be SUM of two columns but, if you can see, WERT column cannot be equal 0 ( because BRNTZ / WERT). In database sometimes this column equal 0 or even null.

I tried NULLIF but i just can't build it. The best i've done is:

  SELECT TOP 1 SUM(BRNTZ / (WERT+0.001) * 100)...

Second of all, Can you tell me if it is good at all? I am afraid that the margin can be taken from another ARTIKELNR. It's all about WHERE statement.

margin = (SELECT TOP 1 SUM(BRNTZ / WERT*100 ) FROM BEW 
         WHERE b.ARTIKELNR = ARTIKELNR AND BEW_LIEF_KUND_NR = $id )

EDIT:

i'm usinq SQL DB
When WERT == 0 then SUM(BRNTZ / WERT*100 ) = 0

What do you want SUM(BRNTZ / WERT*100 ) to be if WERT is 0 ? — Accountant م
– Accountant م, Commented Apr 18, 2018 at 15:43

Yevgen · Accepted Answer · 2018-04-18 16:04:01Z

1

IF() suppose to help:

SELECT
   ARTIKELNR, ARTIKELTEXT, B_DATA,
   IF(WERT>0, 
        (SELECT TOP 1 SUM(BRNTZ / WERT*100 ) FROM BEW 
             WHERE b.ARTIKELNR = a.ARTIKELNR AND USR_NR = $id ), 
             0
    ) as margin
FROM BEW b 
LEFT JOIN arb a ON b.ARTIKELNR = a.ARTIKELNR
WHERE b.USR_NR = $id 
ORDER BY B_DATA DESC

answered Apr 18, 2018 at 16:04

Yevgen

1,3204 gold badges15 silver badges32 bronze badges

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

2 Comments

Kafus Over a year ago

It throws me an error Erroneous syntax near the "IF" keyword

Yevgen Over a year ago

@Kafus for MS SQL Server it might be the wrong answer but i don't have MS SQL Server to check it. You can try to replace "IF" with "IIF"

Collectives™ on Stack Overflow

Build specific SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related