?
Solved

SQL's problem with JSP pages...

Posted on 2003-11-18
11
Medium Priority
?
259 Views
Last Modified: 2012-05-04
Hi, I have this funny problem due to the DB design. In fact I think i don't have much choice other than design in this way.


P_code       Filter A             Fliter B            Filter C           Filter D             Filter F

A134         11/2/2002         10/5/2003       5/5/2001       5/9/2002          5/3/2003
P8A5          5/8/2001          31/1/2003       18/2/1999     7/4/2001          25/2/2003
.
.
.
.



I have this kind of design of my database. And this is the problem.

I have a page, to query a expiring date of each filter. Howwould be my SQL statement would be, i mean i need to go through the data horizontally rather than vertically. I mean i need to go through the product's filter one by one to check for the expired date.


If i use this,

SELECT * from Table1 where Filter > ' "+startDate +" '  

then my db design should be like this

P_code     Filter
A134         11/2/2002        
A134         10/5/2003      
A134          5/5/2001      
A134          5/9/2002          
.
.
.
.

Which i dont want....

Can you help?
Thank You.
0
Comment
Question by:superman19
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 6

Accepted Solution

by:
jarasa earned 1000 total points
ID: 9771445
Hi Superman19.

I'm not really sure of what you want but if what you need is to know how many p_codes had been expired without caring on wich filter had, just make a select like this:

SELECT p_code From Table Where ([Filter A] > startDate OR [Filter B] > startDate OR ...... )

That should work, but if you pretend to know which filter has expired, then you should change your desing and make two tables, one with the p_codes and another one with the filters something like this.

Table 1
p_code
A134
P8A5

Table 2
p_code    Filter Name    Expiry Date
A134       Filter A          11/2/2002
A134       Filter B          10/5/2003
A134       Filter C          7/23/2002
A134       Filter D          1/12/2003
A134       Filter E          12/7/2002
P8A5       Filter A            5/8/2002
P8A5       Filter B            1/18/2003
P8A5       Filter C            7/28/2001
P8A5       Filter D            10/12/2005
P8A5       Filter E            4/7/2004

And then make a query like this:

SELECT table1.p_code, table2.[Filter Name] FROM Table1, Table2 Where Table1.p_code=Table2.p_code and Table2.[Expiry Date] > startDate

This will tell you which p_codes and wich filters are expired.

Have Fun
Javier

0
 
LVL 15

Expert Comment

by:jimmack
ID: 9771467
I'll assume that you're stuck with the table definition you've given above.

How are you using the information?

If you are displaying a table on the page, perhaps with expired filters being shown in red, then your should read in the entire table and iterate through the ResultSet.

For each row in the ResultSet, you should check the value of each date and display the table entry accordingly.

You could minimise the ResultSet by extending your SQL query as follows:

SELECT * from Table1 where FilterA > ' "+startDate +" ' OR FilterB > ' "+startDate+" ' OR FilterC ... etc.

At least this will only return results where at least one of the filters has expired.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9771740
I agree with jarasa that you should revise your table design. Your origianl design in not normalized and the one jarasa suggested is normalized.

If you are stuck with your currect table desgin, then this is the query that you should try:

SELECT p_code, 'Filter A' as filter_name from Table1 where [Filter A] > ?
union
SELECT p_code, 'Filter B' as filter_name from Table1 where [Filter B] > ?
union
SELECT p_code, 'Filter C' as filter_name from Table1 where [Filter C] > ?
union
SELECT p_code, 'Filter D' as filter_name from Table1 where [Filter D] > ?
union
SELECT p_code, 'Filter E' as filter_name from Table1 where [Filter E] > ?
union
SELECT p_code, 'Filter F' as filter_name from Table1 where [Filter F] > ?


I recommend you to use prepared statement for date operation, otherwise you should replace all the '?' with startDate.

let us know if you have further enquires.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:superman19
ID: 9775273
i am so touch...
thank you for your respond..

jarasa, tq.
>>>SELECT p_code From Table Where ([Filter A] > startDate OR [Filter B] > startDate OR ...... )
This SQL i think should sovled my problem..

**In fact my main concern is the performance issues after i design the db. I am running at a very low availibility resource machine. I need to take the performance as one of my main issues. If i design more table, i afraid that the retriving time is longer. That's why i plan to put everythign into one table. (But is this the best way??-for retrieving only one tbale ?)
But put it into 2 table is a better recommandation rather than one is it??
what do you think?
**

jimmack, tq.
I think your SQL statement is also can be done. TQ. But what do you think for the "**" statement i make??

kennethxu, tq.
Do you think that the normalized table cab give me a better performance. If I am not wrong, that will be longer time for retrive data instead getting the whole information at one shoot...
can you share your opinions on the "**" i make?




0
 

Author Comment

by:superman19
ID: 9775290
jarasa.. and another things is the number of p_code i have is a lot. I afraid it'll reach up to 3 thousand. And if I normalized the table into 2, the size will be huge. Imagine each p_code i have 5 filter.. and the Table's size will be a huge number...

but yet, if this is the performance (which i am not sure), i think i will go for your suggestion.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9775913
>> If i design more table, i afraid that the retriving time is longer
the database performance is not measured by number of tables. rather the right design to make sql easiy and fast to access data.

>>I afraid it'll reach up to 3 thousand.
3 thousand is a small number to a database.
0
 

Author Comment

by:superman19
ID: 9776958
>>If i design more table, i afraid that the retriving time is longer
ken, what i mean here is, when i have 2 table, and when i perform a join function or some sort like that in order to retrieve 2 table based on one common p_code, is it will slow my resultset down or not??
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9776977
Hi.

I agree with kennethxu it does not matter the number of tables you have but the way you access to them, if you have two tables and have an index on the second one on p_code and expire date, it will be defenetly faster than with just one table, but if you really don't care about on wich filters has p_code expired, then you could use one table, if you have size problems. But I don't think the change will be that huge on size. Consider to code to numeric value the filter field it will reduce the size, and you can still know on wich one has expired.

What Database are you using?

Normally as much WHERE clauses you have worst perfomance you get since SQL has to process all of them one by one.

Javier
0
 

Author Comment

by:superman19
ID: 9777263
Javier.... hm.. sound interesting... i think i can convince...to use 2 table rather than one...ha....
i am using Mc Access

Table 1
p_code
A134
P8A5

Table 2
p_code    Filter Name    Expiry Date
A134       Filter A          11/2/2002
A134       Filter B          10/5/2003
A134       Filter C          7/23/2002
A134       Filter D          1/12/2003
A134       Filter E          12/7/2002
P8A5       Filter A            5/8/2002
P8A5       Filter B            1/18/2003
P8A5       Filter C            7/28/2001
P8A5       Filter D            10/12/2005
P8A5       Filter E            4/7/2004

another things is when i insert the new line.. i think i will have another issues... how can i insert.. my sql...?
ok lah.. i think i have find my solution.

for this problem title... so let me post another question....... for anohter question...

0
 
LVL 15

Expert Comment

by:jimmack
ID: 9777271
I agree with the other comments made.  If you can, you should probably change your database schema to "normalise" it (as described by jarasa).

I didn't realise that the WHERE clause was inefficient (thanx jarasa ;-)).
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9777294
My pleasure.

:c))
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
In this modest contribution, I want to share with the IT community (especially system administrators, IT Support Engineers and IT Help Desks) about Windows crashes/hangs and how to deal with these particular problems.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question