Tag Info

New answers tagged

0

Perhaps you are looking for this: select issues.id, array_agg(journal.notes) from issues left outer join journal on (issues.id = journal.issue_id and journal.notes != '') group by issues.id Please check this http://sqlfiddle.com/#!1/24db9/2


3

Try changing you statement to match this: FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)') This uses the .value xml method to solve your entitization problem.


1

Yes, if you know what data to insert after corrupt data pages are deleted, you should be fine. make sure you find out the root of corruption to prevent it from happening. also, once everything is fixed, then do a full back and restore it on a different server to make sure everything is fine. Your plan sounds good to me.


0

Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows: CREATE VIEW dbo.MyView WITH SCHEMABINDING AS SELECT COUNT_BIG(*) AS NumberOfRows FROM [dbo].[Products]; GO CREATE ...


1

I took the liberty of editing your SQL so the clauses are on separate lines. I violated CodeReview group discipline by doing that. Sorry! My suggestion to you is to format your queries so the various clauses are clearly visible, as I did. This kind of formatting, in my experience, makes it easier to understand the logic of queries and to spot errors. ...


0

Edit the Database Template and choose your new template for Forward Engineering


1

Since you edited your post here's another try. select orders.customer, temp.order_type, orders2.price from helper temp cross join orders left outer join orders orders2 on orders.customer = orders2.customer and temp.order_type = orders2.order_type order by orders.customer


0

It's a very common problem with different reasons. I strongly suggest that you read these answers by Mike and Aaron -- Why Does the Transaction Log Keep Growing or Run Out of Space? I don't want repeat what they explain very well in the answers.


0

I've just figured out how to do this, so I'll answer it in case anyone else has a similar problem. Introduced SELECTs as far down the tree as possible, to reduce the number of comparisons needed. Introduced PROJECTs as far down the tree too, to reduce the number of columns. Used a JOIN wherever a CARTESIAN PRODUCT was followed by a SELECT. (There weren't ...


2

I've never heard of comments causing a problem. The code you captured from the running server and the posted source are for different SPs. It makes me think that maybe the ..SU01 version is still in your database and being executed (from a job perhaps?) whereas you are trying to debug the ..SU012_XML code. For testing you could add another INSERT to the ...


0

There are two options regarding the transaction log files in SQL Server: Autogrowth and Maxsize. Autogrowth configure the size by which the log will grow when it's full and Maxsize is the the limit where it stops to grow. It seems that you don't have any limit set for the database. In addition the log file size depends on the recovery model you have ...


0

Reasons for transaction log growth can be various: rebuilding indexes, other administrative tasks, some import data operations and so on. You must first inspect your database, and search for administrative tasks that affect on transaction log. Also, full backup doesn't shrink transaction log, so you must do another administrative task to free space from ...


1

Either you run an additional UPDATE (best in one transaction): UPDATE site_stats s SET total_users = 0 , monthly_users = 0 , weekly_users = 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id) Or use this instead (I would do that): UPDATE site_stats s SET total_users = COALESCE(u.total, 0) , monthly_users = ...


3

This would do what you desire: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a, column_b) SELECT p.id, t.a, t.b FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t The subtle difference here is that unnest() calls in the same SELECT list are ...


0

I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server. There is no need to delete all the users. What you can do is use sp_help_revlogin to move all the logins that you are interested or your applicaiton/s need to the new server. Once you move ...


2

The comment from @ChristianAmmer is the key to answering your question. Why ??? First look the the WHERE clause DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds ...


1

I think Binaya Regmi is correct. When deleting in SQL-Server explicitly name the table you want to delete the rows from - especially necessary when using a join. So in your case change the top row from DELETE category.* to DELETE category


0

It would be really helpgul if you said what your issues were. One thing I can see immediately is a scope problem. EXEC() creates its own scope. Your statement to USE a database will use that database only until the EXEC() returns. Then you are back into whatever database the above-posted code is running in. Let's say "MyDB" for the sake of argument. The ...


0

