Re: Odd Sql result

Are you sure you aren't running 9i with:

    alter session set sessiontimezone = 'UTC+72:00':

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 28 March 2002 20:12

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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