sql

Is there a SQL equivalent to Linq .Skip(1000).Take(100)

Is there a SQL equivalent to the Skip method of Linq? For example I would like to be able to select the 1000 - 1100 rows from a database table. Or am I going to have to select the whole table and then find the rows in memory? My problem is that the table can be quite big and I'd like to avoid doing that if possible. ...

Update table on matching text type of data

I have following table I want to update its TownId from following table on matching TownId. The desired result of Table 1 is ...

Consolidating a COUNT query

I have a page where I am running an initial SQL query to get a list of subjects, then I loop over this query and run two additional queries for each record returned from the original subjects query (I happen to be doing this in ColdFusion, but not sure that really matters). These two additional queries do COUNTs for that specific subjec...

Are there any Java API to parse SQL string?

In our system we have separate API to build query where we need to pass each column and table name separately like below: dw.setCOLUMN("Column1"); dw.setCOLUMN("Column2"); dw.setTABLE("TableName"); If I would get chance I will remove this API and go with simple SQL query execution (like many Java application does) but due to the exist...

How does Doctrine handle changes to the database schema?

In brief, what happens when you add a column to a table? What happens when you remove one? In more details, suppose you have the following: class User extends Doctrine_Record { public function setTableDefinition() { $this->hasColumn('username', 'string', 255); $this->hasColumn('password', 'string', 255); } }...

retrieve available day from date rante

I think I'm pretty close on this query, but can't seem to crack it, and I'm not sure if I've got the most efficient approach. I am trying to find a day where a user is not booked from a range of dates where they are booked. Think staff scheduling. I need to find who is available to work on Tuesday, and is working on other days this w...

update columns values with column of another table based on condition.

I have two tables... table1 ( id, item, price ) values: id | item | price ------------- 10 | book | 20 20 | copy | 30 30 | pen | 10 ....table2 ( id, item, price) values: id | item | price ------------- 10 | book | 20 20 | book | 30 Now I want to: update table1 set table1.Price = table2.price where table1.id = table2.i...

Deleting records from a table, is this correct?

Hi! to every One! I want to delete some record from table ,by running this Query in C# is it Correct or not, Please help me SqlCommand cmdRe = new SqlCommand("insert into msisdn_master SELECT * from tblDeactive where msisdn in (" + str_MSISDN + ")", cn); SqlCommand cmdRed = new SqlCommand("delete from tblDeactive where msisdn in ("...

total two or more values under one ID in SQL

![alt text][1] [1]: http://C:\Documents and Settings\Administrator\My Documents\My Pictures\Ashampoo Magical Snap 2\Magical Snap - 2009.11.16 23.07 - 003.jpg In referring to the picture there are several entries where the same Student ID repeats. Where this happens I'd like to combine the money owed by totaling any multiple entries un...

Odd WHERE NOT EXISTS performance on DB2

I am experiencing very odd performance on DB2 version 9.1 when running the query below: select a.CYCL_NUM , a.AC_NUM , a.AUTHS_DTE , a.PL_ID , a.APRVD_RSPN_CDE , a.AUTHS_AMT , a.AUTHS_STS_CDE , a.TRAN_CTGR_CDE , a.MRCHN_CTGR_CDE , d.out_pu_au_amt from nwhd12.chldr_auths a, nwhd12.w_chldr_ac d where cycl_num = 200911 ...

SQL select no result

The SQL below returns no result but there is one record in the table: SELECT DISTINCT ul.* FROM UserLogin ul, ContactPerson cp, UserRole url, Role rl If I run this query, it returns the record: SELECT DISTINCT ul.* FROM UserLogin ul Can you help explain this and how to make the first one work? ...

Is there any harm choosing a large value for varchar in MySQL?

I'm about to add a new column to my table with 500,000 existing rows. Is there any harm in choosing a large value for the varchar? How exactly are varchars allocated for existing rows? Does it take up a lot of disk space? How about memory effects during run time? I'm looking for MySQL specific behavior details not general software desi...

No matter what I do the same value is being stored in MySQL table field...

I have a MySQL table, with the field ('ad_id'). I store variables in different fields with PHP. One of these fields, the 'ad_id' field as mentioned above, stores the same exact nr over and over again, no matter what the "REAL" name is in the PHP file. Example: $ad_id= 12345; When trying to store this, the number 11111 is stored. ALWAY...

how to use = assignment operator with timestamp date column in oracle

I'm using timestamp in dat column in table r3. when I fire command select dat from r3 where dat='16-nov-09'; it shows "no rows selected" but when i fire command select dat from r3 where dat>'15-nov-09'; it shows the whole data of 16-nov-09. Tell me what is wrong in my first command or what i have to do. ...

SQL Decimal separator

I have this query SELECT LEFT( CONVERT(VARCHAR, CAST(RUNNING_BALANCe AS MONEY), 1), LEN( CONVERT(VARCHAR, CAST(RUNNING_BALANCe AS MONEY), 1)) - 1) from load_transaction_history WHERE [POSTED_ON] between '2008-06-10' and '2008-06-15' and account_number='12345678' order by posted_on Now the problem is that the ammounts i ger are like ...

Parameters in query with in clause ?

Hi all, I want to use parameter for query like this : SELECT * FROM MATABLE WHERE MT_ID IN (368134, 181956) so I think about this SELECT * FROM MATABLE WHERE MT_ID IN (:MYPARAM) but it doesn't work... Is there a way to do this ? I actually use IBX and Firebird 2.1 I don't know how many parameters in IN clause. ...

How to pass NULL or empty strings to stored procedure input parameter with ADO and VB?

I have a stored procedure in Sql Server 2005 with a varchar input parameter defined as: @Value varchar(24) = NULL in my VB6 application I try to set the parameter with an ADO funtion: Set prmParamVal = cmdChkParam.CreateParameter(, adVarChar, adParamInput, Len(paramValue), paramValue) If the value I try to pass is an empty (zero le...

How to put a constraint on two combined fields?

I'd like to put a constraint, a check or a foreign key, on two combined fields from table1 to another field in table2. Here is what I tried, but both gave me errors: ALTER TABLE table1 ADD CONSTRAINT foo CHECK (field1 || field2 IN (SELECT fieldx FROM table2)); ALTER TABLE table1 ADD CONSTRAINT foo FOREIGN KEY (field1 || field2)...

Select column, if blank select from another

How does one detect whether a field is blank (not null) and then select another field if it is? What I really need is a IsBlank function that works the same as IsNull but with with blanks. REPLACE doesn't work with blanks, COALESCE only works with NULLS. ...

sql simple query

hey yesterday my friend ask me question select * from user where 1=1 I said this is wrong query but he told me this is true i dont understand it how is it true please explain me how this condition works where 1 = 1? ...