Tag Info

New answers tagged

0

SELECT * FROM stickers WHERE keywords @@@ 'case & 580:*'::tsquery; will work.


1

Will transactions be safely cued on the Subscribers - I imagine this will potentially cause transaction log growth? Transactions will be safely cued on the subscribers and transaction log growth will be negligible. Merge Replication utilizes DML triggers along with change tracking tables in the publication and subscription databases that contains metadata ...


-1

SELECT * FROM `sdriving_turno` WHERE hora_inicio < NOW() AND hora_fin < '17:00:00' you could also do something like SELECT * FROM `sdriving_turno` WHERE hora_fin BETWEEN NOW() AND '18:00:00'


-1

I'll advice that you create another table to store the films first, although not necessary, so the film field would only contain integers in case you might one to restrict users from creating same movies multiple times with different spacing or case etcetera , then you need to take your time to understand the problem well. SELECT b.uid AS ...


0

The correlated subquery acts like a LEFT join between the 2 tables. If there is no row in the REF_MaterialPlantHistory matching the WHERE conditions, the subquery returns a NULL and this yields the error. Try using INNER join: UPDATE prod SET ABCIndicatorCode = hist.ABCIndicatorCode FROM DMT_TEE_DTS_Production AS prod JOIN ...


3

There are several reasons why this can happen: Make sure you are not using the query cache on your old server and not on the new one. Execute always benchmarks with SELECT SQL_NO_CACHE. Execute the same queries several times. If it is a new server -the buffer pool, filesystem cache and other buffer are probably not yet settled for best performance, ...


1

Use a subquery that has the checklist id that has both tags SELECT B.id checklist_id, B.name checklist_name, D.name tag_name FROM ( SELECT AA.checklistId,COUNT(1) tagcount FROM checklist_tag AA INNER JOIN tag BB ON AA.tagId = BB.id WHERE BB.name IN ('tag 1', 'tag 2') GROUP BY AA.checklistId HAVING COUNT(1)=2 ) A INNER JOIN ...


-6

SELECT [33500 List].ID , [33500 List].dayt , [33500 List].tyme , [33500 List].[MOBI Size] , [33500 List].Title , [33500 List].Tiep FROM [33500 List] WHERE ((([33500 List].Title) Like "the *")); SELECT TOP 15 [33500 List].ID , [33500 List].dayt , [33500 List].tyme , [33500 List].[MOBI Size] , [33500 List].Title , ...


3

This is not a great design. You have a couple of options to improve it: Go with your alternative (separate keys). You don't list your requirements, but if it's imperative you only have ONE child record per master record you can enforce this with check constraints. Put the parent key in the child table. If every child record refers to one master record, ...


0

Assuming #T is the original data table, you can use dynamic pivot to get your solution. Code below. As the code uses dynamic pivot, you can have any numbers in your table, not just 1, 2, 3 and 4; and get the results without altering the code. --aggregate data into a single column and insert into a temporary table select * into #D from (select Ques1 from #T ...


2

There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL. My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.


3

In order to get the count across multiple columns, I'd suggest looking at unpivoting the data. Since you are using SQL Server 2008+, you can use CROSS APPLY to convert your multiple columns into multiple rows. Once the data is in the rows, you can easily count the occurrences. select sum(case when value = 1 then 1 else 0 end) Count1, sum(case when ...


1

Assuming you have a recent enough version, infomix supports the LAG and LEAD functions over ranking operations, see http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm These will only work if the current row and the one you consider the previous row are find using the same filtering clauses though, otherwise you and ...


0

In addition to the above suggestions, have you also duplicated the tempdb configurations? It may be that your previous server had multiple tempdbs configured, while your new one only has one. If you need to configure them, have a look here :


1

I suggest you join to previous products that match criteria and (if any exist) narrow down to the one where no later row exists: SELECT P.product_id AS product_id, ,P.deal_dt AS deal_dt ,C.deal_dt AS previous_deal_dt ,C.cancel_dt AS previous_cancel_dt FROM product_shipping p LEFT JOIN product_shipping C ON C.product_id = ...


0

A trigger is an option to do this. create trigger ins_updt_trig on cust for update, insert as begin print 'cust' end This will display a message when an update or insert occurs on 'cust' table. But I don't think this is a better option for a lot of tables because triggers are very difficult to manage.


0

For your first example: Update cust Set name = 'john' output 'cust' --, inserted.Name -- optionally, uncomment this field as well, to get the Key of the updated record. Where custid = '4001'


0

