Sql Help

[nenu_devudni]

code lo highlight chesina conditions toh patu ... ucracct_dqnt_code in ('RWIN','RLIF') kuda satisfy avali ayinapudu .. ee condtion b.UCBCUST_DOB < (SYSDATE - 21900) avasaram ledu ..  

 

DENI ELA RAYALO KASTA CHEPANDI   bye1

 

select
     ltrim(to_char(nvl(a.ucbsvco_code,'0'),'0999999999')),
a.UCBSVCO_WORK_ORDER_BU_CODE,        
            a.ucbsvco_sotp_code,
            to_char(nvl(a.ucbsvco_delinquent_amt,0), '999,999.90'),
            nvl(a.ucbsvco_delinquent_amt,0),
            ltrim(to_char(nvl(a.UCBSVCO_CUST_CODE,999),'999999999') || '-' || a.UCBSVCO_PREM_CODE),
            ltrim(rtrim(c.ucbprem_street_number))
             || rtrim( ' ' || ltrim(c.ucbprem_pdir_code_pre))
                 || rtrim( ' ' || ltrim(c.ucbprem_street_name))
                 || rtrim( ' ' || ltrim(c.ucbprem_pdir_code_post))
                 || rtrim( ' ' || ltrim(c.ucbprem_ssfx_code))
                 || rtrim( ' ' || ltrim(c.ucbprem_utyp_code))
                 || rtrim( ' ' || ltrim(c.ucbprem_unit))
from ucbsvco a, ucbcust b, ucbprem c, ucracct d
where a.ucbsvco_requested_date = trunc(to_date(:work_cutoff_date,'MM/DD/YY'))
    and a.UCBSVCO_SOTP_CODE in ('C11','C12','C13')
and a.UCBSVCO_STUS_CODE = 'O'
and a.UCBSVCO_WORK_ORDER_BU_CODE <> 'AIC '
and a.UCBSVCO_CUST_CODE = b.UCBCUST_CUST_CODE
and a.UCBSVCO_PREM_CODE = c.UCBPREM_CODE
and d.ucracct_cust_code = a.UCBSVCO_CUST_CODE
and d.ucracct_prem_code = a.UCBSVCO_PREM_CODE
and substr(d.ucracct_dqnt_code,1,1) = 'R'

and b.UCBCUST_DOB < (SYSDATE - 21900)
and not exists ( select 'Y' from uztsohb
where uztsohb_svco_code = ucbsvco_code
and uztsohb_record_status = 'E'
)
order by NVL(a.ucbsvco_delinquent_amt, 0) desc;

[micxas]

Help.

[nenu_devudni]

Help.

S%Hi

[innovative]

Where clause lo ee line kooda petti try cheyyi .. 

AND (ucracct_dqnt_code='RWIN' OR ucracct_dqnt_code='RLIF')

[nenu_devudni]

 

Where clause lo ee line kooda petti try cheyyi .. 

AND (ucracct_dqnt_code='RWIN' OR ucracct_dqnt_code='RLIF')

ee condition toh patu 

and substr(d.ucracct_dqnt_code,1,1) = 'R'

and b.UCBCUST_DOB < (SYSDATE - 21900)

 

this condition should be satisfied  // or condition  ... if records in 1st conditon and dob .. else below condition with no dob .. 

and d.ucracct_dqnt_code in ('RWIN','RLIF')

[CheGuevara]

Edi try chey

