Set Decimal to 4 digit

[alisaif]

Hi,

I am using the following in order to obtain the rate from sales invoice in descending order

SELECT DISTINCT Simain.pcode, Sisub.icode, Sisub.ratemin;
 FROM ;
     village!simain ;
    INNER JOIN village!sisub ;
   ON  Simain.sino = Sisub.sino;
   WHERE simain.invdate > {31/12/2015} ;
 INTO CURSOR SiJunk READWRITE NOFILTER
 INDEX on pcode+icode TAG pcic DESCENDING

in Sisub.Ratemin it has four digits, but it is rounding to 2 decimal places.

What wrong with this? Please guide.

Thanks

Saif

 

[Olaf Doschke]

You see 2 digits. Where? In a browse window?

What is the exact type of Ratemin in simin.dbf? Open up the DBF in the table designer to see or use AFILEDS to get the field description including type, width and precision.

If your field is float or double, SET DECIMALS will determine how many decimals are displayed, but not how precise the stored value is. Look at this:

CREATE CURSOR crsData (num B)
INSERT INTO crsData VALUES (0.1234)
? num
? num = 0.12

With default settings SET("DECIMALS") is 2, you get a display of 0.12, but the comparison shows this is not the stored value, as it shows .F.

So in this case seeing is not believing. Or lets put it this way: This is just one further case showing how wrong the "seeing is believing" saying is, and if you already know better, you know what to do, change your display routines. Either SET DECIMALS TO 4, which has a very global effect on all displays, especially with SET FIXED ON, or [tt]? STR(num,6,4)[/tt].

Bye, Olaf.

 

[Mike Lewis]

Saif, I can't add much to what Olaf has said. But let me ask you this. Why are you creating an index on the cursor?

If it is to allow you to use SEEK or SEEK() to find specific invoices, or to use LOCATE with optimisation for that purpose, then that's fine. Just leave it as it is.

But if the index is to allow you to display a report of invoices in descending order, or to let the user step forwards or backwards through the indexes in that order, then it would be neater to omit the index. Instead, use an ORDER BY clause in your SELECT statement.

I'm not saying that ORDER BY will always be faster than INDEX ON (although I suspect it will), bit it means one less line of cocde, and also that you will be using SELECT in a way that it is desgined for.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

What Mike says is true. It's not your central question, but indeed if you index to sort data, you create an index to make use of it for ordering, while SQL ORDER BY makes use of already existing indexes and in the end removes the need to index the result for ordering purposes. That saves double time, as it orders while querying making use of already existing indexes and makes indexing the result unnecessary.

Indexes are good, mainly permanent indexes on your permanent backend data, as they are then read and used many many times for query optimizations.

Creating indexes, especially only for one time usage, is a bad idea in comparison to the backend index usage. Notice sql can make use of multiple index tags for each single partial where condition and also for sorting data, that's a strength you don't have with xbase usage of one index at a time, only.

Bye, Olaf.

 

[alisaif]

Thanks for the reply.

Saif, I can't add much to what Olaf has said. But let me ask you this. Why are you creating an index on the cursor?

Actually I want to get the last selling price of the customer from the invoice.

Is there any other way to get this?

My Sales tables are:

SIMAIN: (Master File)

SINO C(8), && Sales Invoice No.
InvDate d, Sales Invoice Date
pcode c(10), Customer Code

Sisub: (Transaction File)
icode c(10), && Item Code
quantity n(13,3) &&Item Quantity
Rate n(10,3) && Item Rate

Here, I want the last rate of the item.

Please guide

Saif

 

[Olaf Doschke]

Sorting by pcode+icode descending gives you that record at the top?

Well, then

SELECT TOP 1 Simain.pcode, Sisub.icode, Sisub.ratemin;
 FROM ;
     village!simain ;
    INNER JOIN village!sisub ;
   ON  Simain.sino = Sisub.sino;
   WHERE simain.invdate > {^2015-12-31} ;
 ORDER BY pcode desc, icode desc

Bye, Olaf.

 

[alisaif]

Thanks

It is not fetching any record!

Saif

 

[Olaf Doschke]

Are you sure you have any data past 2015?

It's your query, I can't tell you what's wrong with it, if you don't even hava a top 1 record, when sorting it with ORDER BY. To be clear: If this doesn't yield any result, then why would your original query yield any result? I just added top1 and an order. Orderuing keeps the number of result rows, top 1 picks one of them.

To get real help, you should come up with sample data and expected result. I can't mend a query not knowing your data and not knowing the meanings of tables and fields involved. So wither you explain the problem more abstract or more detailed.

Bye, Olaf.

 

[Mike Lewis]

It seems to me that the WHERE clause is redundant. If you omited it, then your latest invoice would always show up in the result, which is what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks

My Sales table contains records from 01/07/2009 till date.

Saif