SQL Calculation showing a single Asterisk

[montypython1]

Greetings,

Why would I receive a single asterisk (*) instead of the calculated amount within an SQL statement?

My previous code worked perfectly, until I "fixed" it to sort differently. The ONLY change that I made to the code (below) is to add the field name "cust_cnty" (see the ">>>>" below) to the "ORDER BY" section. If I remove "cust_cnty" from the "ORDER BY" section, it works perfectly, though it is not sorted properly.

Strangely, if I use the same code with a very small database, it works fine! (unfortunately, my actual database is not small, however).

Note on the following files and field names:
- CapCostRedNVL is the problem calculation
- CapCostRed is the original amount
- There are 3 joined files: ST_Src01, ST_FI, CountyTaxRates

********************************************************
SELECT st_src01.*, st_fi.* ;
FROM st_src01 ;
LEFT OUTER JOIN st_fi ;
ON stocknum = st_fi.stocknumfi ;
>>>> ORDER BY cust_cnty, stocknum ;
INTO CURSOR csr01_fi

SELECT cust_cnty, stocknum, sale_amt, ;
IIF(sale_amt>0, NVL(CapCostRed,0), NVL(CapCostRed,0)*-1) AS CapCostRedNVL, ;
countytaxrates.taxrate ;
FROM csr01_fi ;
LEFT OUTER JOIN countytaxrates ;
ON cust_cnty = countyname
********************************************************

Any help would be greatly appreciated.

Thanks,
Dave

 

[bborissov]

********************************************************
SELECT st_src01.*, st_fi.* ;
    FROM st_src01 ;
    LEFT OUTER JOIN st_fi ;
    ON stocknum = st_fi.stocknumfi ;
    ORDER BY cust_cnty, stocknum ;
    INTO CURSOR csr01_fi

*** Suppose CapCostRed fiedl is 9,2 
SELECT cust_cnty, stocknum, sale_amt, ;
    IIF(sale_amt>0, NVL(CapCostRed,000000.00), NVL(CapCostRed,000000.00)*-1) AS CapCostRedNVL, ;
    countytaxrates.taxrate ;
    FROM csr01_fi ;
    LEFT OUTER JOIN countytaxrates ;
    ON cust_cnty = countyname

In VFP 9 you can change this statement to

IIF(sale_amt>0, NVL(CapCostRed,CAST(0 AS N(9,2)), NVL(CapCostRed,CAST(0 AS N(9,2))*-1) AS CapCostRedNVL, ;

I will change it that way:

NVL(CapCostRed,000000.00)*SIGN(sale_amt) AS CapCostRedNVL, ;

Borislav Borissov

 

[montypython1]

Hi Borislav,

Thanks .... your suggestion worked perfectly. I am curious why my original code stopped displaying the field "CapCostRedNVL" properly, simply because of changing the sort criteria.

Thank you again.
Dave

 

[bborissov]

Because before the first result of NVL() is 0 and VFP define the length of the field as 1 byte, chnaging the order first result is value of the field CapCostRed and then VFP defines length of the field as long as LEN(CapCostRed)

Borislav Borissov

 

[montypython1]

Thank you .... I certainly would NOT have figured that out on my own.

Dave Higgins