question

SteveR-7967 avatar image
0 Votes"
SteveR-7967 asked SteveR-7967 published

IIF Statement in a group by not working

I have the following SQL query. My problem is the IIF at the end of the select statement. I get a invalid syntex near the <

SELECT TOP (100) PERCENT dbo.ictran.loctid, dbo.ictran.sono, dbo.ictran.palletno, dbo.ictran.item, SUM(dbo.ictran.tqty) AS tqty, dbo.sotran.descrip, dbo.sotran.custno,
dbo.arspec.catalog AS custitem, dbo.icitem.plinid, SUM(dbo.icitem.weight * dbo.ictran.tqty) AS pweight, dbo.icitem.weight, dbo.sotran.unitsper,
SUM(dbo.ictran.tqty / dbo.sotran.unitsper) AS cartons, dbo.sotran.salesmn, dbo.icitem.upccode, dbo.icitem.eancode, dbo.icitem.barcode, dbo.icitem.barcodei,
dbo.icitem.stkumid, dbo.icitem.unitsper AS mcarton, dbo.icitem.unitsperi AS incarton, SUM(IIF(dbo.ictran.tqty < dbo.sotran.unitsper,dbo.icitem.stkumid, dbo.icitem.unitsperi,0))
FROM dbo.ictran INNER JOIN
dbo.sotran ON dbo.ictran.sono = dbo.sotran.sono AND dbo.ictran.item = dbo.sotran.item INNER JOIN
dbo.arspec ON dbo.sotran.custno = dbo.arspec.custno AND dbo.sotran.item = dbo.arspec.item INNER JOIN
dbo.icitem ON dbo.ictran.item = dbo.icitem.item
WHERE (dbo.ictran.sono <> ' ') AND (dbo.ictran.rfdone = 0)
GROUP BY dbo.ictran.sono, dbo.ictran.loctid, dbo.ictran.palletno, dbo.ictran.item, dbo.sotran.descrip, dbo.sotran.custno, dbo.arspec.catalog, dbo.icitem.plinid,
dbo.sotran.unitsper, dbo.sotran.salesmn, dbo.icitem.upccode, dbo.icitem.barcode, dbo.icitem.barcodei, dbo.icitem.eancode, dbo.icitem.stkumid, dbo.icitem.weight,
dbo.icitem.unitsper, dbo.icitem.unitsperi
ORDER BY dbo.ictran.sono, dbo.ictran.palletno, dbo.ictran.item

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SteveR-7967 avatar image
0 Votes"
SteveR-7967 answered SteveR-7967 published

This is what I ended up with, and it seems to work
SELECT TOP (100) PERCENT dbo.ictran.loctid, dbo.ictran.sono, dbo.ictran.palletno, dbo.ictran.item, SUM(dbo.ictran.tqty) AS tqty, dbo.sotran.descrip, dbo.sotran.custno,
dbo.arspec.catalog AS custitem, dbo.icitem.plinid, SUM(dbo.icitem.weight * dbo.ictran.tqty) AS pweight, dbo.icitem.weight, dbo.sotran.unitsper,
SUM(dbo.ictran.tqty / dbo.sotran.unitsper) AS cartons, dbo.sotran.salesmn, dbo.icitem.upccode, dbo.icitem.eancode, dbo.icitem.barcode, dbo.icitem.barcodei,
dbo.icitem.stkumid, dbo.icitem.unitsper AS mcarton, dbo.icitem.unitsperi AS incarton,
SUM(CASE WHEN dbo.ictran.tqty < dbo.sotran.unitsper THEN dbo.ictran.tqty / dbo.icitem.unitsperi
ELSE dbo.ictran.tqty / dbo.sotran.unitsper
END) AS cartons
FROM dbo.ictran INNER JOIN
dbo.sotran ON dbo.ictran.sono = dbo.sotran.sono AND dbo.ictran.item = dbo.sotran.item INNER JOIN
dbo.arspec ON dbo.sotran.custno = dbo.arspec.custno AND dbo.sotran.item = dbo.arspec.item INNER JOIN
dbo.icitem ON dbo.ictran.item = dbo.icitem.item
WHERE (dbo.ictran.sono <> ' ') AND (dbo.ictran.rfdone = 0)
GROUP BY dbo.ictran.sono, dbo.ictran.loctid, dbo.ictran.palletno, dbo.ictran.item, dbo.sotran.descrip, dbo.sotran.custno, dbo.arspec.catalog, dbo.icitem.plinid,
dbo.sotran.unitsper, dbo.sotran.salesmn, dbo.icitem.upccode, dbo.icitem.barcode, dbo.icitem.barcodei, dbo.icitem.eancode, dbo.icitem.stkumid, dbo.icitem.weight,
dbo.icitem.unitsper, dbo.icitem.unitsperi
ORDER BY dbo.ictran.sono, dbo.ictran.palletno, dbo.ictran.item

thank you

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @SteveR-7967,

IIF(dbo.ictran.tqty < dbo.sotran.unitsper
,dbo.icitem.stkumid
, dbo.icitem.unitsperi,0)

You have too many arguments in the IIF() function, i.e. four of them instead of three..
Its syntax is as follows: IIF(condition, value_if_true, value_if_false)
Very possible that the last zero value sneaked in by mistake.

logical-functions-iif-transact-sql


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

Hi @SteveR-7967

The IIF function was introduced by SQL Server 2012, so it cannot be used in SQL Server 2008. You can use CASE WHEN(CASE (Transact-SQL)) instead of IIF.

Please check:

 SUM(CASE WHEN dbo.ictran.tqty < dbo.sotran.unitsper THEN dbo.icitem.stkumid
 ELSE dbo.icitem.unitsperi END)

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Where will it leave the answer?

0 Votes 0 ·

Do you want to know how to accept the answer?If yes, please refer to:
109271-image.png

Regards
Echo

0 Votes 0 ·
image.png (268.1 KiB)
SteveR-7967 avatar image
0 Votes"
SteveR-7967 answered

What I am trying to do is to figure number of cartons used. The tqty (Amount used) is less then the sotran.unitsper (which is units in a master carton) to use the icitem.unitsperi (which is a inner pack)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SteveR-7967 avatar image
0 Votes"
SteveR-7967 answered

I probably should have mentioned that this is Sequel Server 2008

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @SteveR-7967,

Welcome to the microsoft TSQL forum!

The parameter error of the IIF function proposed by Yitzhak Khabinsky is an obvious error. Please refer to the IIF grammar and try to modify it. If there are other errors after the modification, please provide us with relevant tables and data for us to test in order to find the errors.

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.