sql

Search field based on multiple parameter

Can anybody modify this , when i insert Emp. name it go to first search and it does not check other paramete could you plz modify this sp for exact search on based on parameter. --select * from Training_TRNS --USP_SearchEmployee '','2008-04-18 00:00:00.000','','','','','' alter Procedure USP_SearchEmployee @EmpName varchar(100)=null, @...

How do I use Perl to parse the output of the sqlplus command?

I have an SQL file which will give me an output like below: 10|1 10|2 10|3 11|2 11|4 . . . I am using this in a Perl script like below: my @tmp_cycledef = `sqlplus -s $connstr \@DLCycleState.sql`; after this above statement, since @tmp_cycledef has all the output of the SQL query, I want to show the output as: 10 1,2,3 11 2,4 Ho...

Why SQL Server Express 2008 install requires Visual Studio 2008 in checklist ?

When installing SQL Server Express Edition 2008, checklist says "Previous version of Visual Studio 2008" and asked me to upgrade to sp1. Unfortunately sp1 for some reason refuses to install on my brand new pc (Windows 7). So why can't I just bypass this ? Why would SQL Server Express needs VS2008 to install that's insane. SQL Server ...

How to write SQL query as named_scope?

How can I translate the following SQL query into a named_scope? select users.*, sum(total_quantity * total_price) as points_spent from orders join users on users.id = orders.user_id where pay_type = 'points' group by user_id order by points_spent desc Thanks! ...

JET SQL: do LEFT JOINS and RIGHT JOINS only work under JET 4.0 and above?

Does the following article apply to all uses of JET (including from within client apps via DAO or OLEDB): http://support.microsoft.com/kb/275058 Does this mean JET 4.0 or above is mandatory for anything involving an Access MDB? ...

The right way to write "ID" in columns in SQL

My first question in here. I like the site so much :) I've a SQL table called Product. In that table I want to make a column for the ID of the product, and I really want to know the right way to write ID because I make a lot of ID columns in the long run. So is it: "ID" or "Id" ? And also a foreign key in another table, is that called...

can use more than 1 column in MySQL Group BY?

Hi, I want write these SQL Query: CREATE VIEW `uniaverage` AS SELECT `averagegrade`.`mjr`,`averagegrade`.`lev`, AVG(`averagegrade`.`average`) AS `uniAVG` FROM `averagegrade` GROUP BY `averagegrade`.`lev`, `averagegrade`.`mjr`; But MySQL Query Browser give this error: Operand Should Contain 1 column(s) I somewhere read...

Execute multiple queries

I am using OleDB for executing my queries in C#, Is there any way I can execute multiple queries in one command statement? I tried to separate them with semi-colon (;) but it gives error "Characters found at the end" I have to execute a few hundreds of queries at once. Edit: I am executing Insert Statements. ...

migrate/ update core data app without erasing user data!

hello, i have a very complicated problem that i would like to share with you and maybe someone can answer it for me. before i start i have to say that i am very new in this. So, i have a coredata iphone app (much like the recipes app) that uses a pre-populated sql database. The user can add/edit his own data but the default data cannot ...

Creating a Mysql view to SELECT coloumns from different tables

I need help in constructing a VIEW on 4 tables. The view should contain the following columns: ER.ID, ER.EMPID, ER.CUSTID, ER.STATUS, ER.DATEREPORTED, ER.REPORT, EB.NAME, CR.CUSTNAME, CR.LOCID, CL.LOCNAME, DI.DEPTNAME The aliases are: EMP_REPORT ER, EMP_BIO EB, CUST_RECORD CR, CUST_LOC CL, DEPT_ID DI The data models are: describe E...

Find amount of updated rows in T-SQL

I need to find the amount of updated rows UPDATE Table SET value=2 WHERE value2=1 declare @aaa int set @aaa = @@ROWCOUNT It doesn't work. How can I do that? ...

SQL's Rownumber with Linq-to-entities

I am converting my project to use EF and also want to covert stored procedures into Linq-to-entities queries. This my SQL query (simple version) that I have trouble to convert: SELECT CategoryID, Title as CategoryTitle,Description, LastProductTitle,LastProductAddedDate FROM ( SELECT C.CategoryID, C.Title,...

sqlite3 date operations when joining two tables in a view?

In short, how to add minutes to a datetime from an integer located in another table, in one select statement, by joining them, in sqlite3? I have a sqlite3 db with: table P(int id, ..., int minutes) and a table S(int id, int p_id, datetime start) I want to generate a view that gives me PS(S.id, P.id, S.start + P.minutes) by joining S....

Question about joins and table with Millions of rows

I have to create 2 tables: Magazine ( 10 millions of rows with these columns: id, title, genres, printing, price ) Author ( 180 millions of rows with these columns: id, name, magazine_id ) . Every author can write on ONLY ONE magazine and every magazine has more authors. So if I want to know all authors of Motors Magazine, I have to ...

use a sql select statement to get parameters for 2nd select statement

Hi there, I am trying to write a sql statement that I have 2 tables Store & StoreTransactions. My first select command looks like SELECT [StoreID],[ParentStoreID] FROM Store Very simple stuff. How do I take the returned StoreID's and use them for my 2nd select statement? SELECT [StoreTransactionID],[TransactionDate],[StoreID] FRO...

Data not entering the table

//loop through usernames to add to league table for ($i = 0; $i < count($user); $i++) { //set some new variables in an array $username = $user[$i]; $squad = $team[$i]; //add details to league table if ( $username != "Ghost") { $database->addUsersToLeagueTable($us...

casting odd smallint time to to datetime format.

Hello everyone, I'm working with a db (SQL server 2008), and have an interesting issue with times stored in the db. The DBA who originally set it up was crafty and stored scheduled times as smallints in 12-hour form-- 6:00AM would be represented as 600. I've figured out how to split them into hours and minutes like thus: select floor(t...

named_scope or find_by_sql?

I have three models: User Award Trophy The associations are: User has many awards Trophy has many awards Award belongs to user Award belongs to trophy User has many trophies through awards Therefore, user_id is a fk in awards, and trophy_id is a fk in awards. In the Trophy model, which is an STI model, there's a trophy_type colu...

Auto Generated - CREATE Table Script in SQL 2008 throws error

I scripted the tables in my dev database using SQL 2008 - generate scripts option (Datbase->right click->Tasks->Generate Scripts) and ran it on the staging database, but the script throws the below error for each table Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('. Msg 319, Level 15, State 1, Line 15 Incorrect sy...

How to SUM columns on multiple conditions in a GROUP BY

I am trying to return a list of Accounts with their Balances, Outcome and Income Account Transaction ------- ----------- AccountID TransactionID BankName AccountID Locale Amount Status Here is what I currently have. Could someone explain where I am going wrong? select a.ACCOUNT...