Return duplicate records 1

[boboleft]

I need some help on duplicate records. I need to be able to output all records to the detail section. I have tried to do the subquery within sql command of crystal report but I haven't be able to do it. Can somebody please help me?

acct# acct_status balance
A12345 open 100
A12345 closed 50
a12345 closed 20

 

[boboleft]

I need to clarify one more thing, what i need is to return accts that has acct_status = closed and balance > 0 and also has an existing open account. for the example listed below, i only want all the acct# A12345 (including both open and closed) to be output to the deteail section.

acct# acct_status balance
A12345 open 100
A12345 closed 50
A12345 closed 20
B12344 open 20
B22555 open 10

 

[Bloke152]

i think you need something like

select a.acct, a.acct_status, a.balance
from table_name a,
(select acct, a.acct_status, balance
from table_name
where acct_status = 'Closed'
and balance > 0) b
where a.acct = b.acct

 

[boboleft]

I am doing it via crystal database command and for some reason, oracle doesn't like when i put the alias (a, b). I am not sure what are other ways to make it work. Can somebody please help me??

thanks

 

[lbass]

Probably something like this would work:

select table.acct, table.acct_status, table.balance
from table
where
(select min(a.acct_status) from table a
where a.acct = table.acct) = 'closed' and
(select min(a.balance) from from table a
where a.acct = table.acct) >= 0

-LB

 

[boboleft]

yes, for some reason, i am not able to use the alias like "a" for the table when I created the query via "database expert-command". I am connecting to an oracle database. Any suggestion??

thanks

 

[boboleft]

I am getting an ORA-00936 Missing Expression error and i have checked multiple times and I am not sure if is causing by the alias. Can somebody please help me??

 

[lbass]

In Oracle, the command might need to look like:

select "table"."acct", "table"."acct_status", "table"."balance"
from "owner"."table" "table"
where
(select min(a."acct_status") from "table" a
where a."acct" = "table"."acct") = 'closed' and
(select min(a."balance") from "table" a
where a."acct" = "table"."acct") >= 0

Your best bet is to look at another report that you have generated without a command and go to database->show SQL query and note the syntax and punctuation appropriate for your datasource and connectivity.

-LB

 

[boboleft]

thanks for the help. This is my first time doing crystal report where i have to use the alias and I am really having trouble getting this report to work. I had the same format but still getting the error message "ora-00936: missing expression". I really don't know how to fix this. Can you please help me again??

 

[lbass]

Why don't you copy your command into the thread, and then we'll see.

-LB

 

[boboleft]

Thanks so much for your help. Here is the code. If i added the alias, i will get the "ora 00936: missing expression " can't open a rowset. I am not really sure where i need to place the alias. I never have trouble when writing this in sql but since I am pretty new to Oracle and I don't know what are the syntax rules. Please help me..thanks

select "customer"."acct_num", "customer"."status", "customer"."balance"
from "test"."customer" "customer"
where (select min(a."status") from "customer" a
where a."acct_num" = "customer"."acct_num") = 'closed' and
(select min(a."balance") from "customer" a
where a."acct_num" = "customer"."acct_num") >= 0

 

[lbass]

Try adding in the owner in the alias sections:

select "customer"."acct_num", "customer"."status", "customer"."balance"
from "test"."customer" "customer"
where (select min(a."status") from "test"."customer" a
where a."acct_num" = "customer"."acct_num") = 'closed' and
(select min(a."balance") from "test"."customer" a
where a."acct_num" = "customer"."acct_num") >= 0

I have used this syntax with Oracle 8i with success. It probably depends upon your driver as well, but I can't really address that.

-LB

 

[boboleft]

Thanks so much. The query worked. I can't figure out how to pull accunts has both acct_status= open and closed?? I just want the one for a12345 and all 3 records needs to be output to the detail section. can you please help me again??


acct# acct_status balance
A12345 open 100
A12345 closed 50
A12345 closed 20
B12344 closed 20
B22555 open 10

 

[lbass]

But that is what the query is designed to do. Just place the fields from the command in the detail section. You should see all three records displayed.

-LB

 

[lbass]

Oh, I see from an earlier thread that you actually only want the ID if there is also an open record. Then the query would be:

select "customer"."acct_num", "customer"."status", "customer"."balance"
from "test"."customer" "customer"
where (select min(a."status") from "test"."customer" a
where a."acct_num" = "customer"."acct_num") = 'closed' and
(select max(a."status") from "test"."customer" a
where a."acct_num" = "customer"."acct_num") = 'open' and
(select min(a."balance") from "test"."customer" a
where a."acct_num" = "customer"."acct_num") >= 0

Instead of using a command as your datasource, you could also do this within CR by inserting a group on {customer.acct_num} and then going to report->selection formula->GROUP and entering:

distinctcount({customer.status},{customer.acct_num}) = 2 and
minimum({customer.balance},{customer.acct_num}) > 0

This assumes there are only two statuses.

-LB