| Test posting HTML [message #108677] |
Wed, 17 November 2004 03:06  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Like so
<table>
<tr>
<th>Heading1</th> <th>Heading2</th> <th>Heading3</th>
</tr>
<tr>
<td>Value1</td> <td>Value2</td> <td>Value3</td>
</tr>
</table>
|
|
|
|
| Re: Test posting HTML [message #108678 is a reply to message #108677] |
Thu, 18 November 2004 06:55  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
I wonder whether there is something fundamentally wrong with the HTML in this post because it went all weird on me in http://www.orafaq.com/forum/t/16946/0/.
The problem with the earlier query arose if table B was (extreme example) empty. Leonard's original query,
DELETE FROM a
WHERE unique_id NOT IN
( SELECT unique_id
FROM b
WHERE filename='TEMP.DAT' );
would delete all rows from A, but
DELETE FROM a
WHERE EXISTS
( SELECT 1
FROM b
WHERE b.unique_id = a.unique_id
AND b.filename <> 'TEMP.DAT' );
or for that matter,
DELETE FROM a
WHERE unique_id IN
( SELECT unique_id
FROM b
WHERE filename <> 'TEMP.DAT' );
would delete nothing.
DELETE FROM a
WHERE NOT EXISTS
( SELECT 1
FROM b
WHERE b.unique_id = a.unique_id
AND b.filename = 'TEMP.DAT' );
is a valid variation on the original query. It might be faster, or slower, or come to the same thing, depending on data volumes, distribution, optimizer settings etc. See www.orafaq.com/msgboard/plsql/messages/19327.htm.
Actually now I think about it, NOT IN and NOT EXISTS are not be quite the same thing if b.unique_id can be null:
SQL> CREATE TABLE a AS SELECT rownum AS unique_id FROM user_objects;
Table created.
SQL> CREATE TABLE b AS SELECT unique_id, 'TEMP.DAT' AS filename FROM a;
Table created.
SQL> update b set unique_id = null where rownum = 1;
1 row updated.
SQL> SELECT unique_id FROM a
2 MINUS
3 SELECT unique_id FROM b;
UNIQUE_ID
----------
1
1 row selected.
SQL> commit;
Commit complete.
SQL> DELETE FROM a
1 WHERE unique_id NOT IN
2 ( SELECT unique_id
3 FROM b
4 WHERE filename='TEMP.DAT' );
<b>0 rows deleted.</b>
SQL> DELETE FROM a
2 WHERE NOT EXISTS
3 ( SELECT 1
4 FROM b
5 WHERE b.unique_id = a.unique_id
6* AND b.filename = 'TEMP.DAT' );
<b>1 row deleted.</b>
SQL> roll
Rollback complete.
SQL> DELETE FROM a
2 WHERE unique_id NOT IN
3 ( SELECT NVL(unique_id,-1)
4 FROM b
5* WHERE filename='TEMP.DAT' );
<b>1 row deleted.</b>
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
| Does this break anything? [message #108679 is a reply to message #108677] |
Fri, 19 November 2004 09:01 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Try domething like this:
(ECHO, SPLIT and LIST_ELEMENT are defined here. The STYLE block isn't necessary but it makes the results look nice.)
DECLARE
v_string VARCHAR2(4000) :=
'commencement_date: 10/04/2003; contractid: IT-3122/IT; delay_to_intervene: 3;' ||
'delay_to_intervene_units: working days; delay_to_repair: 5; delay_to_repair_units: working days;' ||
'details: ; vendor: SEIL; warranty_duration: 36; warranty_duration_units: months;';
v_items VARCHAR2_TT := SPLIT(v_string,';');
v_th_text VARCHAR2(4000);
v_td_text VARCHAR2(4000);
BEGIN
FOR i IN v_items.FIRST..v_items.LAST
LOOP
v_th_text := v_th_text || '<th>' || REPLACE(INITCAP(LIST_ELEMENT(v_items(i),1,':')),'_',' ') || '</th>';
v_td_text := v_td_text || '<td>' || LIST_ELEMENT(v_items(i),2,':') || '</td>';
END LOOP;
ECHO('
td, th, td.p, th.p
{ text-align: left; vertical-align: top;
border: solid 1px navy;
border-top: none;
color: black; background: white;
margin: .2em;
padding: .2em .6em; }
th
{ color: white; background: navy;
min-width: 5em;
font-weight: bold;
border: solid 1px navy; }
');
ECHO('<TABLE cellspacing=0 border=3>');
ECHO('<tr> ' || v_th_text || '</tr>', 130);
ECHO('<tr> ' || v_td_text || '</tr>', 130);
ECHO('</TABLE>');
END; |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
