sql thathalu mamlu ravali

[nenu_devudni]

ee with clause lo una query lo highlight chesina col's add chesa ... and where clause lo add chesina new table vala records join vala thakuva ostunay ..  joins try chesina work avatledu .. deni ela fix cheyalo teliste cheppandi vaya 

 

WITH cte AS
     (SELECT   ucbcust_cust_code cust_code, 
           REPLACE(ucbcust_last_name,'|','') last_name,
           REPLACE(ucbcust_status_ind,'|','') status_ind,
           ucbcust_start_date start_date,
           ucbcust_activity_date activity_date,
           REPLACE(ucbcust_first_name,'|','') first_name, 
           REPLACE(ucbcust_middle_name,'|','') middle_name,
           REPLACE(ucbcust_toto_ind,'|','') toto_ind,
           REPLACE(ucbcust_builder_type,'|','') builder_type,
           REPLACE(ucbcust_pay_by_check_ind,'|','') check_ind,
           ucbcust_end_date end_date,
           REPLACE(ucbcust_email_address,'|','') email_addr,
           REPLACE(ucbcust_primary_prem_code,'|','') pr_prem_code,
           UPPER (ucrtele_phone_area) tele_area, 
           UPPER (ucrtele_phone_number) tele_num,
           UPPER (ucrtele_phone_ext) tele_ext, 
           UPPER (ucrtele_tele_code) tele_code,
           UCRADDR_STREET_NUMBER, 
           UCRADDR_PDIR_CODE_PRE, 
           UCRADDR_STREET_NAME, 
           UCRADDR_SSFX_CODE, 
           UCRADDR_PDIR_CODE_POST, 
           UCRADDR_UTYP_CODE, 
           UCRADDR_UNIT, 
           UCRADDR_CITY, 
           UCRADDR_STAT_CODE, 
           UCRADDR_ZIP, 
           UCRADDR_SEQNO,
               ROW_NUMBER () OVER (PARTITION BY ucbcust_cust_code ORDER BY ucrtele_tele_code) as rnum
          FROM ucbcust, ucrtele, ucraddr a
         WHERE ucbcust_cust_code = ucrtele_cust_code(+)
           AND ucbcust_cust_code(+) = ucraddr_cust_code
           AND a.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                                           FROM ucraddr b 
                                   WHERE b.ucraddr_cust_code = ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')
      ORDER BY DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ'))
    SELECT *
     FROM cte
    WHERE rnum = 1

[nenu_devudni]

[nenu_devudni]

ravali ravali  

[JollyBoy]

(+) idendi never seen before

[Bhai]

 

[Spartan]

@loveindia

[nenu_devudni]

1 minute ago, JollyBoy said:

(+) idendi never seen before

outer join vuncle ... implicit joins antaru vetni .. left right outer ani rase badulu ela use cheyochu 

[Bhai]

Just now, nenu_devudni said:

outer join vuncle ... implicit joins antaru vetni .. left right outer ani rase badulu ela use cheyochu 

k

[nenu_devudni]

@loveindia thatha ekada una ravali 

[Bhai]

7 minutes ago, nenu_devudni said:

@loveindia thatha ekada una ravali 

[nenu_devudni]

[Doola]

.

[former]

1 hour ago, nenu_devudni said:

ee with clause lo una query lo highlight chesina col's add chesa ... and where clause lo add chesina new table vala records join vala thakuva ostunay ..  joins try chesina work avatledu .. deni ela fix cheyalo teliste cheppandi vaya 

 

WITH cte AS
     (SELECT   ucbcust_cust_code cust_code, 
           REPLACE(ucbcust_last_name,'|','') last_name,
           REPLACE(ucbcust_status_ind,'|','') status_ind,
           ucbcust_start_date start_date,
           ucbcust_activity_date activity_date,
           REPLACE(ucbcust_first_name,'|','') first_name, 
           REPLACE(ucbcust_middle_name,'|','') middle_name,
           REPLACE(ucbcust_toto_ind,'|','') toto_ind,
           REPLACE(ucbcust_builder_type,'|','') builder_type,
           REPLACE(ucbcust_pay_by_check_ind,'|','') check_ind,
           ucbcust_end_date end_date,
           REPLACE(ucbcust_email_address,'|','') email_addr,
           REPLACE(ucbcust_primary_prem_code,'|','') pr_prem_code,
           UPPER (ucrtele_phone_area) tele_area, 
           UPPER (ucrtele_phone_number) tele_num,
           UPPER (ucrtele_phone_ext) tele_ext, 
           UPPER (ucrtele_tele_code) tele_code,
           UCRADDR_STREET_NUMBER, 
           UCRADDR_PDIR_CODE_PRE, 
           UCRADDR_STREET_NAME, 
           UCRADDR_SSFX_CODE, 
           UCRADDR_PDIR_CODE_POST, 
           UCRADDR_UTYP_CODE, 
           UCRADDR_UNIT, 
           UCRADDR_CITY, 
           UCRADDR_STAT_CODE, 
           UCRADDR_ZIP, 
           UCRADDR_SEQNO,
               ROW_NUMBER () OVER (PARTITION BY ucbcust_cust_code ORDER BY ucrtele_tele_code) as rnum
          FROM ucbcust, ucrtele, ucraddr a
         WHERE ucbcust_cust_code = ucrtele_cust_code(+)
           AND ucbcust_cust_code(+) = ucraddr_cust_code
           AND a.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                                           FROM ucraddr b 
                                   WHERE b.ucraddr_cust_code = ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')
      ORDER BY DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ'))
    SELECT *
     FROM cte
    WHERE rnum = 1

18

 

May be below condition valla vasthundi emo issue:    
   AND a.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                                           FROM ucraddr b 
                                   WHERE b.ucraddr_cust_code = ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')

[Doola]

mamulu from and join condition lo rayachu kada like....

 FROM ucbcust a

left outer join  ucrtele b on a.ucbcust_cust_code = b.ucrtele_cust_code

left outer join ucraddr c on a.ucbcust_cust_code = c.ucraddr_cust_code and  c.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                                           FROM ucraddr b 
                                   WHERE b.ucraddr_cust_code = ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')

[Bhai]

Just now, Doola said:

mamulu from and join condition lo rayachu kada like....

 FROM ucbcust a

left outer join  ucrtele b on a.ucbcust_cust_code = b.ucrtele_cust_code

left outer join ucraddr c on a.ucbcust_cust_code = c.ucraddr_cust_code and  c.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                                           FROM ucraddr b 
                                   WHERE b.ucraddr_cust_code = ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')

oh... ok