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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
