Home » Other » Test » Test posting HTML
Test posting HTML [message #108677] Wed, 17 November 2004 03:06 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Re: Does this break anything? [message #108680 is a reply to message #108679] Fri, 19 November 2004 09:01 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
It would seem not.
Previous Topic: Error while accessing database as sysoper
Next Topic: operating system
Goto Forum:
  


Current Time: Fri Sep 24 16:02:54 CDT 2021