sql

How can I shorten this MySQL Query?

Here there, Is there anyway of shortening this MySQL query at all??? It needs to grab two diffrent rows from the PriceRuleDetail table from the column called RuleValue based on it's price rule, but using an Alias, have them returned in the same row. It's using subqueries inside the select statement, which I assume is right, but there m...

How to see the values of a table variable at debug time in T-SQL?

Can we see the values (rows and cells) in a table valued variable in SQL Server Management Studio (SSMS) during debug time? If yes, how? ...

SQL Error Must declare the scalar variable

private void FillInvoiceList() { DataTable distinctInvoice = new DataTable(); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["APOS_CONNECTION_STRING"].ConnectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand("Select distinct svc_tag...

How to implement searching on a vertically designed table?

I have a table structure like this (vertical design). I can have unlimited number of attributes (eg: city, phone etc.) for each user. Table: tbl_UserAttributes ┌────────┬───────────┬────────────┐ | UserID │ FieldName │ Value | ├────────┼───────────┼────────────┤ │ 341 │ city │ MyCity1 │ │ 772 │ phone │ 1234567890...

mysql assign auto number to non-primary key column

i have a table with columns cid (customer id) cemail (customer email) cfax (customer email) cname (customer name) now i want that there should be no duplication in cid,cemail,cfax as i do that i make these all as primary keys but if only one value is different database accept data i want that it should check all values what should i...

How should i work in this scenario . Should I use Trigger or Leave on User to manage

I am creating an application in which i am using stored procedure where i m implementing my logic. Now what i need to know is that- i want my database not to contain any invalid entry, for this purpose should i create triggers, for implementing my data validation logic such that when FailPasswordAttemptCount is changed to some value th...

SQL CASE WHEN Recurring expression in case clause

my query: SELECT CASE WHEN DAYS(DATE( SUBSTR(DIGITS(STOCK_MONTH),5,4) CONCAT '-' CONCAT SUBSTR(DIGITS(STOCK_MONTH),9,2) CONCAT '-01') - 1 DAY + 1 MONTH) - DAYS(HUB_ARRIVAL_DT) < 31 THEN ' 030' WHEN DAYS(DATE( SUBSTR(DIGITS(STOCK_MONTH),5,4) CONCAT '-' CONCAT SUBSTR(DIGITS(STOCK_MONTH),9,2) CONCAT '-01') - 1 DAY + 1 MONTH) - DAY...

function for calculating working week SQL Server

I have a similar question posted, but did not quite get the answer I needed I have the following sql code which gets built up in a c# app... This code works fine, except for when the date range is bigger than 1 working week. I need to edit the code so that when the CSIAchieved date falls into the next working week it is 'outside' of SLA...

SQL return rows where count of children does not equal column value

I have a table called Item with columns ItemID (PK), ItemName, ExpectedSubItems and another table called SubItem with columns SubItemID (PK), ItemID (FK), SubItemName. I want to return all rows from Item where the number of SubItems is different from ExpectedSubItems. I tried to use something like:- Select * From Item Join SubItem on ...

Getting the Nth most recent business date - very different query performance using two different methods

I have a requirement to get txns on a T-5 basis. Meaning I need to "go back" 5 business days. I've coded up two SQL queries for this and the second method is 5 times slower than the first. How come? -- Fast with BizDays as ( select top 5 bdate bdate from dbo.business_days where bdate < '20091211' order by bdate Desc ) ,BizDate ...

MySql: Set limit for duplicate column.

OK I have a column on my table called style_number. There can be duplicates of style_number. I want to select everything, but only up to 3 for each style number. So for example if there are 4 rows that have a style_number = "style 7" only 3 of those 4 would be selected (doesn't matter which 3, any 3). Does this make sense? How can I d...

Setting a default value for a stored proc select statement

I am creating a stored proc that selects a value from a table and uses it in another procedure. If the first value that is searched doesn’t exist I need it to use a default value. I’m new to stored procs so I’m not sure of the best practices. Here is the first select statement which may or may not return a value. If it doesn’t return a ...

ORA-01861: literal does not match format string

I'm pretty sure this is something really silly. Instead of spending another 10 minutes with this ( or better said, while I spend another 10 minutes with this ) I'll ask it here in parallel. What's wrong with the following statement: select to_date( '30/10/2009 18:27:35', 'DD/MM/YYY HH24:MI:SS') from dual The error message is: Err...

Sqlite group_concat select with "special needs"

I know how to use group_concat with Sqlite, to do the following: id - f1 - f2 - f3 1 - 1 - a - NULL 2 - 1 - b - NULL 3 - 2 - c - NULL 4 - 2 - d - NULL select id, f1, group_concat(f2), f3 from table group by f1 result: 2 - 1 - a,b - NULL 4 - 2 - c,d - NULL as you can see, the ID's 1 and 3 are dropped, which is the ...

How do I update multiple records in a table with a single MySQL query?

Hello. I would like to update multiple records in a MySQL table using a single query. Basically, this is a tasks table, which has assignments for different people on different dates. When these assignments are changed and submitted via the Online form there is a lot of POST data that gets submitted (pretty much all the pending assignm...

How to take the near number in a column at Oracle Select.

Hello Folks, I have an adress table and I need to take the near number. For example if I´m entenring the number 256 in this case I´ll take 257 because: 254<--256->257 Somebody knows the solution. Thanks and sorry for my bad English. ...

PSQL select max value per minute per id for multiple values a minute per id?

Given a table that looks like this: sensor_id | time | voltage -----------+------------------------+------------ 12292 | 2009-12-01 00:50:04-07 | 2270 12282 | 2009-12-01 00:50:04-07 | 93774 12192 | 2009-12-01 00:50:04-07 | 9386 12609 | 2009-12-01 00:50:05-07 | 0 125...

PK, IDs and simulating an 'object' in a table

The object part is misleading. My question is not specific to one type of sql. ATM i am using sqlite but i will be switching to TSQL (It looks to be what my host is offering) and i am rewriting some tables and logic to clean things up. One pattern i notice is i have a bigint that could possible be one of 2+ keys and sometimes if i need...

How do you bulk import from a file during a Visual Studio database project deployment?

I’ve got a database project in Visual Studio that uses bulk import during its post-deployment tasks. This works perfectly if I give the full path to the file; aka “c:...”. I would like to convert this to use build variables so that I don’t need to give it the full absolute path to the files. Something closer to $(SolutionDir)$(OutDir)\f...

Using SELECT resultset to run UPDATE query with MySQL Stored Procedures

I'm trying to understand MySQL Stored Procedures, I want to check if a users login credentials are valid and if so, update the users online status: -- DROP PROCEDURE IF EXISTS checkUser; DELIMITER // CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50)) BEGIN SELECT id, name FROM users WHERE email = in_emai...