vfp6 and 'as' in select stmt 1

[xenology]

Using vfp6 and modifing an old program. I would like to use an IIF to determine the name of the returned field in the select statement. Is this possible?
example:
select first, last, dob as iif(year(dob)<1980,"Young","Old") from member
Of course, in this example, there would be four columns in the query - first, last, young, old
i can provide more details on why i want to do this if needed.
thanks for any input!
-xeno

 

[torturedmind]

the code

select first, last, dob as iif(year(dob)<1980,"Young","Old") from member

should be

select first, last, [b]iif(year(dob)<1980,"Young","Old") as "dob"[/b] from member

and will generate only three columns. the third will show "Old" or "Young" depending on the value of the original dob field.

i hope this helps. peace!

kilroy
philippines

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get one million miles to the gallon, and explode once a year, killing everyone inside."

 

[xenology]

no, i need the four columns. i was trying to keep from going into the detail as it could be lengthy. the final table has rows of data and how a sales item has performed over the last three years (we only maintain history for 3 years). the table has fields for describing the unit sold and then columns like current_units_jan, current_units_feb,....lastyear_units_jan, lastyear_units_feb,....2yearsago_units_jan, 2yearsago_units_feb, and so on. The table suppling the data has each individual sale for the last three years. The sql select i'm trying to do would take and group by upc_code but read the year(sales_date) and month(sales_date) in the IIF to see if the sum(qty_sold) should be in this years units for the month sold, last year, or two years ago.

make sense?
thanks,
xeno

 

[Mike Lewis]

Xeno,

Why not write a little function that takes the sales figures as parameters and returns the required result. You can then call that function from the SELECT statement.

To go back to your original example, it would look like this:

SELECT first, last, AgeString(dob) FROM member

...

FUNCTION AgeString
LPARAMETER DateBirth

IF YEAR(DateBirth) < 1980
RETURN "Old "
ELSE
RETURN "Young"
ENDIF

ENDFUNC

Obviously, the function can be as complicated as you like. The point is that you are removing the complexity out of the SELECT and into a functon that you can test independently.

One other poinnt: Whatever solution you adopt, be sure that they string is always as long as the longest value (as in "Old " and "Young" in my example). This will avoid truncation problems.

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Mark826]

Is this what your looking for - 4 columns: first,last,young, and old. Only dates with dob < 1980 go in the young column, others in the old.

select first, ;
last, ;
IIF(year(dob)<1980, dob, {}) AS Young, ;
IIF(year(dob)>=1980, dob, {}) AS Old ;
from member


- Mark

 

[xenology]

Perfect Mark! Exactly what I was looking for. BIG star for you!
thanks again!