Combine IIF and select statement to pull data from different tables-CodePudding

I'm trying to do a SQL query using DBF Commander on a FoxPro database that has to pull the quantity ordered and weight from another table and multiple them together to get the total weight.

When I run this is gives me an error that I'm missing a comma. If I encapsulate the IIF(select) statement like this: IIF((select ...) > 8000,"T","F") I get ) missing function.

Is the select within the IIF() not a valid operation?

select 
HT.ordnum,
IIF((select sum(qtyord*weight) from 'DT.dbf' as DT where DT.ordnum = HT.ordnum as TotalWeight) >= 8000, "Big load", "Little Load")
from 'HT.dbf' as HT where HT.ordnum like '111%'

CodePudding user response：

Standard SQL would like look this:

SELECT HT.ordnum,
    CASE WHEN SUM(qtyord.*weight) > 8000 THEN 'Big load' ELSE 'Little load' END
FROM HT.dbf as HT 
WHERE HT.ordnum like '111%'
GROUP BY HT.ordnum

Fox Pro is an older product, so I'm not sure how it's particular SQL dialect will make you change this.

CodePudding user response：

Try after Removing 'as TotalWeight'

CodePudding user response：

In FoxPro, you can probably do something like the following. Difficult to tell though since you did not provide sample data and a description of the expected result.

SELECT HT.ordnum, IIF(sum(DT.qtyord*DT.weight) >= 8000, "Big Load", "Little Load") ;
    FROM HT ;
    Left Join DT On DT.ordnum = HT.ordnum ;
    GROUP BY HT.ordnum ;
    where HT.ordnum like '111%'

NB FoxPro is a DBase/XBase DBMS, iif() is a built-in function, and ";" is a line-continuation

CodePudding user response：

So, since your HT.OrdNum = DT.OrdNum, but you are not really pulling any other value from HT, you can completely get away with just querying from the DT table. In this case, I am pre-querying the TD table, summing the QtyOrd * Weight and grouping by the ordNum. Applying the same where to filter out just those orders will give you one record per ordNum.

From that, its just a simple outer query against the inner pre-query (PQ alias)

SELECT
      PQ.OrdNum,
      IIF( PQ.TotalWeight > 8000, 'Big load', 'Little load' ) LoadSize
   from
      ( select 
              DT.ordnum,
              sum(DT.qtyord * DT.weight ) TotalWeight 
           from 
              DT
           where
              DT.ordnum like '111%'
           group by
              DT.ordnum ) PQ