Results 1 to 15 of 15

Thread: O Joy

  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: O Joy

    OK...cann anyone tell me HOW Oracle can allow 2 tables in a FROM clause with the same alias?

    For example person h and eeodata h

    ???

    Frigin 3rd party vendors

    I'll format the code to make it look readable....but here's what's was thrown at me...and is resourcebase a system table?

    Code:
    SELECT b.requisitionnumber ,h.firstname ||' '||h.lastname Candidate ,i.defaultdisplayvalue gender  ,k.defaultdisplayvalue Race
    ,a.BIDDATE ,a.disposition jobbid_status
    ,c.DATEOFSCREEN ,c.status screenstatus
    ,e.DATEOFINTERVIEW ,e.status interview_status
    ,d.DATEOFSTART ,d.offerstatus ,i.documentid ,--i.DESCRIPTION ,g.content 
    ,h.firstname ||' '||h.lastname||'_'||i.documentid||'.dat' doc_identifier ,decode(i.documenttype ,'0', 'Resume') Documenttype 
    FROM jobbid a ,requisition b ,screen c ,offer d , interview e ,candidate f ,person h ,document i ,documentattachment g ,
    eeodata h  ,(SELECT * FROM optionset WHERE resourcebase = 'String.Enum.General.Gender') i ,race j,
    (SELECT * FROM optionset WHERE resourcebase = 'String.Enum.Candidate.Race') k
    WHERE 
    a.requisitionid = b.requisitionid AND 
    a.requisitionid = c.requisitionid(+) AND 
    a.candidateid = c.candidateid(+) AND 
    a.requisitionid = d.requisitionid(+) AND 
    a.candidateid = d.candidateid(+) AND 
    a.requisitionid = e.requisitionid(+) AND 
    a.candidateid = e.candidateid(+) AND 
    a.candidateid = f.candidateid AND 
    f.personid = h.personid AND 
    a.requisitionid = i.requisitionid AND 
    a.candidateid = i.candidateid AND 
    i.documentid = g.documentid AND 
    f.eeodataid = h.eeodataid AND 
    h.gender = i.datavalue(+) AND  
    h.eeodataid = j.eeodataid(+) AND 
    j.race = k.datavalue(+) AND 
    a.requisitionid IN (SELECT requisitionid FROM 
    requisition WHERE requisitionnumber IN 
    ('1314')
    and i.documenttype = 0
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, and I was told it was run, and asked another Oracle dba to run it it and they said it ran

    I'll have to ask another
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT b.requisitionnumber,
           h.firstname
           ||' '
           ||h.lastname    candidate,
           i.defaultdisplayvalue               gender,
           k.defaultdisplayvalue               race,
           a.biddate,
           a.disposition                       jobbid_status,
           c.dateofscreen,
           c.status                            screenstatus,
           e.dateofinterview,
           e.status                            interview_status,
           d.dateofstart,
           d.offerstatus,
           i.documentid,
           g.content,
           h.firstname
           ||' '
           ||h.lastname
           ||'_'
           ||i.documentid
           ||'.dat' doc_identifier,
           Decode(i.documenttype,'0','Resume') documenttype
    FROM   jobbid a,
           requisition b,
           screen c,
           offer d,
           interview e,
           candidate f,
           person h,
           document i,
           documentattachment g,
           eeodata h,
           (SELECT *
            FROM   optionset
            WHERE  resourcebase = 'String.Enum.General.Gender') i,
           race j,
           (SELECT *
            FROM   optionset
            WHERE  resourcebase = 'String.Enum.Candidate.Race') k
    WHERE  a.requisitionid = b.requisitionid
           AND a.requisitionid = c.requisitionid (+)
           AND a.candidateid = c.candidateid (+)
           AND a.requisitionid = d.requisitionid (+)
           AND a.candidateid = d.candidateid (+)
           AND a.requisitionid = e.requisitionid (+)
           AND a.candidateid = e.candidateid (+)
           AND a.candidateid = f.candidateid
           AND f.personid = h.personid
           AND a.requisitionid = i.requisitionid
           AND a.candidateid = i.candidateid
           AND i.documentid = g.documentid
           AND f.eeodataid = h.eeodataid
           AND h.gender = i.datavalue (+)
           AND h.eeodataid = j.eeodataid (+)
           AND j.race = k.datavalue (+)
           AND a.requisitionid IN (SELECT requisitionid
                                   FROM   requisition
                                   WHERE  requisitionnumber IN ('1314')
                                          AND i.documenttype = 0)
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here...got called to another fire...but cleaned it up, and moved some on the non-ANSI joins around...noticed a missing closed ) in the WHERE...not sure where documenttype=0 goes just yet

    Code:
    SELECT	  b.requisitionnumber 
    		, h.firstname ||' '||h.lastname								AS Candidate 
    		, i.defaultdisplayvalue										AS gender  
    		, k.defaultdisplayvalue										AS Race
    		, a.BIDDATE 
    		, a.disposition												AS jobbid_status
    		, c.DATEOFSCREEN 
    		, c.status													AS screenstatus
    		, e.DATEOFINTERVIEW 
    		, e.status													AS interview_status
    		, d.DATEOFSTART 
    		, d.offerstatus 
    		, i.documentid 
    		,--i.DESCRIPTION ,g.content 
    		, h.firstname ||' '||h.lastname||'_'||i.documentid||'.dat'	AS doc_identifier 
    		, decode(i.documenttype ,'0', 'Resume')						AS Documenttype 
      FROM	  jobbid												a 
    		, requisition											b 
    		, screen												c 
    		, offer													d
    		, interview												e 
    		, candidate												f 
    		, person												h 
    		, document												i 
    		, documentattachment									g 
    		, eeodata												h  
    		, (SELECT * 
    			 FROM optionset 
    			WHERE resourcebase = 'String.Enum.General.Gender')	i 
    		, race													j
    		, (SELECT * 
    			 FROM optionset 
    			WHERE resourcebase = 'String.Enum.Candidate.Race')	k
     WHERE	  a.requisitionid	= b.requisitionid 
    
       AND	  a.requisitionid	= c.requisitionid(+)
       AND	  a.candidateid		= c.candidateid(+)
    
       AND	  a.requisitionid	= d.requisitionid(+)
       AND	  a.candidateid		= d.candidateid(+)
    
       AND	  a.requisitionid	= e.requisitionid(+)
       AND    a.candidateid		= e.candidateid(+)
    
       AND    a.requisitionid	= i.requisitionid
       AND    a.candidateid		= i.candidateid
    
       AND    a.candidateid		= f.candidateid
    
       AND    f.personid		= h.personid
       AND    f.eeodataid		= h.eeodataid
       AND    h.gender			= i.datavalue(+)
       AND    h.eeodataid		= j.eeodataid(+)
       AND    i.documentid		= g.documentid
       AND    j.race			= k.datavalue(+)
       AND    a.requisitionid		IN (SELECT requisitionid 
    								  FROM requisition 
    								 WHERE requisitionnumber IN ('1314')
    -- ????? Where's the Closing ) ?????? Is the following part of the IN or the WEHERE?
    								   AND i.documenttype = 0
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * from v$version

    Can't right now...lengthy security process...

    BUT

    I get the work way ahead of time

    "But, Spock, Is it Possible?"

    "We are seeing it with our own eyes Captain"
    I still don't believe it...

    The question is

    Is there ANY way that this could run on ANY Oracle platform?

    Thoughts? Condolences? Frivolities?
    Last edited by Brett Kaiser; 12-01-09 at 14:18.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey, here's another question

    Do Oracle developers think that unformatted wrapped code is more efficient to the compiler?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    As long as no ambiguity, no error is throw.
    Code:
    SQL> @temp1
    SQL> CREATE TABLE TAB1 (ID11 NUMBER, LABEL11 VARCHAR2(7));
    
    Table created.
    
    SQL> CREATE TABLE TAB2 (ID21 NUMBER, LABEL21 VARCHAR2(7));
    
    Table created.
    
    SQL> INSERT INTO TAB1 VALUES(1,'ONE');
    
    1 row created.
    
    SQL> INSERT INTO TAB2 VALUES(2,'TWO');
    
    1 row created.
    
    SQL> INSERT INTO TAB2 VALUES(1,'THREE');
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SELECT A.ID11, A.LABEL11, A.LABEL21
      2  FROM TAB1 A, TAB2 A
      3  WHERE A.ID11 = A.ID21
      4  /
    
          ID11 LABEL11 LABEL21
    ---------- ------- -------
    	 1 ONE	   THREE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    wow...thanks for the proof

    so what happens when you add an identical column to both tables?

    EDIT: But would you recommend a developer do this?

    Also, WHY would Oracle allow it?

    Is there some hidden mystic meaning?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >so what happens when you add an identical column to both tables?
    Are you incapable or unwilling to expand what I provided & TEST yourself?
    I bet an error is thrown!

    >EDIT: But would you recommend a developer do this?
    NEVER!

    >Also, WHY would Oracle allow it?
    The parser is brain dead code.
    It is valid syntax; even though it makes no real sense.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >cann anyone tell me HOW Oracle can allow 2 tables in a FROM clause with the same alias?
    There is essentially no difference between when the same alias is assigned to 2 tables as when no aliases are used.

    The alias only has meaning & usefulness when the same column_name exists in 2 tables.
    The alias is used as the means to avoid ambiguity & ensure the desired data is referenced.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by anacedent View Post
    Are you incapable or unwilling to expand what I provided & TEST yourself?
    You must be a BOAT load of fun at parties

    I don't have access to a box yet...we are gearing up

    This was thrown over the wall at me, and it's been like 10 years since I worked on Oracle...ah...2001

    Just would never code such crap myself

    Seems like a lot of Oracle developers do however

    Mostly been doing SQL Server 6.x-2005, DB2 z/OS, UDB, IDMS, IMS, yadadadadad

    You?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Mostly been doing SQL Server 6.x-2005, DB2 z/OS, UDB, IDMS, IMS, yadadadadad

    >You?
    Professionally I started doing COBOL on IBM/360 (on punched cards), VAX/VMS sysadmin, *nix sysadmin, Oracle DBA
    I've programmed in COBOL, FORTRAN, PL/1, PASCAL,C,C++, JAVA, PERL, PHP, BASH, SQL, PL/SQL & likely others I have forgotten

    BTW, I'm running Oracle on my Ubuntu laptop now.
    It is a flimsy excuse you don't have access to Oracle.
    Nothing prevents you from downloading Oracle RDBMS & running it on any PC.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    hmmm no kidding....

    I left out the first half of my "Career"

    Just get the download from Oracle?

    Got any DB2/SQL Server/Oracle Translation guides lying around?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Brett. Have a look for Oracle Express. It may obnly be available in a 10.2 version, but I have not checked in a while. It will not have all the bells and whistles, or some of the big enterprise options, but you can certainly test out most of the cases you are getting with no system to work on.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Just get the download from Oracle?
    Oracle Technology Network | World's Largest Community for Developers, DBAs, and Architects
    You need to "register", but it is free to do so.

    Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation contains the whole Oracle Documentation set.

    Enjoy!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •