• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 837
  • Last Modified:

Why do some complex FoxPro SELECT SUM queries return asterisks?

I am having a problem with a complex FoxPro query.  For some reason when I execute the query below it returns asterisks for some of the results.  However, if I add a WHERE clause that specifies a specific smaller subset of records, the summary is calculated correctly and does not return asterisks.  Any ideas on how to get around this limitation?  I have included both queries (asterisk result query and numeric result query [query with saq_sku=="2003" at end])
Query that returns asterisks in some summary columns of some records:

SELECT sad_cat, sad_sub, saq_sku, saq_stloc, saq_qty, nvl(s60_qty,0) s60_qty, nvl(s420_qty,0) s420_qty, nvl(s360_qty,0) s360_qty, nvl(c360_qty,0) c360_qty
FROM scsaqty
JOIN scsadsc ON saq_sadfk==sad_pk
LEFT JOIN (
  SELECT sal_sku s60_sku, sal_stloc s60_stloc, SUM(sal_soldqty) s60_qty
  FROM scsasld
  WHERE sal_solddate>=DATE()-60 AND sal_solddate<=DATE()
  GROUP BY sal_sku, sal_stloc
) s60 ON UPPER(saq_sku+saq_stloc)==UPPER(s60_sku+s60_stloc)
LEFT JOIN (
  SELECT sal_sku s420_sku, sal_stloc s420_stloc, SUM(sal_soldqty) s420_qty
  FROM scsasld WHERE sal_solddate>=DATE()-420 AND sal_solddate<=DATE()-360
  GROUP BY sal_sku, sal_stloc
) s420 ON UPPER(saq_sku+saq_stloc)==UPPER(s420_sku+s420_stloc)
LEFT JOIN (
  SELECT sal_sku s360_sku, sal_stloc s360_stloc, SUM(sal_soldqty) s360_qty
  FROM scsasld
  WHERE sal_solddate>=DATE()-360 AND sal_solddate<=DATE()-300
  GROUP BY sal_sku, sal_stloc
) s360 ON UPPER(saq_sku+saq_stloc)==UPPER(s360_sku+s360_stloc)
LEFT JOIN (
  SELECT sal_sku c360_sku, sal_stloc c360_stloc, SUM(sal_soldqty) c360_qty
  FROM scsasld WHERE sal_solddate>=DATE()-360 AND sal_solddate<=DATE()
  GROUP BY sal_sku, sal_stloc
) c360 ON UPPER(saq_sku+saq_stloc)==UPPER(c360_sku+c360_stloc)
ORDER BY saq_sku, saq_stloc WHERE sad_cat<>12

Open in new window

Query that returns numerical results in ALL summary columns of ALL records:

SELECT sad_cat, sad_sub, saq_sku, saq_stloc, saq_qty, nvl(s60_qty,0) s60_qty, nvl(s420_qty,0) s420_qty, nvl(s360_qty,0) s360_qty, nvl(c360_qty,0) c360_qty FROM scsaqty
JOIN scsadsc ON saq_sadfk==sad_pk
LEFT JOIN ( SELECT sal_sku s60_sku, sal_stloc s60_stloc, SUM(sal_soldqty) s60_qty FROM scsasld WHERE sal_solddate>=DATE()-60 AND sal_solddate<=DATE() GROUP BY sal_sku, sal_stloc ) s60 ON UPPER(saq_sku+saq_stloc)==UPPER(s60_sku+s60_stloc)
LEFT JOIN (
  SELECT sal_sku s420_sku, sal_stloc s420_stloc,
    SUM(sal_soldqty) s420_qty
  FROM scsasld WHERE sal_solddate>=DATE()-420 AND sal_solddate<=DATE()-360
  GROUP BY sal_sku, sal_stloc
) s420 ON UPPER(saq_sku+saq_stloc)==UPPER(s420_sku+s420_stloc)
LEFT JOIN (
  SELECT sal_sku s360_sku, sal_stloc s360_stloc, SUM(sal_soldqty) s360_qty
  FROM scsasld
  WHERE sal_solddate>=DATE()-360 AND sal_solddate<=DATE()-300
  GROUP BY sal_sku, sal_stloc
) s360 ON UPPER(saq_sku+saq_stloc)==UPPER(s360_sku+s360_stloc)
LEFT JOIN (
  SELECT sal_sku c360_sku, sal_stloc c360_stloc, SUM(sal_soldqty) c360_qty
  FROM scsasld WHERE sal_solddate>=DATE()-360 AND sal_solddate<=DATE()
  GROUP BY sal_sku, sal_stloc
) c360 ON UPPER(saq_sku+saq_stloc)==UPPER(c360_sku+c360_stloc)
ORDER BY saq_sku, saq_stloc WHERE sad_cat<>12 AND saq_sku=="2003"

Open in new window

0
dietzmj
Asked:
dietzmj
1 Solution
 
jrbbldrCommented:
Asterisks in a cursor/data table field indicate a numerical over-flow occurred.
And you most likely got an error message indicating that an over-flow was occurring when you got those results.

returns asterisks for some of the results.

Look closely at the size of those fields which are getting the asterisks.  
If it is a Number field type it is defined with N (Total digits incl decimal point,  decimal digits)
Perhaps the specifications for that field needs to be enlarged.

Note - one 'gotcha' that you might be encountering is that the results of a SQL Query often determines the field 'size' by the value of the first record returned.    
So if the field value of the first record was  20.2  the entire field would be defined as N(4,2)
Then if subsequent record field values were something like 20000, those values overflow the field size and be shown as asterisks
Sometimes you have to 'play around' with field content to prevent this from occurring.

Good Luck




0
 
dietzmjAuthor Commented:
To fix it, I simply put CAST() statements in the first line of the SELECT query (ie. CAST(s60_qty AS N(10,2)).  This forced the query to cast a large enough number-format to handle the computations.  Thanks jrbbldr!
0
 
pcelbaCommented:
The second query does not produce asterisks because it returns lower numbers which still fits to the output format.

Did you check if the asterisks are just on the output or in innermost queryresults already?

What is the data type of summarized columns?

What is the outputc (result) data type? You should check it both in innermost queries and in the output cursor.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now