
Please do not post questions asking people to write your code for you. Members of this site come here to help people who make an effort to learn and do their own work. If you do not know how to begin to create your project then find some online study guides, or buy some books.





If you are looking to get the code written then try Elance or one of the other code writing sites. CodeProject is for people who want to write their own code and need a little help learning.
Never underestimate the power of human stupidity
RAH





Hi  I have having a join issue.
we have report which was set up a while ago, needs to be modified.
I have to add one more table, using a LEFT OUTER JOIN but using (+)!
I need to left outer join table B to table A, but it is a substring.
I tried which made sense syntax wise 
substr(A.source,1,4)=substr(B.OFFERNO,1,4)(+)
which did not work.
Below syntax did not error out  does this look right?
substr(A.source,1,4)=substr(B.OFFERNO(+),1,4)





From the Oracle language reference: The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
So the second syntax looks valid to me. Does it give you the expected results?
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull





You are right.
The second one worked for me.
Thank you!





If this is still confusing, you can create a view with the expression and then do your join on the view.





I am sure I am overlooking something very simple but I can't see it.
I have a database of locations with latitudes and longitudes. I need to be able to query for locations that fall within a given radius from a lat/lon point
The code I am intending to use is: (Showing fixed centre 38,118 in this version)
SELECT quakeid, (6371 * acos(cos(radians(38)) * cos(radians(latitude)) * cos(radians(longitude)  radians(118)) + sin(radians(38)) * sin(radians(latitude)))) AS [dkm]
FROM tblUSGSData
(Derived from Creating a store locator[^])
This works fine and returns just over half a million entries.
My problem is that as soon as I add
HAVING [dkm] <= 50
or
WHERE [dkm] <= 50
I get the message
Msg 207, Level 16, State 1, Line 3
Invalid column name 'dkm'.
I obviously have not got my SQL glasses on today as I can't seem to resolve this. Can any one make (polite) suggestions?





Well, [dkm] is an Alias, not a columnname.
You would need to have the same expression in the having or where clause as you have in the select clause.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull





Ah yes. My bad
SELECT quakeid, latitude, longitude, (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude)  radians(122)) + sin(radians(37)) * sin(radians(latitude))))
FROM tblUSGSData
GROUP BY quakeid, latitude, longitude
HAVING (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude)  radians(122)) + sin(radians(37)) * sin(radians(latitude)))) <= 50
works  at least does not produce an error. Not sure about the working bit but that is another story.
Many thanks.





Are you sure you need to use Haversines formula?
If you Radiuses are small enough the Cartesian distance formula would do, depending on what coordinate system you're using of course.
Ignore that question, I just remembered what USGS is, and realized it's a different sort of Quake you're working with.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
modified 13Feb13 6:55am.



