Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Strange Wildcard Results

Strange Wildcard Results

From: MChristensen <dfs_at_xmission.com>
Date: 5 Nov 2002 14:52:47 -0800
Message-ID: <ef70fe56.0211051452.18719427@posting.google.com>


I am seeing strange results from a query where I am looking at a subset of one column
relative to another column.
I try to do this via concatenation of the subset with a '%' as part of a LIKE clause.

In Short, This Works:
...
where B.RECORD_NAME like A.ARINC_REC_CODE || ' %';

And This Returns a 'bad' set of data:
...
where B.RECORD_NAME like A.ARINC_REC_CODE || '%';

What is the Space doing, and why?

This what I expect the query values to be. "4.1.2.1%"
"4.1.2.1 %"
Both of these values should match one and ONLY one record. "4.1.2.1 VHF NAVAID Primary Records"

The former does an outer join and appears to pair NULLs to 'Similar' records.

What is going on here?

Here are the details:

The arrows are to identify any odd spaces or characters in the data. There are none.

  SQL*Plus: Release 8.0.6.0.0 - Production on Tue Nov 5 09:59:31 2002   Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production   JServer Release 8.1.7.4.0 - 64bit Production

        SQL> select
          2  '->' || A.ARINC_REC_CODE || '<-->' || B.RECORD_NAME ||
'<-' as RESULT
          3  from TMP_PARSE_REC_HDR A, MD_SRC_REC_DESC_HDR B
          4  where B.RECORD_NAME like A.ARINC_REC_CODE || ' %';
        
        RESULT
        ----------------------------------------------------------------------------------------------------

->4.1.2.1<-->4.1.2.1 VHF NAVAID Primary Records<-
->4.1.3.1<-->4.1.3.1 NDB NAVAID Primary Records<-
->4.1.4.1<-->4.1.4.1 Waypoint Primary Records<-
->4.1.5.1<-->4.1.5.1 Holding Pattern Primary Records<-
->4.1.6.1<-->4.1.6.1 Enroute Airways Primary Records<-
->4.1.7.1<-->4.1.7.1 Airport Primary Records<-
->4.1.8.1<-->4.1.8.1 Airport Gate Primary Record<-
->4.1.9.1<-->4.1.9.1 Airport SID/STAR/Approach Primary
Records<-
->4.1.10.1<-->4.1.10.1 Runway Primary Records<-
->4.1.11.1<-->4.1.11.1 Airport and Heliport Localizer and
Glide Slope Primary Records<-
->4.1.12.1<-->4.1.12.1 Company Primary Records<-
->4.1.13.1<-->4.1.13.1 Airport and Heliport Localizer Primary
Records<-
->4.1.14.1<-->4.1.14.1 Airport Communications Primary
Records<-
->4.1.15.1<-->4.1.15.1 Airways Marker Primary Records<-
->4.1.16.1<-->4.1.16.1 Cruising Table Primary Records<-
->4.1.17.1<-->4.1.17.1 FIR/UIR Primary Records<-
->4.1.18.1<-->4.1.18.1 Restrictive Airspace Primary Records<-
->4.1.19.1<-->4.1.19.1 Grid MORA Primary Records<-
->4.1.20.1<-->4.1.20.1 Airport MSA Primary Records<-
->4.1.21.1<-->4.1.21.1 Altitude Exclusion Primary Records<-
->4.1.21A.1<-->4.1.21A.1 Note Restriction Primary Records<-
->4.1.21B.1<-->4.1.21B.1 Seasonal Closure Primary Records<-
->4.1.22.1<-->4.1.22.1 Airport and Heliport MLS Primary
Records<-
->4.1.23.1<-->4.1.23.1 Enroute Communications Primary
Records<-
->4.1.24.1<-->4.1.24.1 Preferred Route Primary Records<-
->4.1.25.1<-->4.1.25.1 Controlled Airspace Primary Records<-
->4.1.26.1<-->4.1.26.1 Geographical Reference Table Primary
Records (TG)<-
->4.1.29.1<-->4.1.29.1 GNSS Landing Systems (GLS) Primary
Records<-
->4.1.30.1<-->4.1.30.1 Alternate Record Primary Records<-
->4.2.1.1<-->4.2.1.1 Heliport Primary Records<-
->4.2.4.1<-->4.2.4.1 Heliport MSA Primary Records<-
->4.2.5.1<-->4.2.5.1 Heliport Communications Primary Records<-
32 rows selected. SQL> select 2 '->' || A.ARINC_REC_CODE || '<-->' || B.RECORD_NAME || '<-' as RESULT 3 from TMP_PARSE_REC_HDR A, MD_SRC_REC_DESC_HDR B 4 where B.RECORD_NAME like A.ARINC_REC_CODE || '%'; RESULT ----------------------------------------------------------------------------------------------------
-><-->4.1.2.1 VHF NAVAID Primary Records<-
-><-->4.1.2.1 VHF NAVAID Primary Records<-
-><-->4.1.2.1 VHF NAVAID Primary Records<-
-><-->4.1.2.1 VHF NAVAID Primary Records<-
-><-->4.1.2.1 VHF NAVAID Primary Records<-
-><-->4.1.2.1 VHF NAVAID Primary Records<-
-><-->4.1.2.1 VHF NAVAID Primary Records<-
->4.1.2.1<-->4.1.2.1 VHF NAVAID Primary Records<-
-><-->4.1.2.2 VHF NAVAID Continuation Records<-
-><-->4.1.2.2 VHF NAVAID Continuation Records<-
-><-->4.1.2.2 VHF NAVAID Continuation Records<-
-><-->4.1.2.2 VHF NAVAID Continuation Records<-
-><-->4.1.2.2 VHF NAVAID Continuation Records<-
-><-->4.1.2.2 VHF NAVAID Continuation Records<-
-><-->4.1.2.2 VHF NAVAID Continuation Records<-
-><-->4.1.2.3 VHF NAVAID Simulation Continuation Records<-
-><-->4.1.2.3 VHF NAVAID Simulation Continuation Records<-
-><-->4.1.2.3 VHF NAVAID Simulation Continuation Records<-
-><-->4.1.2.3 VHF NAVAID Simulation Continuation Records<-
-><-->4.1.2.3 VHF NAVAID Simulation Continuation Records<-
-><-->4.1.2.3 VHF NAVAID Simulation Continuation Records<-
-><-->4.1.2.3 VHF NAVAID Simulation Continuation Records<-
-><-->4.1.2.4 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.4 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.4 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.4 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.4 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.4 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.4 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.5 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.5 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.5 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.5 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.5 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.5 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.5 VHF NAVAID Flight Planning Continuation
Records<-
-><-->4.1.2.6 VHF NAVAID Limitation Continuation Record<-
-><-->4.1.2.6 VHF NAVAID Limitation Continuation Record<-
-><-->4.1.2.6 VHF NAVAID Limitation Continuation Record<-
-><-->4.1.2.6 VHF NAVAID Limitation Continuation Record<-
-><-->4.1.2.6 VHF NAVAID Limitation Continuation Record<-
-><-->4.1.2.6 VHF NAVAID Limitation Continuation Record<-
-><-->4.1.2.6 VHF NAVAID Limitation Continuation Record<-
-><-->4.1.3.1 NDB NAVAID Primary Records<-
->4.1.3.1<-->4.1.3.1 NDB NAVAID Primary Records<-
-><-->4.1.3.1 NDB NAVAID Primary Records<-
... ... ...
-><-->4.2.5.3 Heliport Communications Continuation Records<-
676 rows selected. ======================================

