sql thathalu mamlu ravali

[karna11]

@loveindia

[nenu_devudni]

2 hours ago, 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')

ala tried vuncle ... still records are missing .. 

[nenu_devudni]

[nenu_devudni]

@loveindia @mettastar yada unar rajanna ... 

[comradee]

           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')

 

okasari above AND  ni comment chesi execute chey and see the difference.....

[nenu_devudni]

10 minutes ago, comradee said:

           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')

 

okasari above AND  ni comment chesi execute chey and see the difference.....

tried bro .. still it wont fetch all the records .. 

[comradee]

bro okasari ila try chestava  just give a try not sure it gives your desired result.

koncham tweak cheyali vastadi anukunta...

 

    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
                 ROW_NUMBER () OVER (PARTITION BY ucbcust_cust_code ORDER BY ucrtele_tele_code) as rnum
          FROM ucbcust, ucrtele
         WHERE ucbcust_cust_code = ucrtele_cust_code(+)
         ORDER BY DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ')
         ),

         WITH cte1 as
         (SELECT 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
          FROM  ucraddr a
         WHERE  a.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                                           FROM ucraddr b ,cte ct
                                   WHERE b.ucraddr_cust_code = ct.ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')
                                     ),
                                     
     WITH cte2 as
     (select ct.*,ct1.* from cte ct left outer join cte1 ct1 on ct.cust_code=ct1.cust_code)
     
     select * from cte2 WHERE rnum = 1;

 

 

ignore me if you find it as stupidity,i'm not a sql dev.java dev who is  trying learning sql.

[nenu_devudni]

13 minutes ago, comradee said:

bro okasari ila try chestava  just give a try not sure it gives your desired result.

koncham tweak cheyali vastadi anukunta...

 

    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
                 ROW_NUMBER () OVER (PARTITION BY ucbcust_cust_code ORDER BY ucrtele_tele_code) as rnum
          FROM ucbcust, ucrtele
         WHERE ucbcust_cust_code = ucrtele_cust_code(+)
         ORDER BY DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ')
         ),

         WITH cte1 as
         (SELECT 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
          FROM  ucraddr a
         WHERE  a.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                                           FROM ucraddr b ,cte ct
                                   WHERE b.ucraddr_cust_code = ct.ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')
                                     ),
                                     
     WITH cte2 as
     (select ct.*,ct1.* from cte ct left outer join cte1 ct1 on ct.cust_code=ct1.cust_code)
     
     select * from cte2 WHERE rnum = 1;

 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,
                 ROW_NUMBER () OVER (PARTITION BY ucbcust_cust_code ORDER BY ucrtele_tele_code) as rnum
          FROM ucbcust, ucrtele
         WHERE ucbcust_cust_code = ucrtele_cust_code(+)
         ORDER BY DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ')
         ),
         WITH cte1 as
         (SELECT 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
          FROM  ucraddr a
         WHERE  a.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                    FROM ucraddr b ,cte ct
                                   WHERE b.ucraddr_cust_code = ct.ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')
                                     ),                                     
     WITH cte2 as
     (select ct.*,ct1.* from cte ct left outer join cte1 ct1 on ct.cust_code=ct1.cust_code)     
     select * from cte2 WHERE rnum = 1;

-- with cte1 as condition degare it says .. invalid table name bro .. 

[comradee]

5 minutes ago, nenu_devudni said:

 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,
                 ROW_NUMBER () OVER (PARTITION BY ucbcust_cust_code ORDER BY ucrtele_tele_code) as rnum
          FROM ucbcust, ucrtele
         WHERE ucbcust_cust_code = ucrtele_cust_code(+)
         ORDER BY DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ')
         ),
         WITH cte1 as
         (SELECT 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
          FROM  ucraddr a
         WHERE  a.UCRADDR_SEQNO = (SELECT MIN(b.ucraddr_seqno) 
                                    FROM ucraddr b ,cte ct
                                   WHERE b.ucraddr_cust_code = ct.ucbcust_cust_code 
                                     AND b.ucraddr_atyp_code = 'MA'
                                     AND b.ucraddr_status_ind = 'A')
                                     ),                                     
     WITH cte2 as
     (select ct.*,ct1.* from cte ct left outer join cte1 ct1 on ct.cust_code=ct1.cust_code)     
     select * from cte2 WHERE rnum = 1;

-- with cte1 as degare it says .. invalid table name bro .. 

remove WITH  infront of cte1  and cte2

 

keep like this

cte1 as

cte2 as

[nenu_devudni]

10 minutes ago, comradee said:

remove WITH  infront of cte1  and cte2

 

keep like this

cte1 as

cte2 as

it bought a lot of duplicates bhayya ... i think its because of the last join       select ct.*,ct1.* from cte ct left outer join cte1 ct1 on ct.cust_code=ct1.cust_code)     

[comradee]

6 minutes ago, nenu_devudni said:

it bought a lot of duplicates bhayya ... i think its because of the last join       select ct.*,ct1.* from cte ct left outer join cte1 ct1 on ct.cust_code=ct1.cust_code)     

keep inner join and see.

[nenu_devudni]

18 minutes ago, comradee said:

keep inner join and see.

inner join pedithe records count got reduced bro ..   it still does not fetch correct count 

[4Vikram]

1 minute ago, nenu_devudni said:

inner join pedithe records count got reduced bro .. 

aindi aa pani kaledu aa inthaku?

[nenu_devudni]

4 minutes ago, 4Vikram said:

aindi aa pani kaledu aa inthaku?

nu inka padukole tatha ... kale helf sey radu .. 

[4Vikram]

41 minutes ago, nenu_devudni said:

nu inka padukole tatha ... kale helf sey radu .. 

naaku antha manchiga asthe bane unde,, nake oka samasya unadi nuvu chesta ante chepu chepta