Oracle-L: Odd Sql result

From: <dgoulet_at_vicr.com>
Date: Thu, 28 Mar 2002 11:13:26 -0800
Message-ID: <F001.00436146.20020328111326@fatcity.com>

Hey folks,

Now I've seen some odd results form queries in the past, but I'll be d*mned is I can figure this one out. It defies logic.

I've two queries that each return 50 rows from the database & the rows are the same is each case (stubby pencil check), but of the three columns returned the last two reverse. I'm including the SQL, a sample result set, and the view definitions that are being used. If you can figure it out, please let me know. I'm asking OTS as well.

Dick Goulet

Here is the data:

select pd.product_id, pts.PART_TYPE, pts.PART_NUM 2 from specsrv.mfg_products pd, vmecs.parts pts 3 where pts.part_type = pd.PART_TYPe
4 and pd.product_id in (6650,6550,6750,35,9920);

PRODUCT_ID PART_TYPE PART_NUM

---------- -------------------- --------------------
      9920 BR-FT48C12C150A      BRAIN_ASSY_FT
      9920 4-FT48C5C100A        BRAIN_ASSY_FT
      9920 4-FT48C2C50A         BRAIN_ASSY_FT
      9920 4-FT48C12C150A       BRAIN_ASSY_FT
      9920 4-FT48C28C150A       BRAIN_ASSY_FT
      9920 4-SS48B15C250A       BRAIN_ASSY_FT
      9920 BR-FT48B2C100A       BRAIN_ASSY_FT
      9920 4-VI-810718          BRAIN_ASSY_FT
      9920 4-FT300B15C250A      BRAIN_ASSY_FT
      9920 BR-FT48B15C250A      BRAIN_ASSY_FT
      9920 4-FT48B15C250A       BRAIN_ASSY_FT
      9920 BR-FT48B5C200A       BRAIN_ASSY_FT
      9920 BR-FT48B28C250A      BRAIN_ASSY_FT
      9920 4-FT48C3V3C75A       BRAIN_ASSY_FT
      9920 4-FT48C24C150A       BRAIN_ASSY_FT
      9920 4-HT-810354          BRAIN_ASSY_FT
      9920 4-FT48B2C100A        BRAIN_ASSY_FT
      9920 4-FT48B5C200A        BRAIN_ASSY_FT
      9920 4-GW-810354          BRAIN_ASSY_FT
      9920 4-VI-810354          BRAIN_ASSY_FT
      9920 BR-FT300B15C250A     BRAIN_ASSY_FT

select pd.product_id, pts.PART_TYPE, pts.PART_NUM 2 from specsrv.mfg_products pd, vmecs.parts pts 3 where RTRIM(pts.part_type) = pd.PART_TYPE 4 and pd.product_id in (6650,6550,6750,35,9920);

PRODUCT_ID PART_TYPE PART_NUM

---------- -------------------- --------------------
      9920 BRAIN_ASSY_FT        BR-FT48C12C150A
      9920 BRAIN_ASSY_FT        4-FT48C5C100A
      9920 BRAIN_ASSY_FT        4-FT48C2C50A
      9920 BRAIN_ASSY_FT        4-FT48C12C150A
      9920 BRAIN_ASSY_FT        4-FT48C28C150A
      9920 BRAIN_ASSY_FT        4-SS48B15C250A
      9920 BRAIN_ASSY_FT        BR-FT48B2C100A
      9920 BRAIN_ASSY_FT        4-VI-810718
      9920 BRAIN_ASSY_FT        4-FT300B15C250A
      9920 BRAIN_ASSY_FT        BR-FT48B15C250A
      9920 BRAIN_ASSY_FT        4-FT48B15C250A
      9920 BRAIN_ASSY_FT        BR-FT48B5C200A
      9920 BRAIN_ASSY_FT        BR-FT48B28C250A
      9920 BRAIN_ASSY_FT        4-FT48C3V3C75A
      9920 BRAIN_ASSY_FT        4-FT48C24C150A
      9920 BRAIN_ASSY_FT        4-HT-810354
      9920 BRAIN_ASSY_FT        4-FT48B2C100A
      9920 BRAIN_ASSY_FT        4-FT48B5C200A
      9920 BRAIN_ASSY_FT        4-GW-810354
      9920 BRAIN_ASSY_FT        4-VI-810354
      9920 BRAIN_ASSY_FT        BR-FT300B15C250A

desc vmecs.parts
Name                                     Null?    Type
---------------------------------------- -------- ---------------
PART_NUM                                 NOT NULL VARCHAR2(20)
PART_REV                                          VARCHAR2(2)
DESCRIPTION                                       VARCHAR2(30)
PART_TYPE                                         VARCHAR2(20)
PAN_TYPE                                          VARCHAR2(20)
PAN_SIZE                                          NUMBER(22)
STOCKING_UOM                                      VARCHAR2(2)
BUYER                                             VARCHAR2(2)
SOURCE_CODE                                       VARCHAR2(2)
CLASS_CODE                                        NUMBER(22)
NUM_ERRORS                                        NUMBER(22)
DATE_CHANGED                                      DATE

desc specsrv.mfg_products
 Name                                     Null?    Type
 ---------------------------------------- -------- -------------
 PRODUCT_ID                               NOT NULL NUMBER
 PRODUCT_NAME                                      VARCHAR2(60)
 PART_TYPE                                         VARCHAR2(61)
 PROD_DESC                                NOT NULL VARCHAR2(60)
 CONFIGURABLE                             NOT NULL VARCHAR2(1)
 SPEC_TABLE_NAME                                   VARCHAR2(40)
 NUM_SMD_BRDS                                      NUMBER

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Mar 28 2002 - 13:13:26 CST