What wrong in this syntax:

[alisaif]

Hi,

Please let me know what's wrong in the following:

Cast(ICASE(unit='1000/Pcs',((quantity*rate)/1000), unit='1000/Set',((quantity*rate)/1000),;
   unit='100/Pcs',((quantity*rate)/100),unit='100/Set',((quantity*rate)/100),(quantity*rate) As Amount)

Error: Function name is missing).

I want to multiple quantity with rate if the unit is other then '1000/Pcs' or '1000/Set' or '100/Pcs' or '100/Set',
otherwise (quantity * rate ) / 1000.

Please guide..

Saif

 

[Tore Bleken]

Cast(ICASE(unit='1000/Pcs',((quantity*rate)/1000), unit='1000/Set',((quantity*rate)/1000),;
unit='100/Pcs',((quantity*rate)/100),unit='100/Set',((quantity*rate)/100),(quantity*rate)) As Amount

 

[Stefan5627]

Amount is not a type, replace it by Y

Cast(ICASE(unit='1000/Pcs',((quantity*rate)/1000), unit='1000/Set',((quantity*rate)/1000),;
   unit='100/Pcs',((quantity*rate)/100),unit='100/Set',((quantity*rate)/100),(quantity*rate) As Y)

 

[Stefan5627]

Rereading your post you probably want:

Cast(ICASE(unit='1000/Pcs',((quantity*rate)/1000), unit='1000/Set',((quantity*rate)/1000),;
   unit='100/Pcs',((quantity*rate)/100),unit='100/Set',((quantity*rate)/100),(quantity*rate) As Y) as amount

 

[Mike Lewis]

Apart from what the others have correctly told you, your parentheses are unbalanced. To fix that, ass another closing parentheses right at the end.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

You'll find it much easier to spot the syntax errors if you laid out the code in a more readable format:

Cast(ICASE( ;
  unit='1000/Pcs',(quantity*rate)/1000, ;
  unit='1000/Set',(quantity*rate)/1000, ;
  unit='100/Pcs', (quantity*rate)/100,   ;
  unit='100/Set', (quantity*rate)/100,   ;
  quantity*rate ;
    As Amount))

I think you'll agree that it is easier to understand that way. I've also removed the superfluous parentheses.

But the underlying problem remains. The final parameter of CAST() needs to be a data type, not a variable name. So, the following should do what you want:

Cast(ICASE( ;
  unit='1000/Pcs',(quantity*rate)/1000, ;
  unit='1000/Set',(quantity*rate)/1000, ;
  unit='100/Pcs', (quantity*rate)/100,   ;
  unit='100/Set', (quantity*rate)/100,   ;
  quantity*rate ;
    As [b]Currency[/b]))

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Hi,

Thanks for the reply!

I apply this syntax like ;

Select rawSim.Sino, rawSim.Invdate, rawSim.pcode, Customer.full_name,;
   rawSim.enteredby, rawSim.Salesman, rawSim.refno, RawSim.sono, rawSim.refdate, ;
   rawSim.discamount, RawSim.dono, RawSim.Jono, ;
   rawSim.Curr, rawSim.iSocode, rawSim.Currency, rawSim.Sign, rawSim.currrate,;
   rawSim.discrem, rawSim.remarks, rawSi.Rcode, ;
   rawSi.quantity,  rawSi.unit, ;
   rawSi.rate, [i]Cast(ICASE( ;
      unit='1000/Pcs',(quantity*rate)/1000, ;
      unit='1000/Set',(quantity*rate)/1000, ;
      unit='100/Pcs', (quantity*rate)/100,  ;
      unit='100/Set', (quantity*rate)/100,  ;
      (quantity*rate) As Amount))[/i] ;
   FROM ;
   village!rawSim ;
   inner Join village!rawSi ;
   ON  rawSim.Sino = rawSi.Sino ;
   inner Join village!Customer ;
   ON  rawSim.pcode = Customer.pcode;
   WHERE  rawSim.InvDate Between dt1 And dt2 ;
   INTO CurSor ViewSi Readwrite

but still same problem!

What I am missing?

Saif

 

[Tore Bleken]

Count the parentheses that goes with the matching function!

Change this line "(quantity*rate) As Amount)) ;" into "(quantity*rate)) As Amount) ;"

 

[alisaif]

Count the parentheses that goes with the matching function!

Change this line "(quantity*rate) As Amount)) ;" into "(quantity*rate)) As Amount) ;"

It is ok, but facing an another message


"Function argument value, type, or count is invalid."

Saif

 

[Tore Bleken]

Which version of VFP? Try to delete expressions in order to narrow down the area which causes the error.

 

[alisaif]

vfp9 sp2

 

[Tore Bleken]

As I wrote, try to delete from the end and see when the error disappears. Without having sample data it's not so easy, I can't see any error.

Btw, your icase expression can be simplified:

rawSi.rate, cast (icase(;
'1000/'$unit, (quantity*rate)/1000, ;
'100/'$unit, (quantity*rate)/100, ;
(quantity*rate)) As Amount) ;

 

[Olaf Doschke]

You'vbeen told twice, that "as Amount" has to be outside of both ICASE and CAST. What you want is CAST(expression AS CURRENCY) AS AMOUNT.
The AS AMOUNT is setting the result field name and has to come outside and after any expression, not inside any paranthesis.
The AS CURRENCY is inside the CAST and determines the field TYPE, not name. AMOUNT is no known field type.

Besides, your expression is the lengthy ICASE(...) function, to make it clear.

Bye, Olaf.

 

[alisaif]

Thanks but same problem. Finally I did like that

Select rawsim.sino, rawsim.invdate, rawsim.pcode, customer.full_name,;
rawsim.enteredby, rawsim.salesman, rawsim.refno, rawsim.sono, rawsim.refdate, ;
rawsim.discamount, rawsim.dono, rawsim.jono, ;
rawsim.Curr, rawsim.isocode, rawsim.Currency, rawsim.Sign, rawsim.currrate,;
rawsim.discrem, rawsim.remarks, rawsi.rcode, ;
rawsi.quantity, rawsi.unit, ;
rawsi.rate, (quantity*rate) As amount ;
FROM ;
village!rawsim ;
inner Join village!rawsi ;
ON rawsim.sino = rawsi.sino ;
inner Join village!customer ;
ON rawsim.pcode = customer.pcode;
WHERE rawsim.invdate Between dt1 And dt2 ;
INTO Cursor viewsi Readwrite
Index On sino Tag sino Additive
Replace All amount With (Icase(;
'1000/'$unit, (quantity*rate)/1000, ;
'100/'$unit, (quantity*rate)/100, ;
(quantity*rate)))

Thanks for your time sharing.

Saif

 

[Mike Lewis]

Saif, good to see that you have a solution that works. But it's a pity you didn't read our attemtps at helping you. If you had, you would have seen that [tt]AS Amount[/tt] is not applicable in your CAST() function. In fact, you could have left out the casting completely, and had a solution that worked (after re-balancing the parentheses).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Saif, good to see that you have a solution that works. But it's a pity you didn't read our attemtps at helping you. If you had, you would have seen that AS Amount is not applicable in your CAST() function. In fact, you could have left out the casting completely, and had a solution that worked (after re-balancing the parentheses).

Thanks Mike, I will try again.

Saif

 

[Olaf Doschke]

All you'll need now from where you are is actually doing the replace command literally in the source code yourself.

You replace the expression (quantity*rate) for the amount column with the expression you use in your REPLACE statement.

This....

Select rawsim.sino, rawsim.invdate, rawsim.pcode, customer.full_name,;
 rawsim.enteredby, rawsim.salesman, rawsim.refno, rawsim.sono, rawsim.refdate, ;
 rawsim.discamount, rawsim.dono, rawsim.jono, ;
 rawsim.Curr, rawsim.isocode, rawsim.Currency, rawsim.Sign, rawsim.currrate,;
 rawsim.discrem, rawsim.remarks, rawsi.rcode, ;
 rawsi.quantity, rawsi.unit, ;
 rawsi.rate, [COLOR=#CC0000](quantity*rate)[/color] As amount ;
 FROM ;
 village!rawsim ;
 inner Join village!rawsi ;
 ON rawsim.sino = rawsi.sino ;
 inner Join village!customer ;
 ON rawsim.pcode = customer.pcode;
 WHERE rawsim.invdate Between dt1 And dt2 ;
 INTO Cursor viewsi Readwrite

...becomes this:

Select rawsim.sino, rawsim.invdate, rawsim.pcode, customer.full_name,;
 rawsim.enteredby, rawsim.salesman, rawsim.refno, rawsim.sono, rawsim.refdate, ;
 rawsim.discamount, rawsim.dono, rawsim.jono, ;
 rawsim.Curr, rawsim.isocode, rawsim.Currency, rawsim.Sign, rawsim.currrate,;
 rawsim.discrem, rawsim.remarks, rawsi.rcode, ;
 rawsi.quantity, rawsi.unit, ;
 rawsi.rate, [COLOR=#CC0000](Icase(;
 '1000/'$unit, (quantity*rate)/1000, ;
 '100/'$unit, (quantity*rate)/100, ;
 (quantity*rate)))[/color] As amount ;
 FROM ;
 village!rawsim ;
 inner Join village!rawsi ;
 ON rawsim.sino = rawsi.sino ;
 inner Join village!customer ;
 ON rawsim.pcode = customer.pcode;
 WHERE rawsim.invdate Between dt1 And dt2 ;
 INTO Cursor viewsi Readwrite

If you're that far, what holds you back to make this last step, if you aimed so long to get this into one single query?

Bye, Olaf.

 

[alisaif]

Thanks Mr.Olaf for the update.

Saif

 

[TamarGranor]

Somehow posted this in the wrong thread the other day:

Just want to point out for all that Thor has a tool that highlights everything between parens and goes outward one level at a time. Really handy for stuff like this.

Tamar

P.S. It's at Code | Highlighting Text | Highlight Parentheses in the Thor menu.