|
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 13-Feb-13 6:55am.
|
|
|
|
|
By the way, 50 km is about half a degree. Depending on the amount of rows in your table, it might be wise to filter for longitude between -122.5 and -121.5 and latitude between 36.5 and 37.5 first.
Also a simple transformation with 110 km/degree for latitude, and 110 * cos(latitude) for longitude, and then using simple pythagoras might speed up the query.
|
|
|
|
|
Thanks for your input. Yes I am adding some filtering now. I just needed to get the thing working first.
|
|
|
|
|
To add to Jorgen's answer, I've always wondered why SQL Server doesn't allow us to use alias in WHERE and HAVING clauses. The answer to that lies in the logical order in which the query is processed. The WHERE and HAVING clauses are processed before the SELECT clause and the alias do not exist at that stage.
However, technically it should be possible to introduce another stage earlier in the query processing pipeline where a mapping between expressions and their alias is made and WHERE and HAVING clauses can look up to these mappings and substitute the actual expression in place of the alias.
|
|
|
|
|
You are looking for CTE.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Yes, or a simple
SELECT ... FROM
(
SELECT ... FROM table WHERE ...
) T
...
|
|
|
|
|
Yes, thats equivalent. I just prefer the readability of the CTE (which is merely an opinion) plus that you can refer to a CTE in more than one place.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Ado.net is the Library in .net technology. this library help to you for connect your application to the database.
- Now Come in depth
C# is a language. and responsible to create the User Interface like Console, Web, and Window Application.
SQL is also language for create the database and database store the data.
Suppose i need to connect my Front-End to the Back-End.
or We say that C# Communicate to the SQL. now its not possible to two different language communicate to each other. Simple Example is
One Person is Russian and second is Indian now they want to talk to each other. Big Problem Occur when they want to communicate because Russian not known the Hindi language and similar Indian not know the Russian Language. now both person need to mediator or Converter. Converter convert the language means for Indian to Hindi and Russian to Russian Language.
-----Ado.Net Mediator between the Front-End to the Back-End-----
Ado.Net is provider to make the connection between the Front-End to the Back-End.
Thank you
Student of Dr. Sandeep Karan CAC Noida
Himanshu Sharma
|
|
|
|
|
And what is your question?
|
|
|
|
|
That's not a question, it's a Tip (or atleast he thinks so)
|
|
|
|
|
This is really a great tip for the beginners.
i thank you from all the Beginners those who need a Elaborated definition to understand the concepts..
|
|
|
|
|
|
What is the difference between truncating and deleting a table?
After the truncating or deleting do we need to re-index or shrink the table?
We are looking to do some clean-up on some rather large tables on our database. Once we do the clean-up what steps do we need to preform for overall database "health"?
Thanks!
|
|
|
|
|
mrfalk wrote: re-index
If there's no records left, there's little to reindex.
mrfalk wrote: What is the difference between truncating and deleting a table?
Google "MSDN Truncate", and from the manual we learn;
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
So if we are using the Delete statement with the WHERE clause is it necessary to reindex? Is there a "standard" for when to reindex files?
After further research we will be using the delete clause because we want to retain a given number of days data. Within that given number of days data we will also be deleting specific rows based on a given parameter. We just want to make sure we are covering all bases.
|
|
|
|
|
mrfalk wrote: So if we are using the Delete statement with the WHERE clause is it necessary to reindex?
Yes.
mrfalk wrote: Is there a "standard" for when to reindex files?
No.
Start here[^].
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Truncate statement:
1. Deletion is very faster. Almost no (very minimal) transaction log is produced during this operation.
2. Data pages which are used by the table are de allocated for further use by SQL Server in other operations.
3. Identity column value is reset from its original default position. For example if a table has an identity column and identity value start form 1 then after running truncate command the value start incrementing back from 1.
4. As wiping out rows are very faster, so the number of locks are low. Although during TRUNCATE operation table and page lock happens but not each row.
5. TRUNCATE TABLE command does not support where clause, it also does not works if foreign key exists in the table. In addition, table participates in log shipping or replication also does not honor TRUNCATE table command.
6. Records removed by the TRUNCATE COMMAND cannot be restored even though the database recovery model is set to FULL.
7. TRUNCATE statement also does not fire triggers.
DELETE Statement:
1. The DELETE statement removes rows one at a time. For each deleted row the operation writes an entry in the transaction log.
2. DELETE operation is more resource intensive thus consumes more database resources and locks.
3. Where clause can be included with the statement to restrict the number of affected rows.
4. Internally the DELETE operation does not cleanup rows immediately if the table has any index on it, The operation marks the affected rows "to be deleted". The marked records are known as GHOST RECORDS. Although these records are de allocated quickly by a background cleanup process for better performance.
dsdf
|
|
|
|
|
Truncating will also reset the seed for any identity column, whereas delete will not.
-NP
Never underestimate the creativity of the end-user.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
