How can I do this without nested/sub queries? 1

[codefighta]

Hello,

I am using ODBC (with PEAR : DB) to connect to foxpro tables (.dbf) from my PHP app and have this in my a table

ITEM  QTY  DATE
---------------------
1234  2    1/23/2004
1234  3    1/26/2004
1234  1    2/12/2004
1234  5    6/18/2004
4321  8    3/21/2004
4321  2    4/11/2004

and would like to get this from my query

ITEM  JAN FEB MAR APR MAY JUN JUL ... DEC
-----------------------------------------
1234   5   1               5
4321           8   2

I am currently trying to do this with SUM and CASE but get the following error (You can see the query in [userinfo])

 [error_message_prefix] => 
    [mode] => 1
    [level] => 1024
    [code] => -29
    [message] => DB Error: null value violates not-null constraint
    [userinfo] => select imnum, 
          sum(case when extract(month from date) = 1 then qty end) as jan,
          sum(case when extract(month from date) = 2 then qty end) as feb,
          sum(case when extract(month from date) = 3 then qty end) as mar,
          sum(case when extract(month from date) = 4 then qty end) as apr,
          sum(case when extract(month from date) = 5 then qty end) as may,
          sum(case when extract(month from date) = 6 then qty end) as jun,
          sum(case when extract(month from date) = 7 then qty end) as jul,
          sum(case when extract(month from date) = 8 then qty end) as aug,
          sum(case when extract(month from date) = 9 then qty end) as sep,
          sum(case when extract(month from date) = 10 then qty end) as oct,
          sum(case when extract(month from date) = 11 then qty end) as nov,
          sum(case when extract(month from date) = 12 then qty end) as dec
          from itsar404.dbf WHERE cnum='23651' AND imnum<>'' group by imnum [nativecode=S1000 [Microsoft][ODBC Visual FoxPro Driver]Function name is missing ).]
    [backtrace] => Array

I am not sure how to interprete

DB Error: null value violates not-null

and

[ODBC Visual FoxPro Driver]Function name is missing ).]

Any ideas?
Thanks
cf

 

[rgbean]

This is simply invalid syntax for a VFP SQL SELECT statement. VFP ODBC doesn't support CASE() or Extract(). Try something like:

select imnum, 
       sum(iif(month(date) = 1, qty, 0)) as jan,
       sum(iif(month(date) = 2, qty, 0)) as feb,
       sum(iif(month(date) = 3, qty, 0)) as mar,
       sum(iif(month(date) = 4, qty, 0)) as apr,
       sum(iif(month(date) = 5, qty, 0)) as may,
       sum(iif(month(date) = 6, qty, 0)) as jun,
       sum(iif(month(date) = 7, qty, 0)) as jul,
       sum(iif(month(date) = 8, qty, 0)) as aug,
       sum(iif(month(date) = 9, qty, 0)) as sep,
       sum(iif(month(date) = 10, qty, 0)) as oct,
       sum(iif(month(date) = 11, qty, 0)) as nov,
       sum(iif(month(date) = 12, qty, 0)) as dec
          from itsar404.dbf 
        WHERE cnum='23651' AND imnum<>'' 
       group by imnum

Rick

 

[codefighta]

Thanks rgbean!!
That works like a charm!

cf