<td align=”center”>
<div style=”position:relative; z-index:1;”>
<img src=”/images/migmnuproc/body_img.gif” width=”697″ height=”389″ />
</div>
<div style=”position:relative; top:-125; right:187;z-index:2″>
<img src=”/images/migmnuproc/cutButton.jpg” />
</div>
</td>
Month: June 2008
Dad's Last Day
Dad spent the day at the Air Show with my brother and his son, he peacefully slipped away during a nap.
David Gordon Mason
July 26, 1934 – June 8, 2008
When inserting from another file using SQl
make sure you have matching fields selected (ignore timestamps)
INSERT INTO DC@DEVLIB/M@ACTMNT (AGENT, ACCTNO, POLICY, EFFDTE,
EDSNO, INSNAM, AGYTYP) SELECT * FROM masonm/webfl100 a WHERE not
exists (select agent from dc@devlib/M@actmnt b where
a.acctno=b.acctno)
83 rows inserted in M@ACTMNT in DC@DEVLIB.
Inserted records from file.a into file.b where not exists
Don’t you hate when your copy file with *ADD fails due to duplicate key? I used this sql to push the rest in.
INSERT INTO DVDTA/DWXP010
SELECT table1.* FROM masonm/xdwxp010 table1 left join dvdta/dwxp010
table2 on (table1.acctno = table2.acctno and table1.policy =
table2.policy and table1.effdte = table2.effdte and table1.edsno =
table2.edsno) WHERE table2.acctno is null
46 rows inserted in DWXP010 in DVDTA.
SQL LEFT JOIN (resembles not exist)
SELECT table1.* FROM masonm/xdwxp010 table1 left join dvdta/dwxp010
table2 on (table1.acctno = table2.acctno and table1.policy =
table2.policy and table1.effdte = table2.effdte and table1.edsno =
table2.edsno) WHERE table2.acctno is null
I prefer not exists
select * from table1
where not exists (select field2 from table2 where table2.field2 = table1.field1)
SQL INSERT INTO SELECT example
INSERT INTO MASONM/XDWXP010 (POLICY, EFFDTE, EDSNO, EDSDTE, ACTDTE,
COVEND, EXPDTE, CANDTE, CANPND, POLTRM, CANOPT, CANRSN, CANMTD,
CANSTE, NONPAY, INSNAM, INSAD1, INSAD2, INSAD3, INSCTY, INSST,
INSZIP, INSTEL, SERCH, CO, AGENT, SUBPRO, AGYTYP, AGYCOM, ACCTNO,
DIRASS, POLTYP, INSTYP, PROD, EDMNTS, PRVPOL, RELPOL, PRIPOL,
CLMOCC, POLPRT, REINST, NONREN, STATS1, STATS2, STATS3, STATS4,
STATS5, TRANS, TRDATE, TRTIME, IDADD, MAILDT, POLPRE, ASSRSK,
REINFL, NRWRSN, PRTF1, FLAGI1, FLAGI2, FLAGI3, REFQOT, UDDR,
UDDROD, UDDRDT, MVRHLD, RNIMAG, PAYPLN, FACTOR, APRP, INTCHG, COMM,
TOTDUE, TOTFAC, RTEFLG, AUDFRQ, AUDTYP, AUDEND, FINAUD, RETRTE,
INTRSK, PRDFLG, FIXFLD, SUBCAN, SUBEDS, NBRENTY, GLCLAS, ORPOYR,
RETDTE, TAIDTE, FLAGI4, FLAGI5, FLAGI6, POLCFG, FUTUR3, FUTUR4,
FUTUR5, ORGEFF, TRUEXP, FIELDA, FIELDB, FIELDC, FIELDD, FIELDE,
FIELDF, CMOVFG, CMOVPC, COUTRY, RTEMTD, CNVPOL, COMWOP, FACPRM,
DUPNAM, INDIC1, INDIC2, INDIC3, INDIC4, INDIC5, INDIC6, MINPRE,
FLD003, FLD004, NYFTCL, NYFTCD, FLD005, FLD006, AUDDTE, SICCDE,
INTPOL, FLD008, REDEPT, MAXLOS, FLD010, FLTPOL, GRPPGH, GRPDIS,
USELOC, PRGMID, TOTPRM, TYCVBS, NMADIN, BUSDSC, FINCCO, RWTFLG,
RWTPOL, PGMFLG, SHTRSN, UNDRWR, TYPBIZ, HAZGRP, FUTF11, FUTF12,
FUTF13, FUTF14, FUTF31, FUTF51, DSPRET, SIPFLG, EMPLPF, PCERTN,
POLPLI, WRAPUP, OLNINS, APYWAV, DIRHLD, BKTRAN, CLNTID, PREDST,
WAVAMT, RTEFDT, WAVPRM, REFOVR, RLPATP, PNDRFG, RLPCOM, FACRIN,
NXTRVW, REFLOS, TRECDT, AGNDDT, QTAGDT, QTFLDT, DCCLDT, QTFLAG,
SMSTCD, SMSTTX) SELECT xdwxp010.* FROM masonm/zdwxp010
xdwxp010,masonm/webfl100 WHERE XDWXP010.AGENT = WEBFL100.AGENT and
XDWXP010.ACCTNO = WEBFL100.ACCTNO and XDWXP010.POLICY =
WEBFL100.POLICY and XDWXP010.EFFDTE = WEBFL100.EFFDTE and
XDWXP010.EDSNO = WEBFL100.EDSNO
83 rows inserted in XDWXP010 in MASONM.
EDPX Form Validations
<HTML>
<HEAD>
<TITLE>Form Validation Example </TITLE>
<SCRIPT LANGUAGE=”JavaScript”>
<!–
//* This is an attempt to eventually pull out the file level
//* validations from the LANSA repository to be applied on client side
function validateForm()
{
var myattr=new Array();
bldAttrArray(myattr);
if(chkreqelements(myattr,’form’)) {return(true);} else {return(false);}
if(customValidations) {return(true);} else {return(false);}
}
function validateField(vfieldobj)
{
var myfieldobj=vfieldobj;
var myfieldattr=new Array();
bldFieldAttrArray(myfieldobj,myfieldattr);
if(!chkreqelements(myfieldattr,’field’)) ErrField(myfieldobj);
}
function bldAttrArray(bldattrarr)
{
var attstr=”what is in new array\n\n”;
var attnodeName=””;
var attnodeValue=””;
var k=0;
for(i=0;i<document.LANSA.elements.length;i++)
{
for(j=0;j<document.LANSA.elements[i].attributes.length;j++)
{
attnodeName=document.LANSA.elements[i].attributes[j].nodeName.toUpperCase();
if(attnodeName==”EDPX”)
{
attnodeValue=document.LANSA.elements[i].attributes[j].nodeValue;
attnodeValue=attnodeValue.toUpperCase();
k=bldattrarr.length;
bldattrarr[ k ]=document.LANSA.elements[i].id+”:”+attnodeValue;
attstr += bldattrarr[ k ]+” “;
}
}
attstr += “\n”;
}
//* remove comment bellow to see all EDPX elements in form
//*alert( attstr );
}
function bldFieldAttrArray(fieldobj,fieldattarr)
{
var fieldattobj=fieldobj;
var fieldattstr=”what is in field array\n\n”;
var fieldattnodeName=””;
var fieldattnodeValue=””;
var k=0;
for(j=0;j<fieldattobj.attributes.length;j++)
{
fieldattnodeName=fieldattobj.attributes[j].nodeName.toUpperCase();
if(fieldattnodeName==”EDPX”)
{
fieldattnodeValue=fieldattobj.attributes[j].nodeValue;
fieldattnodeValue=fieldattnodeValue.toUpperCase();
k=fieldattarr.length;
fieldattarr[ k ]=fieldattobj.id+”:”+fieldattnodeValue;
fieldattstr += fieldattarr[ k ]+” “;
}
}
fieldattstr += “\n”;
//* remove comment bellow to see all EDPX elements in form
//* alert(fieldattstr);
}
function chkreqelements(attarr,atttyp)
{
var cremsg=”The Following errors have been found: “;
//* For LANSA users Messages can be put in a browse list *NOBICP then do a merge list here
var crems00=”is required and must be entered”
var crems01=”has bad address “;
var crems02=”has invalid characters a-z A-Z “;
var crems03=”has invalid characters a-z A-Z 0-9 “;
var crems04=”is not a number “;
var crems05=”can not be a number “;
var crems06=”is invalid date “;
var crems07=”is not an acceptable range for Date of Birth “;
var crems08=”is invalid format ###-##-#### “;
var crems09=” “;
var crems10=” “;
var crems11=” “;
var crems12=” “;
var crems13=” “;
var crems14=” “;
var crems15=” “;
var crems99=”ERROR COUNT -> “;
var creid=””;
var creval=””;
var crestr=””;
var dotdot=0;
var creobj=””;
var crereq=””;
var cretyp=””;
var cretxt=””;
var errcnt=0;
var cretmp=””;
cremsg += “\n\n”;
for(i=0;i<attarr.length;i++)
{
crestr=attarr[i];
dotdot=crestr.indexOf(“:”);
creid=crestr.substr(0,dotdot);
creval=crestr.substr(dotdot+1,crestr.length-1);
crereq=creval.match(“REQ”);
creobj=document.getElementById(creid);
creobj.value=trimTrailingBlanks(creobj.value);
if(creobj.value.length == 0)
{
//* Format of message here
cretmp=creobj.name+” as NULL => ”
if(creval.match(“REQ”)) {errcnt=errcnt+1;cremsg +=cretmp+crems00+”\n”;}
}
else
{
//* Format of message here
cretmp=creobj.name+” as “+creobj.value+” => ”
//* Add new validation checks here
if(creval.match(“EML”)) {if(!emailValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems01+”\n”;}}
if(creval.match(“A2Z”)) {if(!isAlpha(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems02+”\n”;}}
if(creval.match(“A29″)) {if(!isAlphanumeric(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems03+”\n”;}}
if(creval.match(“NUM”)) {if(!isNumeric(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems04+”\n”;}}
if(creval.match(“NAN”)) {if(isNumeric(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems05+”\n”;}}
if(creval.match(“DTE”)) {if(!dmyValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems06+”\n”;continue}}
if(creval.match(“DOB”)) {if(!dmyValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems07+”\n”;}}
if(creval.match(“SSN”)) {if(!isSSN(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems08+”\n”;}}
if(creval.match(“ZIP”)) {if(!isZipcodeUSA(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems09+”\n”;}}
if(creval.match(“ZZZ”)) {if(!dummyValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems10+”\n”;}}
if(creval.match(“ZZZ”)) {if(!dummyValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems11+”\n”;}}
if(creval.match(“ZZZ”)) {if(!dummyValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems12+”\n”;}}
if(creval.match(“ZZZ”)) {if(!dummyValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems13+”\n”;}}
if(creval.match(“ZZZ”)) {if(!dummyValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems14+”\n”;}}
if(creval.match(“ZZZ”)) {if(!dummyValidator(creobj)){errcnt=errcnt+1;cremsg +=cretmp+crems15+”\n”;}}
}
}
if(errcnt!= 0)
{
if(errcnt!=1) cremsg+=”\n”+crems99+errcnt;
if(atttyp==’form’) alert( cremsg );
return(false);
}
else
{return(true);}
}
function trimTrailingBlanks(TrimtrailingblanksValue)
{trim=TrimtrailingblanksValue;
if (trim.length==0) return trim;
for (var i=trim.length-1;i>=0;i–) if (trim.substring(i,i+1) !=’ ‘) {i++;break;}
if (i<0) i=0;
trim=trim.substring(0,i);
return trim;
}
//* ==============================================
//* Validation functions — Add new functions here
//* ==============================================
function isNumeric(elem){
var numericExpression = /^[0-9]+$/;
if(elem.value.match(numericExpression)){
return true;
}else{ return false;}
}
// If the element’s string matches the regular expression it is all letters
function isAlpha(elem){
var alphaExp = /^[a-zA-Z]+$/;
if(elem.value.match(alphaExp)){
return true;
}else{ return false;}
}
// If the element’s string matches the regular expression it is numbers and letters
function isAlphanumeric(elem){
var alphaExp = /^[0-9a-zA-Z]+$/;
if(elem.value.match(alphaExp)){
return true;
}else{ return false;}
}
function lengthRestriction(elem, min, max){
var uInput = elem.value;
if(uInput.length >= min && uInput.length <= max){
return true;
}else{ return false;}
}
function emailValidator(elem){
var emailExp = /^[\w\-\.\+]+\@[a-zA-Z0-9\.\-]+\.[a-zA-z0-9]{2,4}$/;
if(elem.value.match(emailExp)){
return true;
}else{ return false;}
}
function dummyValidator(elem){
if(elem==elem){
return true;
}else{ return false;}
}
function dmyValidator(elem) {
var RegExPattern = /^(?=\d)(?:(?:(?:(?:(?:0?[13578]|1[02])(\/|-|\.)31)\1|(?:(?:0?[1,3-9]|1[0-2])(\/|-|\.)(?:29|30)\2))(?:(?:1[6-9]|[2-9]\d)?\d{2})|(?:0?2(\/|-|\.)29\3(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))|(?:(?:0?[1-9])|(?:1[0-2]))(\/|-|\.)(?:0?[1-9]|1\d|2[0-8])\4(?:(?:1[6-9]|[2-9]\d)?\d{2}))($|\ (?=\d)))?(((0?[1-9]|1[012])(:[0-5]\d){0,2}(\ [AP]M))|([01]\d|2[0-3])(:[0-5]\d){1,2})?$/;
if (elem.value.match(RegExPattern)) {
return true;
} else { return false;}
}
function ssnValidation(elem) {
var ssn = elem.value;
var matchArr = ssn.match();
var numDashes = ssn.split(‘-‘).length – 1;
if (matchArr == null || numDashes == 1 || (parseInt(matchArr[1],10)==0)) {return false;} else { return true;}}
function isSSN(elem){
var numericExpression = /^(\d{3})-?\d{2}-?\d{4}$/;
if(elem.value.match(numericExpression)){
return true;
}else{ return false;}
}
function isZipcodeUSA(elem)
{
var iszip = elem.value;
var numDashes = elem.value.split(‘-‘).length – 1;
var islength = elem.value.length;
if (islength == 5)
{
if(isNumeric(elem))
{
return true;
} else { return false;}
}
}
//#######################################
//### Highlight/Unhighlight Focused Input Fields
//#######################################
function focusWork(focusworkThis,focusworkEvent)
{var Event=focusworkEvent;
var This=focusworkThis;
if (Event.type==’focus’) This.style.backgroundColor=’Yellow’;
if (Event.type==’blur’)
{ This.style.backgroundColor=’White’;
validateField(This);}
}
//#######################################
//### Error Notify Red Background Fields
//#######################################
function ErrField(errfieldThis)
{ var This=errfieldThis;
This.style.backgroundColor=’Pink’;
}
function isCustom()
{
//* Put your custom specific validations here (false=fail)
if(‘a’ == ‘a’) {return(true);}
else {return false;}
}
</SCRIPT>
</HEAD>
<BODY>
<CENTER>
<FONT SIZE=”5″ COLOR=”#006600″ FACE=”verdana”>Form Validation</FONT>
</CENTER><BR><BR>
<FORM METHOD=POST ACTION=”http://www.MerchantsGroup.com/” NAME=”LANSA”>
<TABLE ALIGN=”CENTER”>
<TR>
<TD>First Name</TD>
<TD><INPUT TYPE=”TEXT” NAME=”FIRST NAME” ID=”fname” EDPX=”RXQA2Z004010REQ” onfocus =”focusWork(this,event)” onblur=”focusWork(this,event)”></TD>
</TR>
<TR>
<TD>Last Name</TD>
<TD><INPUT TYPE=”TEXT” NAME=”LAST NAME” ID=”lname” EDPX=”REQa2z” onfocus =”focusWork(this,event)” onblur=”focusWork(this,event)””></TD>
</TR>
<TR>
<TD>Date of Birth</TD>
<TD><INPUT TYPE=”TEXT” NAME=”DATE OF BIRTH” ID=”dob” EDPX=”REQDTEDOB” onfocus =”focusWork(this,event)” onblur=”focusWork(this,event)””></TD>
</TR>
<TR>
<TD>Email</TD>
<TD><INPUT TYPE=”TEXT” NAME=”EMAIL” ID=”email1″ EDPX=”REQEML” onfocus =”focusWork(this,event)” onblur=”focusWork(this,event)”></TD>
</TR>
<TR>
<TD>Email 2</TD>
<TD><INPUT TYPE=”TEXT” NAME=”EMAIL2″ ID=”email2″ EDPX=”OPTEML” onfocus =”focusWork(this,event)” onblur=”focusWork(this,event)””></TD>
</TR>
<TR>
<TD>SS Number</TD>
<TD><INPUT TYPE=”TEXT” NAME=”SSN” ID=”SSN” EDPX=”OPTSSN” onfocus =”focusWork(this,event)” onblur=”focusWork(this,event)””></TD>
</TR>
<TR>
<TD>Zip Code</TD>
<TD><INPUT TYPE=”TEXT” NAME=”zip” ID=”zip” EDPX=”REQOPTZIP” onfocus =”focusWork(this,event)” onblur=”focusWork(this,event)””></TD>
</TR>
<TR>
<TD><INPUT TYPE=”SUBMIT” VALUE=”Submit Form” ID=”BUTTON2″ onClick=”return validateForm()”></TD>
<TD><INPUT TYPE=”RESET” VALUE=”Reset”></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
Fast and Dirty CSS Print layout
{style type=”text/css”>
@media print{
body{ background-color:#FFFFFF; background-image:none; color:#000000 }
div.chromestyle { display: none;}
div.noprint4u {display:none;}
tr.even { background-color:#FFFFFF; background-image:none; color:#000000 }
}
</style
LANSA Handle Event with Debug Prompt
function mmHandleEvent(Proc,Func)
{
var dbugDEV = prompt( "Enter Device Name for Debug Else leave blank" );
if(dbugDEV!="" ) document.LANSA.action+="+BDEBUG+"+dbugDEV+"+%20";
document.LANSA._PROCESS.value=Proc;
document.LANSA._FUNCTION.value=Func;
document.LANSA.submit();
}
LANSA JOBS
877 526 7220
LANSA Checkboxes in Expandable List
Must have _BLNAME before List and _BLEND at end. (1 underscore)
<input type=”hidden” name=”__ZZCHKUSER-<RDML MERGE=”&ROWNUM” FORMAT=4> D” value=”<RDML MERGE=”ZZCHKUSER”>” size=”1″ > (2 underscores )
This works. Field ZZCHKUSER is in Browselist that has *NOBICP
<input type=”hidden” name=”__ZZCHKUSER-<RDML MERGE=”&ROWNUM” FORMAT=4> D” value=”<RDML MERGE=”ZZCHKUSER”>” size=”1″ >
<input type=checkbox name=”mChkBoxQ<RDML MERGE=”LISTCOUNT”>” value=”” <RDML MERGE=ZZCHKNODE> onclick=”SetCBState(this, ‘__ZZCHKUSER-<RDML MERGE=”&ROWNUM” FORMAT=4> D’, ‘Y’, ‘N’)”></strong></td>
This is the stuff for the page component
<INPUT TYPE=”HIDDEN” NAME=”_BLNAME” VALUE=”X1BL01LST” SIZE=”10″>
<RDML MERGE=”&BLX1BL01LST”>
<INPUT TYPE=”HIDDEN” NAME=”_BLEND” VALUE=”X1BL01LST” SIZE=10>
This is the stuff for the list component
<!– Component START for B1ABSLST / MAM20080528 –>
<tr height=”20″ class='<RDML MERGE=”ZTRCOLOR”>’ >
<td align=”left”>
<A href=”JavaScript:document.LANSA.PLISTENTRY.value = ‘<RDML MERGE=”LISTCOUNT”>’; HandleEvent(‘<RDML MERGE=”*PROCESS”>’,'<RDML MERGE=”*FUNCTION”>’);”> <IMG src=”/IMAGES/ball_blue.gif” ALT=”Go To Account Bill Maintenance” BORDER=”0″></A>
<strong><RDML MERGE=”ZZNAM”></strong></td>
<td align=”left” class=”arial1blackbold”> <strong> <RDML MERGE=”ZZACT”></strong></td>
<td align=”left” class=”arial1blackbold”><center> <strong><RDML MERGE=”ZZCNT”></strong></center></td>
<td align=”right” class=”arial1blackbold”> <strong><input type=checkbox name=”mChkBoxL<RDML MERGE=”LISTCOUNT”>” value=”” <RDML MERGE=ZZCHKNODE> onclick=”blueBallsChk(this,<RDML MERGE=”LISTCOUNT”>)”></strong></td>
<td align=”right” class=”arial1blackbold”> <strong>
<input type=”hidden” name=”__ZZCHKUSER-<RDML MERGE=”&ROWNUM” FORMAT=4> D” value=”<RDML MERGE=”ZZCHKUSER”>” size=”1″ >
<input type=checkbox name=”mChkBoxQ<RDML MERGE=”LISTCOUNT”>” value=”” <RDML MERGE=ZZCHKNODE> onclick=”SetCBState(this, ‘__ZZCHKUSER-<RDML MERGE=”&ROWNUM” FORMAT=4> D’, ‘Y’, ‘N’)”></strong></td>
</tr>
<!– Component END for B1ABSLST / MAM20080506 –>
CALL me for LANSA jobs
Mark
877 526 7220