sql babulu ravali

[nenu_devudni]

customer ki, telephone info ki 2 tables unay ... oka cust ki  multiple phone numb undochu .. so customer table lo oka row ki telephone table lo 2 rows undochu .. so ipudu nenu aa rendu tables ni join chesi telephone table meda outer join peta ..

select cust_name, etc, etc, phone_num, ext from customer, telephone_tab where customer.col1 = telephone.col1 (+)

ee info ni util dwara oka dat file loki extract chestuna ... prob endi ante .. outer join vala tele table lo una multiple vlaues kuda ostunay .. so edo oka value ee oche laga query rayali .. edi etla sadhyam .. kasta chepapdni .. podunundi kotuku chastuna  

[nenu_devudni]

 ravali ravali 

[loveindia]

Oracle / SQL Server aa man??..

edaina kuda you can use row_number() and get only one value... CTE use chesi, select from cte where rnum = 1 ani pettu only one record per customer vastundi... 

meanwhile you are not doing an outer join in this query only inner join... just fyi man...

[loveindia]

1 minute ago, nenu_devudni said:

 ravali ravali 

[mtkr]

partition and select

[mtkr]

10 minutes ago, loveindia said:

Oracle / SQL Server aa man??..

edaina kuda you can use row_number() and get only one value... CTE use chesi, select from cte where rnum = 1 ani pettu only one record per customer vastundi... 

meanwhile you are not doing an outer join in this query only inner join... just fyi man...

[nenu_devudni]

16 minutes ago, loveindia said:

Oracle / SQL Server aa man??..

edaina kuda you can use row_number() and get only one value... CTE use chesi, select from cte where rnum = 1 ani pettu only one record per customer vastundi... 

meanwhile you are not doing an outer join in this query only inner join... just fyi man...

Oracle uncle 

[chelsea]

you can use rownumber or  select  select cust_name, etc, etc, phone_num, ext from customer Outer apply( select top 1 telephone from  telephone_tab where customer.col1 = telephone.col1   order by insertdate most recent kavali ante desc )

[nenu_devudni]

edi query vuncle ..  telephone table lo multiple rows unte .. DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ') based on this i have prioritize and select just one row ...

 

 SELECT

result # 

cust_code lastname   area  number        ext        telecode 

1    CUST NAME1        901    3888515    NULL    BU    
1    CUST NAME1        901    4522682    NULL    BU
1    CUST NAME1        901    3215657    NULL   HM
1    CUST NAME1        901    3563942    NULL    CEL
1    CUST NAME1        901    3617623    NULL   CEL

 

[nenu_devudni]

[loveindia]

ok my bad man... nuvvu (+) ani text editor anukunna... so its an actual left outer join...

[samajaaragamana]

44 minutes ago, loveindia said:

Oracle / SQL Server aa man??..

edaina kuda you can use row_number() and get only one value... CTE use chesi, select from cte where rnum = 1 ani pettu only one record per customer vastundi... 

meanwhile you are not doing an outer join in this query only inner join... just fyi man...

[loveindia]

aa select statement ki aa result ki relation ekkada undi man? Date field edanna undaa aa query lo... intaki aa 5 records which is the one you want to see man?

[loveindia]

;WITH CTE AS(
SELECT ucbcust_cust_code cust_code, 
           REPLACE(ucbcust_last_name,'|','') last_name,           
           upper(ucrtele_phone_area), 
           upper(ucrtele_phone_number),
           upper(ucrtele_phone_ext), 
           upper(ucrtele_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 (+)
      and ucbcust_cust_code = 1
    ORDER BY DECODE (ucrtele_tele_code, 'BU', 'AA', 'HM', 'AB', 'ZZ')
)
SELECT *
FROM CTE 
WHER Rnum = 1

 

something like this will help you man...

[nenu_devudni]

4 minutes ago, loveindia said:

ok my bad man... nuvvu (+) ani text editor anukunna... so its an actual left outer join...

  

MAAFF KARO vuncle .. ipudu dheko .. result 

cust_code lastname   area  number        ext        telecode 

1    CUST NAME1        901    3888515    NULL    BU    
1    CUST NAME1        901    4522682    NULL    BU
1    CUST NAME1        901    3215657    NULL   HM
1    CUST NAME1        901    3563942    NULL    CEL
1    CUST NAME1        901    3617623    NULL   CEL