Here are the specifics of the tabes and data:

The MD_SRC_REC_DESC_HDR table contains 92 unique records similar to this example:
"4.1.27.1 Flight Planning Arrival/Departure Data Primary Records"

The First set of numbers are section numbers like in a book. The records are ALL unique as a whole as are the section numbers themselves.


        SQL> select count(*) 
          2  from MD_SRC_REC_DESC_HDR;
        
         COUNT(*)
        ---------
               92
        
        SQL> select count(distinct(RECORD_NAME)) 
          2  from MD_SRC_REC_DESC_HDR;
        
        COUNT(DISTINCT(RECORD_NAME))
        ----------------------------
                                  92
        
        SQL> select count(distinct(substr(RECORD_NAME,1,INSTR(RECORD_NAME,'
'))))
          2  from MD_SRC_REC_DESC_HDR;
        
        COUNT(DISTINCT(SUBSTR(RECORD_NAME,1,INSTR(RECORD_NAME,''))))
        ------------------------------------------------------------
                                                                  92
        
        SQL> select count(*) 
          2  from MD_SRC_REC_DESC_HDR
          3  where RECORD_NAME is NULL;
        
         COUNT(*)
        ---------
                0
======================================        


        

The ARINC_REC_CODE table contains 32 unique section numbers and 7 NULLs.
The Arrows show there are no imbedded spaces or "odd" characters in the data.


        SQL> select '->' || ARINC_REC_CODE || '<-' as REC_CODE 
          2  from TMP_PARSE_REC_HDR
          3  order by REC_CODE;
        
        REC_CODE
        ------------------------

->4.1.10.1<-
->4.1.11.1<-
->4.1.12.1<-
->4.1.13.1<-
->4.1.14.1<-
->4.1.15.1<-
->4.1.16.1<-
->4.1.17.1<-
->4.1.18.1<-
->4.1.19.1<-
->4.1.2.1<-
->4.1.20.1<-
->4.1.21.1<-
->4.1.21A.1<-
->4.1.21B.1<-
->4.1.22.1<-
->4.1.23.1<-
->4.1.24.1<-
->4.1.25.1<-
->4.1.26.1<-
->4.1.29.1<-
->4.1.3.1<-
->4.1.30.1<-
->4.1.4.1<-
->4.1.5.1<-
->4.1.6.1<-
->4.1.7.1<-
->4.1.8.1<-
->4.1.9.1<-
->4.2.1.1<-
->4.2.4.1<-
->4.2.5.1<-
-><-
-><-
-><-
-><-
-><-
-><-
-><-
39 rows selected. =======================================

Any Ideas???? Received on Tue Nov 05 2002 - 16:52:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US