For performance reasons, you might want to build a view that ties your
tables together with the substring. Then you can query the view in your
application. Try it both ways and see if there is a performance
difference. Be sure to consider the load on your server, e.g., the number
and size of temporary tables built and destroyed, in your testing.
"Alex Ninan" <[EMAIL PROTECTED]>
03/25/2003 05:15 PM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: RE: Query Help
SELECT t1.load_number, t1.file_name, t1.time_stamp, t1.total_lines,
decode(instr(t2.name, t1.load_number, 1,1)
0, 'Upload Error',
t1.status)
FROM Table1 t1, Table2 t2, Table3 t3
WHERE SUBSTR(t1.file_name, 1, INSTR(t1.file_name, '_',1,1)-1) =
t3.module_code
AND t3.is_active = 'Y'
This should help, let me know if it works... btw you have not given the
condition to join "table2" in the "where" clause which can cause
problems...
Cheers
Alex
Sr. Software Engg
Equinix
-----Original Message-----
From: Rosa, Issac [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 8:54 AM
To: SQL
Subject: Query Help
Anyone want to take a stab a helping out with this query? I'm using
oracle9i.
Here's the situation. I have 3 tables as described below with an
example of the data. The only way to join the tables are using
substrings of the fields in the other tables.
Pseudocode:
select tbl1.load_number, tbl1.file_name, tbl1.time_stamp,
tbl1.total_lines,
if tbl1.load_number not in string tbl2.name then 'Upload Error' Else
tbl1.status
where substr(file_name) = tbl3.module_code
and tbl3.is_active = 'Y'
Table1
Load_number pk (ex. 247025)
file_name (ex. REVE3_TWATAI_FEB-03_02-27-03-558)
time_stamp (ex. 2/27/2003 11:38:00 AM)
status (only display this status if load_number is not in table 2 which
is only found in the text of the Name field)
total_lines (ex. 6153)
Table 2
Batch_ID pk (ex. 2210020)
Name (ex. NA REV REVE3 MARRS 3 FEB-03 558 02/27/03 13:36 MARRS3 2442120:
A 247025)
Table3
module_code (ex. REVE3)
is_active
Thanks,
Issac Rosa
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Get the mailserver that powers this list at http://www.coolfusion.com
