Hi there, I need an sql query to give me the following: All the values in a column of type varchar(50) which will NOT convert or will throw an error if cast / converted to an int. E.g. row 1: '1' row 2: '2' row 3: '3a' row 4: '4.5' I need row 3....however there are tens of thousands of rows. Thanks! ...
Hi, This is for SS 2005. Why I am i only getting 4000 characters and not 8000? It truncates the string @SQL1 at 4000. ALTER PROCEDURE sp_AlloctionReport( @where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS DECLARE @SQL1 NVARCHAR(Max) SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, Ve...
Hey Guys I have to answer the following question "For each year in the database, list the year and the total number of movies that were released in that year, showing these totals in decreasing order. That is, the year(s) with the largest number of movies appear first. If some years have the same number of movies, show these in increasi...
My insert stored procedure: ALTER procedure proj_ins_all ( @proj_number INT, @usr_id INT, @download DATETIME, @status INT ) as INSERT INTO project (proj_number, usr_id, date_download, status_id) VALUES (@proj_number, @usr_id, @download, @status) select SCOPE_IDENTITY() ... runs fine when called manually like so: exec proj_ins_al...
I'm unsure how to go about this either because it late, or I'm a MySQL noob (probably both to be honest). I have a table called 'poll_votes' which comprises of: ID, poll_id, answer_id, ip, time_posted What I need to do, is for each unique answer_id in a specific poll_id, calculate the percentage out of the total number of answers th...
My procedure was working fine in a 2005 database, but my PC died and when I set it up again I installed 2008 rather than 2005 - now this procedure doesn't return any results. I ask the question about the difference between the 2 versions simply because I have used this logic before and I haven't changed the procedure since I created it a...
I'm using the awesome_nested_set plugin in my Rails project. I have two models that look like this (simplified): class Customer < ActiveRecord::Base has_many :categories end class Category < ActiveRecord::Base belongs_to :customer # Columns in the categories table: lft, rgt and parent_id acts_as_nested_set :scope => :customer_...
I am writing a turbogears2 application. I have a table like this: class Order(DeclarativeBase): __tablename__ = 'order' # id of order id = Column(Integer, autoincrement=True, primary_key=True) # buyer's id buyer_id = Column(Integer, ForeignKey('user.user_id', onupdate="CASCADE", ondelete="CASCADE"), primary...
Is there a function to extract a timespan field from a datetime2 field? e.g datetime2 has '01/01/2009 12:30:00' i want '12:30:00' ...
I'm working with a table in MySQL that contains the following columns: id, january, february, march, april, etc The data in the table looks like this: aa, 0, 0, 1, 0 ab, 1, 0, 1, 0 ac, 1, 1, 0, 0 ad, 1, 1, 1, 0 To query it, I could easily do this: select * from table where january = 1 and february = 1 The result would be: ac, 1...
For example: select * from T where T.id IN(4,78,12,45) I want the returned record set just order by the position in the 'IN' clause. How can I do this? ...
I am trying to work out the difference between customers that have been billed in the last 3 months and the whole customer base. I have been able to calculate the customers that have been billed using the following SQL DECLARE @DateFrom AS DATETIME SET @DateFrom = CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + ...
Hi (excuse me for my bad english) ! When I make this: gallery_qs = Gallery.objects.all()\ .annotate(Count('photos'))\ .extra(select={'photo_id': 'photologue_photo.id'}) The sql query is : SELECT (photologue_photo.id) AS `photo`, `photologue_gallery`.* FROM `photologue_gallery` LEFT OUTER ...
How can I display an image in C# or VB when the name is in a database? I am using visual studio 2008 VB or C# I want to display an image on a windows form The image name is in a SQL2005 Database The image is stored in a directory structure. Ideally I would also like to be able to browse a directory select an image and add the image n...
Hello, I would like to change the existing DTS schedule date for a job using activex. The activex is going to be run from another DTS. Is this doable? Any suggestions/hints are appreciated. Thanks, ...
What is the best technique for getting related records by SP using one db round-trip. I need to create Master.List structure on app level in C#. I have master-detail tables: 1. I need to search rows in detail table. 2. I need to find corresponding rows in master table. 3. I need to return two cursors: A: All corresponding rows from...
Hi all. I have a problem wich is a little strange. My page contains a html link which refreshes the page and calls a PHP variable. This variable appends a date string to the url string which is fed into a MySQL query, which grabs records matching this date. I think this is causing an injection as it sometimes deletes the user from the d...
I am new to php and MySQL. I have a site where a user can choose a state and display hospitals in that state. It works well in FireFox for all states, but in IE, when I have state with a couple hundred hospitals, the screen flashes and I eventually get a message that the page cannot be displayed. For smaller states with a few hospital...
Its not really a subtraction I'm looking for. And I know its not a union or intersection... I have been given a long and complex stored procedure that returns a table of active and inactive documents. I have also been given a similar stored procedure that returns another table that contains only the active documents. How could I ge...
If the value of @param is null, which is better to use: WHERE t.column = COALESCE(@param, '') WHERE t.column = IFNULL(@param, '') WHERE (@param IS NULL OR t.column = @param) Is the OR more expensive than comparing the column to a value that will return all values for the specified column? My understanding is that options 1 & 2 will ...