Forum Stats

  • 3,781,431 Users
  • 2,254,519 Discussions
  • 7,879,691 Comments

Discussions

connect by clause

hemant_k
hemant_k Member Posts: 403 Bronze Badge

hi

I am trying as below

expecting 30 records however getting only one record

the query is

with dts as (
select to_date('01-apr-2021','dd-mon-yyyy') start_date,
to_date('01-apr-2021','dd-mon-yyyy')+level-1 hdate from dual
connect by level<=to_date('30-apr-2021','dd-mon-yyyy')-to_date('01-apr-2021','dd-mon-yyyy')+1
)
select * from (
select b.hdate,e.lbrcode,e.prdacctid,e.CblDate,e.Balance4,rank() over (partition by e.LBrCode,e.PrdAcctId order by e.CblDate desc) rnk
from cbl e ,dts b
where a.cbldate<=b.hdate and e.lbrcode=7 and e.prdacctid='123     000000000000022200000000'
) where rnk=1


Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    edited Oct 23, 2021 9:49PM Accepted Answer

    Problem is all transactions in your data sample have same balance amount - not a good sample. And logic is still not clear. What identifies customer and what identifies transaction? It looks like LBrCode is customer, PrdAcctId is customer account and CblDate is transaction date. If so, why you expect 30 rows and not 60 (30 rows for each account) or 30 rows with total amount on customer both accounts?

    SY.

«13

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    edited Oct 22, 2021 10:25AM

    We don't know your data. All I can tell your query will return as many rows as there are distinct combinations of e.LBrCode, e.PrdAcctId with e.CblDate <= Apr 30 2021. If you want 30 rows per e.LBrCode, e.PrdAcctId combination you need to use outer join. So please provide data sample, desired results and explain logic to get the results.

    SY.

    hemant_k
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,381 Red Diamond
    edited Oct 22, 2021 10:41AM

    Hi, @hemant_k

    expecting 30 records however getting only one record
    

    Do you want one row in the result set for every row in dts? If so, maybe you need an outer join. As Solomon said, post a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that sample data. To make it easier to post, you might want to change the sub-query dts to produce fewer rows, e.g. April 1 through 7.

    hemant_k
  • hemant_k
    hemant_k Member Posts: 403 Bronze Badge

    sir

    actually the same query when put in Apex it gives me desired result..

    how do I use outer join here ?

    please explain

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond

    First you need to post a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that sample data along with logic to get the results.

    SY.

    Frank Kulashhemant_k
  • hemant_k
    hemant_k Member Posts: 403 Bronze Badge

    here is a sample data

    it runs fine in apex but when I try to run the same on client's server

    i get only one record for each account

    with sampletbl as (
    select 1 as lbrcode,'SBDD    000000000000001900000000' as prdacctid,'17-apr-2019' as cbldate,20000 as balance4 from dual
    union all
    select 1 as lbrcode,'SBDD    0000000000000019100000000' as prdacctid,'17-apr-2019' as cbldate,20000 as balance4 from dual
    union all
    select 1 as lbrcode,'SBDD    000000000000001900000000' as prdacctid,'30-apr-2019' as cbldate,20000 as balance4 from dual
    )
    ,
     dts as (
    select to_date('01-apr-2021','dd-mon-yyyy') start_date,
    to_date('01-apr-2021','dd-mon-yyyy')+level-1 hdate from dual
    connect by level<=to_date('30-apr-2021','dd-mon-yyyy')-to_date('01-apr-2021','dd-mon-yyyy')+1
    )
    select * from (
    select a.*,b.*,rank() over (partition by a.lbrcode,a.prdacctid order by cbldate desc) rnk 
    from sampletbl a , dts b 
    where a.cbldate<=b.hdate
    ) where rnk=1
    order by hdate
    
    
    


  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy

    Do not compare a different datatypes.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,009 Red Diamond

    You didn't post expected results and logic to get them.

    SY.

  • hemant_k
    hemant_k Member Posts: 403 Bronze Badge

    oh sorry sir

    expected result is as below

    "LBRCODE","PRDACCTID","HDATE","BALANCE4","RNK"
    "1","SBDD    000000000000001910000000","01-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","02-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","03-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","04-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","05-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","06-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","07-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","08-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","09-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","10-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","11-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","12-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","13-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","14-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","15-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","16-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","17-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","18-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","19-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","20-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","21-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","22-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","23-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","24-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","25-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","26-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","27-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","28-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","29-APR-2021","20000","1"
    "1","SBDD    000000000000001910000000","30-APR-2021","20000","1"