[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
01/12/2008 at 01:22AM PST, ID: 23077691
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
7.0

Problem building imported Store procedure from the Development Center

Asked by Enuda in SQL Query Syntax, DB2 Database

Tags: IBM, DB2 UDBv8.2 Development Center, Store Procedure, SQL0104N An unexpected token "END-OF-STATEMENT" was found following "MATCH_CNT INTEGER". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=41. SQLSTATE=42601

Hi all,
I am very confused about this and need help debugging the attached store procedure script.  I am new with SP...as a matter of fact this is my first. I have read and followed the syntax rules as best as I know today, but I keep getting the following error:
 SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "MATCH_CNT    INTEGER".  Expected tokens may include:  "<psm_semicolon>". LINE NUMBER=41.  SQLSTATE=42601

I wrote the Storproc statements using notepad and from my DB2 UDB Control Center, I imported the saved file using the Development Center that walked me through how to do this -- from the lauchpad thru establishing a connection, importing the file, define a few other required parameters and tried to build. Nothing happened! I selected Build button many times and nada!! nothing!
So, I tried using the CC Command Editor panel although I did not think that would work and sure enough I am getting the error you see above.

My primary interest is to get this proc to work and I will work on learning more about the nuances of this whatchamacallit storproc business!!  First, can you tell me if the script has syntax errors? (I did pore through the book a couple of time and I did not see anything).

Second, do I have another way of "compiling" this proc outside of the Control Center GUI?

As always, I am mightily appreciative of all the wonderful assistance that I get from this forum.

Thanks
 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:
461:
462:
463:
464:
465:
466:
467:
468:
469:
470:
471:
472:
473:
474:
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
488:
489:
490:
491:
492:
493:
494:
495:
496:
497:
498:
499:
500:
501:
502:
503:
504:
505:
506:
507:
508:
509:
510:
511:
512:
513:
514:
515:
516:
517:
518:
519:
520:
521:
522:
523:
524:
525:
526:
527:
528:
529:
530:
531:
532:
533:
534:
535:
536:
537:
538:
539:
540:
541:
542:
543:
544:
545:
546:
547:
548:
549:
550:
551:
552:
553:
554:
555:
556:
557:
558:
559:
560:
561:
562:
563:
564:
565:
566:
567:
568:
569:
570:
571:
572:
573:
574:
575:
576:
577:
578:
579:
580:
581:
582:
583:
584:
585:
586:
587:
588:
589:
590:
591:
592:
593:
594:
595:
596:
597:
598:
599:
600:
601:
602:
603:
604:
605:
606:
607:
608:
609:
610:
611:
612:
613:
614:
615:
616:
617:
618:
619:
620:
621:
622:
623:
624:
625:
626:
627:
628:
629:
630:
631:
632:
633:
634:
635:
636:
637:
638:
639:
640:
641:
642:
643:
644:
645:
646:
647:
648:
649:
650:
651:
652:
653:
654:
655:
656:
657:
658:
659:
660:
661:
662:
663:
664:
665:
666:
667:
668:
669:
670:
671:
672:
673:
674:
675:
676:
677:
678:
679:
680:
681:
682:
683:
684:
685:
686:
687:
688:
689:
690:
691:
692:
693:
694:
695:
696:
697:
698:
699:
700:
701:
702:
703:
704:
705:
706:
707:
708:
709:
710:
711:
712:
713:
714:
715:
716:
717:
718:
719:
720:
721:
722:
723:
724:
725:
726:
727:
728:
729:
730:
731:
732:
733:
734:
735:
736:
737:
738:
739:
740:
741:
742:
743:
744:
745:
746:
747:
748:
749:
750:
751:
752:
753:
754:
755:
756:
757:
758:
759:
760:
761:
762:
763:
764:
765:
766:
767:
768:
769:
770:
771:
772:
773:
774:
775:
776:
777:
778:
779:
780:
781:
782:
783:
784:
785:
786:
787:
788:
789:
790:
791:
792:
793:
794:
795:
796:
797:
798:
799:
800:
801:
802:
803:
804:
805:
806:
807:
808:
809:
810:
811:
812:
813:
814:
815:
816:
817:
818:
819:
820:
821:
822:
823:
824:
825:
826:
827:
828:
829:
830:
831:
832:
833:
834:
835:
836:
837:
838:
839:
840:
841:
842:
843:
844:
845:
846:
847:
848:
849:
850:
851:
852:
853:
854:
855:
856:
857:
858:
859:
860:
861:
862:
863:
864:
865:
866:
867:
868:
869:
870:
871:
872:
873:
874:
875:
876:
877:
878:
879:
880:
881:
882:
883:
884:
885:
886:
887:
888:
889:
890:
891:
892:
893:
894:
895:
896:
897:
898:
899:
900:
901:
902:
903:
904:
905:
906:
907:
908:
909:
910:
911:
912:
913:
914:
915:
916:
917:
918:
919:
920:
921:
922:
923:
924:
925:
926:
927:
928:
929:
930:
931:
932:
933:
934:
935:
936:
937:
938:
939:
940:
941:
942:
943:
944:
945:
946:
947:
948:
949:
950:
951:
952:
953:
954:
955:
956:
957:
958:
959:
960:
961:
962:
963:
964:
965:
966:
967:
968:
969:
970:
971:
972:
973:
974:
975:
976:
977:
978:
979:
980:
981:
982:
983:
984:
985:
986:
987:
988:
989:
990:
991:
992:
993:
994:
995:
996:
997:
998:
999:
1000:
1001:
1002:
1003:
1004:
1005:
1006:
1007:
1008:

CREATE PROCEDURE DB2ADMIN.CFDHSRCH
(  IN vSNDX_LST1 	VARCHAR(20)
,  IN vSNDX_LST2 	VARCHAR(20)
,  IN vSNDX_LST3 	VARCHAR(20)
,  IN vSNDX_LST4 	VARCHAR(20)
,  IN vSNDX_LST5 	VARCHAR(20)
,  IN vSNDX_LST6	VARCHAR(20)
,  IN vSNDX_FRST1 	VARCHAR(20)
,  IN vSNDX_FRST2 	VARCHAR(20)
,  IN vSNDX_FRST3 	VARCHAR(20)
,  IN vSNDX_FRST4	VARCHAR(20)
,  IN vSNDX_MDL1 	VARCHAR(20)
,  IN vSNDX_MDL2 	VARCHAR(20)
,  IN vSNDX_MDL3 	VARCHAR(20)
,  IN vSNDX_MDL4	VARCHAR(20)
,  IN vNM_LST 		VARCHAR(80)
,  IN vNM_FRST 		VARCHAR(80)
,  IN vNM_MDL 		VARCHAR(80)
,  IN vFL_FRST		CHAR(1)
,  IN vFL_LST		CHAR(1)
,  IN vFL_MDL		CHAR(1)
,  IN vDOB		DATE
,  IN vMONTH		INTEGER
,  IN vYEAR		INTEGER
,  IN vID_SSN           VARCHAR(9)
,  IN vPRSN_TYPE	INTEGER
,  IN vPRECISION	INTEGER
,  IN vDOB_PRECISION	INTEGER
,  IN vPAGE_LEN		INTEGER
,  IN vPAGE_NUM		INTEGER
, OUT vTOTAL		INTEGER )
 
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL MODIFIES SQL DATA
 
P1: BEGIN
--VARIABLE DECLARATION
 
DECLARE pMATCH_CNT    INTEGER;
DECLARE pPG_STRT      INTEGER;
DECLARE pPG_END       INTEGER;
DECLARE vSQLCODE      INTEGER;
DECLARE vROW_NUM      INTEGER;
DECLARE vROW_CNT      INTEGER;
DECLARE vRANK_FNM     INTEGER;
DECLARE vRANK_LNM     INTEGER;
DECLARE vNM_FRST_0    INTEGER;
DECLARE vNM_MDL_0    INTEGER;
 
DECLARE GLOBAL TEMPORARY TABLE TBL_ID
 (
  ID_BIRTH	INTEGER NOT NULL
 ,ID_RANK	         INTEGER
 ,DOB_RANK	INTEGER
 );
 
DECLARE GLOBAL TEMPORARY TABLE TBL_RANK
 (ID_ROW   	INTEGER NOT NULL
		GENERATED ALWAYS AS IDENTITY
			(START WITH 1
			,INCREMENT BY 1
			,NO MAXVALUE
			,NO CYCLE
			,ORDER )
 ,ID_BIRTH	INTEGER NOT NULL );
 
P2: BEGIN
 
-- vPRSN_TYPE
--1 = CHILD
--2 = MOM
--3 = DAD
 
-- vPRECISION	
-- 1 = SOUNDEX, NICKNAMES
-- 2 = SOUNDEX, NO NICKNAMES
-- 3 = EXACT
-- 4 = LIKE
-- 5 = SSN
 
-- vDOB_PRECISION	
-- 0 = ANY
-- 1 = YEAR
-- 2 = YEAR/MONTH
-- 3 = YEAR/MONTH/DAY
 
DECLARE RTNCRSR CURSOR WITH RETURN TO CLIENT FOR
     SELECT B1.ID_BIRTH
	   ,B1.NM_FST
	   ,B1.NM_MDL
	   ,B1.NM_LST
	   ,B1.ID_SSN
	   ,B1.CD_BR_SEX AS CHILD_CD_SEX
	   ,B1.DT_DOB AS CHILD_DT_DOB
	   ,B1.TX_BR_FL_CNTY AS CHILD_TX_BR_FL_CNTY
	   ,CASE WHEN vPRSN_TYPE = 3 THEN B3.NM_FRST
	   ELSE B2.NM_FRST END AS PARENT_NM_FRST
	   ,CASE WHEN vPRSN_TYPE = 3 THEN B3.NM_MDL
	   ELSE B2.NM_MDL END AS PARENT_NM_MDL
	   ,CASE WHEN vPRSN_TYPE = 3 THEN B3.NM_LST
	   ELSE B2.NM_LST END AS PARENT_NM_LST
	   ,B2.NM_MAIDEN AS PARENT_NM_MAIDEN
	   ,CASE WHEN vPRSN_TYPE = 3 THEN B3.ID_SSN
	   ELSE B2.ID_SSN END AS PARENT_ID_SSN
	   ,CASE WHEN vPRSN_TYPE = 3 THEN B3.DT_DOB
	   ELSE B2.DT_DOB END AS PARENT_DT_DOB
FROM BR_DOH_BIRTH_RECORD B1
INNER JOIN SESSION.TBL_RANK T1
   ON T1.ID_BIRTH = B1.ID_BIRTH
  AND T1.ID_ROW BETWEEN pPG_STRT AND pPG_END
 LEFT OUTER JOIN BR_PERSON_MOTHER B2
   ON B2.ID_PERSON_MOTHER = B1.ID_PERSON_MOTHER
 LEFT OUTER JOIN BR_PERSON_FATHER B3
   ON B3.ID_PERSON_FATHER = B1.ID_PERSON_FATHER
ORDER BY T1.ID_ROW ;
 
 
SET pPG_STRT = CASE WHEN vPAGE_NUM < 2 THEN 1 ELSE vPAGE_NUM * vPAGE_LEN END;
 
SET pPG_END = pPG_STRT + vPAGE_LEN;
 
SET vNM_FRST_0 = COALESCE(LENGTH(RTRIM(vNM_FRST)),0);
SET vNM_MDL_0  = COALESCE(LENGTH(RTRIM(vNM_MDL)),0);
 
IF vNM_FRST_0 = 0 THEN
	SET vNM_FRST = '%';
END IF;
 
IF vNM_MDL_0 = 0 THEN
	SET vNM_MDL = '%';
END IF;
 
--IF vDOB_PRECISION = 0 THEN
--	SET vDOB	= NULL;
--	SET vMONTH	= NULL;
--	SET vYEAR	= NULL;
--END IF;
 
--BUILD INITIAL LIST OF ID'S
 
IF vPRSN_TYPE = 1 THEN
 
--SOUNDEX, NICKNAMES CHILD
	IF vPRECISION = 1 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B1.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0 = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B1
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		  LEFT OUTER JOIN MPHONE_FRST M2
		    ON M2.NM_UPR_FRST = B1.NM_UPR_FRST
		   AND M2.CD_MPHONE_FRST IN (vSNDX_FRST1, vSNDX_FRST2, vSNDX_FRST3, vSNDX_FRST4)
		  LEFT OUTER JOIN MPHONE_FRST M3
		    ON M3.NM_UPR_FRST = B1.NM_UPR_MDL
		   AND M3.CD_MPHONE_FRST IN (vSNDX_MDL1, vSNDX_MDL2, vSNDX_MDL3, vSNDX_MDL4)
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B1.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
 			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B1
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		 INNER JOIN NICKNAME N1 ON N1.NM_NICK = B1.NM_UPR_FRST AND N1.NM_NICK = vNM_FRST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B1.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B1
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		 INNER JOIN NICKNAME N1 ON N1.NM_FRST = B1.NM_UPR_FRST AND N1.NM_FRST = vNM_FRST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE ;
	END IF;
 
--SOUNDEX, NO NICKNAMES CHILD
	IF vPRECISION = 2 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B1.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B1
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		  LEFT OUTER JOIN MPHONE_FRST M2
		    ON M2.NM_UPR_FRST = B1.NM_UPR_FRST
		   AND M2.CD_MPHONE_FRST IN (vSNDX_FRST1, vSNDX_FRST2, vSNDX_FRST3, vSNDX_FRST4)
		  LEFT OUTER JOIN MPHONE_FRST M3
		    ON M3.NM_UPR_FRST = B1.NM_UPR_MDL
		   AND M3.CD_MPHONE_FRST IN (vSNDX_MDL1, vSNDX_MDL2, vSNDX_MDL3, vSNDX_MDL4)
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE ;
	END IF;
 
--LIKE CHILD
	IF vPRECISION = 4 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B1.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END  ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B1
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		 WHERE B1.NM_UPR_LST  LIKE vNM_LST
		   AND B1.NM_UPR_FRST LIKE vNM_FRST
		   AND B1.NM_UPR_MDL  LIKE vNM_MDL;
	END IF;
 
--EXACT CHILD
	IF vPRECISION = 3 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B1.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B1
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		WHERE B1.NM_UPR_LST = vNM_LST
		AND B1.NM_UPR_FRST LIKE vNM_FRST
		AND B1.NM_UPR_MDL LIKE vNM_MDL ;
	END IF;
 
--SSN CHILD
	IF vPRECISION = 5 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT ID_BIRTH
			,3
			,0
		  FROM BR_DOH_BIRTH_RECORD
		 WHERE ID_SSN = vID_SSN;
	END IF;
 
END IF ;
 
IF vPRSN_TYPE = 2 THEN
 
--SOUNDEX, NICKNAMES MOM
	IF vPRECISION = 1 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		  LEFT OUTER JOIN MPHONE_FRST M2
		    ON M2.NM_UPR_FRST = B1.NM_UPR_FRST
		   AND M2.CD_MPHONE_FRST IN (vSNDX_FRST1, vSNDX_FRST2, vSNDX_FRST3, vSNDX_FRST4)
		  LEFT OUTER JOIN MPHONE_FRST M3
		    ON M3.NM_UPR_FRST = B1.NM_UPR_MDL
		   AND M3.CD_MPHONE_FRST IN (vSNDX_MDL1, vSNDX_MDL2, vSNDX_MDL3, vSNDX_MDL4)
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST  = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		 INNER JOIN NICKNAME N1 ON N1.NM_NICK = B1.NM_UPR_FRST AND N1.NM_NICK = vNM_FRST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		 INNER JOIN NICKNAME N1 ON N1.NM_FRST = B1.NM_UPR_FRST AND N1.NM_FRST = vNM_FRST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_MAIDEN LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_MAIDEN = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_MAIDEN AND B1.NM_UPR_MAIDEN <> B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		  LEFT OUTER JOIN MPHONE_FRST M2
		    ON M2.NM_UPR_FRST = B1.NM_UPR_FRST
		   AND M2.CD_MPHONE_FRST IN (vSNDX_FRST1, vSNDX_FRST2, vSNDX_FRST3, vSNDX_FRST4)
		  LEFT OUTER JOIN MPHONE_FRST M3
		    ON M3.NM_UPR_FRST = B1.NM_UPR_MDL
		   AND M3.CD_MPHONE_FRST IN (vSNDX_MDL1, vSNDX_MDL2, vSNDX_MDL3, vSNDX_MDL4)
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_MAIDEN LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_MAIDEN = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST  = B1.NM_UPR_MAIDEN AND B1.NM_UPR_MAIDEN <> B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		 INNER JOIN NICKNAME N1 ON N1.NM_NICK = B1.NM_UPR_FRST AND N1.NM_NICK = vNM_FRST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_MAIDEN LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_MAIDEN = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_MAIDEN AND B1.NM_UPR_MAIDEN <> B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		 INNER JOIN NICKNAME N1 ON N1.NM_FRST = B1.NM_UPR_FRST AND N1.NM_FRST = vNM_FRST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE    ;
	END IF;
 
--SOUNDEX, NO NICKNAMES MOM
	IF vPRECISION = 2 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST  = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		  LEFT OUTER JOIN MPHONE_FRST M2
		    ON M2.NM_UPR_FRST = B1.NM_UPR_FRST
		   AND M2.CD_MPHONE_FRST IN (vSNDX_FRST1, vSNDX_FRST2, vSNDX_FRST3, vSNDX_FRST4)
		  LEFT OUTER JOIN MPHONE_FRST M3
		    ON M3.NM_UPR_FRST = B1.NM_UPR_MDL
		   AND M3.CD_MPHONE_FRST IN (vSNDX_MDL1, vSNDX_MDL2, vSNDX_MDL3, vSNDX_MDL4)
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
	UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_MAIDEN LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_MAIDEN = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER  AND B1.NM_UPR_MAIDEN <> B1.NM_UPR_LST
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST  = B1.NM_UPR_MAIDEN
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		  LEFT OUTER JOIN MPHONE_FRST M2
		    ON M2.NM_UPR_FRST = B1.NM_UPR_FRST
		   AND M2.CD_MPHONE_FRST IN (vSNDX_FRST1, vSNDX_FRST2, vSNDX_FRST3, vSNDX_FRST4)
		  LEFT OUTER JOIN MPHONE_FRST M3
		    ON M3.NM_UPR_FRST = B1.NM_UPR_MDL
		   AND M3.CD_MPHONE_FRST IN (vSNDX_MDL1, vSNDX_MDL2, vSNDX_MDL3, vSNDX_MDL4)
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE;
	END IF;
 
--LIKE MOM
	IF vPRECISION = 4 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		 WHERE B1.NM_UPR_LST  LIKE vNM_LST
		   AND B1.NM_UPR_FRST LIKE vNM_FRST
		   AND B1.NM_UPR_MDL  LIKE vNM_MDL
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_MAIDEN LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_MAIDEN = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER   AND B1.NM_UPR_MAIDEN <> B1.NM_UPR_LST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		 WHERE B1.NM_UPR_MAIDEN LIKE vNM_LST
		   AND B1.NM_UPR_FRST   LIKE vNM_FRST
		   AND B1.NM_UPR_MDL    LIKE vNM_MDL
		   ;
	END IF;
 
--EXACT MOM
	IF vPRECISION = 3 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		WHERE B1.NM_UPR_LST = vNM_LST
		AND B1.NM_UPR_FRST LIKE RTRIM(vNM_FRST)||'%'
		AND B1.NM_UPR_MDL LIKE RTRIM(vNM_MDL)||'%'
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_MAIDEN LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_MAIDEN = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER  AND B1.NM_UPR_MAIDEN <> B1.NM_UPR_LST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		WHERE B1.NM_UPR_MAIDEN = vNM_LST
		AND B1.NM_UPR_FRST LIKE RTRIM(vNM_FRST)||'%'
		AND B1.NM_UPR_MDL LIKE RTRIM(vNM_MDL)||'%'
	        ;
	END IF;
 
--SSN MOM
	IF vPRECISION = 5 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			,3
			,0
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_MOTHER B1
		    ON B1.ID_PERSON_MOTHER = B2.ID_PERSON_MOTHER
		 WHERE B2.ID_SSN = vID_SSN;
	END IF;
 
END IF ;
 
IF vPRSN_TYPE = 3 THEN
 
--SOUNDEX, NICKNAMES DAD
	IF vPRECISION = 1 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_FATHER B1
		    ON B1.ID_PERSON_FATHER = B2.ID_PERSON_FATHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		  LEFT OUTER JOIN MPHONE_FRST M2
		    ON M2.NM_UPR_FRST = B1.NM_UPR_FRST
		   AND M2.CD_MPHONE_FRST IN (vSNDX_FRST1, vSNDX_FRST2, vSNDX_FRST3, vSNDX_FRST4)
		  LEFT OUTER JOIN MPHONE_FRST M3
		    ON M3.NM_UPR_FRST = B1.NM_UPR_MDL
		   AND M3.CD_MPHONE_FRST IN (vSNDX_MDL1, vSNDX_MDL2, vSNDX_MDL3, vSNDX_MDL4)
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_FATHER B1
		    ON B1.ID_PERSON_FATHER = B2.ID_PERSON_FATHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		 INNER JOIN NICKNAME N1 ON N1.NM_NICK = B1.NM_UPR_FRST AND N1.NM_NICK = vNM_FRST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		UNION
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_FATHER B1
		    ON B1.ID_PERSON_FATHER = B2.ID_PERSON_FATHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		 INNER JOIN NICKNAME N1 ON N1.NM_FRST = B1.NM_UPR_FRST AND N1.NM_FRST = vNM_FRST
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE ;
	END IF;
 
--SOUNDEX, NO NICKNAMES DAD
	IF vPRECISION = 2 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_FATHER B1
		    ON B1.ID_PERSON_FATHER = B2.ID_PERSON_FATHER
		 INNER JOIN MPHONE_LST M1
		    ON M1.NM_UPR_LST = B1.NM_UPR_LST
		   AND M1.CD_MPHONE_LST IN (vSNDX_LST1, vSNDX_LST2, vSNDX_LST3, vSNDX_LST4, vSNDX_LST5, vSNDX_LST6)
		  LEFT OUTER JOIN MPHONE_FRST M2
		    ON M2.NM_UPR_FRST = B1.NM_UPR_FRST
		   AND M2.CD_MPHONE_FRST IN (vSNDX_FRST1, vSNDX_FRST2, vSNDX_FRST3, vSNDX_FRST4)
		  LEFT OUTER JOIN MPHONE_FRST M3
		    ON M3.NM_UPR_FRST = B1.NM_UPR_MDL
		   AND M3.CD_MPHONE_FRST IN (vSNDX_MDL1, vSNDX_MDL2, vSNDX_MDL3, vSNDX_MDL4)
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE ;
	END IF;
 
--LIKE DAD
	IF vPRECISION = 4 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END  ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_FATHER B1
		    ON B1.ID_PERSON_FATHER = B2.ID_PERSON_FATHER
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		 WHERE B1.NM_UPR_LST  LIKE vNM_LST
		   AND B1.NM_UPR_FRST LIKE vNM_FRST
		   AND B1.NM_UPR_MDL  LIKE vNM_MDL ;
	END IF;
 
--EXACT DAD
	IF vPRECISION = 3 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			, ( CASE WHEN vFL_FRST = 'Y' AND B1.NM_UPR_FRST LIKE vNM_FRST THEN + 1
				 WHEN vNM_FRST_0  = 0 THEN +1
				 WHEN B1.NM_UPR_FRST = vNM_FRST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_LST = 'Y' AND B1.NM_UPR_LST LIKE vNM_LST THEN + 1
				 WHEN B1.NM_UPR_LST = vNM_LST THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vFL_MDL = 'Y' AND B1.NM_UPR_MDL LIKE vNM_MDL THEN + 1
				 WHEN vNM_MDL_0 = 0 THEN +1
				 WHEN B1.NM_UPR_MDL = vNM_MDL THEN + 1
			    ELSE 0 END
			    +
			    CASE WHEN vPRECISION < 3 THEN +3 ELSE 0 END   ) AS ID_RANK
			, ( CASE WHEN D1.CALENDARDATE = vDOB THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARMONTH = vMONTH THEN + 1 ELSE 0 END
			    +
			    CASE WHEN D1.CALENDARYEAR = vYEAR THEN + 1 ELSE 0 END ) AS DOB_RANK
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_FATHER B1
		    ON B1.ID_PERSON_FATHER = B2.ID_PERSON_FATHER
		LEFT OUTER JOIN DATE_DIM D1 ON B1.ID_DOB = D1.ID_DATE
		WHERE B1.NM_UPR_LST = vNM_LST
		AND B1.NM_UPR_FRST LIKE RTRIM(vNM_FRST)||'%'
		AND B1.NM_UPR_MDL LIKE RTRIM(vNM_MDL)||'%' ;
	END IF;
 
--SSN DAD
	IF vPRECISION = 5 THEN
		INSERT INTO SESSION.TBL_ID
		SELECT B2.ID_BIRTH
			,3
			,0
		  FROM BR_DOH_BIRTH_RECORD B2
		 INNER JOIN BR_PERSON_FATHER B1
		    ON B1.ID_PERSON_FATHER = B2.ID_PERSON_FATHER
		 WHERE B2.ID_SSN = vID_SSN;
	END IF;
 
END IF ;
 
INSERT INTO SESSION.TBL_RANK
(ID_BIRTH)
SELECT ID_BIRTH	
  FROM SESSION.TBL_ID
 WHERE (DOB_RANK = vDOB_PRECISION OR vDOB_PRECISION = 0) 	
   AND ID_RANK = 6
UNION
SELECT ID_BIRTH	
  FROM SESSION.TBL_ID
 WHERE (DOB_RANK = vDOB_PRECISION OR vDOB_PRECISION = 0)	
   AND ID_RANK = 5
UNION
SELECT ID_BIRTH	
  FROM SESSION.TBL_ID
 WHERE (DOB_RANK = vDOB_PRECISION OR vDOB_PRECISION = 0)	
   AND ID_RANK = 4
UNION
SELECT ID_BIRTH	
  FROM SESSION.TBL_ID
 WHERE (DOB_RANK = vDOB_PRECISION OR vDOB_PRECISION = 0)	
   AND ID_RANK = 3  ;
 
SELECT COALESCE(MAX(ID_ROW),0) INTO vTOTAL FROM SESSION.TBL_RANK ;
 
 
--RETURN
OPEN RTNCRSR ;
 
RETURN;
 
END P2 ;
END P1
[+][-]01/12/08 10:39 AM, ID: 20644829

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Query Syntax, DB2 Database
Tags: IBM, DB2 UDBv8.2 Development Center, Store Procedure, SQL0104N An unexpected token &quot;END-OF-STATEMENT&quot; was found following &quot;MATCH_CNT INTEGER&quot;. Expected tokens may include: &quot;&lt;psm_semicolon&gt;&quot;. LINE NUMBER=41. SQLSTATE=42601
Sign Up Now!
Solution Provided By: TextReport
Participating Experts: 3
Solution Grade: B
 
 
[+][-]01/12/08 12:11 PM, ID: 20645102

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01/13/08 07:21 AM, ID: 20647865

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 30-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]01/13/08 07:39 AM, ID: 20647921

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-91 / EE_QW_2_20070628