Hi,
In my database I have a field with the type decimal. In the select query I want to return true if this field is smaller than 1 and false if this field is 1. How can I do this?
I need something like that:
Select id, name, (mydecimalField < 1) from mytable
KaaN
I am using derived column to convert string data to datetime .My string data :
Start Date
15/06/2015
30/06/2015
NULL
2015/06/24
I wrote in derived column expression:
(DT_DATE)(SUBSTRING(([Start Date]),7,2) + "/" + SUBSTRING(([Start Date]),5,2) + "/" + SUBSTRING(([Start Date]),1,4))
which is giving error :
[Derived Column 1 [5452]] Error: The "component "Derived Column 1" (5452)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "Start Date" (5650)" specifies failure on error. An error occurred on the specified object of the specified component.
How to write the expression in derived column to handle null value as well.
I'm having problems executing TOP n queries on a database that was migrated from 6.5 to 7. I can get it to work on the Authors table in pubs, not in my other dbs. Here is an example:
CREATE TABLE dbo.tblsapParentCust (
Parent char (10) NOT NULL ,
Name varchar (40) NULL,
IsSoldTo bit NOT NULL DEFAULT 0,
CONSTRAINT PK_tblsapParentCust PRIMARY KEY CLUSTERED
(Parent)
)
GO
<load in some data>
SELECT TOP 10 * FROM tblsapParentCust
The select statement results in a syntax error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '10'.
I can switch over to pubs and change the query to reference the Authors table, and it runs fine.
If anyone can explain this behavior to me, I would appreciate it.
Thanks,
Buddy
I have 4 tables which I am extracting 2 distinct values;
Select Distinct UniId, PID from dbo.Event1
Select Distinct UniId, PID from dbo.Event2
Select Distinct UniId, PID from dbo.Event3
Select Distinct UniId, PID from dbo.Event4
Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)
Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .
I need to create a temporary table using dynamic queries and then i have to use the temporary table for data manipulatuion.
Can someone help me out on this.
EG
sp_executesql N'Select top 1 * into #tmp from table1'
select * from #tmp
Hi,
I wanted to know a query which will create a final result table from a combination of select queries.
The select query is like :
1. select col1 , col2 , null from table1
2. select null , col2 , null from table2
3. select null , null , col3 from table 3.
null are inserted as i wanted a single select query which will merge all the columns from all the tables and finally create a result table.
Thanks in advance.
I wrote a simple select query that counts the number of records I have in certain zip codes. How can I get a total of the "count" column at the bottom of the results? For example, my results may look like this:
ZIP | (no column name for "count")
_____________________________________
89502 | 10
89509 | 15
89521 | 25
What statement would I use to get the total of '50' displayed in the resluts? Thank you in advance
-Lance
Hi
Will somebody please explain how to combine asp.net dropdown lists to write
a SQL database select query. I am using VWdeveloper and C Sharp.
For example, say I have 3 dropdownlists on my webpage as below,
List 1, Cities, London, Rome, Barcelona etc
List 2, Restaurants by Type, Italian, chinese, Indian etc
List 3, Number of tables/ seats 10-20, 20- 40, 50 -100
I want someone to be able to search for a restaurant by selecting an item from each dropdownlist
such as, "Barcelona" "Italian" "50-100"
This search query would return all the Italian restaurants in Barcelona with 50-100 tables/seats.
I would also like the select query to work even if one of the dropdownlists items is not selected.
Hope somebody can clear this up?
Also would sql injection attacks be a threat by doing it this way?
Thanks all
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
TIA.
I am trying to select the max for each Schedule_Number when ProcessDescription = 'Exit Cold Rinse'. In the following table, 2:00 and4:00 should be returned for 12345_001 and 12345_002 respectively. Ihave tried to join the two queries and would like to use the currentSchedule_Number as one of the criteria when determining the max.Below is some code that I've used thus far? Does anyone havesuggestions?*Schedule_Number * Process_Description * TMDT*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PMSelect *From(Select distinct Schedule_NumberFrom dbo.Process_DataWHERE left(Schedule_Number,5) = '12345') as Query1left join(Select *From dbo.Process_DataWhere TMDT =(SELECT Max(TMDT)FROM dbo.Process_DataWHERE Process_Description = 'Exit Cold Rinse' andQuery1.Schedule_Number = Query2.Schedule_Number)) as Query2on Query1.Schedule_Number=Query2.Schedule_Number
View 1 Replies View RelatedI am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedBackground
SQL Server 2005 Standard 9.0.1399 64bit
Windows 2003 64-bit
8gb RAM
RAID-1 70gb HD 15K SCSI (Log Files, OS)
RAID-10 1.08TB HD 10K SCSI (Data Files)
Runs aproximately _Total 800 Transaction/Second
We deliver aproximately 70-80 million ad views / day
8 Clustered Windows 2003 32-bit OS IIS Servers running Asp.net 2.0 websites
All 8 servers talking to the one SQL server via a private network (server backbone).
In SQL Server Profiler, I see the following SQL statements with durations of 2000 - 7000:
select top 1 keywordID, keyword, hits, photo, feed from dbo.XXXX where hits > 0 order by hits
and
UPDATE XXXX SET hits=1906342 WHERE keywordID = 7;
Where the hits number is incremented by one each time that is selcted for that keyword ID.
Sometimes these happen so frequently the server stops accepting new connectinos, and I have to restart the SQL server or reboot.
Any ideas on why this is happening?
Regards,
Joe
Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie
View 1 Replies View RelatedI have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
Thank you all very much for the information.
I want to get output of below query in single row.
Select 'Name'
Select 'Surname'
Expected output is Name,Surname
Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.
Here's the query:
update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'
Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?
I found out how to select the top 10 used stored procedures. But how do you select the top 10 select statements that are used on the system or database? I had a DBA show me this one time but forgot it after that job. Thanks
View 1 Replies View RelatedI have an intermittent issue where some remote PC's occasionally fail to execute select queries that have a join or return multiple result sets, however simple one table select queries continue to work okay. When it does happen the PC's needs to be rebooted to get to work again. This may only happen some PC's while others continue to work away okay.
I am using a VB6 application and ADO to connect to the database and the error message I get is a General Network Error, Server Not Found when it fails to execute the query. I have ran SQL Profiler on the server and while simple select queries continue to run away okay, a query a join does not even seem to show up in the profiler. The program has been working fine for 15 years with 1000's of users and has only now become an issue on one site for a number of users. Have tried moving the database to a different server and swapping network cards on the local PC's but can't seem to find the cause. The processor and the memory don't seem to be under load, but I am not sure if there is something else in SQL that is causing it to hang under certain conditions.
There have been network analysts experts in to run scans on the network, but I have not had the results of this back yet. Other applications do not seem to be affected so if this analysis does not show up anything.
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedHi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
Regards,
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
View 4 Replies View Related how can i execute for example
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code
View 3 Replies View RelatedHi All,
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Regards,
Rich
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
View 1 Replies View RelatedCould anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please
View 1 Replies View RelatedI just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
Thank you all very much in advance.
Hi!
Where to write MDX Queries and how to call the query in Analysis Services.
Please help me on this.
I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...
Any ideas? Thanks.
Hi and thanks for reading.
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk,
eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate,
l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription
FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk
LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk
LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID
LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows.
But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question).
Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (".
But, I hate using two queries and just wonder if there's a better way around this.
Appreciate the help, and thanks for reading.
-colin
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
Thanks.
It is given the following schema of relations:
Donators(Id <<PK>>, Name, Surname, IdCity <<FK(City)>>, Group, Sex, Age);
Files(IdFile <<PK>>, Donator <<FK(Donators)>>, Date, Type);
City(IdCity <<PK>>, Name, State);
I need to find a query for each of the following:
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution:
SELECT d.Name, d.Surname
FROM Donators d, Filles f
WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age)
FROM Donators d, Files f
WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id
FROM Donators d, Files f, City c
WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity
FROM City c
WHERE NOT IN (SELECT c.idcity
FROM City c, Donators d, Files f
WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Thanks