| Need help on a query [message #631975] |
Thu, 22 January 2015 09:36  |
bahubcd
Messages: 40
Registered: July 2007
Location: Bangalore
|
Member |
|
|
Dear Experts,
I am having a requirement. There is a table ITEM_ACT that has 2 columns. item_no and activate date. No primary key i.e. Multiple (more than 2) dates can exist for an item_no. The requirement is a query which gives the output as below.
ITEM NO FUTURE IND(Y/N)
A Y
A N
B Y
C N
D N
D Y
E N
E Y
The condition for FUTURE_IND is whether activate_date is in future then Y or its today or past then N.
Please note again, There can be multiple records in the table ITEM_ACT for an item_no.
The query also should be performance tuned(ideally avoid grouping/aggregate as much as possible)
Appreciate any help/idea on writing a query that suits above stated requirement.
Many Thanks,
bahubcd.
|
|
|
|
|
|
|
|
| Re: Need help on a query [message #631979 is a reply to message #631976] |
Thu, 22 January 2015 11:41  |
bahubcd
Messages: 40
Registered: July 2007
Location: Bangalore
|
Member |
|
|
Dear Bill/John,
Thanks for your replies.
No, its not a college question. Its part of a bigger requirement to display the data in a report to the users.
I had used two count(item_no)'s for activate_date > and <= sysdate and then in an outer query use a case to display 'Y', 'N' accordingly. But the table ITEM_ACT has 16 Million records and due to count function, the report is getting way too slower. So I was thinking if somebody has any better idea to get the desired output in a different way and also not impacting performance.
Regards,
Bahubcd
|
|
|
|
| Re: Need help on a query [message #631980 is a reply to message #631979] |
Thu, 22 January 2015 11:44 |
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
I do not see how any tuning is possible. You have no filters, no aggregation, no oerdering. So your only option is to scan the entire table, applying a CASE to the ACTIVATE column.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
