Home » SQL & PL/SQL » SQL & PL/SQL » records in loop (oracle 10g)
records in loop [message #398938] Sun, 19 April 2009 22:13 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
i have a loop someting like below

in a code below where it is mentioned
IF P_OUTPUT IN ('CM', 'EM', 'OM')

will it fetch only one record for the value
rwwe_CONTklT='1234'

AND SAY it doesnt find the related information will it not go to the next record in the loop where
rwwe_CONTklT='7877' for the related info??



FOR I IN 1 .. viewtable.COUNT LOOP
        IF NVL(viewtable(I).fogval, 'NA') <> 'NA' THEN
          IF mn_LIST_TYPE = 'U' THEN
            mn_RECORDEXIST := 'FALSE';
            IF mn_base_TYPE = 'C' THEN
              mn_newSQL               := 'SELECT kl_contklt_val, kl_title, kl_forename, kl_surname, kl_full_name, kl_new, assk_new_flg';
              mn_newSQL               := mn_newSQL ||
                                         ' FROM rwwe_contklt, rwwe_tiil_deer_new ';
              mn_newSQL               := mn_newSQL ||
                                         ' WHERE kl_customer_group_val = ' || '''' ||
                                         viewtable(I).fogval || '''';
              mn_newSQL               := mn_newSQL ||
                                         ' AND assk_conk = kl_contklt_val ';
              mn_newSQL               := mn_newSQL ||
                                         ' AND NVL(kl_deleted_flg, ''A'') = ''A''';
              mn_newSQL               := mn_newSQL ||
                                         ' AND NVL(assk_kltmgr_flg, ''N'') = ''Y''';
              mn_newSQL               := mn_newSQL ||
                                         ' AND kl_new IS NOT NULL ';
              mn_newSQL               := mn_newSQL ||
                                         ' AND assk_new_flg IN (' ||
                                         mn_new_FLAGS || ') ';
              mn_BEST_CONTklT_FOUND   := 'FALSE';
              mn_CONTklT_WITH_DISSENT := 'FALSE';
              OPEN LR_val_RECORD FOR mn_newSQL;
              LOOP
                FETCH LR_val_RECORD
                  INTO mn_kl_CONTklT_val, mn_kl_TITLE, mn_kl_FORENAME, mn_kl_SURNAME, mn_kl_FULL_NAME, mn_kl_new, mn_ASSK_new_FLG;
                EXIT WHEN LR_val_RECORD%NOTFOUND;
                mn_RECORDEXIST := 'TRUE';
                BEGIN
                  IF mn_BEST_CONTklT_FOUND = 'FALSE' THEN
                    IF P_OUTPUT IN ('CM', 'EM', 'OM') THEN
                      BEGIN
                        SELECT klTC_call_NUMBER,
                               klTC_ddl_CONSENT_VALUE,
                               klTC_Cddl_CONSENT_VALUE,
                               NVL(klTC_DNC_CONSENT_VALUE, 'C')
                          INTO mn_call_NUMBER,
                               mn_ddl_CONSENT_VALUE,
                               mn_Cddl_CONSENT_VALUE,
                               mn_call_CONSENT
                          FROM rwwe_CONTklT_call_CONSENT, rwwe_CONTklT
                         WHERE klTC_CONTklT_val = mn_kl_CONTklT_val
                           AND kl_CONTklT_val = klTC_CONTklT_val
                           AND NVL(klTC_DELETED_FLG, 'N') = 'N'
                           AND NVL(klTC_PRIMARY_FLG, 'N') = 'Y'
                           AND NVL(klTC_DNC_equals_VALUE, 'C') = 'C'
                           AND ((NVL(kl_FULL_NAME, ' ') <> ' ') OR
                               (LENGTH(NVL(klTC_call_NUMBER, ' ')) > 8))
                           AND kl_new IS NOT NULL 
                           AND ROWNUM = 1
                        ;
                        IF INSTR(mn_SUPP, '1', 1) <> 0 THEN
                         
                          IF (NVL(mn_ddl_equals_VALUE, 'C') = 'C' AND
                             NVL(mn_Cddl_equals_VALUE, 'C') = 'C') THEN
                            mn_ddl_equals := 'C';
                          ELSE
                            mn_ddl_equals := 'D';
                          END IF;
                        ELSE
                          mn_ddl_equals := 'N';
                        END IF;
                      EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                          mn_call_equals  := 'D';
                          mn_ddl_equals        := 'N'; 
                          mn_call_NUMBER   := NULL;
                          mn_ddl_equals_VALUE  := NULL;
                          mn_Cddl_equals_VALUE := NULL;
                      END;
Re: records in loop [message #398939 is a reply to message #398938] Sun, 19 April 2009 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>will it fetch only one record for the value
>rwwe_CONTklT='1234'
It appears "rwwe_CONTklT" is a table name.
How can a table name equal some string value?


Re: records in loop [message #398940 is a reply to message #398939] Sun, 19 April 2009 22:28 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
WHERE klTC_CONTklT_val = mn_kl_CONTklT_val
AND kl_CONTklT_val = klTC_CONTklT_val


alologise for the typo

the above one is what i was refering to if klTC_CONTklT_val='1234'

Re: records in loop [message #398942 is a reply to message #398938] Sun, 19 April 2009 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
YoU nEeD tO fIx The prOBlem wiTH Caps LOck.
Re: records in loop [message #398943 is a reply to message #398938] Sun, 19 April 2009 22:34 Go to previous message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Previous Topic: char vs length()
Next Topic: substring up to 5 characters after removing special characters
Goto Forum:
  


Current Time: Wed Dec 07 18:29:49 CST 2016

Total time taken to generate the page: 0.11005 seconds