sql

sql make the joined tables as one table may be???

Is there any way to name the complete select as a table? I will try to explain what I am trying to do. I have this SELECT * FROM `Databse1`.`table1` JOIN `Database2`.`table2` ON `table2`.`customerID` = `table1`.`customerID` WHERE `table1`.`recordID` IN (1,2,3,4) I have another table, table3 that...

SQL Server, can't insert null into primary key field?

I'm about ready to rip my hair out on this one. I'm fairly new to MS SQL, and haven't seen a similar post anywhere. When I try to do a statement like this: INSERT INTO qcRawMatTestCharacteristic VALUES(NULL, 1,1,1,1,1,1,1,'','','', GETDATE(), 1) I get the following: Cannot insert the value NULL into column 'iRawMatTestCharacte...

Updates in Linq To Sql

For updating records, instead of querying the context and updating each record individually, we currently use code that does DeleteAllOnSubmit on existing set of rows and InsertAllOnSubmit on new set of rows. This worked fine for majority of our scenarios, as in if the same row (content) is being inserted/deleted, it gets removed even th...

Clean way to retry failed System.Data.SqlClient operations

I inherited a fairly large C# codebase which is littered hundreds of times with the following way of doing DB operations: using (SqlConnection connection = new SqlConnection(connectionString)) {    SqlCommand command = new SqlCommand(queryString, connection);    command.Connection.Open();    command.ExecuteNonQuery(); } However, due ...

Convert varchar to date.

I (unfortunately) have some dates that were saved into varchar columns. These columns contain dates in the following format: mmddyy For example: 010110 I need to import these values into a table which is set to datetime and formats dates like this: 2010-09-17 00:00:00.000 How can I convert the string above into a dateti...

Unexpected index scan in mysql query plan

I'm getting an index scan on a join with a unique column; it claims to be examining a large number of rows even when it's looking up just one row. This is the query: select t.id, t.twitter_id, t.screen_name, t.text from tweets t inner join twitter_handle th on th.handle = t.screen_na...

after joining two tables the result does not match with the original table

I have problem with inner joining 2 tables: LOOKUP and PERF. LOOKUP ...has only creative name and "perf." table has the creative name as well as the values to it. I need to get the values into LOOKUP (where both the table has common creative name). FYI: UCID is nothing but creative name Also, creative names have duplicates in both the...

sql sort numeric then alphabetically

in this example : 10-20 20-40 50-60 v k r a 12 month 1 month how can i sort it in this order ?: 10-20 20-40 50-60 a k r v 1 month 12 month i use abs(value) but in the alphabetical case doesn't work ...

T-SQL between periods gaps

Hi, I have some data on my table like: DAY | QTY | Name 1/1/2010 | 1 | jack 5/1/2010 | 5 | jack 2/1/2010 | 3 | wendy 5/1/2010 | 2 | wendy my goal is to have a SP requesting a period of time (example: '2010-1-1' to '2010-1-5'), and get no gaps. Output example: DAY | QTY | Name 1/1/2010 | 1 | jack 2/1/2010 | 0 |...

Any Mysql guru who knows how to handle a custom group calculation scenario in 1 query?

I hope any sense can be made from my explanation. I was able to create the query, however my query only works for Items related to Containers, AND only if no more than one Items are related. I really hope anybody can be of any assistance! Consider the following objects: Container Person Item I have one table where instances of all o...

Query to find all FK constraints and their delete rules (SQL Server)

In SQL Server 2005, can I issue an SQL query to list all FK constraints on tables within the DB, and show the delete rule? (ie nothing, cascade, set null, or set default) The output I'm looking for is something akin to: FK_NAME ON_DELETE ================================== FK_LINEITEM_STATEMENT CASCADE FK_ACCOUNTREP_...

mysql query to three tables, want to use JOIN instead subquery

I want to use join instead subquery to find the trade id not exist on trade_log filtered by ip and current date for mysql syntax below. SELECT plug.id as a, plug.url as b, trade.id as c FROM plug, trade WHERE trade.id = plug.trade_id AND trade.id NOT IN (SELECT trade_log.trade_id ...

Shrink data base SQL Server 2008

HI I have made a maintenance package in that have used shrink database task for specific database, it ran successfully, found slight increase in previous db size. Initial size(129 gb) after running the package(130gb). I am expecting after shrinkning it should shrink? what might be happen? am sure package scheduled to run and check the...

How to refer to a variable create in the course of executing a query in T-SQL, in the WHERE clause?

What the title says, really. If I SELECT [statement] AS whatever, why can't I refer to the whatever column in the body of the WHERE clause? Is there some kind of a workaround? It's driving me crazy. ...

Delphi Interbase Sql Conversion to Sql Server and Oracle

hello, i have a delphi application which uses database interbase / firebird. To consult and write data I use the InterBase components palette (IBTable, IBQuery, IBDataset). I'm performing the conversion of my system to sqlserver / Oracle but i have thousands of queries that are assembled at runtime with SQL Instructions Specific of datab...

Should I use a T-SQL function, view, or stored proc ?

Hi Guys, I've got a question about reusing table data but a view won't work in this scenario as I have a parameter that needs to be passed in. Basically this part of the system requires a travellerid to be sent to the procedure and a list of arrangers returned for that specific traveller. There are around 7 business rules that are used ...

Is PARTITION RANGE ALL in your explain plan bad?

Here's my explain plan: SELECT STATEMENT, GOAL = ALL_ROWS 244492 4525870 235345240 SORT ORDER BY 244492 4525870 235345240 **PARTITION RANGE ALL** 207633 4525870 235345240 INDEX FAST FULL SCAN MCT MCT_PLANNED_CT_PK 207633 4525870 235345240 Just wondering if this is the best optimized plan for querying huge partitioned table...

Why does the following SQL script take forever to run?

I have the following Oracle SQL: Begin -- tables for c in (select table_name from user_tables) loop execute immediate ('drop table '||c.table_name||' cascade constraints'); end loop; -- sequences for c in (select sequence_name from user_sequences) loop execute immediate ('drop sequence '||c.sequence_name); end loop; End; It was giv...

SQL Stored Procedure Convert Date Parameter.

Hi, I have an sql stored procedure which accepts two dates, however when I send them in my open query, oracle does not like the date format for some reason.... How can I change the dateformat to YYYY-MM-DD from dd-mm-yyyy in the stored procedure before sending using it. e.g SET @startdate = CONVERT?? Thanks, Tina ...

Does Transact-SQL have a similar function to MS Logparser Quantize?

If you are familer with Microsoft Logparser you probably recognize the Quantize function which will truncate a value to the nearest multiple of another value. It is quite handy for grouping date-time fields into increments. Date-Time Count 1/1/2010 00:00 100 1/1/2010 00:15 134 1/1/2010 00:30 56 .......