I'm not sure how to join to get latest previous record. Start there. Get the latest previous record with a self-join: select c.product_id, min(p.deal_dt) as prev_dt from product_shipping as c join product_shipping as p on c.product_id = p.product_id and c.deal_dt >= p.deal_dt group by c.product_id That will produce rows for which prev_dt = ...


0

Make the outer joins inner joins, and enforce the relationships. Superficially, it looks like every PAMtx should have a proper transaction type and meter mapping anyway. If you can't do that -- because the relationships doesn't always hold -- try a UNION of an inner join to one (or two) WHERE NOT EXISTS queries for those rows that don't match. Also ...


0

Normalize. Determine which of fields 1-5 represent they, and pre-process your CSV into two files representing two related tables that can be joined on that key. For example, if fields 1 and 2 uniquely identify each row, then you need one table with field 1,2,3,5 and another with 1,2,4. Save yourself some later trouble by defining primary keys for both ...


1

You can use derive table to aggregate all your column values in a single column and do ordering and counting in the derived table. Example below. Select item0 as item, count(*) as cnt from (SELECT item0 from finalBuild UNION ALL SELECT item1 from finalBuild UNION ALL SELECT item2 from finalBuild) as item group by item0 order by count(*) desc;


1

Join positions to itself. In pseudo-sql: select o.position, n.position from positions as o -- old inner join positions as n -- new on o.application_id = n.application_id and o.country = n.country and o.feed_id = n.feed_id and o.created = <max value for this app, country and feed that's less than n.created> There's a ...


0