Are you using a named instance or a default one? Did you enabled a remote connection on that instance? Right click on the server and in the Server Properties dialog under the Connections tab check the Allow remote connections to this server option In the TCP/IP Properties -> IP Addresses -> IPAII: For a named instance you should set set the TCP Dynamic ...


0

Got some feedback on this question from this Postgres listserv, so thought I'd share it here: Jeff: I'd go with a small shared_buffers, like 128MB, and let the OS cache as much as possible. This minimizes the amount of double buffering. And set work_mem to about 6GB, then bump it up if that doesn't seem to cause problems. In the scenario you ...


0

How about this: Select * from tableB b inner join (select a.*, row_number() over (partition by a.type order by a.type) rn from tableA a) a1 on b.type=a1.type and a1.name='SEARCHED_NAME' and a1.rn=1 PS. Not tested, as you didn't provide a script to create and populate the tables.


2

An array representing the path from the root up to the leaf should achieve the desired sort order: WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL LIMIT 10 ) UNION ALL SELECT r.depth + 1, r.path || n.node, n.* FROM node_rec r JOIN nodes n ON n.parent = ...


0

I suspect something like SELECT nullif(inv_val, lag(inv_val) over (order by ind_invno)) as inv_val, ind_invno, ind_invdte, ind_csm_cd, csm_name, ind_fgm_cd FROM soh_invoice, sod_invoice, som_party WHERE inh_orm_cd = ind_orm_cd AND inh_invno = ind_invno AND inh_wh_cd = ind_wh_cd AND ind_csm_cd = csm_cd AND inh_invno = ind_invno AND inh_wh_cd = ...


4

You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc. Then work towards meeting those targets. That is the ...


2

If you are grouping you don't need a distinct and move the conditions from the having to the where select a, group_a, sum(quantity) from cdr where type = 'DATA' and group_a is not null group by a, group_a;


1

The tool you should be using is sqlcmd. It is installed with the client tools and replaces the deprecated osql.


3

The main recommendations appear to be: If you have the install discs / files, then you should be able to install just the Client Tools. Download SQL Server 2012 (SP1) Express (I would try the "SQL Server Management Studio Express (Tools only)" version first) Check out: http://www.mssqltips.com/sqlservertip/1807/sql-server-2008-client-tools-installation/ If ...


4

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice: CREATE TABLE item ( item_id serial PRIMARY KEY, grp_id integer NOT NULL REFERENCES grp(grp_id) ); If you have an item_id, better make it unique and ideally a surrogate ...


1

