Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: combining two sql queries into one result

    In one table I have the columns:
    ID
    Start Date
    End Date
    Description
    Comment

    in another table I have the columns:
    ID
    Project Name

    I want to create a query that will query the two tables and combine the results. The ID from table 1 and 2 are identical.

    I dont know why the table was split in two to include two parts of information but now I wish to combine it. Is this possible? Is there a function I can look into?

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Is this possible?
    Yes, there's lots of threads in this forum on this subject. This is the latest one:
    http://www.dbforums.com/oracle/16965...table-2-a.html

    Post 2 will give you the syntax you need.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    I've currently got a horribly looking SQL code that reads:

    Code:
    SELECT CORE.TABLE1.TREATMENT_ID, CORE.TABLE1.PACKAGE_NAME,
            CORE.TABLE2.PROGRAM_ID,
            CORE.TABLE3.FORMAT_ID
    FROM CORE.TABLE1
    FULL OUTER JOIN CORE.TABLE2
    ON CORE.TABLE1.REQ_ID=CORE.TABLE2.REQ_ID
    FULL OUTER JOIN CORE.TABLE3
    ON CORE.TABLE1.REQ_ID=CORE.TABLE3.REQ_ID
    WHERE CORE.TABLE1.START_DATE>='01-JUN-2013'
    which appears to do what I want. The problem is that when I add
    AND CORE.TABLE1.START_DATE<'01-JUL-2013'
    to the bottom, I then get no return. do you know why this is?

    EDIT: if I change it to '02-JUL-2013' I get a response.... but '01-JUL-2013' gives me nothing =S
    Last edited by penfold1992; 09-24-13 at 09:09.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I suspect that START_DATE contains time along with date (so values in there are 01-jul-2013 07:30 and such). Therefore, try with
    Code:
    where trunc(start_date ...
    Note that you should ALWAYS use TO_DATE function with the appropriate format mask. Don't rely on implicit conversion; '01-JUL-2013' is a string, not a date. If you are lucky (i.e. your NLS settings make it possible), Oracle converts it to a date value and you eventually get a result, but that's bad practice. Use
    Code:
    where start_date < to_date('01.07.2013', 'dd.mm.yyyy')
    (or similar) instead.

  5. #5
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Littlefoot View Post
    I suspect that START_DATE contains time along with date (so values in there are 01-jul-2013 07:30 and such). Therefore, try with
    Code:
    where trunc(start_date ...
    Note that you should ALWAYS use TO_DATE function with the appropriate format mask. Don't rely on implicit conversion; '01-JUL-2013' is a string, not a date. If you are lucky (i.e. your NLS settings make it possible), Oracle converts it to a date value and you eventually get a result, but that's bad practice. Use
    Code:
    where start_date < to_date('01.07.2013', 'dd.mm.yyyy')
    (or similar) instead.

    I dont know why it kicks out the format in a "dd-mon/yyyy" format (or dd-mmm-yyyy) and i tried to_date('01.07.2013', 'dd/mm/yyyy') and I got something that appeared to work so thanks for that (however i have no idea how i was supposed to know the format... and i have access to another database which calculates the date but inserts it in as a string... which is why i just used a string this time too

    I will also add the trunc too, but this is working fine,
    WHERE START_DATE>= to_date('01.06.2013', 'dd/mm/yyyy')
    AND START_DATE< to_date('01.07.2013', 'dd/mm/yyyy')

    thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >which appears to do what I want. The problem is that when I add
    >AND CORE.TABLE1.START_DATE<'01-JUL-2013'
    >to the bottom, I then get no return. do you know why this is?
    >EDIT: if I change it to '02-JUL-2013' I get a response.... but '01-JUL-2013' gives me nothing


    when SQL contains the line below
    >WHERE CORE.TABLE1.START_DATE>='01-JUN-2013'
    and you then include the line below
    >AND CORE.TABLE1.START_DATE<'01-JUL-2013'

    how can the same DATE value be greater than or equal to '01-JUN-2013' and less than '01-JUN-2013' CONCURRENTLY?
    Since the two conditions are mutually exclusive, this is why no rows are returned when coded like above
    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.

  7. #7
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by anacedent View Post
    when SQL contains the line below
    >WHERE CORE.TABLE1.START_DATE>='01-JUN-2013'
    and you then include the line below
    >AND CORE.TABLE1.START_DATE<'01-JUL-2013'

    how can the same DATE value be greater than or equal to '01-JUN-2013' and less than '01-JUN-2013' CONCURRENTLY?
    Easy, because you thought it was 01-JUN-2013 < x > 01-JUN-2013

    but actually... i wrote 01-JUN-2013 < x > 01-JUL-2013

  8. #8
    Join Date
    Dec 2012
    Posts
    63
    [QUOTE=Littlefoot;6602959]I suspect that START_DATE contains time along with date (so values in there are 01-jul-2013 07:30 and such). Therefore, try with
    Code:
    where trunc(start_date ...
    I am not sure what is meant here...
    I tried to use:
    Code:
    WHERE TRUNC(CORE.TABLE1.START_DATE) >= to_date('01.06.2013', 'dd/mm/yyyy')
    QUOTE]

    to try to include all of them but I got an error. I also tried:
    Code:
    WHERE TRUNC(CORE.TABLE1.START_DATE,'DDD')
    but the same thing

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Man, go to sleep, you are doing and talking nonsense.

    JUN is, obviously, not JUL (as you already noticed).

    You tried TO_DATE('01.06.2013', 'dd/mm/yyyy') ? Why do you mix dots with slashes?

    Why exactly did you TRUNC 'DDD'; what did you want to do by saying that?

  10. #10
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Littlefoot View Post
    Man, go to sleep, you are doing and talking nonsense.

    JUN is, obviously, not JUL (as you already noticed).

    You tried TO_DATE('01.06.2013', 'dd/mm/yyyy') ? Why do you mix dots with slashes?
    how can the same DATE value be greater than or equal to '01-JUN-2013' and less than '01-JUN-2013' CONCURRENTLY?

    This is what was said by anacedent... not me.
    I use dots because for some reason writing '01/06/2013' came back blank, i dont know why this happened for the same reason i dont know why using
    '02-JUL-2013' worked but '01-JUL-2013' also came back blank.


    [QUOTE=Littlefoot;6602996]
    Why exactly did you TRUNC 'DDD'; what did you want to do by saying that
    [QUOTE]
    I wanted to remove all the hours, minutes and seconds from each 'START_DATE' so that I could compare them to see if they were > 01/06/2013.

    Code:
    WHERE TRUNC(CORE.TABLE1.START_DATE) >=
    I thought this would be the way to remove the hours minutes and seconds from the dates before it did its comparison.

    I assumed that if I did this instead
    Code:
    WHERE CORE.TABLE1.START_DATE >= TRUNC(to_date('01.06.2013', 'dd/mm/yyyy'))
    it would attempt to remove the hours minutes and seconds off the 01.06.2013 date which would make no difference because having just a date assumes the time 00:00:00 anyway. I didnt want to truncate the 01.06.2013, i wanted to truncate the dates which were being compared.

  11. #11
    Join Date
    Jul 2009
    Posts
    8
    I would suggest you find out what type of field was used to store the date. It could be a string field or it could be a date/time field. Type typing in this to see a description of the table:

    Desc CORE.TABLE1

    This should give you the description of all the fields in the database and you can see what type of field you are dealing with.

    If it shows the field as date/time then you must deal with Oracle's date/time formats. Oracle uses a number to represent date/time similar to what the show star trek did. It was like 2738.23. When you select out the data oracle automatically changes that number to a corresponding date/time. To see how to convert back and forth to compare on a date/time field check out this:

    https://forums.oracle.com/thread/686...art=0&tstart=0

Posting Permissions

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