missing comma error with IIF and LIKE

[WhiteHatMarine]

cannot seem to isolate this one...if I change the LIKE to = it works.

Error: Missing comma

SELECT aa.wono
,IIf(aa.item LIKE '7*' ,'Homeopathic','not homo') AS TEST

FROM (((ppwohd10 aa INNER JOIN (icitem10 bb LEFT JOIN tblProductTypes dd ON bb.itmclss = dd.ItemCode) ON aa.item = bb.item) INNER JOIN iciloc10 ee ON aa.item = ee.item) INNER JOIN arcust10 cc ON aa.custno = cc.custno) LEFT JOIN tblDivision ff ON ee.gllink = ff.GlLink

WHERE aa.status != 4 AND aa.compqty=0 AND aa.reqdte >= {09/01/2012} AND aa.reqdte <= {10/31/2012}
ORDER BY aa.reqdte

 

[DSummZZZ]

IIF() won't take a "LIKE" for comparison. I'm not sure what string you're looking for, but for finding a substring match, you'll have to use something like:
IIF('7*' $ aa.item,'Homeopathic','not homo') AS TEST ...


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[WhiteHatMarine]

ok, thanks thats a huge help to know. So I have it working now with this statment:

,IIF( (SUBSTR(aa.item, 1, 1) = '7') = 1, 'Homo', 'Not Homo') AS TEST

Is there a better way to do a string comparison? I need something similair to the LIKE operators , #, ? , * etc..

For instance I need compare a string to see if it has: (any four digits) (any single character) (any two digits) (multiple characters). In a LIKE I would use: ####?##*

How can I do that in an IIF()? or if not using an IIF set the result to a variable with AS ?

For example this worked in Access and even using HXTT driver with Foxpro DBF, but now Im using a native FoxPro ODBC driver (for other reasons I wont get into) and it no longer works:
IIf(aa.item Like '####?##*',IIf(aa.item Like '####M##*','Tab/Encap','Packaging'),IIf(aa.item Like '?###?##*','Packaging',IIf(aa.item Like '?####?:##*','Packaging','Manufacturing'))) AS MyGroup

 

[mmerlinn]

&nbsp;

Based on what you state, it appears that you may have digits in ANY position, but MUST have them in positions 1, 2, 3, 4, 6, & 7.

If that is correct, then this might work for you (untested):

[tt][blue]
IIF(CHRTRAN('0123456789', LEFT(aa.item, 4) + SUBSTR(aa.item, 6, 2), ' ') = '', 'Homo', 'Not Homo') AS TEST
[/blue][/tt]




mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[mmerlinn]

[&nbsp;]
You could do the same thing this way (not tested):

[blue][tt]IIF(EMPTY(CHRTRAN('0123456789', LEFT(aa.item, 4) + SUBSTR(aa.item, 6, 2), ' ')), 'Homo', 'Not Homo') AS TEST[/tt][/blue]

If the 6 positions are all digits, then this returns "Homo". "Not Homo" is returned if any of the six positions has any other character.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Olaf Doschke]

LIKE is an sql clause, but once you go to use Foxpro functions like IIF is, inside that LIKE sql doesn't work, but you can use the LIKE() function instead:

IIF(LIKE(aa.item,'7*') ,'Homeopathic','not homo') AS TEST

One further note, just for the sake of completeness: In SQL usage in WHERE LIKE has other joker characters as the LIKE function, you use % instead of *, so a functioning where caluse would be WHERE item LIKE '7%'. Here the LIKE clause won't help you. If using an exression to compute a field of the result, you leave the SQL standard and have to go VFP for the whole expression. IIF is not part of the ansi SQL standard, as far as I know. I just googled and found no evidence for but also not against. It's rather CASE like in T-SQL, which replaces IIF in the ansi sql standard. What I can say for sure is, that IIF is not part of the SQL Engine alone in VFP, it works on it's own outside of SQL, too.

Bye, Olaf.

 

[WhiteHatMarine]

Thanks all, this has been a HUGE help. It is so close to totally working now. On my last IIF if the test is false I would like to set myDept AS [myGroup] [myDiv] [myProdType] but get an error: Function name is missing

Is this a VFP function limitation or syntax issue? Any thoughts on getting around this?


IIf(ff.gllink = 'DEF' OR ff.gllink = 'FRP',IIf(LIKE(aa.item, '7*'),'Homeopathic', IIf(LIKE(aa.item, '5*') OR LIKE(aa.item, '04*') OR LIKE(aa.item, '05*'),'Nutritional',IIf(LIKE(aa.item, '4*') Or LIKE(aa.item, '1*') OR LIKE(aa.item, '09*') OR LIKE(aa.item, '3*'),'Nutritional','Food'))),ff.Division) AS myDiv
,
IIf(IsNull(dd.ProdType),'Powder',dd.ProdType) AS myProdType
,
IIf(LIKE(aa.item, '########?####*'),IIf(LIKE(aa.item, '##########M####*'),'Tab/Encap','Packaging'),IIf(LIKE(aa.item, '?######?####*'),'Packaging',IIf(LIKE(aa.item, '?########?####*'),'Packaging','Manufacturing'))) AS myGroup
,
IIf([myGroup] = 'Manufacturing' AND [myProdType] = 'Liquid','Manufacturing - Liquid', [myGroup] [myDiv] [myProdType]) AS myDept

 

