Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: chris preston
  Where is chris preston?
 Kingston
 Jamaica
 chris preston
 Tags
Subject: join 2 varables in IIF statement
Thread ID: 373376 Message ID: 373376 # Views: 58 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, March 21, 2013 7:36:06 PM         
   


Hello All
I am getting this error

Error: Command contains unrecognized phrase/keyword

The program below if to show for the last 5 years by month the quantity sold for each stock item

they want to see in a spreadsheet format the following layout

ItemKey, ItemDescription, Jan_13, Feb_13 etc for 2013 then coniuing Jan_12,feb_12,mar_12 etc etc
It will end up as a spreadsheet with 5 * 12 columns +3 columns
I wanted to use in the IFF line "Jan" + y which is the last 2 char of the year

Don't know how else I can make the lines different. Each loop i will add to a cursor that will store year after year



cdte= YEAR(DATE())
sdte= cdte - 5
stdte = '01/01/'+ALLTRIM(STR(sdte))
dte = CTOD(stdte)
etdte = '31/12/'+ALLTRIM(STR(YEAR(DATE())))
dte1= CTOD(etdte)

SQLEXEC(oConnection1,"select * from arlinh where documentdate >= ?dte and documentdate <= ?dte1","tmp")
SQLEXEC(oConnection1,"select itemkey ,itemdescription1 from inmast","itm")
x=sdte
y= RIGHT(ALLTRIM(STR(x)),2)
DO WHILE x <= YEAR(DATE())


SELECT itm.itemkey,itm.itemdescription1,x as yr, ;
SUM(IIF (month(documentdate) == 1, qtyshipped, 0)) as "Jan_"+y, ;
SUM(IIF (month(documentdate) == 2, qtyshipped, 0)) as "Feb_"+y, ;
SUM(IIF (month(documentdate) == 3, qtyshipped, 0)) as "Mar_"+y, ;
SUM(IIF (month(documentdate) == 4, qtyshipped, 0)) as "Apr_"+y, ;
SUM(IIF (month(documentdate) == 5, qtyshipped, 0)) as "May_"+y, ;
SUM(IIF (month(documentdate) == 6, qtyshipped, 0)) as "Jun_"+y, ;
SUM(IIF (month(documentdate) == 7, qtyshipped, 0)) as "Jul_"+y, ;
SUM(IIF (month(documentdate) == 8, qtyshipped, 0)) as "Aug_"+y, ;
SUM(IIF (month(documentdate) == 9, qtyshipped, 0)) as "Sep_"+y, ;
SUM(IIF (month(documentdate) == 10, qtyshipped, 0)) as "Oct_+y, ;
SUM(IIF (month(documentdate) == 11, qtyshipped, 0)) as "Nov_"+y, ;
SUM(IIF (month(documentdate) == 12, qtyshipped, 0)) as "Dec_"+y ;
FROM itm ;
LEFT JOIN ;
(SELECT itemkey, documentdate, qtyshipped FROM tmp) AS A ;
ON itm.itemkey = A.itemkey WHERE YEAR(documentdate) = x ;
GROUP BY 1 ,2,3 INTO CURSOR SalesData

ENTIRE THREAD

join 2 varables in IIF statement Posted by chris preston @ 3/21/2013 7:36:06 PM
RE: join 2 varables in IIF statement Posted by Anders Altberg @ 3/21/2013 8:35:54 PM
RE: join 2 varables in IIF statement Posted by chris preston @ 3/21/2013 11:19:08 PM
RE: join 2 varables in IIF statement Posted by Anders Altberg @ 3/22/2013 11:51:44 AM
RE: join 2 varables in IIF statement Posted by chris preston @ 3/22/2013 4:05:22 PM
RE: join 2 varables in IIF statement Posted by Cetin Basoz @ 3/22/2013 2:10:51 PM