Search Results

Search found 1805 results on 73 pages for 'varchar'.

Page 28/73 | < Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >

  • How do I execute a sql statement through a variable (dyname sql) that tries to do an insert into a variable table?

    - by Testifier
    If I do what I wanna do with a TEMPORARY TABLE, it works fine: DECLARE @CTRFR VARCHAR(MAX) SET @CTRFR = 'select blah blah blah' -- <-- very long select statement. this returns a 0 or some greater number. Please note! --> I NEED THIS NUMBER. IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo][#CTRFRResult]') AND type IN ( N'U' ) ) DROP TABLE [dbo].[#CTRFRResult] CREATE TABLE #CTRFRResult ( CTRFRResult VARCHAR(MAX) ) SET @CTRFR = 'insert into #CTRFRResult ' + @CTRFR EXEC(@CTRFR) The above works fine. The problem is that several databases are using the same TEMP table. Therefore I need to use a VARIABLE table (instead of a temporary table). What I have below is not working because it says that the table must be declared. DECLARE @CTRFRResult TABLE ( CTRFRResult VARCHAR(MAX) ) SET @CTRFR = 'insert into @CTRFRResult ' + @CTRFR -- I think the issue is here. EXEC(@CTRFR) Setting @CTRFR to 'insert into...' is not working because I'm assuming the table name is out of scope. How would I go about mimicking the temporary table code using a variable table?

    Read the article

  • The best way to return related data in a SQL statement

    - by Darvis Lombardo
    I have a question on the best method to get back to a piece of data that is in a related table on the other side of a many-to-many relationship table. My first method uses joins to get back to the data, but because there are multiple matching rows in the relationship table, I had to use a TOP 1 to get a single row result. My second method uses a subquery to get the data but this just doesn't feel right. So, my question is, which is the preferred method, or is there a better method? The script needed to create the test tables, insert data, and run the two queries is below. Thanks for your advice! Darvis -------------------------------------------------------------------------------------------- -- Create Tables -------------------------------------------------------------------------------------------- DECLARE @TableA TABLE ( [A_ID] [int] IDENTITY(1,1) NOT NULL, [Description] [varchar](50) NULL) DECLARE @TableB TABLE ( [B_ID] [int] IDENTITY(1,1) NOT NULL, [A_ID] [int] NOT NULL, [Description] [varchar](50) NOT NULL) DECLARE @TableC TABLE ( [C_ID] [int] IDENTITY(1,1) NOT NULL, [Description] [varchar](50) NOT NULL) DECLARE @TableB_C TABLE ( [B_ID] [int] NOT NULL, [C_ID] [int] NOT NULL) -------------------------------------------------------------------------------------------- -- Insert Test Data -------------------------------------------------------------------------------------------- INSERT INTO @TableA VALUES('A-One') INSERT INTO @TableA VALUES('A-Two') INSERT INTO @TableA VALUES('A-Three') INSERT INTO @TableB (A_ID, Description) VALUES(1,'B-One') INSERT INTO @TableB (A_ID, Description) VALUES(1,'B-Two') INSERT INTO @TableB (A_ID, Description) VALUES(1,'B-Three') INSERT INTO @TableB (A_ID, Description) VALUES(2,'B-Four') INSERT INTO @TableB (A_ID, Description) VALUES(2,'B-Five') INSERT INTO @TableB (A_ID, Description) VALUES(3,'B-Six') INSERT INTO @TableC VALUES('C-One') INSERT INTO @TableC VALUES('C-Two') INSERT INTO @TableC VALUES('C-Three') INSERT INTO @TableB_C (B_ID, C_ID) VALUES(1, 1) INSERT INTO @TableB_C (B_ID, C_ID) VALUES(2, 1) INSERT INTO @TableB_C (B_ID, C_ID) VALUES(3, 1) -------------------------------------------------------------------------------------------- -- Get result - method 1 -------------------------------------------------------------------------------------------- SELECT TOP 1 C.*, A.Description FROM @TableC C JOIN @TableB_C BC ON BC.C_ID = C.C_ID JOIN @TableB B ON B.B_ID = BC.B_ID JOIN @TableA A ON B.A_ID = A.A_ID WHERE C.C_ID = 1 -------------------------------------------------------------------------------------------- -- Get result - method 2 -------------------------------------------------------------------------------------------- SELECT C.*, (SELECT A.Description FROM @TableA A WHERE EXISTS (SELECT * FROM @TableB_C BC JOIN @TableB B ON B.B_ID = BC.B_ID WHERE BC.C_ID = C.C_ID AND B.A_ID = A.A_ID)) FROM @TableC C WHERE C.C_ID = 1

    Read the article

  • MYSQL variables - SET @var

    - by Lizard
    I am attempting to create a mysql snippet that will analyse a table and remove duplicate entries (duplicates are based on two fields not entire record) I have the following code that works when I hard code the variables in the queries, but when I take them out and put them as variables I get mysql errors, below is the script SET @tblname = 'mytable'; SET @fieldname = 'myfield'; SET @concat1 = 'checkfield1'; SET @concat2 = 'checkfield2'; ALTER TABLE @tblname ADD `tmpcheck` VARCHAR( 255 ) NOT NULL; UPDATE @tblname SET `tmpcheck` = CONCAT(@concat1,'-',@concat2); CREATE TEMPORARY TABLE `tmp_table` ( `tmpfield` VARCHAR( 100 ) NOT NULL ) ENGINE = MYISAM ; INSERT INTO `tmp_table` (`tmpfield`) SELECT @fieldname FROM @tblname GROUP BY `tmpcheck` HAVING ( COUNT(`tmpcheck`) > 1 ); DELETE FROM @tblname WHERE @fieldname IN (SELECT `tmpfield` FROM `tmp_table`); ALTER TABLE @tblname DROP `tmpcheck`; I am getting the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@tblname ADD `tmpcheck` VARCHAR( 255 ) NOT NULL' at line 1 Is this because I can't use a variable for a table name? What else could be wrong or how wopuld I get around this issue. Thanks in adavnce

    Read the article

  • MySQL Multiple Table Join

    - by hitman001
    I have a 3 tables that I'm trying to join and get distinct results. CREATE TABLE `car` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB mysql> select * from car; +----+-------+ | id | name | +----+-------+ | 1 | acura | +----+-------+ CREATE TABLE `tires` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tire_desc` varchar(255) DEFAULT NULL, `car_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `new_fk_constraint` (`car_id`), CONSTRAINT `new_fk_constraint` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB mysql> select * from tires; +----+-------------+--------+ | id | tire_desc | car_id | +----+-------------+--------+ | 1 | front_right | 1 | | 2 | front_left | 1 | +----+-------------+--------+ CREATE TABLE `lights` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `lights_desc` varchar(255) NOT NULL, `car_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `new1_fk_constraint` (`car_id`), CONSTRAINT `new1_fk_constraint` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB mysql> select * from lights; +----+-------------+--------+ | id | lights_desc | car_id | +----+-------------+--------+ | 1 | right_light | 1 | | 2 | left_light | 1 | +----+-------------+--------+ Here is my query. mysql> SELECT name, group_concat(tire_desc), group_concat(lights_desc) FROM car left join tires on car.id = tires.car_id left join lights on car.id = car_id group by car.id; +-------+-----------------------------------------------+-----------------------------------------------+ | name | group_concat(tire_desc) | group_concat(lights_desc) | +-------+-----------------------------------------------+-----------------------------------------------+ | acura | front_right,front_right,front_left,front_left | right_light,left_light,right_light,left_light | +-------+-----------------------------------------------+-----------------------------------------------+ I get duplicate entires and this is what I would like to get. +-------+-----------------------------------------------+--------------------------------+ | name | group_concat(tire_desc) | group_concat(lights_desc) | +-------+-----------------------------------------------+--------------------------------+ | acura | front_right,front_left | right_light,left_light | +-------+-----------------------------------------------+--------------------------------+ I cannot use distinct in group_concat because I might have legitimate duplicates which I would like to keep. Is there any way to do this query using joins and not using inner selects like the statement below? SELECT name, (select group_concat(tire_desc) from tires where car.id = tires.car_id), (select group_concat(lights_desc) from lights where car.id = lights.car_id) FROM car Also, if I will use inner selects, will there be any performance issues over joins?

    Read the article

  • How to create festival calendar in ASP.net

    - by Atul
    I want to make a festival calendar using asp.net from that I used two ajax calendar and one textbox it is a festival textbox where we enter festival which FromDate and ToDate respectively. I want to do this as following point If I enter in textbox Christmas and Choose Fromdate=25/12/2011 and ToDate=31/12/2011 then it will be valid If I choose fromDate=25/12/2011 and ToDate=24/12/2011 then it will invalid If I choose Fromdate=25/12/2011 and Todate=28/12/2011 then also it is invalid because it coming in between 25/12/2011 and 31/12/2011 If I Choose fromdate=1/1/2011 and ToDate=1/1/2011 then it is valid If I choose fromdate=21/12/2011 and 25/12/2011 then it is invalid because of already Christmas done in 1/1/2011 And all date should show in gridview like 25-dec-2011 format Here is my code: DateTime dt1 = Convert.ToDateTime(txt_fromdate.Text); DateTime dt2 = Convert.ToDateTime(txt_todate.Text); if (dt1 > dt2) { con.Open(); com = new SqlCommand("BTNN_MovieDB_Festival_Details_Insert", con); com.Parameters.Add("@fromdate", SqlDbType.VarChar).Value = dateformat_mmdd(txt_fromdate.Text.ToString().Trim()); com.Parameters.Add("@todate", SqlDbType.VarChar).Value = dateformat_mmdd(txt_todate.Text.ToString().Trim()); com.Parameters.Add("@return", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue; com.ExecuteNonQuery(); con.Close(); showdata(); } else if (dt1 < dt2) { lblerror.Text = "ToDate should be greater than FromDate"; }

    Read the article

  • '<=' operator is not working in sql server 2000

    - by Lalit
    Hello, Scenario is, database is in the maintenance phase. this database is not developed by ours developer. it is an existing database developed by the 'xyz' company in sql server 2000. This is real time database, where i am working now. I wanted to write the stored procedure which will retrieve me the records From date1 to date 2.so query is : Select * from MyTableName Where colDate>= '3-May-2010' and colDate<= '5-Oct-2010' and colName='xyzName' whereas my understanding I must get data including upper bound date as well as lower bound date. but somehow I am getting records from '3-May-2010' (which is fine but) to '10-Oct-2010' As i observe in table design , for ColDate, developer had used 'varchar' to store the date. i know this is wrong remedy by them. so in my stored procedure I have also used varchar parameters as @FromDate1 and @ToDate to get inputs for SP. this is giving me result which i have explained. i tried to take the parameter type as 'Datetime' but it is showing error while saving/altering the stored procedure that "@FromDate1 has invalid datatype", same for "@ToDate". situation is that, I can not change the table design at all. what i have to do here ? i know we can use user defined table in sql server 2008 , but there is version sql server 2000. which does not support the same. Please guide me for this scenario. **Edited** I am trying to write like this SP: CREATE PROCEDURE USP_Data (@Location varchar(100), @FromDate DATETIME, @ToDate DATETIME) AS SELECT * FROM dbo.TableName Where CAST(Dt AS DATETIME) >=@fromDate and CAST(Dt AS DATETIME)<=@ToDate and Location=@Location GO but getting Error: Arithmetic overflow error converting expression to data type datetime. in sql server 2000 What should be that ? is i am wrong some where ? also (202 row(s) affected) is changes every time in circular manner means first time sayin (122 row(s) affected) run again saying (80 row(s) affected) if again (202 row(s) affected) if again (122 row(s) affected) I can not understand what is going on ?

    Read the article

  • mysql report sql help

    - by sfgroups
    I have mysql table with data like this. record will have server with total cpu and virtual server with cpu assinged type, cpu srv1, 10 vsrv11, 2 vsrv12, 3 srv2, 15 vsrv21, 6 vsrv22, 7 vsrv23, 1 from the above data, I want to create output like this. server, total cpu, assigned cpu, free cpu srv1, 10, 5, 5 srv2, 15, 14, 1 Can you help me on creating sql query for this report? I have changed my table and data like this. CREATE TABLE `cpuallocation` ( `servertype` varchar(10) DEFAULT NULL, `servername` varchar(20) DEFAULT NULL, `hostname` varchar(20) DEFAULT NULL, `cpu_count` float DEFAULT NULL, UNIQUE KEY `server_uniq_idx` (`servertype`,`servername`,`hostname`) insert into cpuallocation values('srv', 'server1', '',16); insert into cpuallocation values('vir', 'server1', 'host1',5); insert into cpuallocation values('vir', 'server1', 'host2',2.5); insert into cpuallocation values('vir', 'server1', 'host3',4.5); insert into cpuallocation values('srv', 'server2', '',8); insert into cpuallocation values('vir', 'server2', 'host1',5); insert into cpuallocation values('vir', 'server2', 'host2',2.5); insert into cpuallocation values('srv', 'server3', '',24); insert into cpuallocation values('vir', 'server3', 'host1',12); insert into cpuallocation values('vir', 'server3', 'host2',2); insert into cpuallocation values('srv', 'server4', '',12); Update: I created two view, now I getting the result I want. create view v1 as select servername, sum(cpu_count) as cpu_allocated from cpuallocation where servertype='vir' group by servername; create view v2 as select servername, cpu_count as total_cpu from cpuallocation where servertype='srv'; select a.servername, a.total_cpu, b.cpu_allocated from v2 as a left join v1 as b on a.servername=b.servername; +------------+-----------+---------------+ | servername | total_cpu | cpu_allocated | +------------+-----------+---------------+ | server1 | 16 | 12 | | server2 | 8 | 7.5 | | server3 | 24 | 14 | | server4 | 12 | NULL | +------------+-----------+---------------+ 4 rows in set (0.00 sec) Is it possible to create a query with-out creating views?

    Read the article

  • Query to bring count from comma seperated Value

    - by Mugil
    I have Two Tables One for Storing Products and Other for Storing Orders List. CREATE TABLE ProductsList(ProductId INT NOT NULL PRIMARY KEY, ProductName VARCHAR(50)) INSERT INTO ProductsList(ProductId, ProductName) VALUES(1,'Product A'), (2,'Product B'), (3,'Product C'), (4,'Product D'), (5,'Product E'), (6,'Product F'), (7,'Product G'), (8,'Product H'), (9,'Product I'), (10,'Product J'); CREATE TABLE OrderList(OrderId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, EmailId VARCHAR(50), CSVProductIds VARCHAR(50)) SELECT * FROM OrderList INSERT INTO OrderList(EmailId, CSVProductIds) VALUES('[email protected]', '2,4,1,5,7'), ('[email protected]', '5,7,4'), ('[email protected]', '2'), ('[email protected]', '8,9'), ('[email protected]', '4,5,9'), ('[email protected]', '1,2,3'), ('[email protected]', '9,10'), ('[email protected]', '1,5'); Output ItemName NoOfOrders Product A 4 Product B 3 Product C 1 Product D 3 Product E 4 Product F 0 Product G 2 Product H 1 Product I 2 Product J 1 The Order List Stores the ItemsId as Comma separated value for every customer who places order.Like this i am having more than 40k Records in my dB table Now I am assigned with a task of creating report in which I should display Items and No of People ordered Items as Shown Below I Used Query as below in my PHP to bring the Orders One By One and storing in array. SELECT COUNT(PL.EmailId) FROM OrderList PL WHERE CSVProductIds LIKE '2' OR CSVProductIds LIKE '%,2,%' OR CSVProductIds LIKE '%,2' OR CSVProductIds LIKE '2,%'; 1.Is it possible to get the same out put by using Single Query 2.Does using a like in mysql query slows down the dB when the table has more no of records i.e 40k rows

    Read the article

  • Field to display Previous 30 Day Total

    - by whytheq
    I've got this table: CREATE TABLE #Data1 ( [Market] VARCHAR(100) NOT NULL, [Operator] VARCHAR(100) NOT NULL, [Date] DATETIME NOT NULL, [Measure] VARCHAR(100) NOT NULL, [Amount] NUMERIC(36,10) NOT NULL, --new calculated fields [DailyAvg_30days] NUMERIC(38,6) NULL DEFAULT 0 ) I've populated all the fields apart from DailyAvg_30days. This field needs to show the total for the preceding 30 days e.g. 1. if Date for a particular record is 2nd Dec then it will be the total for the period 3rd Nov - 2nd Dec inclusive. 2. if Date for a particular record is 1st Dec then it will be the total for the period 2nd Nov - 1st Dec inclusive. My attempt to try to find these totals before updating the table is as follows: SELECT a.[Market], a.[Operator], a.[Date], a.[Measure], a.[Amount], [DailyAvg_30days] = SUM(b.[Amount]) FROM #Data1 a INNER JOIN #Data1 b ON a.[Market] = b.[Market] AND a.[Operator] = b.[Operator] AND a.[Measure] = b.[Measure] AND a.[Date] >= b.[Date]-30 AND a.[Date] <= b.[Date] GROUP BY a.[Market], a.[Operator], a.[Date], a.[Measure], a.[Amount] ORDER BY 1,2,4,3 Is this a valid approach or do I need to approach this from a different angle?

    Read the article

  • Simple UPDATE query with (sometime) long query times

    - by Eric
    I run a dedicated MySQL server (2 cores, 16GB RAM) serving 100-200 requests per second. It is getting sluggish during peak traffic and I have a hard time optimizing the server. So I'm looking for some ideas now that I have done lots of Innodb fine-tuning with the "TUNING PRIMER" The query that now generates most slow queries is the following (see result from mysqldumpslow): Count: 433 Time=3.40s (1470s) Lock=0.00s (0s) Rows=0.0 (0), UPDATE user_sessions SET tid='S' WHERE idsession='S' I am very surprised to have so many long queries for such a simple query with no locking. Fyi, the table is InnoDB and has 14000 rows. It contains all active sessions on the site with approx 10 UPDATE and SELECT hits per second. Here is its structure: CREATE TABLE `user_sessions` ( `personid` mediumint(9) NOT NULL DEFAULT '0', `ip` varchar(18) COLLATE utf8_unicode_ci NOT NULL, `idsession` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `datum` date NOT NULL DEFAULT '0000-00-00', `tid` time NOT NULL DEFAULT '00:00:00', `status` tinyint(4) NOT NULL DEFAULT '0', KEY `personid` (`personid`), KEY `idsession` (`idsession`), KEY `datum` (`datum`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci Any ideas?

    Read the article

  • Proftpd user-auth with mod_sql/mod_sql_passwd

    - by Zae
    I'm reading up how to interface ProFTPd with MySQL for an implementation I'm working on, I noticed it seems like all the example code or instructions I see have the user login field in MySQL set as "varchar(30)". I don't see anything saying there's a limit to the field length for ProFTPd, but I wanted to check around anyway. The project this setup is going to get mixed into was planning to have their universal usernames support "varchar(255)". Can I use that safely? or is there an FTP limitation elsewhere I'm missing? Running ProFTPd 1.3.4a(custom compiled), MySQL 5.1.54(ubuntu repos)

    Read the article

  • Dates not recognized as dates in pivot table pulling directly from SQL Server

    - by Michael K
    My pivot pulls from an external data source with a date column. Excel doesn't see this column as a date and the 'Format Cells' option panel doesn't change how the dates are displayed. The cell data is left-aligned, suggesting a string rather than a date. I have tried cast(myvar as date) and convert(varchar, myvar, 101) and convert(varchar, myvar, 1) in the base table, but none of these have been picked up by Excel as dates. If the column is recognized as a date, I can group by week and month. I understand that if I can't fix this, the next step is to add columns with weeks and months for each date to the table, but I'd like to give formatting the column one more shot before doing that.

    Read the article

  • Mysql query help - Alter this mysql query to get these results?

    - by sandeepan-nath
    Please execute the following queries first to set up so that you can help me:- CREATE TABLE IF NOT EXISTS `Tutor_Details` ( `id_tutor` int(10) NOT NULL auto_increment, `firstname` varchar(100) NOT NULL default '', `surname` varchar(155) NOT NULL default '', PRIMARY KEY (`id_tutor`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ; INSERT INTO `Tutor_Details` (`id_tutor`,`firstname`, `surname`) VALUES (1, 'Sandeepan', 'Nath'), (2, 'Bob', 'Cratchit'); CREATE TABLE IF NOT EXISTS `Classes` ( `id_class` int(10) unsigned NOT NULL auto_increment, `id_tutor` int(10) unsigned NOT NULL default '0', `class_name` varchar(255) default NULL, PRIMARY KEY (`id_class`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=229 ; INSERT INTO `Classes` (`id_class`,`class_name`, `id_tutor`) VALUES (1, 'My Class', 1), (2, 'Sandeepan Class', 2); CREATE TABLE IF NOT EXISTS `Tags` ( `id_tag` int(10) unsigned NOT NULL auto_increment, `tag` varchar(255) default NULL, PRIMARY KEY (`id_tag`), UNIQUE KEY `tag` (`tag`), KEY `id_tag` (`id_tag`), KEY `tag_2` (`tag`), KEY `tag_3` (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ; INSERT INTO `Tags` (`id_tag`, `tag`) VALUES (1, 'Bob'), (6, 'Class'), (2, 'Cratchit'), (4, 'Nath'), (3, 'Sandeepan'), (5, 'My'); CREATE TABLE IF NOT EXISTS `Tutors_Tag_Relations` ( `id_tag` int(10) unsigned NOT NULL default '0', `id_tutor` int(10) default NULL, KEY `Tutors_Tag_Relations` (`id_tag`), KEY `id_tutor` (`id_tutor`), KEY `id_tag` (`id_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `Tutors_Tag_Relations` (`id_tag`, `id_tutor`) VALUES (3, 1), (4, 1), (1, 2), (2, 2); CREATE TABLE IF NOT EXISTS `Class_Tag_Relations` ( `id_tag` int(10) unsigned NOT NULL default '0', `id_class` int(10) default NULL, `id_tutor` int(10) NOT NULL, KEY `Class_Tag_Relations` (`id_tag`), KEY `id_class` (`id_class`), KEY `id_tag` (`id_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `Class_Tag_Relations` (`id_tag`, `id_class`, `id_tutor`) VALUES (5, 1, 1), (6, 1, 1), (3, 2, 2), (6, 2, 2); In the present system data which I have given , tutor named "Sandeepan Nath" has has created class named "My Class" and tutor named "Bob Cratchit" has created class named "Sandeepan Class". Requirement - To execute a single query with limit on the results to show search results as per AND logic on the search keywords like this:- If "Sandeepan Class" is searched , Tutor Sandeepan Nath's record from Tutor Details table is returned(because "Sandeepan" is the firstname of Sandeepan Nath and Class is present in class name of Sandeepan's class) If "Class" is searched Both the tutors from the Tutor_details table are fetched because Class is present in the name of the class created by both the tutors. Following is what I have so far achieved (PHP Mysql):- <?php $searchTerm1 = "Sandeepan"; $searchTerm2 = "Class"; mysql_select_db("test"); $sql = "SELECT td.* FROM Tutor_Details AS td LEFT JOIN Tutors_Tag_Relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor LEFT JOIN Classes AS wc ON td.id_tutor = wc.id_tutor LEFT JOIN Class_Tag_Relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor LEFT JOIN Tags as t1 on ((t1.id_tag = ttagrels.id_tag) OR (t1.id_tag = wtagrels.id_tag)) LEFT JOIN Tags as t2 on ((t2.id_tag = ttagrels.id_tag) OR (t2.id_tag = wtagrels.id_tag)) where t1.tag LIKE '%".$searchTerm1."%' AND t2.tag LIKE '%".$searchTerm2."%' GROUP BY td.id_tutor LIMIT 10 "; $result = mysql_query($sql); echo $sql; if($result) { while($rec = mysql_fetch_object($result)) $recs[] = $rec; //$rec = mysql_fetch_object($result); echo "<br><br>"; if(is_array($recs)) { foreach($recs as $each) { print_r($each); echo "<br>"; } } } ?> But the results are :- If "Sandeepan Nath" is searched, it does not return any tutor (instead of only Sandeepan's row) If "Sandeepan Class" is searched, it returns Sandeepan's row (instead of Both tutors ) If "Bob Class" is searched, it correctly returns Bob's row If "Bob Cratchit" is searched, it does not return any tutor (instead of only

    Read the article

  • AspxGridView Specified method is not supported. problem

    - by shamim
    Bellow is my .aspx aspxGridview syntax <dx:ASPxGridView ID="ASPxGridView1" runat="server" AutoGenerateColumns="False" KeyFieldName="intProductCode" onrowinserted="ASPxGridView1_RowInserted"> <Columns> <dx:GridViewCommandColumn VisibleIndex="0"> <EditButton Visible="True"> </EditButton> <NewButton Visible="True"> </NewButton> <DeleteButton Visible="True"> </DeleteButton> </dx:GridViewCommandColumn> <dx:GridViewDataTextColumn Caption="intProductCode" FieldName="intProductCode" VisibleIndex="1"> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn Caption="strProductName" FieldName="strProductName" VisibleIndex="2"> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn Caption="SKU" FieldName="SKU" VisibleIndex="3"> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn Caption="PACK" FieldName="PACK" VisibleIndex="4"> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn Caption="intQtyPerCase" FieldName="intQtyPerCase" VisibleIndex="5"> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn Caption="mnyCasePrice" FieldName="mnyCasePrice" VisibleIndex="6"> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn Caption="intTBQtyPerCase" FieldName="intTBQtyPerCase" VisibleIndex="7"> </dx:GridViewDataTextColumn> <dx:GridViewDataCheckColumn Caption="bIsActive" FieldName="bIsActive" VisibleIndex="8"> </dx:GridViewDataCheckColumn> <dx:GridViewDataTextColumn Caption="intSortingOrder" FieldName="intSortingOrder" VisibleIndex="9"> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn Caption="strProductAccCode" FieldName="strProductAccCode" VisibleIndex="10"> </dx:GridViewDataTextColumn> </Columns> </dx:ASPxGridView> Bellow is my C# syntax : protected void Page_Load(object sender, EventArgs e) { if (this.IsPostBack != true) { BindGridView(); } } private void BindGridView() { DB_OrderV2DataContext db = new DB_OrderV2DataContext(); var r = from p in db.tblProductInfos select p; ASPxGridView1.DataSource = r; ASPxGridView1.DataBind(); } protected void LinqServerModeDataSource1_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { DB_OrderV2DataContext db = new DB_OrderV2DataContext(); var r= from p in db.tblProductInfos select p; e.QueryableSource = r; } protected void ASPxGridView1_RowInserted(object sender, DevExpress.Web.Data.ASPxDataInsertedEventArgs e) { DB_OrderV2DataContext db = new DB_OrderV2DataContext(); tblProductInfo otblProductInfo = new tblProductInfo (); otblProductInfo.intProductCode = (db.tblProductInfos.Max(p => (int?)p.intProductCode) ?? 0) + 1;//oProductController.GenerateProductCode(); otblProductInfo.strProductName = Convert.ToString(e.NewValues["strProductName"]); otblProductInfo.SKU = Convert.ToString(e.NewValues["SKU"]); otblProductInfo.PACK = Convert.ToString(e.NewValues["PACK"]); otblProductInfo.intQtyPerCase = Convert.ToInt32(e.NewValues["intQtyPerCase"]); otblProductInfo.mnyCasePrice = Convert.ToDecimal(e.NewValues["mnyCasePrice"]); otblProductInfo.intTBQtyPerCase = Convert.ToInt32(e.NewValues["intTBQtyPerCase"]); otblProductInfo.bIsActive = Convert.ToBoolean(e.NewValues["bIsActive"]); otblProductInfo.intSortingOrder = (db.tblProductInfos.Max(p => (int?)p.intSortingOrder) ?? 0) + 1;//oProductController.GenerateSortingOrder(); db.tblProductInfos.InsertOnSubmit(otblProductInfo);//the InsertOnSubmit method called in the preceding code was named Add and the DeleteOnSubmit method was named Remove. db.SubmitChanges(); BindGridView(); //oProductController.InsertAndSubmit(); // ASPxGridView1.DataBind(); } My SQL syntax CREATE TABLE [dbo].[tblProductInfo]( [intProductCode] [int] NOT NULL, [strProductName] [varchar](100) NULL, [SKU] [varchar](50) NULL, [PACK] [varchar](50) NULL, [intQtyPerCase] [int] NULL, [mnyCasePrice] [money] NULL, [intTBQtyPerCase] [int] NULL, [bIsActive] [bit] NULL, [intSortingOrder] [int] NULL, [strProductAccCode] [varchar](max) NULL, CONSTRAINT [PK_tblProductInfo] PRIMARY KEY CLUSTERED ( [intProductCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] When i want to insert ,show me error message Specified method is not supported. How to solve it.

    Read the article

  • Fluent NHibernate Many to one mapping

    - by Jit
    I am creating a NHibenate application with one to many relationship. Like City and State data. City table CREATE TABLE [dbo].[State]( [StateId] [varchar](2) NOT NULL primary key, [StateName] [varchar](20) NULL) CREATE TABLE [dbo].[City]( [Id] [int] primary key IDENTITY(1,1) NOT NULL , [State_id] [varchar](2) NULL refrences State(StateId), [CityName] [varchar](50) NULL) My mapping is follows public CityMapping() { Id(x = x.Id); Map(x = x.State_id); Map(x = x.CityName); HasMany(x = x.EmployeePreferedLocations) .Inverse() .Cascade.SaveUpdate() ; References(x = x.State) //.Cascade.All(); //.Class(typeof(State)) //.Not.Nullable() .Cascade.None() .Column("State_id") ; } public StateMapping() { Id(x => x.StateId) .GeneratedBy.Assigned(); Map(x => x.StateName); HasMany(x => x.Jobs) .Inverse(); //.Cascade.SaveUpdate(); HasMany(x => x.EmployeePreferedLocations) .Inverse(); HasMany(x => x.Cities) // .Inverse() .Cascade.SaveUpdate() //.Not.LazyLoad() ; } Models are as follows: [Serializable] public partial class City { public virtual System.String CityName { get; set; } public virtual System.Int32 Id { get; set; } public virtual System.String State_id { get; set; } public virtual IList<EmployeePreferedLocation> EmployeePreferedLocations { get; set; } public virtual JobPortal.Data.Domain.Model.State State { get; set; } public City(){} } public partial class State { public virtual System.String StateId { get; set; } public virtual System.String StateName { get; set; } public virtual IList<City> Cities { get; set; } public virtual IList<EmployeePreferedLocation> EmployeePreferedLocations { get; set; } public virtual IList<Job> Jobs { get; set; } public State() { Cities = new List<City>(); EmployeePreferedLocations = new List<EmployeePreferedLocation>(); Jobs = new List<Job>(); } //public virtual void AddCity(City city) //{ // city.State = this; // Cities.Add(city); //} } My Unit Testing code is below. City city = new City(); IRepository<State> rState = new Repository<State>(); Dictionary<string, string> critetia = new Dictionary<string, string>(); critetia.Add("StateId", "TX"); State frState = rState.GetByCriteria(critetia); city.CityName = "Waco"; city.State = frState; IRepository<City> rCity = new Repository<City>(); rCity.SaveOrUpdate(city); City frCity = rCity.GetById(city.Id); The problem is , I am not able to insert record. The error is below. "Invalid index 2 for this SqlParameterCollection with Count=2." But the error will not come if I comment State_id mapping field in the CityMapping file. I donot know what mistake is I did. If do not give the mapping Map(x = x.State_id); the value of this field is null, which is desired. Please help me how to solve this issue.

    Read the article

  • Mysql - Help me alter this search query involving multiple joins and conditions to get the desired r

    - by sandeepan-nath
    About the system - We are following tags based search. Tutors create packs - tag relations for tutors stored in tutors_tag_relations and those for packs stored in learning_packs_tag_relations. All tags are stored in tags table. The system has 6 tables - tutors, Users (linked to tutor_details), learning_packs, learning_packs_tag_relations, tutors_tag_relations and tags Please run the following fresh queries to setup the system :- CREATE TABLE IF NOT EXISTS learning_packs_tag_relations ( id_tag int(10) unsigned NOT NULL DEFAULT '0', id_tutor int(10) DEFAULT NULL, id_lp int(10) unsigned DEFAULT NULL, KEY Learning_Packs_Tag_Relations_FKIndex1 (id_tag), KEY id_lp (id_lp), KEY id_tag (id_tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS learning_packs ( id_lp int(10) unsigned NOT NULL AUTO_INCREMENT, id_status int(10) unsigned NOT NULL DEFAULT '2', id_author int(10) unsigned NOT NULL DEFAULT '0', name varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (id_lp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ; CREATE TABLE IF NOT EXISTS tutors_tag_relations ( id_tag int(10) unsigned NOT NULL DEFAULT '0', id_tutor int(10) DEFAULT NULL, KEY Tutors_Tag_Relations (id_tag), KEY id_tutor (id_tutor), KEY id_tag (id_tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS users ( id_user int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL DEFAULT '', surname varchar(155) NOT NULL DEFAULT '', PRIMARY KEY (id_user) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ; CREATE TABLE IF NOT EXISTS tutor_details ( id_tutor int(10) NOT NULL AUTO_INCREMENT, id_user int(10) NOT NULL, PRIMARY KEY (id_tutor) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ; CREATE TABLE IF NOT EXISTS tags ( id_tag int(10) unsigned NOT NULL AUTO_INCREMENT, tag varchar(255) DEFAULT NULL, PRIMARY KEY (id_tag), UNIQUE KEY tag (tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; ALTER TABLE learning_packs_tag_relations ADD CONSTRAINT Learning_Packs_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE learning_packs ADD CONSTRAINT Learning_Packs_ibfk_2 FOREIGN KEY (id_author) REFERENCES users (id_user) ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE tutors_tag_relations ADD CONSTRAINT Tutors_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO test.users ( id_user , name , surname ) VALUES ( NULL , 'Vivian', 'Richards' ), ( NULL , 'Sachin', 'Tendulkar' ); INSERT INTO test.users ( id_user , name , surname ) VALUES ( NULL , 'Don', 'Bradman' ); INSERT INTO test.tutor_details ( id_tutor , id_user ) VALUES ( NULL , '52' ), ( NULL , '53' ); INSERT INTO test.tutor_details ( id_tutor , id_user ) VALUES ( NULL , '54' ); INSERT INTO test.tags ( id_tag , tag ) VALUES ( 1 , 'Vivian' ), ( 2 , 'Richards' ); INSERT INTO test.tags (id_tag, tag) VALUES (3, 'Sachin'), (4, 'Tendulkar'); INSERT INTO test.tags (id_tag, tag) VALUES (5, 'Don'), (6, 'Bradman'); INSERT INTO test.learning_packs (id_lp, id_status, id_author, name) VALUES ('1', '1', '52', 'Cricket 1'), ('2', '2', '52', 'Cricket 2'); INSERT INTO test.tags (id_tag, tag) VALUES ('7', 'Cricket'), ('8', '1'); INSERT INTO test.tags (id_tag, tag) VALUES ('9', '2'); INSERT INTO test.learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '1'), ('8', '52', '1'); INSERT INTO test.learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '2'), ('9', '52', '2'); =================================================================================== Requirement Now I want to search learning_packs, with the same AND logic. Help me modify the following query so that searching pack name or tutor's name, surname results all active packs (either directly those packs or packs created by those tutors). ================================================================================== select lp.* from Learning_Packs AS lp LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON lp.id_lp = lptagrels.id_lp LEFT JOIN Tutors_Tag_Relations as ttagrels ON lp.id_author = ttagrels.id_tutor LEFT JOIN Tutor_Details AS td ON ttagrels.id_tutor = td.id_tutor LEFT JOIN Users as u on td.id_user = u.id_user JOIN Tags as t on (t.id_tag = lptagrels.id_tag) or (t.id_tag = ttagrels.id_tag) where lp.id_status = 1 AND ( t.tag LIKE "%Vivian%" OR t.tag LIKE "%Richards%" ) group by lp.id_lp HAVING count(lp.id_lp) 1 limit 0,20 As you can see, searching "Cricket 1" returns that pack but searching Vivian Richards does not return the same pack. Please help

    Read the article

  • Mysql optimization question - How to apply AND logic in search and limit on results in one query?

    - by sandeepan-nath
    This is a little long but I have provided all the database structures and queries so that you can run it immediately and help me. Run the following queries:- CREATE TABLE IF NOT EXISTS `Tutor_Details` ( `id_tutor` int(10) NOT NULL auto_increment, `firstname` varchar(100) NOT NULL default '', `surname` varchar(155) NOT NULL default '', PRIMARY KEY (`id_tutor`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ; INSERT INTO `Tutor_Details` (`id_tutor`,`firstname`, `surname`) VALUES (1, 'Sandeepan', 'Nath'), (2, 'Bob', 'Cratchit'); CREATE TABLE IF NOT EXISTS `Classes` ( `id_class` int(10) unsigned NOT NULL auto_increment, `id_tutor` int(10) unsigned NOT NULL default '0', `class_name` varchar(255) default NULL, PRIMARY KEY (`id_class`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=229 ; INSERT INTO `Classes` (`id_class`,`class_name`, `id_tutor`) VALUES (1, 'My Class', 1), (2, 'Sandeepan Class', 2); CREATE TABLE IF NOT EXISTS `Tags` ( `id_tag` int(10) unsigned NOT NULL auto_increment, `tag` varchar(255) default NULL, PRIMARY KEY (`id_tag`), UNIQUE KEY `tag` (`tag`), KEY `id_tag` (`id_tag`), KEY `tag_2` (`tag`), KEY `tag_3` (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ; INSERT INTO `Tags` (`id_tag`, `tag`) VALUES (1, 'Bob'), (6, 'Class'), (2, 'Cratchit'), (4, 'Nath'), (3, 'Sandeepan'), (5, 'My'); CREATE TABLE IF NOT EXISTS `Tutors_Tag_Relations` ( `id_tag` int(10) unsigned NOT NULL default '0', `id_tutor` int(10) default NULL, KEY `Tutors_Tag_Relations` (`id_tag`), KEY `id_tutor` (`id_tutor`), KEY `id_tag` (`id_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `Tutors_Tag_Relations` (`id_tag`, `id_tutor`) VALUES (3, 1), (4, 1), (1, 2), (2, 2); CREATE TABLE IF NOT EXISTS `Class_Tag_Relations` ( `id_tag` int(10) unsigned NOT NULL default '0', `id_class` int(10) default NULL, `id_tutor` int(10) NOT NULL, KEY `Class_Tag_Relations` (`id_tag`), KEY `id_class` (`id_class`), KEY `id_tag` (`id_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `Class_Tag_Relations` (`id_tag`, `id_class`, `id_tutor`) VALUES (5, 1, 1), (6, 1, 1), (3, 2, 2), (6, 2, 2); Following is about the tables:- There are tutors who create classes. Tutor_Details - Stores tutors Classes - Stores classes created by tutors And for searching we are using a tags based approach. All the keywords are stored in tags table (while classes/tutors are created) and tag relations are entered in Tutor_Tag_Relations and Class_Tag_Relations tables (for tutors and classes respectively)like this:- Tags - id_tag tag (this is a a unique field) Tutors_Tag_Relations - Stores tag relations while the tutors are created. Class_Tag_Relations - Stores tag relations while any tutor creates a class In the present data in database, tutor "Sandeepan Nath" has has created class "My Class" and "Bob Cratchit" has created "Sandeepan Class". 3.Requirement The requirement is to return tutor records from Tutor_Details table such that all the search terms (AND logic) are present in the union of these two sets - 1. Tutor_Details table 2. classes created by a tutor in Classes table) Example search and expected results:- Search Term Result "Sandeepan Class" Tutor Sandeepan Nath's record from Tutor Details table "Class" Both the tutors from ... Most importantly, there should be only one mysql query and a LIMIT applicable on the number of results. Following is a working query which I have so far written (It just applies OR logic of search key words instead of the desired AND logic). SELECT td . * FROM Tutor_Details AS td LEFT JOIN Tutors_Tag_Relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor LEFT JOIN Classes AS wc ON td.id_tutor = wc.id_tutor INNER JOIN Class_Tag_Relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor LEFT JOIN Tags AS t ON t.id_tag = ttagrels.id_tag OR t.id_tag = wtagrels.id_tag WHERE t.tag LIKE '%Sandeepan%' OR t.tag LIKE '%Nath%' GROUP BY td.id_tutor LIMIT 20 Please help me with anything you can. Thanks

    Read the article

  • Errors with parameter datatype in PostgreSql query

    - by John
    Im trying to execute a query to postgresql using the following code. It's written in C/C++ and I keep getting the following error when declaring a cursor: DECLARE CURSOR failed: ERROR: could not determine data type of parameter $1 Searching on here and on google, I can't find a solution. Can anyone find where I have made and error and why this is happening? thanks! void searchdb( PGconn *conn, char* name, char* offset ) { // Will hold the number of field in table int nFields; // Start a transaction block PGresult *res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { printf("BEGIN command failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } // Clear result PQclear(res); printf("BEGIN command - OK\n"); //set the values to use const char *values[3] = {(char*)name, (char*)RESULTS_LIMIT, (char*)offset}; //calculate the lengths of each of the values int lengths[3] = {strlen((char*)name), sizeof(RESULTS_LIMIT), sizeof(offset)}; //state which parameters are binary int binary[3] = {0, 0, 1}; res = PQexecParams(conn, "DECLARE emprec CURSOR for SELECT name, id, 'Events' as source FROM events_basic WHERE name LIKE '$1::varchar%' UNION ALL " " SELECT name, fsq_id, 'Venues' as source FROM venues_cache WHERE name LIKE '$1::varchar%' UNION ALL " " SELECT name, geo_id, 'Cities' as source FROM static_cities WHERE name LIKE '$1::varchar%' OR FIND_IN_SET('$1::varchar%', alternate_names) != 0 LIMIT $2::int4 OFFSET $3::int4", 3, //number of parameters NULL, //ignore the Oid field values, //values to substitute $1 and $2 lengths, //the lengths, in bytes, of each of the parameter values binary, //whether the values are binary or not 0); //we want the result in text format // Fetch rows from table if (PQresultStatus(res) != PGRES_COMMAND_OK) { printf("DECLARE CURSOR failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } // Clear result PQclear(res); res = PQexec(conn, "FETCH ALL in emprec"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { printf("FETCH ALL failed"); PQclear(res); exit_nicely(conn); } // Get the field name nFields = PQnfields(res); // Prepare the header with table field name printf("\nFetch record:"); printf("\n********************************************************************\n"); for (int i = 0; i < nFields; i++) printf("%-30s", PQfname(res, i)); printf("\n********************************************************************\n"); // Next, print out the record for each row for (int i = 0; i < PQntuples(res); i++) { for (int j = 0; j < nFields; j++) printf("%-30s", PQgetvalue(res, i, j)); printf("\n"); } PQclear(res); // Close the emprec res = PQexec(conn, "CLOSE emprec"); PQclear(res); // End the transaction res = PQexec(conn, "END"); // Clear result PQclear(res); }

    Read the article

  • SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

    - by pinaldave
    Concatenating  string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL Server 2012 has introduced new function CONCAT which concatenates the strings much efficiently. When we concat values with ‘+’ in SQL Server we have to make sure that values are in string format. However, when we attempt to concat integer we have to convert the integers to a string or else it will throw an error. However, with the newly introduce the function of CONCAT in SQL Server 2012 we do not have to worry about this kind of issue. It concatenates strings and integers without casting or converting them. You can specify various values as a parameter to CONCAT functions and it concatenates them together. Let us see how to concat the values in Sixty Seconds: Here is the script which is used in the video. -- Method 1: Concatenating two strings SELECT 'FirstName' + ' ' + 'LastName' AS FullName -- Method 2: Concatenating two Numbers SELECT CAST(1 AS VARCHAR(10)) + ' ' + CAST(2 AS VARCHAR(10)) -- Method 3: Concatenating values of table columns SELECT FirstName + ' ' + LastName AS FullName FROM AdventureWorks2012.Person.Person -- Method 4: SQL Server 2012 CONCAT function SELECT CONCAT('FirstName' , ' ' , 'LastName') AS FullName -- Method 5: SQL Server 2012 CONCAT function SELECT CONCAT('FirstName' , ' ' , 1) AS FullName Related Tips in SQL in Sixty Seconds: SQL SERVER – Concat Function in SQL Server – SQL Concatenation String Function – CONCAT() – A Quick Introduction 2012 Functions – FORMAT() and CONCAT() – An Interesting Usage A Quick Trick about SQL Server 2012 CONCAT Function – PRINT A Quick Trick about SQL Server 2012 CONCAT function What would you like to see in the next SQL in Sixty Seconds video? Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video Tagged: Excel

    Read the article

  • SQL SERVER – Concat Function in SQL Server – SQL Concatenation

    - by pinaldave
    Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though, SQL Server is now in moving very fast and have proved many times a good enterprise solution, it does not have some basic functions. I naturally asked him for example and he suggested CONCAT() which exists in MySQL and Oracle. The answer is very simple – the equalent function in SQL Server to CONCAT() is ‘+’ (plus operator without quotes). Method 1: Concatenating two strings SELECT 'FirstName' + ' ' + 'LastName' AS FullName Method 2: Concatenating two Numbers SELECT CAST(1 AS VARCHAR(10)) + 'R' + CAST(2 AS VARCHAR(10)) Method 3: Concatenating values from table columns SELECT FirstName + ' ' + LastName FROM AdventureWorks.Person.Contact Well, this may look very simple but sometime it is very difficult to find the information for simple things only. Do you have any such example which you would like to share with community? Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL String, SQL Tips and Tricks, T SQL, Technology

    Read the article

  • SQL SERVER – Create Primary Key with Specific Name when Creating Table

    - by pinaldave
    It is interesting how sometimes the documentation of simple concepts is not available online. I had received email from one of the reader where he has asked how to create Primary key with a specific name when creating the table itself. He said, he knows the method where he can create the table and then apply the primary key with specific name. The attached code was as follows: CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](100) NULL) GO ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ([ID] ASC) GO He wanted to know if we can create Primary Key as part of the table name as well, and also give it a name at the same time. Though it would look very normal to all experienced developers, it can be still confusing to many. Here is the quick code that functions as the above code in one single statement. CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](100) NULL CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ([ID] ASC) ) GO Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, Readers Question, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

    Read the article

  • Re-running SSRS subscription jobs that have failed

    - by Rob Farley
    Sometimes, an SSRS subscription for some reason. It can be annoying, particularly as the appropriate response can be hard to see immediately. There may be a long list of jobs that failed one morning if a Mail Server is down, and trying to work out a way of running each one again can be painful. It’s almost an argument for using shared schedules a lot, but the problem with this is that there are bound to be other things on that shared schedule that you wouldn’t want to be re-run. Luckily, there’s a table in the ReportServer database called dbo.Subscriptions, which is where LastStatus of the Subscription is stored. Having found the subscriptions that you’re interested in, finding the SQL Agent Jobs that correspond to them can be frustrating. Luckily, the jobstep command contains the subscriptionid, so it’s possible to look them up based on that. And of course, once the jobs have been found, they can be executed easily enough. In this example, I produce a list of the commands to run the jobs. I can copy the results out and execute them. select 'exec sp_start_job @job_name = ''' + cast(j.name as varchar(40)) + '''' from msdb.dbo.sysjobs j  join  msdb.dbo.sysjobsteps js on js.job_id = j.job_id join  [ReportServer].[dbo].[Subscriptions] s  on js.command like '%' + cast(s.subscriptionid as varchar(40)) + '%' where s.LastStatus like 'Failure sending mail%'; Another option could be to return the job step commands directly (js.command in this query), but my preference is to run the job that contains the step. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video

    - by pinaldave
    Collation is a very important concept but often ignored. I have often seen developers either not understanding this or ignored it – this is plain wrong. In simple word we can say Collation is the language or interpreting done by SQL Server. Well, in today’s SQL in Sixty Seconds we are going to observe how collation affects the resultset. Today’s blog post is inspired from my earlier blog post SQL SERVER – Effect of Case Sensitive Collation on Resultset. I strongly encourage you to read this earlier blog post for sample code as well additional explanation related to the concept shared in today’s SQL in Sixty Seconds. Here is the code used in the video. USE TempDB GO -- Sample Data Building CREATE TABLE ColTable (Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS, Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS) ; INSERT ColTable(Col1, Col2) VALUES ('Apple','Apple'), ('apple','apple'), ('pineapple','pineapple'), ('Pineapple','Pineapple'); GO -- Retrieve Data SELECT * FROM ColTable GO -- Retrieve Data SELECT * FROM ColTable ORDER BY Col1 GO -- Retrieve Data SELECT * FROM ColTable ORDER BY Col2 GO -- Clean up DROP TABLE ColTable GO Related Tips in SQL in Sixty Seconds: SQL SERVER – Effect of Case Sensitive Collation on Resultset Example of Width Sensitive and Width Insensitive Collation Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31 Change Collation of Database Column – T-SQL Script Find Collation of Database and Table Column Using T-SQL Default Collation of SQL Server 2008 Cannot resolve collation conflict for equal to operation If we like your idea we promise to share with you educational material. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video

    Read the article

  • SQL SERVER – Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video

    - by pinaldave
    This is 25th video of series SQL in Sixty Seconds we started a few months ago. Even though this is 25th video it seems like we have just started this few days ago. The best part of this SQL in Sixty Seconds is that one can learn something new in less than sixty seconds. There are many concepts which are not new for many but just we all have 60 seconds to refresh our memories. In this video I have touched a very simple question which I receive very frequently on this blog. Q1) How to get current date time? Q2) How to get Only Date from datetime? Q3) How to get Only Time from datetime? I have created a sixty second video on this subject and hopefully this will help many beginners in the SQL Server field. This sixty second video describes the same. Here is a similar script which I have used in the video. SELECT GETDATE() GO -- SQL Server 2000/2005 SELECT CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond, CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly; GO -- SQL Server 2008 Onwards SELECT CONVERT(TIME,GETDATE()) AS HourMinuteSeconds; SELECT CONVERT(DATE,GETDATE()) AS DateOnly; GO Related Tips in SQL in Sixty Seconds: Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime Get Current System Date Time Get Date Time in Any Format – UDF – User Defined Functions Date and Time Functions – EOMONTH() – A Quick Introduction DATE and TIME in SQL Server 2008 I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can. If we like your idea we promise to share with you educational material. Image Credit: Movie Gone in 60 Seconds Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video

    Read the article

  • SQL SERVER – Removing Leading Zeros From Column in Table – Part 2

    - by pinaldave
    Earlier I wrote a blog post about Remvoing Leading Zeros from Column In Table. It was a great co-incident that my friend Madhivanan (no need of introduction for him) also post a similar article over on BeyondRelational.com. I strongly suggest to read his blog as well as he has suggested some cool solutions to the same problem. On original blog post asked two questions 1) if my sample for testing is correct and 2) If there is any better method to achieve the same. The response was amazing. I am proud on our SQL Community that we all keep on improving on each other’s contribution. There are some really good suggestions as a comment. Let us go over them right now. Improving the ResultSet I had missed including all zeros in my sample set which was an overlook. Here is the new sample which includes all zero values as well. USE tempdb GO -- Create sample table CREATE TABLE Table1 (Col1 VARCHAR(100)) INSERT INTO Table1 (Col1) SELECT '0001' UNION ALL SELECT '000100' UNION ALL SELECT '100100' UNION ALL SELECT '000 0001' UNION ALL SELECT '00.001' UNION ALL SELECT '01.001' UNION ALL SELECT '0000' GO Now let us go over some of the fantastic solutions which we have received. Response from Rainmaker SELECT CASE PATINDEX('%[^0 ]%', Col1 + ' ‘') WHEN 0 THEN '' ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1)) END FROM Table1 Response from Harsh Solution 1 SELECT SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + 'a'), LEN(Col1)) FROM Table1 Response from Harsh Solution 2 SELECT RIGHT(Col1, LEN(Col1)+1 -PATINDEX('%[^0 ]%', Col1 + 'a' )) FROM Table1 Response from lucazav SELECT T.Col1 , label = CAST( CAST(REPLACE(T.Col1, ' ', '') AS FLOAT) AS VARCHAR(10)) FROM Table1 AS T Response from iamAkashSingh SELECT REPLACE(LTRIM(REPLACE(col1,'0',' ')),' ','0') FROM table1 Here is the resultset of above scripts. It will remove any leading zero or space and will display the number accordingly. If you believe there is a better solution, please leave a comment. I am just glad to see so many various responses and all of them teach us something new. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

    Read the article

< Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >