|
Thanks, for some reason I missed that!
Hate when it happens 
|
|
|
|
|
|
Hi,
I have a table that looks like this:
Name Job Salary
Jim Eng 50000
Jane NULL 100000
Bill NULL 55000
Bill Dev 55000
I need a query that would strip out the rows that have NULLs for 'Job' if there's a row with the same 'Name' with a non NULL 'Job'.
The result would look like this:
Jim Eng 50000
Jane NULL 100000
Bill Dev 55000
I could easily do this in C# after I bring all the rows in, but I thought it would be more interesting to get the right data with a query.
Any ideas?
thanx
|
|
|
|
|
SELECT * FROM MyTable<br />
WHERE Job IS NOT NULL<br />
OR Name NOT IN (SELECT NAME FROM MyTable WHERE Job IS NOT NULL)
|
|
|
|
|
thanks Andy, that was it!
|
|
|
|
|
Hi
can u help me?
i have created an Entity Relation Diagram in Visio Enterprise Architect 2007. Now i want to export the ERD from Visio to SQL Server 2005.I don`t know how to do that?
I need help on this stage.
Thanks.
|
|
|
|
|
Hi all
i am having problems with a case statment within my query, i want to list a series of dates and times, and then list against it if it is AM or PM
im trying to do something like the below, but im getting an error
anyone any ideas ??
select mydatetime,
CASE mydatetime
WHEN datepart(hour,mydatetime) > 11 THEN 'PM'
ELSE 'AM'
as exp1
from mytable
thanks
simon
|
|
|
|
|
DOH !!!
select mydatetime,
CASE
WHEN datepart(hour,mydatetime) > 11 THEN 'PM'
ELSE 'AM'
as exp1
from mytable
|
|
|
|
|
CASE statemet to be terminated with END
which is missing in your select query
Regards
KP
|
|
|
|
|
Hi,
Try this code:
select mydatetime,
'exp1' =
case
when datepart(hour,mydatetime) > 11 THEN 'PM'
else 'AM'
end
from mytable
|
|
|
|
|
Hi
We are using reporting service,
How to change the reporting services column header,headers...etc
into different language, in c# we use resource file and use this
resource file to change different language(multilingual Option).
How can change these into reporting services.
Regards
Arunkumar Sundaravelu
|
|
|
|
|
Hey
I have written the following the stored procedure and executed it.But i am getting the following error. I don't know the reason for this.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[GSU_Site_ReterieveActiveSitesOnSearch]
@whereClause nvarchar(2000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @sqlstr as varchar(max)
set @sqlstr='SELECT Site.siteid as siteid,'
set @sqlstr=@sqlstr+ 'Site.Sitename as sitename, '
set @sqlstr= @sqlstr+ 'Customer.customerid,'
set @sqlstr= @sqlstr+ 'Customer.customername as CustomerName,'
set @sqlstr= @sqlstr+ 'Site.City as City,'
set @sqlstr= @sqlstr+ 'site.Address as Address,'
set @sqlstr =@sqlstr+ 'Site.state , '
set @sqlstr= @sqlstr+ 'Country.countryid as countryid,'
set @sqlstr= @sqlstr+ 'Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,'
set @sqlstr= @sqlstr+ 'SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country '
set @sqlstr= @sqlstr+ 'ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid '
set @sqlstr= @sqlstr+ 'INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON '
set @sqlstr= @sqlstr+ 'SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON Site.GSUStatusID=GSUStatus.GSUStatusID '
set @sqlstr= @sqlstr+@whereClause
--
--set @sqlstr=@sqlstr+' WHERE GSUStatus.GSUStatusID=' +@GSUStatusID
--if @BusinessUnitID <> 0
--set @sqlstr=@sqlstr+'and site.BusinessUnitID ='+@BusinessUnitID
--if @CountryID <> 0
--set @sqlstr=@sqlstr+'and site.countryid='+@CountryID
--if @CustomerID <> 0
--set @sqlstr=@sqlstr+'and site.customerid='+@CustomerID
--if @SystemTypeID <> 0
--set @sqlstr=@sqlstr+'and site.SystemTypeID='+@SystemTypeID
--if @SiteName <> ''
--set @sqlstr=@sqlstr+'and site.Sitename like ' + @SiteName
--if @Address <> ''
--set @sqlstr=@sqlstr+'site.Address like '+ @Address
--if @City <> ''
--set @sqlstr=@sqlstr+'site.City like '+ @City
--if @State <> ''
--set @sqlstr=@sqlstr+'and site.state like '+ @State
print @sqlstr
exec @sqlstr
END
I executed the procedure by pasing parameters
Exec [GSU_Site_ReterieveActiveSitesOnSearch]
" where GSUStatus.GSUStatusID=1 and site.Sitename like 'lakshmisite' "
and getting the following error
- exc {"The name 'SELECT Site.siteid as siteid,Site.Sitename as sitename, Customer.customerid,Customer.customername as CustomerName,Site.City as City,site.Address as Address,Site.state , Country.countryid as countryid,Country.countryname as country,Businessunit.businessunitid ,businessunit.businessunitname as BUName,SystemType.SystemTypeID,SystemType.SystemTypeName FROM Site INNER JOIN Country ON Country.countryid = Site.countryid INNER JOIN Customer ON Customer.customerid=Site.customerid INNER JOIN Businessunit ON Businessunit.businessunitID=Site.BusinessUnitID INNER JOIN SystemType ON SystemType.SystemTypeID=Site.SystemTypeID INNER JOIN GSUStatus ON S' is not a valid identifier."} System.Exception {System.Data.SqlClient.SqlException}
Please let me know the problem in this.
Thanks
Kusuma
|
|
|
|
|
|
may be you have definied only one parameter and in the execution statement you have give two parameters
PPK
|
|
|
|
|
Hi fiends
i have login table in sqlserver2000.
1000 data include in that tabe
now i change the system.create login table.
how can i include all the data in easy way..
can u help me
saravanan
|
|
|
|
|
I'm not getting you.
is that you are trying to copy rows from one table to another.
Regards
KP
|
|
|
|
|
hi
i have created the index in order col1, col2, now if in my application or on query analyzer i write the script
select * from table1 where col2 = 'value 2' and col1 = 'value 1'
will the index be used or not as order is changed in my srcipt. i want to know about both sql server and oracle.
Regards
Shajeel
|
|
|
|
|
This sounds suspiciously like homework to me, but I'll answer it anyway.
If you have created two indexes, then they will most likely be used. Note that the usage depends entirely on the query execution plan that the DB server determines is the best usage. Suppose that there are only 2 possible values in col2, then the engine may determine that it needs to do a sequential scan because there is not enough differentiation in the data to allow it to perform an indexed scan. Similarly, if there are only a few rows in the table then it may perform a sequential scan because the engine has determined that the row count is too few to allow it to index search efficiently.
As always, the best way to find out the answer is to try it out yourself.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
i think i explained it wrong i have not created two indexes, i have created only one index with two columns like
CREATE UNIQUE INDEX Index1
ON table1 (col1, col2)
so in order col1 is first and col2 is second. in where i used
where col2 = 'val 2' and col1 = 'val 1' i.e. filter in wrong order.
Regards
Shajeel
|
|
|
|
|
Hi Shajeel
It shouldn't matter which way round you have the where-clause, because SQL-Server (and Oracle) are able to recognise that the index is potentially applicable (because both RDBMS products use a cost-based optimiser). The main criteria as-to whether the index will be used will be how selective the index is (i.e. how many records does SQL-Server think will be returned from the query based on its statistics).
Regards
Andy
|
|
|
|
|
I have faced a problem to write a update statement in SQL server 2005.Is it possible to use the alias name for a table in the update statement? If not then why?
|
|
|
|
|
I'me not sure if it is possible in SQL 2005, but it is in SQL 2000. You query should look like this:
<br />
Update tableName<br />
Set ...<br />
From tableName alias ...<br />
Wout Louwers
|
|
|
|
|
Hi all--I tried searching for this, but it was hard to know what to search on...
I have two users, say User and Update. Update comes in once a day and does a bunch of stuff, but I need to make sure it doesn't do anything until any existing Users close their connections (or else data will probably be compromised).
And second, I need to ensure that while Update is doing its thing, all other users will have their connection requests denied. Then after Update is finished, he'll restore connection abilities to the Users.
Can anyone tell me how to accomplish this? Or maybe point me in the right direction?
Thanks a lot,
John
|
|
|
|
|
Does this have to be done during the day or can you set a logoff time and run the Update overnight?
__________________
Bob is my homeboy.
|
|
|
|
|
I have a DataSheet form that has Adds Allowed and Edits Disabled. For cells of existing records that are using comboboxes to select from a lookup table the combobox still drops although the user can't make a new selection and then close it. Is there a setting that will keep the CB from dropping in the same way that no new text can be typed into textfields at all.
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
