Exception join logic needed

[gregwga50]

I have 5 files that contain order#. I want to make sure specific order# has been not used in any of the 5 files. Is there a way to do this with one SQL stmt without have to do 5 separate selects, e.g.

BEGSR CHKORD
FOUND = 'N';

SELECT COUNT(*) into :CNT from FILE1 WHERE FILEORD# = :WKORD;
If CNT <> 0;
FOUND = 'Y';
LEAVESR;

SELECT COUNT(*) into :CNT from FILE2 WHERE FILEORD# = :WKORD;
If CNT <> 0;
FOUND = 'Y';
LEAVESR;

SELECT COUNT(*) into :CNT from FILE3 WHERE FILEORD# = :WKORD;
If CNT <> 0;
FOUND = 'Y';
LEAVESR;
.
.
.
.
ENDSR

[gregwga50]

Sorry about the o instead of 0, I haven't figured out how to edit a post yet

[Jim_IT]

Greg,
encapsulating the 5 select statements into a single sub-select works fine.

select count(*) into :CNT from
(select FILEORD# from FILE1
 union all select FILEORD# from FILE2
 union all select FILEORD# from FILE3
 union all select FILEORD# from FILE4
 union all select FILEORD# from FILE5) as a
where FILEORD# = :WKORD

Just make sure that you have just the keyed fields as output from each file and the query optimizer will use the indexes to access each file.

Jim

p.s. I haven't figured out how to edit my own posts as well.

[B.Hauser]

I think for perfomance issues it might be better to include the WHERE condition in the sub-selects, something like this

Select Count(OrderNo) into :IsFound
  From (   Select OrderNo
             From Table1
             Where OrderNo = :YourOrder
           Fetch First Row Only
        Union All
           Select OrderNo
             From Table2
             Where OrderNo = :YourOrder
           Fetch First Row Only      
        Union All  
           Select OrderNo
             From Table3
             Where OrderNo = :YourOrder
           Fetch First Row Only
        Union All
           Select OrderNo
             From Table4
             Where OrderNo = :YourOrder
           Fetch First Row Only          
        Union All
           Select OrderNo
             From Table5
             Where OrderNo = :YourOrder
           Fetch First Row Only) x

Birgitta

[Jim_IT]

Brigitta,

The VE plans are exactly the same if the where predicate is inside or outside the sub-select statement.

I prefer the where predicate outside so there is only one set of parameters which need to be passed.

Jim

[Vectorspace]

Birgitta, you have "fetch first one record only" in each subselect. I assume that is because we are only testing for record existence and do not actually need the count?
Jim - would that make Birgitta's more efficient that yours?

[B.Hauser]

Just run both statements through Visual Explain and compare the results.
It could be that the optimizer is able to detect that both statements return the same result and rewrites and analyzes them with an identical statement code.
If not my statement might be the better one, because all select information is in the inner SELECT statement and only a single row is returned.

There are only 2 ways that can affect the optimizer's decisions:
1. The way in which a query is written
2. Providing the "right" indexes

Birgitta

[gregwga50]

I think for perfomance issues it might be better to include the WHERE condition in the sub-selects, something like this

Select Count(OrderNo) into :IsFound
From ( Select OrderNo
From Table1
Where OrderNo = :YourOrder
Fetch First Row Only
Union All
Select OrderNo
From Table2
Where OrderNo = :YourOrder
Fetch First Row Only
Union All
Select OrderNo
From Table3
Where OrderNo = :YourOrder
Fetch First Row Only
Union All
Select OrderNo
From Table4
Where OrderNo = :YourOrder
Fetch First Row Only
Union All
Select OrderNo
From Table5
Where OrderNo = :YourOrder
Fetch First Row Only) x

Birgitta

Birgitta,
Unfortunately, I am going to have to throw another twist into this. As it turns out, the field names for OrderNo are no the same in all 5 Tables. I figure that changes things, particularly the Select Count(OrderNo) into :IsFound clause. Sorry about that.

[B.Hauser]

But the columns containing the OrderNo have at least compatible data types in all 5 Tables?
If not you need to CAST the columns.

If the columns have different names, but compatible data types, just use Count(*) instead of Count(ColumnName).

You may also rename the columns in your (Sub-)Select-Statements such as:

Select Count(xOrderNo) into :IsFound
  From (  Select OrderNoA as xOrderNo
             From Table1
             Where OrderNoA = :YourOrder
          Fetch First Row Only
        Union All
          Select OrderNoB as xOrderNo
             From Table2
             Where OrderNoB = :YourOrder
          Fetch First Row Only
        Union All
          Select OrderNoC as xOrderNo
             From Table3
             Where OrderNoC = :YourOrder
          Fetch First Row Only
        Union All
          Select OrderNoD as xOrderNo
             From Table4
             Where OrderNoD = :YourOrder
             Fetch First Row Only
        Union All
          Select OrderNoE as xOrderNo
             From Table5
             Where OrderNoE = :YourOrder
          Fetch First Row Only) x

or

Select Count(xOrderNo) into :IsFound
  From (  Select OrderNoA
             From Table1
             Where OrderNoA = :YourOrder
          Fetch First Row Only
        Union All
          Select OrderNoB
             From Table2
             Where OrderNoB = :YourOrder
          Fetch First Row Only
        Union All
          Select OrderNoC
             From Table3
             Where OrderNoC = :YourOrder
          Fetch First Row Only
        Union All
          Select OrderNoD
             From Table4
             Where OrderNoD = :YourOrder
             Fetch First Row Only
        Union All
          Select OrderNoE
             From Table5
             Where OrderNoE = :YourOrder
          Fetch First Row Only) x (xOrderNo)

Birgitta

[gregwga50]

But the columns containing the OrderNo have at least compatible data types in all 5 Tables?
If not you need to CAST the columns.

If the columns have different names, but compatible data types, just use Count(*) instead of Count(ColumnName).

You may also rename the columns in your (Sub-)Select-Statements such as:

Select Count(xOrderNo) into :IsFound
From ( Select OrderNoA as xOrderNo
From Table1
Where OrderNoA = :YourOrder
Fetch First Row Only
Union All
Select OrderNoB as xOrderNo
From Table2
Where OrderNoB = :YourOrder
Fetch First Row Only
Union All
Select OrderNoC as xOrderNo
From Table3
Where OrderNoC = :YourOrder
Fetch First Row Only
Union All
Select OrderNoD as xOrderNo
From Table4
Where OrderNoD = :YourOrder
Fetch First Row Only
Union All
Select OrderNoE as xOrderNo
From Table5
Where OrderNoE = :YourOrder
Fetch First Row Only) x

or

Select Count(xOrderNo) into :IsFound
From ( Select OrderNoA
From Table1
Where OrderNoA = :YourOrder
Fetch First Row Only
Union All
Select OrderNoB
From Table2
Where OrderNoB = :YourOrder
Fetch First Row Only
Union All
Select OrderNoC
From Table3
Where OrderNoC = :YourOrder
Fetch First Row Only
Union All
Select OrderNoD
From Table4
Where OrderNoD = :YourOrder
Fetch First Row Only
Union All
Select OrderNoE
From Table5
Where OrderNoE = :YourOrder
Fetch First Row Only) x (xOrderNo)

Birgitta

Yes, they are all the same data type. It's the same piece of information, just called something different in each file.............thanks again

[chrisr]

Whenever I read your answers I am always enlightened. Fully explained and easy to take in.
Thank you Birgitta.

[chrisr]

Sorry about the o instead of 0, I haven't figured out how to edit a post yet

You should have an Edit button to the bottom right of your post, next to Quote, Comment, Flag & Like.