stored proc from excel with parameters
If I create a stored proc in sql that has a parameter, can I run that from Excel? ...
sql
Help with string formatting in SQL Server Query
Hello, I have the following SQL query: SELECT DISTINCT ProductNumber, PageNumber FROM table I am trying to modify the query so that PageNumber will be formatted. You see, PageNumber is in any of the following formats, where 'x' is a digit: xxx, xxx xxx xxx-xxx xx, xxx-xxx xx-xx, xxx xx-xx, xxx-xxx I want to format PageNumber so th...
Convert varchar 114 on datetime equals what?
I have trouble in setting the equal condition on a datetime field after converting it to hh:mm:ss.mmm: select top 1 convert(varchar, timestamp, 114) from TEST_TABLE /* returns a single entry of 11:33:35:000 */ Then I want to find all entries with that timestamp: select * from TEST_TABLE where convert(varchar, timestamp, 114) = '11:3...
SQL generated by LINQ for logging
Hi all. Plz help. I have this query and its result implements IEnumerable. RoutesEntities routesModel = new RoutesEntities(); LocalesEntities localesModel = new LocalesEntities(); var routesQuery = from rs in routesModel.Routes.ToList() join ls in localesModel.Locales.ToList() on rs.LocaleID equal...
sql help please
Hi All, The following sql snippet below is a subselect of a parent sql. Is there a way to say, "If customer doesn't exist I want to run a different sql"? select orderdate, (select contactname from customers where customerID = 'ALFKI' or select 'No Customer') as contactname from orders I know this can be solved with a join but...
Why can't I GROUP BY 1 when it's OK to ORDER BY 1?
Why are column ordinals legal for ORDER BY but not for GROUP BY? That is, can anyone tell me why this query SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY OrgUnitID cannot be written as SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY 1 When it's perfectly legal to write a query like SELECT OrgUnitID FROM Employee A...
How to Assign a JPQL query to a simple Java object?
I need to assign the result of a jpql result to a simple class java object I have something like this class myObject() { @id private Long id; private String Name; private String description; ... //getters and setters } I need to somehow to store the result of this SQL query, example // could be anytable SELECT DISTINCT c.table_i...
Can anyone fathom a why do this or how it works correctly with this bit of SQL?
I've changed the table names but I found this FROM statement in a SP I'm optimizing and I'm wondering the how this could come into being, why would you ever do anything like this, and why does it still work. FROM tblPezFill pf RIGHT OUTER JOIN tblWolveLocation stfl RIGHT OUTER JOIN tblDuckPez pp RIGHT OUTER JOIN tblChaos o I...
Timezone Daylight Saving problem in PHP and SQL
So in my PHP code, I do timezone selection for the user, where I have a table of timezones and their hour-shift values from GMT. I then use that to add to the DATETIME column values whenever the user picks his timezone in the SETTINGS. So I have two functionalities: reading from DATETIME column in the database, and WRITING to the DATETI...
SQL 2005 - two tables Join on some id,
Let's say I have 2 tables. I want join them so that for every account I get 1 row where the account's information is there PLUS the primaryContact's information appended to the table. Is this possible? ID's are unique keys. ACCOUNT TABLE accountid | name | income | primaryContact 123456789 Jack Johnson 1...
SQL Error on Audit Trail Insert after Scope_Identity()
I'm trying to use a stored procedure to do an insert on a main table, then also insert that data into a historical data table. I don't want to use a trigger, but rather just do a simple insert of all columns into the history table. I am getting this error, however. "An explicit value for the identity column in table 'ipaudittrail' can...
Best way to migrate a MySQL table with dupes to another table with a UNIQUE constraint
I am trying to work out the best approach for a data migration. I am migrating some data (~8000 rows) from a table like this: CREATE TABLE location ( location_id INT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY, addr VARCHAR(1000) NOT NULL, longitude FLOAT(11), latitude FLOAT(11) ) Engine = InnoDB, DEFAULT CHARSET=UTF8; ...
Oracle SELECT : Count of events per team and per year
As said in the title, I want to select the number of events per team and per year. The select statement below works fine but doesn't give me exactly what I am looking for. SELECT Team.team_id, TO_CHAR(Event.START_DATE_TIME, 'yyyy') AS year, count(event_id) AS events FROM Team LEFT OUTER JOIN Event ON Event.team_id = Team.team_id...
Finding rows with the maximum
Hi - I have a pretty simple table in SQLite, with the following schema: CREATE TABLE IF NOT EXISTS Palettes (id INTEGER PRIMARY KEY AUTOINCREMENT, class TEXT, count INTEGER, name TEXT); These represent color palettes, and several palettes can have the same name, but different counts (i.e. sizes). What I wa...
In SQLite3, how can I do SQL-escaping in a LIKE clause?
I'd like to run a LIKE query in sqlite3 with the user's input safely escaped. Basically, I want to do something like this: char* query = "SELECT * FROM table WHERE LOWER(notes) LIKE '%?%'"; sqlite3_stmt* statement; sqlite3_prepare_v2( database, query, -1, &statement, NULL ); But the ? is not honored when inside the LIKE expression. ...
Nesting sql queries inside case
Hi guys, I am trying to write a SQL query where I am joining two tables and retrieving a few columns. Once this is done, based on a two fields (source table enum, id in the corresponding source table), I need to retrieve a value matching the id in the source table. The issue is that I cannot join all the source tables, and want to do ...
How do I specify a mysql cascade rule for the following...
In mysql, I've got the following: TABLE docs ( ID title content ) TABLE specialDocs ( docID -> docs(ID) userID -> users(ID) ) So specialDocs is a subset of documents that can belong to users. I am wondering: Is it possible to specify a cascade rule so that when a user is deleted, the docs that belong to that use...
MYSQL How to use trim in select query
my table have set of records around 50, in the table i have column called USERNAME , but some of username leading and trailing have the white space , so am not getting exact order result because of white space, So tell me how to use the trim in SELECT query , Thanks ...
Proper datastructure for the following...
I want to represent documents in a database. There are several different types of documents. All documents have certain things in common, but not all documents are the same. For example, let's say I have a basic table for documents... TABLE docs ( ID title content ) Now let's say I have a subset of documents that can ...
How to insert into sql server Many to many relationship tables
I imported data into csv table via .net app, pls help, how can i aInsert into all the tables so that i can query; student avg score to a question or test student answers to a question or answer frequency to a question etc My tables are in this form; Csv Ans Quest Test Ans_Quest_Test --------- ------ ...