Home » SQL & PL/SQL » SQL & PL/SQL » sub query logic
sub query logic [message #411485] Sat, 04 July 2009 04:36 Go to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
i need to get a unique count of applicaation who do not have the same fupcode .

i have applications from start of this bank till date and every application is assigned a FUpcode

i have to get the count of applications in previous month who have a new fupcode.

i mean these applications should not have been assigned this fup code before in their lifetime.

as one application can have many fup codes assigned depnding on the business logic.

so i need to track the applications with new fup codes only in the last month.

here is the query i tried but the result doesnt match and i fail to understand how to get this logic done.

My Query :

select count(*) from (

select count(fup_code) from fup_detl where trim(fup_date8) < '20080630' and fup_code in

(select distinct fupcode from fup_detl where trim(fup_date8) between '20080601' and '20080630' ) group by substr(appl_id,1,12) having count(fup_code)=1)

Re: sub query logic [message #411486 is a reply to message #411485] Sat, 04 July 2009 04:50 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Provide test case with proper formating.

regards,
Delna
Re: sub query logic [message #411489 is a reply to message #411485] Sat, 04 July 2009 05:06 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
i hope you have understood my problem and the logic am trying to implement.

please let me know il expalin again if you require.

i need to remove the application from previous months count if the applications had been the same fupcode in their lifetime before previous month.

i get more than 1 lakhs records whic isnt correct.

can you let me know if you need anything else.

i cannot disclose the data here.apologies for the same.
Re: sub query logic [message #411492 is a reply to message #411485] Sat, 04 July 2009 05:21 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Without knowing tables used in query and the table structure, it is almost impossible to provide help correctly.

regards,
Delna
Re: sub query logic [message #411493 is a reply to message #411485] Sat, 04 July 2009 05:26 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
ONLY ONE TABLE

fupcode

fupdate

appid all from same table
Re: sub query logic [message #411494 is a reply to message #411485] Sat, 04 July 2009 05:40 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ok, table name is fup_detl.
having three columns
fupcode
fupdate
appid.

And what about fup_date8?

Also provide data type of each and every column using DESC FUP_DETL command.

regards,
Delna
Re: sub query logic [message #411495 is a reply to message #411485] Sat, 04 July 2009 05:43 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
all in varchar 2

fudate8 also varchar
total 3 columns

fupdate8

appid

fupcode

hope that helps

anyother info you need please let me know

thanks for your help
Re: sub query logic [message #411511 is a reply to message #411485] Sat, 04 July 2009 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: sub query logic [message #411515 is a reply to message #411485] Sat, 04 July 2009 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: sub query logic [message #411665 is a reply to message #411495] Mon, 06 July 2009 05:08 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
if all are in Varchar2 how come you apply the code
 WHERE TRIM (fup_date8) BETWEEN '20080601' AND '20080630')                                     

convert all the varchars to date with TO_DATE function then try to filter
Previous Topic: How to find the occurance of non numeric characters in number column.
Next Topic: Circled processing
Goto Forum:
  


Current Time: Sat Dec 03 01:14:33 CST 2016

Total time taken to generate the page: 0.09546 seconds