I often use a SELECT to generate other SQL: select 'insert dbo.TEST(A, B) values (' + convert(varchar(20), A) + ', ''' + B + ''');' from dbo.TEST; Save the output and re-use as needed. If column B contains text delimiters you'll have to deal with this.


0

You cannot execute multiple MySQL statements from PHP with mysqli_query, but you can with mysqli_multi_query!


1

You can also use the bcp utility to import or export data. In your case use something like the following command: bcp "SELECT A, B FROM TEST" queryout Test.txt -c -T To insert data from the file into another table: bcp Database.Schema.Table in Test.txt -T –c Hope this helps as an alternative


-3

Figure out.Some simple thing I forgot......just use "as" select l.depart_code,l.depart_name,y.HR_NO,y.HR_NAME,m.PAY_SCOPE,x.per,x.com from psr_main m,hr_depart_data n,hr_depart_data l,( select calculate_time, employee_id, (accident_insure_per+job_insure_per) as per, (accident_insure_com+job_insure_com+zhi_insure_com) as com from ...


0

This ugly sed expression seems to do the job: $ sed -e '/^\=*$/d; /^\-*$/d; s/¦$//g; s/^ *//g; s/ *¦ */,/g' > /tmp/import Field 1 ¦ Field 2 ¦ Field 3 ¦ Field 4 ¦ Field 5 ¦ ================================================= value ¦ value ¦ value ¦ value ¦ value ¦ ------------------------------------------------- ¦ ¦ ¦ ...


0

select distinct( p.* ) from Qualified q, Professor p where q.EmpId = p.EmpId select Class, Semester, p.EmpId from Qualified q, Teaches T Professor p where q.EmpId = p.EmpId and T.EmpId = p.EmpId group by p.EmpId


0

"Safely" can have several meanings: If you mean transactionally-safe, that is not possible, as you are using MyISAM. Operations on MyISAM are not guaranteed to be atomic, and so recoverable. Fully crash-safe tables require InnoDB (or Aria). The good news is that most MyISAM ALTER TABLE operations are implemented by recreating the table as a separate set of ...


0

Just for fun, why not try this SQL instead, which rearranges things a bit: create table #t(name sysname) insert #t(Name) select Name from sys.databases where state_desc = 'ONLINE' except select name from ( values (cast('master' as sysname)), ('model'), ('msdb'), ('tempdb'), ('ReportServer'), ('ReportServerTempDB'), ...


1

Try following. select T1.M_ID, (select T2.T_NAME from TEAM_MASTER as T2 where T1.TEAM1_ID = T2.T_ID) as t1_Name, (select T2.T_NAME from TEAM_MASTER as T2 where T1.TEAM2_ID = T2.T_ID) as t2Name from MATCH_MASTER as T1;


0

Would This work? Got rid of case statement in where clause... SELECT name, OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U') AS [LookUpID] FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC') AND state_desc = 'ONLINE' AND ISNULL(OBJECT_ID(QUOTENAME(name) + ...


0

Thanks to @PieterGeerkins I've started to take a look into double-entry bookkeeping more, which seems to have helped me resolve the original problem. Using one 'account_id' in 'chart_of_accounts' for the jackpot (instead of one 'account_id' for each jackpot time period) allows me to not worry about referencing 'account_id' from the table 'jackpots'. I'll ...


0

It was an issues on the hard drive. It never showed any errors in event viewer or the controller. When we rebooted the server, the errors showed up and we replaced the drives.


1

Make sure your group name returned matches (identically) to your actual workload group name. Some times, I've found, it's helpful to reconfigure even if it doesn't say it needs to be done. use master GO -- Create a test DB which our CF will be based on CREATE DATABASE RGDB; GO -- enable RG ALTER RESOURCE GOVERNOR RECONFIGURE GO CREATE FUNCTION ...


2

Any shrinking of files (data or log) should be done in coordination with your backup strategy - and as others have pointed out it can be a pretty bad idea in many cases (certainly if done regularly). Side-stepping the exact question slightly: if your log files grow back to the same sort of size in a day or there-abouts then there is obviously something in ...


3

Shrinking of log file ON DAILY BASIS like one you do with query you posted is seriously bad and you should NOT do that. The main reason is that after you have shrinked the file it will grow again when new transaction comes and since INSTANT FILE INITIALIZATION is not there for Log files, its only for data files, when log file will grow DB engine will ...


2

... on a daily basis ... ALTER DATABASE ABC SET RECOVERY SIMPLE; Yes, this is as bad practice as it gets. Whats the point of having a backup chain if you break it daily? Now to your question: Does shrinking the log affect performance? Yes. Indirectly, because you shrink to a way too small file, you are dramatically affecting performance as log ...


2

Assuming referential integrity and all columns to be NOT NULL, this should be simplest and fastest: SELECT * FROM Receipe r WHERE NOT EXISTS ( SELECT 1 FROM Ingredients i JOIN RecipeIngredients ON ri.IngredientsId = i.ID WHERE ri.RecipeID = r.Id AND ri.RequiredQuantity > i.AvailableQuantity ); Basically, use a NOT EXISTS ...


4

In SQL Server, the log file is used to support transactions, and guarantee database consistency. If you remember the structure of the log file, you know that it may grow in the following cases: When there are huge DML (UPDATE, DELETE etc.) activity within database done by one transaction. When there are many transactions going at the same time and writing ...


-1

I assume you use SQL Server because you used the tag: Add an index to [PAMTx].CustomerId Foreign keys are not automatically indexed in SQL Server.


0

I would say you don't need all the start events, just the completed. I don't see that you are capturing data just for the SCOM database either. Extended events can cause an overhead just near to what SQL Server traces will depending on what you are trying to capture. You are basically capturing every query that comes across the server and telling it to wait ...


0

In EXCEL, from the Data ribbon, Get External Data tab, select From Other Data Sources -> From SQL Server. Follow the wizard to connect to your server and create a query.


0

"ORA-00918: column ambiguously defined" means that both tables contain one or more of the columns in your select and/or predicate, and Oracle doesn't know from which table to pull/query the column. Try putting an alias in front of each table, then putting the same alias in front of each column name. (sorry I couldn't be more specific, without a describe ...


1

Start with: Create a ChartOfAccounts table with the Account code as Primary Key. Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field. Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transction reversals (if used at all). This is necessary in order to generate ...


4

Making use of Jeff Moden's Tally-Ho! CSV splitter from here: CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values ...


0

Here is one way to do: select id,name from recipe; 1 Recipe 1 2 Recipe 2 3 Recipe 3 select recipe_id,ingredient_id,required_quantity from recipe_ingredients; 1 1 10 1 2 5 1 4 10 2 1 10 2 2 5 2 3 20 select id,name,available_quantity from ingredients; 1 Ingredient 1 100 2 Ingredient 2 10 3 Ingredient 3 30 4 ...


3

Create a trigger on update (another on delete is probably a good idea). CREATE TRIGGER [Table].[uPreventUpdateOops] ON [Table] FOR UPDATE AS BEGIN DECLARE @Count int SET @Count = @@ROWCOUNT; IF @Count >= (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID('Table') AND index_id = ...


0

If you can define the T-SQL query to filter the rows to a specific date range, create a view and choose the view object within Excel instead of the source table.


-1

You can reduce the time of your query only by indexing the columns that you are using. id and otherval in your case. But be very careful before you enable an index on a column as this can even affect you adversely. Thanks Arun



Top 50 recent answers are included