Home » SQL & PL/SQL » SQL & PL/SQL » How to create a simple join query?
icon9.gif  How to create a simple join query? [message #239869] Wed, 23 May 2007 09:03 Go to next message
asparrow
Messages: 8
Registered: May 2007
Junior Member
Embarassed
Hi Everybody....

I am REALLY new to Oracle but have a need to determine who, from a population of users, has created records of a certain type over the same time duration.

Here's the query that gives me the users I need over the timeframe:

select *
from test.ldap
where to_date(creation_date) > '31-DEC-2006'
and to_date(creation_date) < '1-APR-2007'
and approval_status = 'APPROVED'

and here's the query that gives me the record types I need over the same time period:

select *
from shiptest.bol_base
where bol_type = 'BOL'
and origin_flag <> 'LB'
and to_date(creation_date) > '31-DEC-2006'
and to_date(creation_date) < '1-APR-2007'
and status <> 'I'
and rbill is not null

There is a field in shiptest.bol_base called creator that corresponds to the test.ldap userid field, so I'd need to pull the records where test.ldap(userid) is equal to shiptest.bol_base(creator).

Please help....

Thanks!
Re: How to create a simple join query? [message #239874 is a reply to message #239869] Wed, 23 May 2007 09:21 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
asparrow wrote on Wed, 23 May 2007 10:03
Embarassed
Hi Everybody....

I am REALLY new to Oracle but have a need to determine who, from a population of users, has created records of a certain type over the same time duration.

Here's the query that gives me the users I need over the timeframe:

select *
from test.ldap
where to_date(creation_date) > '31-DEC-2006'
and to_date(creation_date) < '1-APR-2007'
and approval_status = 'APPROVED'



Since you're new, you might as well start using the correct way to use DATEs in queries before you get into a bad habit. Please use the TO_DATE function on character strings such as '31-DEC-2006'.

In your case above, it is impossible for a value to be greater than the character string "3" and less than the character string "1," at least not anywhere other than the Bizzaro world.

Additionally, if creation_date is a DATE column, using a TO_DATE function on it can only cause problems.
Re: How to create a simple join query? [message #239875 is a reply to message #239869] Wed, 23 May 2007 09:22 Go to previous messageGo to next message
Snowblitzz
Messages: 8
Registered: May 2007
Junior Member
try:

select *
from test.ldap t, shiptest.bol_base st
where to_date(t.creation_date) > '31-DEC-2006'
and to_date(t.creation_date) < '1-APR-2007'
and t.approval_status = 'APPROVED'
and st.bol_type = 'BOL'
and st.origin_flag <> 'LB'
and st.to_date(creation_date) > '31-DEC-2006'
and st.to_date(creation_date) < '1-APR-2007'
and st.status <> 'I'
and st.rbill is not null
and t.userid = st.creator

or

select *
from test.ldap t, shiptest.bol_base st
where to_date(t.creation_date) > '31-DEC-2006'
and to_date(t.creation_date) < '1-APR-2007'
and t.approval_status = 'APPROVED'
where t.user in (
select creator
from shiptest.bol_base st
where st.bol_type = 'BOL'
and st.origin_flag <> 'LB'
and st.to_date(creation_date) > '31-DEC-2006'
and st.to_date(creation_date) < '1-APR-2007'
and st.status <> 'I'
and st.rbill is not null
)

Note: Oracle knows a lot of date formats, you have to declare witch to use at some points, check oracle date formats on the oracle website, it can be usefull.

I didnt change anything about them couz i expect you tryed to run these and they worked.

[Updated on: Wed, 23 May 2007 09:25]

Report message to a moderator

Re: How to create a simple join query? [message #239876 is a reply to message #239875] Wed, 23 May 2007 09:23 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
And you have made the same mistake of comparing DATEs to character strings. Not a good way to teach someone who is just starting out.
Re: How to create a simple join query? [message #239879 is a reply to message #239876] Wed, 23 May 2007 09:29 Go to previous messageGo to next message
Snowblitzz
Messages: 8
Registered: May 2007
Junior Member
just added some information about it why i left the dates they way they are, its more the way how i use it then how the dates are set.

--- Learn by helping them answer there questions and let them learn from there mistakes... if it doesnt work they ask again Wink
Re: How to create a simple join query? [message #239889 is a reply to message #239876] Wed, 23 May 2007 09:56 Go to previous messageGo to next message
asparrow
Messages: 8
Registered: May 2007
Junior Member
Thanks for your help - I'll ask about the date formatting on this end to find a better way to do it!
icon14.gif  Re: How to create a simple join query? [message #239904 is a reply to message #239879] Wed, 23 May 2007 10:50 Go to previous messageGo to next message
asparrow
Messages: 8
Registered: May 2007
Junior Member
Hi -

Just wanted to let you know that I was able to use your queries and make it work. Thanks for showing me how to do it! Cool
Re: How to create a simple join query? [message #239934 is a reply to message #239904] Wed, 23 May 2007 12:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Could you please show us your query?
I am curious what you learned from joy_division's remarks.
Re: How to create a simple join query? [message #239938 is a reply to message #239934] Wed, 23 May 2007 12:49 Go to previous messageGo to next message
asparrow
Messages: 8
Registered: May 2007
Junior Member
Hello,

I used snowblitzz's

select *
from test.ldap t, shiptest.bol_base st
where to_date(t.creation_date) > '31-DEC-2006'
and to_date(t.creation_date) < '1-APR-2007'
and t.approval_status = 'APPROVED'
and st.bol_type = 'BOL'
and st.origin_flag <> 'LB'
and to_date(st.creation_date) > '31-DEC-2006'
and to_date(st.creation_date) < '1-APR-2007'
and st.status <> 'I'
and st.rbill is not null
and t.userid = st.creator

and it worked, so the thanks for the help was directed to snowblitzz.

I have sent an email to our DBA group here at work requesting more info on how to use dates in queries, but have not heard anything back yet ( I was using what they'd provided earlier for the to_date field). I am not sure what joy_division meant regarding the value not being greater than three or less than one, but am willing to learn.
Re: How to create a simple join query? [message #239946 is a reply to message #239938] Wed, 23 May 2007 13:04 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
But you have not used the advice I have given.

This is invalid:
where to_date(t.creation_date) > '31-DEC-2006' 


Is MUST be like this:
where t.creation_date > to_date('31-DEC-2006','DD-MON-YYYY') 


It "may" have worked for you by accident/luck/your personal session settings, but do this and try it again:
alter session set nls_date_format='MM/DD/YYYY';
and it won't work anymore. What happens when someone changes the NLS parameter at the database level. All your code will stop working.

And once again, I'll put it in bold so you see it. Do NOT use TO_DATE on a DATE column. It can never help and can only hurt.
Re: How to create a simple join query? [message #239948 is a reply to message #239938] Wed, 23 May 2007 13:09 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
asparrow wrote on Wed, 23 May 2007 13:49
Hello,
I am not sure what joy_division meant regarding the value not being greater than three or less than one, but am willing to learn.



Your original code is:
where to_date(t.creation_date) > '31-DEC-2006'
and to_date(t.creation_date) < '1-APR-2007'


The STRING '31-DEC-2006' comes alphabetically after the STRING '1-APR-2007'. Therefore there is no possible value that can be both greater than the string that starts with the character "3" and less than a string that starts with the character "1."

Look:
FOO SCOTT>l
  1  with data as (select '31-DEC-2006' invalid_date from dual
  2  union
  3  select '1-APR-2007' from dual)
  4  select * from data
  5* order by invalid_date
FOO SCOTT>/

INVALID_DAT
-----------
1-APR-2007
31-DEC-2006

[Updated on: Wed, 23 May 2007 13:14]

Report message to a moderator

Re: How to create a simple join query? [message #239958 is a reply to message #239946] Wed, 23 May 2007 14:02 Go to previous messageGo to next message
asparrow
Messages: 8
Registered: May 2007
Junior Member
Hello,

Thank you for your perserverance to show me the light. I appreciate your clarification of the alphabetic listing regarding 1 versus 3.

I did not use your original advice because another contributor to the topic did not use the syntax you suggested, and the query worked.

Since your last emails, I have re-run it using both the original syntax as well as that which was modified according to the methods you provided.

I am getting the same set of 157 records in both cases. Perhaps it is just a fluke, or maybe the DBAs have set the NLS parameter to something that allows the invalid queries - I don't know. Perhaps, because I'm using Toad it's doing some kind of conversion of its own?

I will try to use the correct methodology from this point forward. Again, thank you for your assistance and clarifications.
Re: How to create a simple join query? [message #239966 is a reply to message #239958] Wed, 23 May 2007 14:37 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
asparrow wrote on Wed, 23 May 2007 15:02

I am getting the same set of 157 records in both cases. Perhaps it is just a fluke, or maybe the DBAs have set the NLS parameter to something that allows the invalid queries


Yes, currently it works. Any change to the NLS setting will cause it to fail. Different clients will have their own setting (TOAD, SQL Developer, SQL*Plus, etc.) so why not prevent your code from failing by using the correct method for dealing with DATEs? All you need is a new DBA to come in who thinks the date format should be different and then all your code has to be rewritten.
If you are new to Oracle then it should be easy. It's those who have been writing poor code for so long that will not be able to adapt due to their comfortable ways of coding.
Re: How to create a simple join query? [message #240025 is a reply to message #239934] Wed, 23 May 2007 23:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Wed, 23 May 2007 19:38
Could you please show us your query?
I am curious what you learned from joy_division's remarks.

Looks like a narrow escape, but I think joy has another soul rescued!
Re: How to create a simple join query? [message #240062 is a reply to message #239869] Thu, 24 May 2007 01:04 Go to previous messageGo to next message
Snowblitzz
Messages: 8
Registered: May 2007
Junior Member
Might be you select wrong dates...
Something about oracle and dates

if u use a string like '24-05-2007' or '24 may 2007' you know it is a date. but oracle will see it as a string.

if you are compairing a date field it can give errors but in some cases it just compairs them as strings and that way you will have dates in it that can differ from what you should say

like
date < '22-02-2005' --> can give as result '12-01-2007'
wondering why? well the string is indeed based on lower characters. (i think < and > is a bad use on strings)

just be shure you compair 2 Oracle formated dates with eachother. The default dateformat oracle uses can be differend on each instance (database). Its how its set up but most commenly is used: 'DD-MM-YYYY HH24:MI:SS' (as far as i know)

remember that dates can have times and if you set a string to date and dont add times the time will be set to 00:00:00 at default.

For example if you have a timefield in your database from today witch is like made an hour ago (say '24-05-2007 06:55:23') and you want to ask with a query everything from today and back.

if you should say select where < '24-05-2007' you will get every day BEFORE today (<= also doesnt work). just use < '25-05-2007' instead. you notice you wont get any 25-04-2007 dates in it (even if they excist)
this becouz there BELOW '25-04-2007 00:00:00'

Be shure not to compair dates as string you wont get good results
Be shure that you dont compair 1 string and 1 date also no good
To be save on every front compair date with date (pref. in the same format)

For formatting i find usefull: Trunc, to_date and sometimes to_string (in combination with to_date)

For more information on outputformats look at:
http://www.techonthenet.com/oracle/functions/to_date.php
http://www.techonthenet.com/oracle/functions/trunc_date.php

Also usefull: http://www.psoug.org/reference/date_func.html

hope its usefull in a way.

Added:
Also very usefull if you use
-- where to_date(t.creation_date) > '31-DEC-2006'
-- and to_date(t.creation_date) < '31-DEC-2006'
you can also set
-- where to_date(t.creation_date) between '31-DEC-2006' and '31-DEC-2006'
note: (example as original query, no improvents on dates made)

Hint: on the 157 result, try storing them both and compairing them to see if there exactly the same.

Quote:
It's those who have been writing poor code for so long that will not be able to adapt due to their comfortable ways of coding

Very Happy hehe, cant be anything else then the truth

[Updated on: Thu, 24 May 2007 01:14]

Report message to a moderator

icon7.gif  Re: How to create a simple join query? [message #240236 is a reply to message #240062] Thu, 24 May 2007 07:46 Go to previous message
asparrow
Messages: 8
Registered: May 2007
Junior Member
Hi -

It's most helpful, as was your query yesterday. Thanks for all the great links and advice. I really appreciate it!
Previous Topic: SYSDATE from another database, how to get?
Next Topic: Datapump can't run in stored procedure
Goto Forum:
  


Current Time: Thu Dec 08 08:34:57 CST 2016

Total time taken to generate the page: 0.09765 seconds