[Olaf Doschke]

[myGroup] [myDiv] [myProdType]

What should that be? An expression?
An expression with three fields should have two operators + or -. What do you want?

You don't see the wood for the trees?

Besides missing +, or a function having these strings as parameters, you don't write field names in [] in VFP. You write myGroup+myDiv+myProdType

Bye, Olaf.

 

[Koen Piller]

RipRoar,

all these IFF's, please consider to make use of ICASE() - unless ofcourse you are working with a VFP version < 9

Regards,

Jockey(20

 

[Mike Lewis]

Hi RipRoar,

I'm joining this party late, and can't add much to the good advice you've already been given.

However, Olaf suggested you use this clause:

IIF(LIKE(aa.item,'7*') ,'Homeopathic','not homo') AS TEST

I believe the arguments to the LIKE() function should be the other way round:

IIF(LIKE([b]'7*', aa.item[/b]) ,'Homeopathic','not homo') AS TEST

In other words, the wildcard string comes first, and the string being tested comes second. It's not particularly intuitive, but that's FoxPro for you.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[EzLogic]

One thing to keeip in mind (this has nothing to do with the LIKE(), rather the sql in general), is if the 1st record is 'Not Homo' (for the field test), then the field test length will be the length of "Not Homo", and the remaining records matching will be truncated in the result cursor.

if you want to keep them, then padr() the result of each field..so, or do this:

IIF(LIKE('7*', aa.item) ,'Homeopathic','not homo    ') AS TEST

or

IIF(LIKE('7*', aa.item) ,padr('Homeopathic',11),padr('not homo',11)) AS TEST ]/code]

otherwise, it cursor data might look like this: (again, if the 1st record pulled was "Not Homo")
field Test:
Not Homo
Homeopat
etc..


Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[WhiteHatMarine]

All the input is a huge help, thanks again. This is my first time dealing with FoxPro.

So the final problem Im dealing with is using the field alias's ( myGroup myDiv myProdType ) in the evaluation and result expression of the IIF.

I was able to get around the evaluation portion by just using the ENTIRE IIF statement that created the alias, ugly but works.

FoxPro err's "too complex" when trying this technique in the result expression.

So this is what I have now, but FoxPro does not recognize the myGroup myDiv myProdType alias's:

IIf(
IIf(my statement that creates an alias myGroup) = 'Manufacturing' AND IIf(my statement that creates an alias myProdType) = 'Liquid'
,padr('Manufacturing - Liquid',100)
,myGroup + myDiv + myProdType
) AS myDept

I can get around this using other techniques but this would be very helpful.

I like wood, I like tree's.

 

[Olaf Doschke]

Caution, there are at least two aliases you can talk of here: Alias of a table or field alias.

Inside an IIF you have an if and else branch, both can have expression evaluating to a cartain field value, but the field alias has to be outside of the IIF.
You can have SELECT IIF(condition, expr1, expr2) AS field, you can not have Select IIF(condition, expr1 AS Alias1, exp2 as Alias2), this is not foxpro specific, you can'`t have a field in the end result, which has one name in some records and another name in other records.

So field aliasing has to be outside of any IIF.

Bye, Olaf.

 

[Olaf Doschke]

Also: You can't address a field by an alias name, before the result is generated. Within an SQL you have to use the original names. This also doesn't differ from T-SQL, for example.

Eg you can't do SELECT tablefield as aliasname WHERE aliasname = value, you have to SELECT tablefield as aliasname WHERE tablefield = value

So if you want to concatenate three result fields you have to do your sql as innner sql and add an outer sql, that uses the result as input for concatenation,

eg instead of
select sum(field) as sum, count(field) as count, sum/count as average FROM ...
you need
select temp.sum, temp.count, temp.sum/temp.count as average FROM (select sum(field) as sum, count(field) as count FROM ...) temp

Besides here you could of course als do
select sum(field) as sum, count(field) as count, Avg(field) as average FROM ...

Bye, Olaf.

 

[WhiteHatMarine]

Turns out LIKE() only supports * and ? and NOT # for digits. At least in my environment which is Cold Fusion and FoxPro ODBC Driver.

Any suggestion for testing for digits?

item = 30400M01

IIF(LIKE('#####M##*', item),PADR('yes',3),PADR('no',3) ) AS myResult

myResult from this is "no", as you can see it should be "yes"

 

[Mike Lewis]

Any suggestion for testing for digits?

How about this:

LEN(CHRTRAN(Item, "0123456789")) = 0

That will return .T. if Item is all digits, .F. otherwise (but also .T. if Item is empty).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Sorry: minor typing error. Should have been this:

LEN(CHRTRAN(Item, "0123456789"[b], ""[/b])) = 0

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Take Mikes digit check on the left part and add AND LIKE('?????M*', item). Overall (LIKE('#####M##*', item) could be done as LEN(CHRTRAN(Left(Item,8), "0123456789M", "")) = 0 AND LIKE('?????M??*', item).

(untested)

Bye, Olaf.