sql

Extending business model and store it in database

Hey Guys I have a (kinda general) question about C# 4.0, MS SQL and a business object generated via ADO.NET entity data model. Let's say I got the MS SQL Table Foo with the rows: ID uniqueidentifier TITLE nvarchar(20) DESCRIPTION text ADDITIONALDATA image My initial idea was to serialize custom data (the extended properties and t...

join where the specifying column depends on the results

I want to set up a join so that the following pseudocode would apply SELECT * FROM client_rates cl, languages l IF cl.Language is numeric // i.e. is not a specific language join condition = WHERE cl.Language = l.category ELSE join condition = WHERE cl.Language = l.Language Is it possible to achieve this just in (My)SQL? ...

cannt get a record by the row number

i get an error with this query select ID, ROW_NUMBER() OVER(ORDER BY ID) as num from T_TASK where ROW_NUMBER() = 5 and this one select ID, ROW_NUMBER() OVER(ORDER BY ID) as num from T_TASK where num = 4 whats wrong with the queries? ...

Days of last month inPL/SQL

Hello, How can I get all the Days of the previous month in PL/SQL ? Thanks. ...

Yet another many-to-many NHibernate question

hey there Trying to implement relatively simple relationship between User and Role. User can be in many roles, same Role can belong to any number of Users. Roles are shared, i.e. all admin Users refer to the very same instance of class Role. User mapping: <class name="User" lazy="false" table="Users"> <id name="Id" type="int"> ...

SQL Count distinct times with a 30 minute difference

I'm trying to find a SQL query that will count the number of distinct start times that are at least 30 minutes different. I have a number of employees that are paid a credit when they start work on at least three distinct times in a week, where the start time is at least 30 minutes different from the other start times. For example: se...

Finding Caller of SQL Function

There's a SQL function that I'd like to remove from a SQL Server 2005 database, but first I'd like to make sure that there's no one calling it. I've used the "View Dependencies" feature to remove any reference to it from the database. However, there may be web applications or SSIS packages using it. My idea was to have the function in...

How should I keep a pointer to the parent record when I'm inserting child records in PL/SQL?

I have a package that will be inserting a single parent record in one table, and dependent child records in another table with a FK on the parent_id. Each of these functions will be called by an external program, and all calls to insert children may not be contained within the same transaction. I'm wondering if there is any way for me ...

Why do I have to use DISTINCT for this to work?

Hi, here's my problem: I have an SQL query that makes 4 calls to a lookup table to return their values from a list of combinations in another table. I finally got this working, and for some reason, when I run the query without DISTINCT, I get a ton of data back, so I'm guessing that I'm either missing something or not doing this correctl...

Access sql that triggered the trigger from within trigger (Sybase)

Is there a way to access the sql that triggered a trigger from within the trigger? I've managed to get it by joining to the master..monProcessSQLText MDA table but this only works for users with the mon_role and I don't want to give that to everyone. Is there a global variable I've missed? I'm trying to log all the updates run against...

Select exactly one row for each employee using unordered field as criteria

I have a data set that looks like the following. EMPLID PHONE_TYPE PHONE ------ ---------- -------- 100 HOME 111-1111 100 WORK 222-2222 101 HOME 333-3333 102 WORK 444-4444 103 OTHER 555-5555 I want to select exactly one row for each employee using the PHONE_TYPE field to establ...

how to update rows by a range of dates in teradata

Hi, I have two tables in a teradata database that look like this accounts account_number integer date_updated_last datetime delinquency_code varchar(3) payments account_number integer statement_date datetime delinquency_code varchar(3) the delinquency code column is populated in accounts, but is not populated in payments. I would li...

SQL Server: Why can't I create a view inside of a Begin...End Block

This Code: BEGIN CREATE VIEW [dbo].[dummy] AS SELECT 1 AS Dummy END GO Does not work, returning the error: Incorrect syntax near the keyword 'VIEW'. Why? Notes: The presence of the GO statement seems to make no difference The inner statement works fine outside of the code block delimiters. This is part of a larger query but tested...

SELECT clause: Optional Columns?

I want to run a single query on my MYSQL database. The table I am searching has a file_type field that will be one of three values (1,2,3). Depending what these values are I want to look for different information from the database. So for example if the 'file_type' field is 1 or 2 then I want to SELECT fields a,b,c,d However if I notice...

Display more than one row with the same result from a field

I need to show more than one result from each field in a table. I need to do this with only one SQL sentence, I don´t want to use a Cursor. This seems silly, but the number of rows may vary for each item. I need this to print afterwards this information as a Crystal Report detail. Suppose I have this table: idItem Cantidad <mo...

large insert in two tables. First table will feed second table with its generated Id

Hi all, One question about how to t-sql program the following query: Table 1 I insert 400.000 mobilephonenumbers in a table with two columns. The number to insert and identity id. Table 2 The second table is called SendList. It is a list with 3columns, a identity id, a List id, and a phonenumberid. Table 3 Is called ListInfo and co...

SQLDeveloper changing case (breaking formatting)

Using SQLDeveloper 2.1.1.64, if you try typing the following code: DECLARE v_status_code NUMBER; v_status_text VARCHAR2(30); v_to_delete NUMBER := 5; BEGIN PACKAGE_NAME.Delete(v_to_delete, v_status_code, v_status_text); END; Pressing Enter after the PACKAGE_NAME.Delete(...) line will make Delete go into all caps (DELETE). I ...

ORDER BY with a CASE statement for column with alias

I need a stored procedure which will allow me to return sorted results based on two input parameters: @sortColumnName and @sortDirection. I wrote the following stored procedure, but when I run it, I am getting this error: "Invalid column name 'LastPayCheckDate'." SELECT Name, SUM(Pay), MAX(PayCheckDate) as LastPayCheckDate FROM Employee...

Python String Formats with SQL Wildcards and LIKE

I'm having a hard time getting some sql in python to correctly go through MySQLdb. It's pythons string formatting that is killing me. My sql statement is using the LIKE keyword with wildcards. I've tried a number of different things in Python. The problem is once I get one of them working, there's a line of code in MySQLdb that burps on...

PHP cries Syntax Error on simple SQL WHERE clause.

mysql_query('SELECT ID FROM documents WHERE (Key="' .$theKey. '" AND Value="' .$theValue. '")'); Isn’t this right? Also tried with a semicolon at the end of the string, but no luck. Driving me crazy, PHP keeps spitting out: You have an error in your SQL syntax; check the manual that c...