ODBC returning different totals to 4GL

[bugg_tb]

I've recently been given access to our databases with ODBC and a playing around. Our sales figures are held in a table called D2_Move, when I run a query to select all the sales figures in a database and add them up it returns a smaller figure than when run against 4GL, I can't work out what could be causing the discrepancy, record locking perhaps although I've set ODBC to READ UNCOMMITTED and I've tried it on a machine with no users on it. So as you can see I'm at a bit of dead loss the query is a simple select * from D2_Move where mv_type=002 so its not uber complicated. Any ideas what could be causing the discrepancy?

Cheers

Tom

 

[tamhas]

You might try doing a count with the same query. Want to bet it's different?

 

[bugg_tb]

Thanks for the reply, I suspect you will probably be right when I run a count query also. But no really knowing how the Progress SQL works with the 4GL brokers etc, what causes should I be looking at, considering I know the data is correct and I am running the same query just in SQL and not 4GL.

Cheers

Tom

 

[tamhas]

To really answer the question, I think we would have to see both queries and know a little about the structure of the table. Depending on the number of records involved, you might end up having to bring back detail instead of summary data, capture it in a file, and do a diff to see what's different. That's brute force, but potentially effective.

From what you have told us so far, no, there is no systematic "typical" reason why an ABL query and the corresponding SQL query wouldn't produce the same result. So, the "typical" answer is that there is some way in which the two queries are not actually equivalent.

 

[bugg_tb]

Well the 4GL code looks like this....

/* INTRANET_S_Base_Values_Analysis.p */ /* last print date 110407 */ 
 
/* RUNTIME ~ 7 min. */ 
 
/********************************************************/ 
/* PROGRESS Base Values Analysis - csv file re Intranet */ 
/********************************************************/ 
 
DEF VAR qbf-count   AS INT. 
DEF VAR qbf-loop    AS INT. 
DEF VAR qbf-time    AS INT. 
 
DEF VAR qbf-002     AS CHAR. 
DEF VAR qbf-003     AS DEC. 
DEF VAR qbf-004     AS DEC. 
DEF VAR qbf-005     AS DEC. 
DEF VAR qbf-003#    AS DEC. 
DEF VAR qbf-004#    AS DEC. 
DEF VAR qbf-005#    AS DEC.  
 
DEF BUFFER D2_MOVE FOR SYDB.D2_MOVE. 
DEF BUFFER A1_TRAD_POINT FOR SYDB.A1_TRAD_POINT. 
 
DEFINE VARIABLE mh AS INTEGER. 
DEFINE VARIABLE yr AS INTEGER. 
DEFINE VARIABLE idayb AS INTEGER. 
DEFINE VARIABLE idaye AS INTEGER. 
DEFINE VARIABLE imon AS INTEGER. 
DEFINE VARIABLE iyr AS INTEGER. 
DEFINE VARIABLE bdate AS DATE FORMAT "99/99/9999". 
DEFINE VARIABLE edate AS DATE FORMAT "99/99/9999".     
 
ASSIGN 
    qbf-count    = 0 
    qbf-time     = TIME 
    /* additional */ 
    mh = MONTH(TODAY) 
    yr = YEAR(TODAY). 
     
CASE mh:     
    WHEN 1 THEN 
        ASSIGN  
            imon = 1 
            idaye = 31 
            iyr = yr. 
    WHEN 2 THEN 
        ASSIGN 
            imon = 2 
            iyr = yr. 
    WHEN 3 THEN 
        ASSIGN 
            imon = 3 
            idaye = 31 
            iyr = yr.     
    WHEN 4 THEN 
        ASSIGN 
            imon = 4 
            idaye = 30 
            iyr = yr. 
    WHEN 5 THEN 
        ASSIGN 
            imon = 5 
            idaye = 31 
            iyr = yr. 
    WHEN 6 THEN 
        ASSIGN 
            imon = 6 
            idaye = 30 
            iyr = yr. 
    WHEN 7 THEN 
        ASSIGN 
            imon = 7 
            idaye = 31 
            iyr = yr. 
    WHEN 8 THEN 
        ASSIGN 
            imon = 8 
            idaye = 31 
            iyr = yr.  
    WHEN 9 THEN 
        ASSIGN 
            imon = 9 
            idaye = 30 
            iyr = yr. 
    WHEN 10 THEN 
        ASSIGN 
            imon = 10 
            idaye = 31 
            iyr = yr. 
    WHEN 11 THEN 
        ASSIGN 
            imon = 11 
            idaye = 30 
            iyr = yr. 
    WHEN 12 THEN 
        ASSIGN 
            imon = 12 
            idaye = 31 
            iyr = yr. 
END CASE. 
 
CASE mh: 
    WHEN 2 THEN 
        CASE yr: 
            WHEN 2004 OR WHEN 2008 OR WHEN 2012 OR WHEN 2016 OR WHEN 2020 OR WHEN 2024 THEN  
                ASSIGN 
                    idaye = 29. 
            OTHERWISE 
                ASSIGN 
                    idaye = 28. 
END CASE. 
END. 
 
ASSIGN 
    idayb = 1 
    bdate = DATE(imon,idayb,iyr) 
    edate = DATE(imon,idaye,iyr). 
 
 
OUTPUT TO \\enconfs10\csv\base_values_analysis.csv. 
 
 
/* Headers */ 
PUT CONTROL 
    '~"' "T/P Name" '~"' 
    "~," 
    '~"' "Movement Type" '~"' 
    "~," 
    '~"' "Value" '~"' 
    "~," 
    '~"' "Cost" '~"' 
    "~," 
    '~"' "Profit" '~"' 
    "~," 
    CHR(13) CHR(10). 
     