If I guessed the missing pieces correctly, this query using a crosstab() function should do the job (and much faster than the original - except for small cardinalities where not much can be gained to begin with): It requires that you install the additional module tablefunc first (once per database. Detailed instructions in this related answer on SO: ...


2

You don't need window functions: UPDATE site_stats SET total_users = totalUsers, monthly_users = monthlyUsers, weekly_users = weeklyUsers FROM ( SELECT u.local_site AS site, count (u.*) AS totalUsers, count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers, count(CASE WHEN u.last_visit ...


3

Assuming the original table design is somewhat like this: CREATE TABLE dbo.Area ( RowID integer PRIMARY KEY, GroupID integer NOT NULL, ParentID integer NULL, ); Sample data: INSERT dbo.Area (RowID, GroupID, ParentID) VALUES (1, 1, NULL), -- Root (2, 1, 1), (3, 1, 2), (4, 2, NULL), -- Root (5, 2, 4), (6, 2, 5), ...


1

I am assuming that your tags are stored in the "Tag" table, but if not it should be an easy fix for the below SQL. I have not used SQLite3 before, but this should be generic SQL. I used the literal "John," but just replace with a variable for whatever the user enters. Also, if you wanted to limit the count to at least x number then put in a having clause. ...


0

If it was working recently and it's not working now, you've either got a whole bucket load more data or a worse query plan. I'd suggest you'd notice the former. The latter may be due to the statistics getting stale.


1

Given your first two preconditions I'd be looking at a clustered index on InsertDateTime.


-5

Run this Script After you got Same Result SET ANSI_NULLS Off;


9

...why with clustered index, the deadlock is still there (though hit rate seems to be dropped) The question isn't precisely clear (e.g. how many updates and to which id values are in each transaction) but one obvious deadlock scenario arises with multiple single-row updates within a single transaction, where there is an overlap of [id] values, and the ...


11

NULL is not a value. Something cannot '=' NULL You want: SELECT Name FROM FinancialInstitution WHERE Name IS NULL


2

This is just guesswork: SELECT t.id, t.position, y.position AS y_position, t.created_at FROM tbl t JOIN tbl y USING (id) WHERE y.created_at = t.created_at - 1; Basically, you seem to be after a self-join. Using table aliases you can join the same table multiple times in one query. You can just subtract an integer from a date - assuming it is a date, ...


0

Well, if you really want to do this, this is a possible way: create table t1 (id number, c1 number); create table t2 (id number, c2 number); create table t3 (id number, c3 number); create table t4 (id number, c4 number); Original: select t1.id, t1.c1, t2.c2, t3.c3, t4.c4 from t1, t2, t3, t4 where t1.id = t2.id and t2.id = t3.id and t3.id = t4.id; ...


0

It's not clear what you want but this may be it: SELECT Name, SUM(CASE WHEN Used IS NULL THEN ItemQty ELSE 0 END) AS QtyNotUsed FROM ContainersReceived GROUP BY Name ; To be efficient, add an index on (Name, Used, QtyNotUsed) If you have another index, on (Used, Name, QtyNotUsed), this query will be ...


0

Alex, The following will work for SQL Server. Hopefully you'll be able to translate it to Postgresql. select t.id, t.position, y.position as y_position, t.Created_at from MyData t inner join MyData y on y.id = t.id and y.Created_date = DATEADD(day, -1, t.Created_date)


0

They're all listed in Setting Database Options: bulkcopy: ALTER DATABASE .. SET RECOVERY BULK_LOGGED; trunc. log: ALTER DATABASE .. SET RECOVERY SIMPLE; dbo use: ALTER DATABASE ... SET RESTRICTED_USER; single: ALTER DATABASE ... SET SINGLE_USER; ansi null default: ALTER DATABASE ... SET ANSI_NULL_DEFAULT ON; db chaining: ALTER DATABASE ... SET DB_CHAINING ...


0

Since the content of 'Keywords' is interpreted by the RDBMS instead of being read literally, your code is vulnerable to SQL Injection . This is also why you get the syntax errors. You must sanitize the content of 'Keywords'. There's an answer on Stack Overflow : here.


2

I guess you could (ab)use MERGE for this. First create a (temporary) table: CREATE TABLE tempIDs ( PersonId INT, FinancialInstitutionId INT ) ; Then MERGE into Person (instead of INSERT), so you can use columns of the tables involved in the OUTPUT clause: MERGE INTO Person USING FinancialInstitution AS fi ON 1 = 0 WHEN NOT MATCHED THEN INSERT ...


0

A primary key will enforce uniqueness in your data, which is almost always highly desired. One of the issues you'll run into if you allow yourself to have multiple copies of the same record is that any results you have will be skewed to the point of being unusable for many purposes. Also, this will happen, no matter how much you try to say it won't. You ...


3

The error message is self-explanatory: PLS-00221: 'GET_DDL' is not a procedure or is undefined GET_DDL is not a procedure, its a function that returns a CLOB value. http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#ARPLS66885


0

Your design seems like a good one for what you want to achieve. Keeping your fact table as simple as possible is a good practice i.e. keeping only foreign keys against likes/un-likes, which then links back to dimension tables, such as User, Content etc. Don't be shy to index your fact table, for quicker search and updates.


0

Here is some recommentations: Make sure all the columns used in the joins are indexed properly. if possible, replace subqueries with indexed temporary tables. Move when possible the filtering clause from the join statement to the where statement (e.g. Where pf.pathLength BETWEEN 2 AND 5) Try to move the joins order to start filtering with the table ...


2

What you have seems pretty solid. How about create table vote ( user_id int not null, content_id int not null, time_recorded datetime not null, vote int not null, primary key (user_id, content_id), foreign key (user_id) references `user`(user_id) on delete cascade, foreign key (content_id) references content(content_id) on ...



Top 50 recent answers are included