sql

rownum in Subquery

I was wondring why this doesn't work: SELECT ( SELECT COALESCE(MAX(a.LaufNr),0) + TBL.Rownum FROM schemaB.PersonRelatedTbl A WHERE A.Persid = Tbl.Persid ) , Tbl.Some_Other_Attributs FROM schemaA.PersonRelatedTbl TBL WHERE ... This + TBL.Rownum gives an error why? greets Auro ...

Linq to sql stored proc not returning results

I have a Linq to SQL DBML Contained within the DBML is a stored proc When the proc is executed via Microsoft SQL Server Managment tool it return 60 rows. Bu the DBML doesnt think that it returns any results. Has anyone had this issue before or know how to resolve it? Sp EDIT Code: [Function(Name="dbo.WQNT_PeekViewNextZone")] publ...

Inserting multiple rows using JdbcTemplate

How can I execute the following SQL in a scalable way using JdbcTemplate running on mySQL. In this case, scalable means: Only one SQL statement is executed on the server it works for any number of rows. Here's the statement: INSERT INTO myTable (foo, bar) VALUES ("asdf", "asdf"), ("qwer", "qwer") Assume that I have a list of POJO'...

How to execute SQL file in remote server using ANT

I want to execute .sql file at /x/y/z location of a UNIX server, using ANT script from my local Windows XP system. The .sql files are at UNIX server. I have used the below ant target from my Windows XP system: <target name="execute" > <sshexec host="hostname" username="UNIX system username" keyfile="/export/home/appldev...

MySQL: Join two tables

Hello, I'm having problems with design correct SQL query. I'm tired like a hell today, been working over 12 horus (deadline soon) and cannot find issue... Tables: buddies | userid | buddyid users | id | username Now, what I'd like to do: Query table buddies for all user friends (when ID = userid OR ID = buddyid). Having no problems ...

MySql Join a View Table as a Boolean

I have a users table, and a view table which lists some user ids... They look something like this: Users: User_ID | Name | Age | ... 555 John Doe 35 556 Jane Doe 24 557 John Smith 18 View_Table User_ID 555 557 Now, when I do run a query to retrieve a user: SELECT User...

SQL Ordering by Date but Maintain Foreign Key Groupings

Let's say I have an item table with columns id (PK), parent_id (FK), name, and date. I also have an item_parent table with columns id (PK) and name. Items can belong to the same parent, or an item can have no parent (parent_id can be null). I am trying to construct a SQL query to select all items ordered by date (DESC), keeping all item...

Defining table structure for a database?

Up until now, my experience with databases has always been working with an intermediate definition layer that we have where I work. i.e. SQL wasn't directly written for the table definitions, but generated from an intermediate file which wrote out SQL scripts for creating the appropriate tables, upgrade scripts between schema changes, a...

SQL How do i update like this?

I'm trying to update all SQL rows in the [Temp_LTGData] table setting the [CORP_REG_NO] value to the value in another row in the same table where the [CUSTOMER_NUMBER] matches. Ultimately I need to do this with quite a few columns, does anyone know if this can be done? I can't seem to use the LTGSource alias like in a select query :( ...

Drupal search user by profile firstname and lastname query

Hi! I am writing module which allows to quick add users only by providing First and Lastname (without emial address or user name). Those users are 'ghost' like, just for a temporary uses. Before adding user module needs to check if user with the same first name and last name already exists in system to avoid creating multiple ghost user...

Match a field from a form's contents with a table in a database (MySQL)

I have set up a form on my site. And I'm wondering, how can I match what people enter in a field in that form with something in another table. The form submits to one table, and I want to check if what's submitted with the form is in another table. In this case, I have a field named "Title", and I want to check if what's been entered fr...

How to use OR using Django's model filter system?

It seems that Django's object model filter method automatically uses the AND SQL keyword. For example: >>> Publisher.objects.filter(name__contains="press", country__contains="U.S.A" will automatically translate into something like: SELECT ... FROM publisher WHERE name LIKE '%press%' AND country LIKE '%U.S.A.%' However, I was won...

Comparing the Oracle Interval Data Type

I have an oracle time interval in a select statement like this: SELECT ... CASE WHEN date1 - date2 > 0 THEN 'No' ELSE 'YES' END This fails with an invalid data type. So I tried this, and it still fails. SELECT ... CASE WHEN date1 - date2 > (sysdate - sysdate) THEN 'No' ELSE 'YES' END Is there any way t...

How to select most populated record?

I have the unfortunate luck of having to deal with a db that contains duplicates of particular records, I am looking for a quick way to say "get the most populated record and update the duplicates to match it". From there I can select distinct records and get a useful set of records. Any ideas? It's mainly names and addresses if that ...

SQL Server Update trigger for bulk updates

Below is a SQL Server 2005 update trigger. For every update on the email_subscriberList table where the isActive flag changes we insert an audit record into the email_Events table. This works fine for single updates but for bulk updates only the last updated row is recorded. How do I convert the below code to perform an insert for ever...

Selecting from a table and inserting into another table's column of a different type using query in ms access.

I have some txt files that contain tables with a mix of different records on them which have diferent types of values and definitons for columns. I was thinking of importing it into a table and running a query to separate the different record types since a identifier to this is listed in the first column. Is there a way to change the val...

SQL Server - is there a way to mass resolve collation conflicts

We have a situation where the collation of databases, tables, and some columns are different between our dev and production SQL Servers, and it's wreaking havoc on development. Things will work on dev and then break due to collation conflicts when promoted, data and structures will be copied from prod to dev which in turn breaks the que...

How do I order a SELECT on a column's character length?

I want to select * from tbl order by characters, for example: a) 1 coloum has a b) 1 coluum has ab I want it to place ab first and then a ...

Progressive count using a query ?

I use this query to SELECT userId, submDate, COUNT(submId) AS nSubms FROM submissions GROUP BY userId, submDate ORDER BY userId, submDate obtain the total number of submissions per user per date. However I need to have the progressive count for every user so I can see how their submissions accumulate over time. Is this possible to i...

SQL - Grab Detail Rows as Columns in Join.

This is my query thus far: select C.ACCOUNTNO,C.CONTACT,KEY1,KEY4 from contact1 C left join CONTSUPP CS on C.accountno=CS.accountno where C.KEY1!='00PRSP' AND (C.U_KEY2='2009 FALL' OR C.U_KEY2='2010 SPRING' OR C.U_KEY2='2010 J TERM' OR C.U_KEY2='2010 SUMMER') Now, I have another table (CONTSUPP) which contains multipl...