Not able to select distinct record 1

[pnad]

Hi,

I have this query that displays the detail records first and then the summary records:

select
'10' || '050' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8)||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11)||
rpad(' ',15) ||
decode(a.check_recon_status, 'V', 'V',' ')||
rpad(' ',80) as col1
from table a, table b
where a.tran_id = b.tran_id
and a.check_recon_date = sysdate
Union
select '20' || '050' || '1234512345' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 107) as col2
from table a, table b
where a.tran_id = b.tran_id
and a.check_recon_date = sysdate
group by a.check_date
Union
select '30' || '050' || '9999999999' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 107) as col3
from table a, table b
where a.tran_id = b.tran_id
and a.check_recon_date = sysdate
group by a.check_date;

Now, when there is a check that has a check date which is different from the check recon date then the grouping fails and it shows multiple summary records at the end..for example ..we had a scenario where the check recon date is different from the check date and it showed data as follows:

10048748079320200000106052008011800000007988 V
10048748079320200000108632008040900000002500
10048748079320200000108642008040900000065942
10048748079320200000108652008040900000005500
10048748079320200000108662008040900000125000
10048748079320200000108672008040900000004452
20048748079320200000000012008011800000007988
20048748079320200000000052008040900000203394
30048999999999900000000012008011800000007988
30048999999999900000000052008040900000203394


instead of

10050123451234500000106052008011800000007988 V
10050123451234500000108632008040900000002500
10050123451234500000108642008040900000065942
10050123451234500000108652008040900000005500
10050123451234500000108662008040900000125000
10050123451234500000108672008040900000004452
20050123451234500000000052008040900000211382
30050999999999900000000052008040900000211382


How do I make sure that it groups properly. Please advise.

Thanks.

 

[Dagon]

I'm unclear from your data what you are trying to achieve. In any case, the data looks to be incorrect because the first lot shows 10048, which can't possibly be coming from the query. Can you show the data consistently and also highlight the bits that are incorrect.

I'm also very surprised the query works when you have a condition like check_recon_date = sysdate. Since sysdate includes a time component, your check_recon_date would have to be exact to the second for this to work.

 

[ChrisHunt]

If you want your summary lines to show the check recon date instead of the check date, you're going to have to write that into the query instead of hoping that the database reads your mind somehow:

...
select '20' || '050' || '1234512345' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_recon_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 107) as col2
from table a, table b
where a.tran_id = b.tran_id
and a.check_recon_date = TRUNC(sysdate)
group by a.check_recon_date
...

Note that I've TRUNCated the sysdate to avoid the time component problem Dagon refers to.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[pnad]

Sorry, I didnt realize that the data was all muddled.

This is what we are supposed to get:

10050123451234500000106052008011800000007988 V
10050123451234500000108632008040900000002500
10050123451234500000108642008040900000065942
10050123451234500000108652008040900000005500
10050123451234500000108662008040900000125000
10050123451234500000108672008040900000004452
20050123451234500000000052008040900000211382
30050999999999900000000052008040900000211382

and this is what we get:

10050123451234500000106052008011800000007988 V
10050123451234500000108632008040900000002500
10050123451234500000108642008040900000065942
10050123451234500000108652008040900000005500
10050123451234500000108662008040900000125000
10050123451234500000108672008040900000004452
20050123451234500000000012008011800000007988
20050123451234500000000052008040900000203394
30050999999999900000000012008011800000007988
30050999999999900000000052008040900000203394

Now the check recon date (date when check is reconciled as void or cleared) can be different from the check date (which is the date that the check is issued) . When the check is issued then it is in outstanding status and when it is reconciled then it is given a cleared or void status. The recon date can be anywhere from one day to one week..and so the requirement is that the output should have the check date but the users want to know only the check dates for the checks that were reconciled today. I hope I am clear enough?

So whats happening right now is that when I group on check date, the summary records show 2 different records because the check date is different from the check recon date.

 

[Dagon]

It looks as if you just need to group by check_recon_date and select the max(check_date). But I'm still a bit unclear on your requirements. Why do you need to do three different queries with a different hard-coded string on the front for each one ? Why is there a group by on the second and third but not the first ? Is there any difference at all between the second and third ? Do you really want exactly the same data coming through from each part of the query apart from a different string added to the front ?