Hi Steve,
Sorry for the long delay.
SELECT SQL is very powerfull and worth the time to learn. Also INSERT SQL and UPDATE SQL are very usefull.
lets see if I understand your question. The usage of
.. AS Cur1 is most like
USE ... ALIAS . IOW Cur1 and Cur2 are aliases used within the SQL Select statement. If you use it without the AS clause in FROM and JOIN, you have to replace each Cur1 with Rate and each Cur2 with cdr. I personally like the Cur
n style because it's more easy for copying between differnt SELECTS (and it helps for macroexpansion in tablenames, but this an other story).
I tend to 'overnormalize', sorry for it. I do without thinking about it.
The first selects should gather all not empty records from cdr and connect them with all records from rate wich fits in any way. A record dnis="0115411555555" should be result in two records, one for Rate.Code="0115411" and one for rate.code="01154".
The work is done in the ON clause wich compares dnis=code and also eliminates the empty recods of cdr table.
IOW FROM .. INNER JOIN ... connects to tables by the rule given by ON. Any record that fit not to that comparsion will not be listed in the result set. If one record from the one "side" of JOIN finds more than one record on the other "side" of the join the result set will hold the number of 'Hits'. This is why we get records for each Rate.Code="0115411" and rate.code="01154".
The second select reads the maximum codes for a given UniqueID.
The third select gives the only those records from the first select that we have found to be those with the longest code for each UniqueID. Again the work is done in the ON clause which says "Give me those records from curHelp which could be found in curSum, eleminate the rest". The result is the information we need. Now we have to pass those information to the cdr table.
I will change the example so that it hold your unifier. I also remove the Cur* from the SELECT's. Simple single step trough the code and see whats happens. Browse the result of each SELECT so you can see what happens.
SELECT cdr
SET ORDER TO _Primary
INDEX ON CDR.DNIS+CDR.StartDT TAG _Primary
SET EXACT OFF
SET STEP ON
SELECT;
cdr.DNIS+cdr.StartDT AS CDR_Uni,;
Rate.DEST AS CDR_Dest,;
Rate.CODE AS CDR_Code,;
Rate.Rate AS CDR_Rate,;
cdr.Duration AS CDR_Duration,;
IIF(cdr.Duration >0,;
IIF(cdr.Duration<Rate.MinimumSec,;
(Rate.MinimumSec/60),
(CEILING(cdr.Duration/Rate.INCREMENT)/60*Rate.INCREMENT)),;
0) AS CDR_billdur,;
IIF(cdr.Duration > 0,Rate.Rate*cdr.billdur,0) AS CDR_Charge;
FROM Rate,;
INNER JOIN cdr;
ON ALLTRIM(cdr.dnis)=Rate.CODE;
AND !EMPTY(cdr.DEST);
INTO CURSOR curHelp
SELECT;
curHelp.CDR_Uni,;
curHelp.CDR_Code,;
MAX(LEN(ALLTRIM(curHelp.CDR_Code);
FROM curHelp;
INTO CURSOR curSum;
GROUP BY 1,2
SELECT;
curHelp.CDR_Uni,;
curHelp.CDR_Dest,;
curHelp.CDR_Code,;
curHelp.CDR_Rate,;
curHelp.CDR_Duration,;
curHelp.CDR_billdur,;
curHelp.CDR_Charge;
FROM curHelp,;
INNER JOIN curSum;
ON curSum.CDR_Uni==curHelp.CDR_Uni;
AND curSum.CDR_Code==curHelp.CDR_Code;
INTO CURSOR curHelp1
SET RELATION TO CDR_Uni INTO CDR
REPLACE;
CDR.Rate WITH CDR_Rate,;
CDR.DEST WITH CDR_Dest,;
CDR.CODE WITH CDR_Code,;
CDR.BillDur WITH CDR_BillDur,;
CDR.Charge WITH CDR_Charge;
ALL
SET RELATION TO
USE
USE IN curSum
USE IN curHelp
SELECT cdr
SET ORDER TO
HTH
Agnes
Words are given to man to enable him to conceal his true feelings.
Charles Maurice de Talleyrand-Périgord
Weeks of programming can save you hours of planning.
OffThere is no place like [::1]