sql

How do I do a case sensitive comparison in sql?

Hi Just started a tutorial in SQL for beginners. I'm doing some exercises now and I would like to know how to change the title. If you look at here: you'll see that I have made firstname, lastname, title, age and salary. And I wrote the letters in small letter. How can I change it to capital letter? http://tinypic.com/r/amtpgm/3 I...

Query input must contain at least one table or query

I'm really hoping that somebody could help me regarding this problem. I am using Microsoft Access 2007 to build some forms while connecting to my sql database. Everything seems ok except the forms inserting data in the database showing this kind of message "Query input must contain at least one table or query" I'm not finding where the...

SQL / WINDOWS - optimal char lengths - DOES IT MATTER

Three little questions for the clever people of stackoverflow.... WINDOWS: max file name length in windows is 255 - why is this and why not 256? why is the maximum fully qualified filename (full path) stated as 32,767 when in reality it has to be a max of 255/260 to avoid any errors. SQL: when creating chars or varchars fields in ...

Query SQL Server data dictionary to find all databases containing table 'x'

I'm not overly familiar with the SQL Server data dictionary, but I've assumed what I'm trying to do is possible. We have a number of replicated databases, under different names say: Client1 Client2 Client3 Rather than rely on a naming convention, I was hoping to identify these databases, based on whether they include a key table, call ...

how to print database diagram sql

Hi I would like to print the database diagram in sql. I'm using SQL Server Management Studio. What I did I right click on it, and select "View Page Breaks". I wanted to have the diagram in A3 Format. But when I tried to print it, I had 4 pages. Is there a way where I can print the whole diagram in just one page? Thanks ...

Problem with SQL function

I have a function CREATE OR REPLACE FUNCTION Total_order_price (p_order# IN NUMBER) RETURN NUMBER IS l_total_order_amount NUMBER := 0; BEGIN SELECT Sum(fo.n_portions * di.price) + Sum(do.n_units * dr.price) AS total_order_price INTO l_total_order_amount FROM customer_order co, food_order fo, ...

How to pass a null variable to a SQL Stored Procedure from C#.net code

Hi, Im calling a SQL stored procedure from a piece of C#.net code: SqlHelper.ExecuteDataset(sqlConnection, CommandType.StoredProcedure, STORED_PROC_NAME, sqlParameters); where the sqlParameters variable is defined as: SqlParameter[] sqlParameters = new SqlParameter[SQL_NUMBER_PARAMETERS]; Log.Logger.Debug(string.For...

Please help me debug my SQL query.

I have a query: Select n_portions, dish_name from food_order, dish where n_portions= (select max (n_portions) FROM food_order); It's meant to return: fish pie 3 steak and chips 1 pasta bake 2 stuffed peppers 1 But i get: Pasta bake 35 Fish pie 35 Steak and chips 35 Stuffed peppers 35 Ham and rice 35 Lamb curry ...

google or other search engine's SQL code (or other DB-specific code)

Has someone seen the actual SQL ( or bigtable or whatever ) code that actually makes the search in some major search engine like google, yahoo, etc? I'm just curious. (please don't close, let me reformulate if it's not acceptable as a SO question) ...

Make a query Count() return 0 instead of empty

I have a report that tracks how long certain items have been in the database, and does so by tracking it over a series of age ranges (20-44, 45-60, 61-90, 91-180, 180+). I have the following query as the data source of the report: SELECT DISTINCT Source.ItemName, Count(SELECT Source.DateAdded FROM Source WHERE Int(Date()-Source.DateAd...

Select most frequently occurring records using two or more grouping columns

I have a table which has no primary key and I can't add one - the relevant columns from it are: Department | Category | -------------+-----------+ 0001 | A | 0002 | D | 0003 | A | 0003 | A | 0003 | C | 0004 | B | I want to retrieve a s...

LINQ and paging with a listview

Hi, I have a page with a listview control and a datapager control. The listviews datasource is set programatically using this code: Dim dal as new dalDataContext Dim bookmarks = From data In dal.getData(userid) listview1.DataSource = bookmarks listview1.DataBind() When i test this page in a browser it comes up with the error: 'ListV...

in general, should every table in a database have an identity field to use as a PK?

This seems like a duplicate even as I ask it, but I searched and didn't find it. It seems like a good question for SO -- even though I'm sure I can find it on many blogs etc. out there. SO will have more of a debate than you can get on a blog. I'm running into an issue with a join: getting back too many records. I think of this as "exp...

connecting to a public database through vb.net

i am wondering if there is a public database where you can test your SQL and can connect to? i would like to test a connection to a database using vb.net, and would like to run a few sql statements. ...

SQL table with a single row?

What is the point (if any) in having a table in a database with only one row? Note: I'm not talking about the possibility of having only one row in a table, but when a developer deliberately makes a table that is intended to always have exactly one row. Edit: The sales tax example is a good one. I've just observed in some code I'm r...

Maintain SQL sort state throughout next query?

I saw this post but didn't understand if this was the same issue. My database can be sorted on each column. Once the db is sorted, how do I maintain that order after my next query? For example: Order db by age: sprintf(sql, "select * from Customer_Table ORDER BY age_ID LIMIT 100 OFFSET %s;", offset); then the user looks at the...

Using Prepared Statements to set Table Name

Im trying to use prepared statements to set a table name to select data from but i keep getting an error when i execute the query. The error and sample code is displayed below. [Microsoft][ODBC Microsoft Access Driver] Parameter 'Pa_RaM000' specified where a table name is required. private String query1 = "SELECT plantID, edrman, pl...

Downsides (If any) With SQL Server 2005 XML Datatype

I have a need to record XML fragments in a SQL Server 2005 database for logging purposes (SOAP messages, in this case). Is there any reason to use the XML datatype over a simple varchar or nvarchar column? I can see instances already where being able to use XPath to get into the data in the XML would be rather nice, and I haven't been a...

How can I create a tabular report in SQL when the column names are in the database, not the query?

http://www.geocities.com/colinpriley/sql/sqlitepg09.htm has a nice technique for creating a tabular report where the column names for the table can be coded in the query but in my case, the columns should be values from the database. Say I have daily sales figures like: Transaction Date Rep Product Amount 1 July 1 ...

SQL GROUP BY CASE statement with aggregate function

I have a column which looks something like this: CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product And I would like to put it in my GROUP BY clause, but this seems to cause problems because there is an aggregate function in column. Is there a way to GROUP BY a column alias such as some_product in this case,...