main-loop: 
FOR EACH SYDB.D2_MOVE NO-LOCK 
  WHERE (( SYDB.D2_MOVE.mv_date >= bdate   AND  SYDB.D2_MOVE.mv_date <= edate )  AND (SYDB.D2_MOVE.mv_type = "002"  OR SYDB.D2_MOVE.mv_type = "003")),EACH SYDB.A1_TRAD_POINT NO-LOCK 
  OF SYDB.D2_MOVE 
  BREAK BY SYDB.D2_MOVE.trad_code 
    BY SYDB.D2_MOVE.mv_type: 
 
  qbf-count  = qbf-count + 1. 
  ASSIGN 
    qbf-002 = IF (SYDB.D2_MOVE.mv_type = "002") THEN ("Sales") ELSE ("Returns") 
    qbf-003 = IF (SYDB.D2_MOVE.mv_type = "002") THEN (SYDB.D2_MOVE.mv_value) ELSE -(SYDB.D2_MOVE.mv_value) 
    qbf-004 = IF (SYDB.D2_MOVE.mv_type = "002") THEN (SYDB.D2_MOVE.mv_cost) ELSE -(SYDB.D2_MOVE.mv_cost) 
    qbf-005 = IF (SYDB.D2_MOVE.mv_type = "002") THEN ((SYDB.D2_MOVE.mv_value - SYDB.D2_MOVE.mv_cost)) ELSE -((SYDB.D2_MOVE.mv_value - SYDB.D2_MOVE.mv_cost)). 
  ACCUMULATE qbf-003 (SUB-TOTAL BY SYDB.D2_MOVE.mv_type). 
  ACCUMULATE qbf-004 (SUB-TOTAL BY SYDB.D2_MOVE.mv_type). 
  ACCUMULATE qbf-005 (SUB-TOTAL BY SYDB.D2_MOVE.mv_type). 
  ASSIGN 
    qbf-003# = (ACCUM SUB-TOTAL BY SYDB.D2_MOVE.mv_type qbf-003) 
    qbf-004# = (ACCUM SUB-TOTAL BY SYDB.D2_MOVE.mv_type qbf-004) 
    qbf-005# = (ACCUM SUB-TOTAL BY SYDB.D2_MOVE.mv_type qbf-005). 
  IF LAST-OF(SYDB.D2_MOVE.mv_type) THEN 
  PUT CONTROL 
    '~"' SYDB.A1_TRAD_POINT.trad_name '~"' 
    "~," 
    '~"' qbf-002 '~"' 
    "~," 
    qbf-003# 
    "~," 
    qbf-004# 
    "~," 
    qbf-005# 
    CHR(13) CHR(10). 
 
 
END.

Now I know nothing about 4GL but it does create a nice CSV along the lines of..

Branch                     Value  Cost   Profit               
BranchName   Sales   ££££    ££££   ££££
BranchName   Return   ££££    ££££   ££££
BranchName2   Sales   ££££    ££££   ££££
BranchName2   Return   ££££    ££££   ££££

and so on it just looks up transaction codes 002 and 003.

My sql script as shameful as it is doesn't need to do that I just want a huge table with all the transactions listed for transaction type 002(sales) and 003(returns)

So I wrote

SELECT "tc", "mv_value" AS val, "mv_cost" as cost, "mv_type" as tpe 
 FROM 
(Select trad_code as 'tc', mv_value, mv_cost, 
mv_type, mv_date 
From D2_MOVE 
GROUP BY trad_code, mv_value, 
mv_cost, mv_type, mv_date 
HAVING (mv_date Between '2007-10-01' And '2007-10-31') AND 
(mv_type ='002')) AS temptable 
GROUP BY "tc", "mv_type", "mv_value", "mv_cost"

Now I realise its ugly it's been hacked around a lot once I realised the figures didn't match, anyway you get the jist. Select the columns needed, between the dates specified and for type 002.

It always come up short, as you pointed out, missing records.

Anyway, let me know if you see anything obvious.

Cheers

Tom

 

[tamhas]

Well, the first thing that strikes me doesn't have anything to do with your problem and that is that is certainly the hard way to find the beginning and end of the current month. All one needs for the begin date is to extract the month and year and combine it with 1 and create a date. For the end of the month, do the same with month + 1 (checking to see if there is a year roll over, and then subtract one from that date. Probably cuts out 90+% of the lines of code.

But, the other thing that leaps out at me is that the ABL code is break by branch and transaction type, while the SQL code is group by branch, value, cost, type, and date. Seems like that is going to produce rather different groupings.

 

[bugg_tb]

Well as I say the progress code is nothing to do with me, its the guy sat opposite at work, I just leave him to it.

But SQL wise the data should be grouped by BranchCode and so as we're summing the whole bunch of data for that branch they should total to the same amount(I think), regardless of (sub)groupings.

 

[bugg_tb]

This morning I re ran the query on Access just a basic select all within the month and of 002 status and it returned them, I worked out which transactions were missing and re-ran the query and they appeared and it seemed to drop others.
So I switched on ODBC logging, which reported nothing but successes.
Now I'm utterly confused


Edit: After more poking around and matching dates it appears it's skipping duplicate records? There is no distinct in my code, I've stripped it down to this....

Select ALL trad_code as 'tc', mv_value, mv_cost, cust_code, prod_code
From D2_MOVE
GROUP BY tc, cust_code, mv_value, mv_cost, mv_type, mv_date, prod_code
HAVING (mv_date Between '2007-10-01' And '2007-10-31') AND (mv_type
='002') AND (tc ='A09')

So now it pulls out records between the dates and with sales type 002 and only for branch A09 but its skipping dupes... would someone like to enlighten me as to why?

Cheers

Tom

 

[bugg_tb]

Solved it, removed the Having and replaced it with a Where clause and it stopped skipping the dupes. doh.

Cheers

Tom