select
ltrim(to_char(nvl(a.ucbsvco_code,'0'),'0999999999')),
a.UCBSVCO_WORK_ORDER_BU_CODE,
a.ucbsvco_sotp_code,
to_char(nvl(a.ucbsvco_delinquent_amt,0), '999,999.90'),
nvl(a.ucbsvco_delinquent_amt,0),
ltrim(to_char(nvl(a.UCBSVCO_CUST_CODE,999),'999999999') || '-' || a.UCBSVCO_PREM_CODE),
ltrim(rtrim(c.ucbprem_street_number))
|| rtrim( ' ' || ltrim(c.ucbprem_pdir_code_pre))
|| rtrim( ' ' || ltrim(c.ucbprem_street_name))
|| rtrim( ' ' || ltrim(c.ucbprem_pdir_code_post))
|| rtrim( ' ' || ltrim(c.ucbprem_ssfx_code))
|| rtrim( ' ' || ltrim(c.ucbprem_utyp_code))
|| rtrim( ' ' || ltrim(c.ucbprem_unit))
from ucbsvco a, ucbcust b, ucbprem c, ucracct d
where a.ucbsvco_requested_date = trunc(to_date(:work_cutoff_date,'MM/DD/YY'))
and a.UCBSVCO_SOTP_CODE in ('C11','C12','C13')
and a.UCBSVCO_STUS_CODE = 'O'
and a.UCBSVCO_WORK_ORDER_BU_CODE <> 'AIC '
and a.UCBSVCO_CUST_CODE = b.UCBCUST_CUST_CODE
and a.UCBSVCO_PREM_CODE = c.UCBPREM_CODE
and d.ucracct_cust_code = a.UCBSVCO_CUST_CODE
and d.ucracct_prem_code = a.UCBSVCO_PREM_CODE
and ((substr(d.ucracct_dqnt_code,1,1) = 'R'
and b.UCBCUST_DOB < (SYSDATE - 21900)) or (ucracct_dqnt_code in ('RWIN','RLIF') and substr(d.ucracct_dqnt_code,1,1) = 'R'))
and not exists ( select 'Y' from uztsohb
where uztsohb_svco_code = ucbsvco_code
and uztsohb_record_status = 'E'
)
order by NVL(a.ucbsvco_delinquent_amt, 0) desc;

[nenu_devudni]

Edi try chey

select
ltrim(to_char(nvl(a.ucbsvco_code,'0'),'0999999999')),
a.UCBSVCO_WORK_ORDER_BU_CODE,
a.ucbsvco_sotp_code,
to_char(nvl(a.ucbsvco_delinquent_amt,0), '999,999.90'),
nvl(a.ucbsvco_delinquent_amt,0),
ltrim(to_char(nvl(a.UCBSVCO_CUST_CODE,999),'999999999') || '-' || a.UCBSVCO_PREM_CODE),
ltrim(rtrim(c.ucbprem_street_number))
|| rtrim( ' ' || ltrim(c.ucbprem_pdir_code_pre))
|| rtrim( ' ' || ltrim(c.ucbprem_street_name))
|| rtrim( ' ' || ltrim(c.ucbprem_pdir_code_post))
|| rtrim( ' ' || ltrim(c.ucbprem_ssfx_code))
|| rtrim( ' ' || ltrim(c.ucbprem_utyp_code))
|| rtrim( ' ' || ltrim(c.ucbprem_unit))
from ucbsvco a, ucbcust b, ucbprem c, ucracct d
where a.ucbsvco_requested_date = trunc(to_date(:work_cutoff_date,'MM/DD/YY'))
and a.UCBSVCO_SOTP_CODE in ('C11','C12','C13')
and a.UCBSVCO_STUS_CODE = 'O'
and a.UCBSVCO_WORK_ORDER_BU_CODE <> 'AIC '
and a.UCBSVCO_CUST_CODE = b.UCBCUST_CUST_CODE
and a.UCBSVCO_PREM_CODE = c.UCBPREM_CODE
and d.ucracct_cust_code = a.UCBSVCO_CUST_CODE
and d.ucracct_prem_code = a.UCBSVCO_PREM_CODE
and ((substr(d.ucracct_dqnt_code,1,1) = 'R'
and b.UCBCUST_DOB < (SYSDATE - 21900)) or (ucracct_dqnt_code in ('RWIN','RLIF') and substr(d.ucracct_dqnt_code,1,1) = 'R'))
and not exists ( select 'Y' from uztsohb
where uztsohb_svco_code = ucbsvco_code
and uztsohb_record_status = 'E'
)
order by NVL(a.ucbsvco_delinquent_amt, 0) desc;

 

records anni em fetch avtatledu  bye1