Re: In-line Query --- Advices pls

It's not very common, though it has its use. Consider the following (simplified) table:

CREATE TABLE INTF_033_IN

  ROW_TYPE            VARCHAR2(2),
  RECORD_NO           VARCHAR2(7),
  ID_NO               VARCHAR2(8),
  ERR_MESSAGE         VARCHAR2(80)) ;

And a sample data from this table:

"ROW_TYPE","RECORD_NO","ID_NO","ERR_MESSAGE"
"HR","0000001","CBBB7977", NULL
"D","0000001",NULL, NULL
"HR","0000002","CBBB8159", NULL
"D","0000002",NULL,"Some error message"

1. only rows of row_type = 'D' can contain a not NULL err_message
2. only rows of row_type = 'HR' can contain a not NULL id_no
3. rows with the same record_no belong to the same logical tuple
4. rows of different types cannot be merged into one single row in the table itself, as there are other attributes diferentiating them.

Consider the following problem: write a single select query that will return only one row, containing the id_no, for each record_no having a not null err_message.

The solution is:
SELECT a.id_no FROM

    (SELECT id_no, record_no
    FROM intf_033_in
    WHERE row_type = 'HR'
    ) a,
    (SELECT id_no, record_no
    FROM intf_033_in
    WHERE row_type = 'D'
    AND err_message IS NOT NULL
    ) b
   WHERE a.record_no = b.record_no;

Anything else will imply creating views for a and b, in fact creating extra overhead for the DBA. As with any query, tuning may be required. The query above is part of a subquery in the WHERE clause of an UPDATE statement, meaning tuning - or at least proper indexing - is definitely required, because it indeed joins on id_no with the id_no of the table to be updated. The original update statement is given for reference, and I think it is the most efficient way to accomplish this.

I can't speculate what a DBA may think of it, as I am not one.

UPDATE intf_au_company
SET ind_chg = NULL
WHERE EXISTS
 (SELECT 1 FROM
  (SELECT a.ckr_nummer ckr_nummer FROM
   (SELECT proces_run_no, ckr_nummer, recordnummer    FROM intf_033_in
   WHERE row_type = 'HR'
   ) a,
   (SELECT proces_run_no, ckr_nummer, recordnummer    FROM intf_033_in
   WHERE row_type = 'D'
   AND foutboodschap IS NULL
   ) b
  WHERE a.recordnummer = b.recordnummer
  AND a.proces_run_no = b.proces_run_no
  AND a.proces_run_no =
   (SELECT max(proces_run_no)
   FROM intf_log
   WHERE proc_name = 'proc_033_in'
   AND log_type = 'E')
  ) c
  WHERE intf_au_company.ckr_nummer = c.ckr_nummer);

Hth,
Radu

"Jane" <janeyiu_at_optonline.net> wrote in message
news:O9MF7.42407$D7.10305440_at_news02.optonline.net...

> I'd like some advices on the use of in-line queries....Select ....from
> (select...from...)
> Is this something that freaks out the "typical" DBAs ?
> what about joining it with other table(s) ?
> What sort of care should be applied ?
> How common is it used in your experience ?
>
> Thanks
> jane
>
>