|
Hi,
I would like to know which is the most latest and good way of writing SQL Joins. Is it by using *=, =* etc.
eg:
select distinct * from employee,department
where employee.DepartmentID *=department.DepartmentID
( or)is it ANSI Standard?
Eg: SELECT distinct *
FROM employee
LEFT OUTER JOIN
department
ON employee.DepartmentID = department.DepartmentID
Please help.
rmr
|
|
|
|
|
shivarishxxxx wrote: select distinct * from employee,department
where employee.DepartmentID *=department.DepartmentID
This type of join is slowly being phased out. The Inner/Outer join is the way to go for you.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks for your prompt reply.
rmr
|
|
|
|
|
Does anybody know how to get the sql log information?
Thanks,
Rob
http://www.vwsolutions.com
|
|
|
|
|
Hello,
I have the following query; the results I need are returned however so are null values.
I need to exclude any results that are not defined in the case function.
select
(case when status like 'dialing%' then dtime end) as startcall
, (case when status like 'line%' then dtime end) as endcall
, (case when status like 'line%' then trunk end) as trunk
from
Table1
All of my references mention boolean logic, however; I am not sure how to apply it or if that's the right approach.
Any help or information would be greatly appreciated.
Regards,
Hulicat
|
|
|
|
|
Hulicat wrote: I need to exclude any results that are not defined in the case function
Then shouldn't it go in a WHERE clause?
Since I don't actually know what you are trying to achieve I cannot say further what the WHERE clause should look like.
|
|
|
|
|
I am trying to obtain only the results defined in the where clauses without any nulls
Sample of Current Results:
StartCall……………...........EndCall………………………………..........Trunk
NULL……... ..................12/4/2006 3:09:48 PM………..………. 8
NULL…………… .............NULL……………………………. ............NULL
NULL……………….. ...............NULL……………………………. ............NULL
NULL……………. ............NULL……………………………. ............NULL
NULL…………….. ............NULL……………………………. ............NULL
NULL…………..................NULL……………………………. ............NULL
NULL………………. ..........NULL……………………………. ............NULL
NULL…….......…............NULL……………………………. ............NULL
12/4/2006 3:27:57 PM….....NULL……………………………. ............NULL
NULL…….. ………...........NULL……………………………. ............NULL
NULL…….. ………...........NULL…………………………….. ............NULL
Desired output:
Startcall……………………….........EndCall…………………………...........Trunk
12/4/2006 3:27:57 PM……....…12/4/2006 3:09:48 PM……....…….8
Regards,
Hulicat
|
|
|
|
|
select CallId,
max(case when status like 'dialing%' then dtime end) as startcall,
max(case when status like 'line%' then dtime end) as endcall,
max(case when status like 'line%' then trunk end) as trunk
from Table1
group by CallId
order by CallId
This assumes that all of the records for a single phone call share a unique identifier named "CallId".
|
|
|
|
|
I have a sqlconnection to a sql server 2005.
This server is in one server and my application is in another server.
I create a userid and password but when i try to connect to my database an error saying
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
appears. I do not know what is the problem for this.
Can somebody help me
Thanks
Javierarka
|
|
|
|
|
Your connection string requests a trusted connection (this overrides any user name and password) and it can't create one for some reason or another. Possibly because the SQL Server is on another machine and you are connecting with a machine account rather than a domain account.
|
|
|
|
|
In VS2005 you could go to Server Explorer and set up a successful connection with your target database. Once you have established a successful connection you can then click on the server and the properties dialog will show the ConnectionString . You can put this in your code to validate that the connection works and then use the supplied string to target where your error is at.
|
|
|
|
|
Hi all,
I have a stored procedure which runs on Sql Server 2005 which returns 5 rows representing exchange rates. If I call this procedure in Sql Server management studio it responds in about a second. If however I do this using ADO.NET, it takes many times longer and sometimes times out.
Incidentally, there is a table which contains over 100 million rows used in a join in this proc which could point to performance issues, but as I say it works ok within Management studio.
Any ideas?
ADO code below...
using (SqlConnection conn = new SqlConnection(Utils.ConnectionString))
{
conn.Open();
SqlCommand command = new SqlCommand("FxGetRates", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@date", SqlDbType.SmallDateTime).Value = valueDate;
List<FxRate> results = new List<FxRate>();
using (SqlDataReader reader = command.ExecuteReader())
{
int ord_assetId = reader.GetOrdinal("AssetId");
int ord_domesticcurrencyid = reader.GetOrdinal("DomesticCurrencyId");
int ord_foreigncurrencyid = reader.GetOrdinal("ForeignCurrencyId");
int ord_marketdatasourceid = reader.GetOrdinal("MarketDataSourceId");
int ord_rate = reader.GetOrdinal("Rate");
while (reader.Read())
{
FxRate result = new FxRate();
result.AssetId = reader.GetInt32(ord_assetId);
result.DomesticCurrencyId = reader.GetInt32(ord_domesticcurrencyid);
result.ForeignCurrencyId = reader.GetInt32(ord_foreigncurrencyid);
result.MarketDataSourceId = reader.GetInt32(ord_marketdatasourceid);
result.Rate = reader.GetDouble(ord_rate);
results.Add(result);
}
}
return results.ToArray();
Regards,
Rob Philpott.
|
|
|
|
|
Roughly how many rows would you expect this to return? It looks as though the problem is in the creation of the FxRate rows and the addition of it to the List.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks for the reply.
Just five. And when debugging its apparant that the time is spent on the ExecuteReader statement, not in getting the values out from it later on.
Regards,
Rob Philpott.
|
|
|
|
|
OK - this is where I do my shameless plug and point you towards your new best friend;) - SQL Profiler. This article Improving the performance of queries with SQL Server Part 1[^] should be able to help you (or at least point you in the right direction).
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Have you tried single-stepping using the debugger? If the performance problem is not your stored procedure then perhaps opening the connection that is slow.
|
|
|
|
|
what is the query for removing table space in oracle
Warm Regards,
Mushq
|
|
|
|
|
|
hi ,
i want to send email thru sql server, how can i do this
sdfsdfsdfs
|
|
|
|
|
hi
This is the syntax for sending email in sql server 2005
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
regards
dharani
|
|
|
|
|
How to get different result/row of following select query :
Select * from Countries where country='India'
or
Select * from Countries where country='INDIA'
means Case-Sensitive searching in Character column.
Thanks in Advance
Regards
Girish Kumar Sharma
|
|
|
|
|
User UPPER/LOWER as below:
select upper('AsGas'),lower('AsGas')
if it doesn't solve your problem explain more...
Human knowlege belongs to the world
|
|
|
|
|
i mean; if there are two rows named "India" and "INDIA"; and if i want to get "India" then "INDIA" should not be there; and if i want "INDIA" then "India" should not be there.
Only given string should be in selection criteria please.
Data is in MS-Access.
Thanks in Advance.
Girish Kumar Sharma
-- modified at 6:34 Tuesday 16th January, 2007
|
|
|
|
|
You could cast is as a VARBINARY and compare the binary versions. I've never tried it, but you could give it a go:
SELECT *
FROM MyTable
WHERE CAST(MyColumn AS VARBINARY(100)) = CAST('SomeValue' AS VARBINARY(100));
|
|
|
|
|
Colin Angus Mackay wrote: SELECT * FROM MyTable WHERE CAST(MyColumn AS VARBINARY(100)) = CAST('SomeValue' AS VARBINARY(100));
Hi
Just curious to know if cast works on MS Access database?
Thanks
Chandra
|
|
|
|