Re: Strange Wildcard Results

It appears that your seven null records in TMP_PARSE_REC_HDR are giving you this problem, since all multiple records are in sets of seven, with no associated ARINC_REC_CODE value:

> -><-->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<-

Include in your query the following condition:

and A.ARINC_REC_CODE is not null

and see if that corrects your errant output.

dfs_at_xmission.com (MChristensen) wrote in message news:<ef70fe56.0211051452.18719427_at_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 Wed Nov 06 2002 - 12:56:53 CST