SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

geeves: Sep 16, 2004

Been away from SQL for a while nad having trouble with this query for a report I'm trying to generate

started out with this that works:

code:

SELECT DISTINCT t.*, pc.name AS categoryName FROM topic t 

    JOIN programCategory pc ON pc.id = t.categoryId 

    WHERE t.dateCreated > DATE_ADD(NOW(), INTERVAL -7 DAY) 

    ORDER BY t.numStories DESC;

What I'd like to be able to do is attach another table, but only return the number of returned (in this case published) rows where the keywords match

does not work - returns one row

code:

SELECT DISTINCT t.*, pc.name AS categoryName, COUNT(p.id) AS numPublished FROM topic t 

    JOIN programCategory pc ON pc.id = t.categoryId 

    LEFT JOIN published p ON LOWER(p.userEnteredKeyword) = LOWER(t.keyword)

    WHERE t.dateCreated > DATE_ADD(NOW(), INTERVAL -7 DAY) 

    ORDER BY t.numStories DESC;

I'm sure it's something small I'm missing,

# ? May 9, 2011 21:04

Adbot: ADBOT LOVES YOU

# ? May 19, 2024 19:09

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

geeves posted:

Been away from SQL for a while nad having trouble with this query for a report I'm trying to generate

started out with this that works:
code:
SELECT DISTINCT t.*, pc.name AS categoryName FROM topic t 

    JOIN programCategory pc ON pc.id = t.categoryId 

    WHERE t.dateCreated > DATE_ADD(NOW(), INTERVAL -7 DAY) 

    ORDER BY t.numStories DESC;
What I'd like to be able to do is attach another table, but only return the number of returned (in this case published) rows where the keywords match

does not work - returns one row
code:
SELECT DISTINCT t.*, pc.name AS categoryName, COUNT(p.id) AS numPublished FROM topic t 

    JOIN programCategory pc ON pc.id = t.categoryId 

    LEFT JOIN published p ON LOWER(p.userEnteredKeyword) = LOWER(t.keyword)

    WHERE t.dateCreated > DATE_ADD(NOW(), INTERVAL -7 DAY) 

    ORDER BY t.numStories DESC;
I'm sure it's something small I'm missing,

You want a GROUP BY. In this case, I'd say you want to have a subquery and do the group by in there.

code:

SELECT DISTINCT t.*, pc.name AS categoryName, p.numPublished FROM topic t 

    JOIN programCategory pc ON pc.id = t.categoryId 

    LEFT JOIN (SELECT userEnteredKeyword, count(id) AS numPublished from published 
               GROUP BY userEnteredKeyword) p ON LOWER(p.userEnteredKeyword) = LOWER(t.keyword)

    WHERE t.dateCreated > DATE_ADD(NOW(), INTERVAL -7 DAY) 

    ORDER BY t.numStories DESC;

# ? May 9, 2011 21:46

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

How good is indexing performance (specifically for MSSQL, but in general works too) for number ranges? For example, if I did "where number BETWEEN 1 AND 1000000", does the index really help in that case, or only if it's a simple equals?

# ? May 9, 2011 22:05

geeves: Sep 16, 2004

Jethro posted:

You want a GROUP BY. In this case, I'd say you want to have a subquery and do the group by in there.

code:

SELECT DISTINCT t.*, pc.name AS categoryName, p.numPublished FROM topic t 

    JOIN programCategory pc ON pc.id = t.categoryId 

    LEFT JOIN (SELECT userEnteredKeyword, count(id) AS numPublished from published 
               GROUP BY userEnteredKeyword) p ON LOWER(p.userEnteredKeyword) = LOWER(t.keyword)

    WHERE t.dateCreated > DATE_ADD(NOW(), INTERVAL -7 DAY) 

    ORDER BY t.numStories DESC;

Thanks Jethro! - had something close to that earlier, but didn't have the GROUP BY in the query

# ? May 9, 2011 22:07

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

Golbez posted:

How good is indexing performance (specifically for MSSQL, but in general works too) for number ranges? For example, if I did "where number BETWEEN 1 AND 1000000", does the index really help in that case, or only if it's a simple equals?

The query planner might opt not use the index if it decides that it needs to get some other information from the table anyway, or might decide to use it :iiam:

# ? May 9, 2011 23:21

Duke of Straylight: Oct 22, 2008; by Y Kant Ozma Post

Golbez posted:

How good is indexing performance (specifically for MSSQL, but in general works too) for number ranges? For example, if I did "where number BETWEEN 1 AND 1000000", does the index really help in that case, or only if it's a simple equals?

I don't know for sure whether it'll be used in practice, that depends on the specific database you're working with, but at least the index type shouldn't be stopping it from helping. Hash indexes can only support equals queries, while btree indexes can support queries with number ranges or comparison operators in general, and afaik MSSQL only uses btree indexes.

# ? May 10, 2011 07:28

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

mySQL Replication question. Is there a good way to do this on a database by database basis?

Let's say we have 10 databases on a server. We replicate now to a single slave and it works fine. The whole idea is so that we can do testing on live data, and not actually do it on the production server. The problem is, if we end up writing to the data, we get out of sync and have to dump it and start over from scratch. Not a huge deal because it's ~130mb gzipped.

But it would be easier to just drop one database and bring it back online from a dump, instead of all of them. Otherwise we're just running a script to do the job in the middle of the night. The other guys want something where we can literally hit 'stop replicating', then do whatever, then hit 'start replicating' and have it magically fix itself. We're using 5.0 with statement-based replication.

Has anyone used maatkit?

http://www.maatkit.org/doc/mk-table-sync.html

Bob Morales fucked around with this message at 16:05 on May 10, 2011

# ? May 10, 2011 15:53

Aredna: Mar 17, 2007; Nap Ghost

This is on SQL Server 2008. I'm optimizing a query and want to test the performance of several different indexes. The issue is that the table is loaded into cache after the first run increasing the speed by 50x.

I found some methods of clearing the system cache for benchmarking, but I'm not the only one on the server and don't want to cause slowdowns for others just so I can get a better benchmark.

Without being able to test on a server by myself, what are my best options left?

# ? May 10, 2011 17:06

spiritual bypass: Feb 19, 2008; Grimey Drawer

How about copying the table into a test database and fooling around with that? Your license allows you to run as many databases as you want on one server, right?

Or does clearing the cache affect the whole server and not just the context of a single database?

# ? May 10, 2011 17:45

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

If I have a query such as:

code:

SELECT (val1-val2)/val2 FROM table

How do I get it to return a value of x instead of null when val1 > 0 and val2 == 0?

edit: durr this works right?

SELECT IF(val1 > 0 AND val2 = 0, 100, (val1-val2)/val2) FROM table

fletcher fucked around with this message at 19:35 on May 10, 2011

# ? May 10, 2011 19:33

spiritual bypass: Feb 19, 2008; Grimey Drawer

fletcher posted:

If I have a query such as:
code:
SELECT (val1-val2)/val2 FROM table
How do I get it to return a value of x instead of null when val1 > 0 and val2 == 0?

http://www.4guysfromrolla.com/webtech/102704-1.shtml

# ? May 10, 2011 19:36

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

fletcher posted:

If I have a query such as:
code:
SELECT (val1-val2)/val2 FROM table
How do I get it to return a value of x instead of null when val1 > 0 and val2 == 0?

edit: durr this works right?

SELECT IF(val1 > 0 AND val2 = 0, 100, (val1-val2)/val2) FROM table

Would SELECT COALESCE(((val1-val2) / val2), "x") work? Though that only checks for the divide-by-zero, it doesn't check that val1 is positive.

# ? May 10, 2011 19:36

wellwhoopdedooo: Nov 23, 2007; Pound Trooper!

fletcher posted:

If I have a query such as:
code:
SELECT (val1-val2)/val2 FROM table
How do I get it to return a value of x instead of null when val1 > 0 and val2 == 0?

edit: durr this works right?

SELECT IF(val1 > 0 AND val2 = 0, 100, (val1-val2)/val2) FROM table

SELECT CASE WHEN val > 0 THEN (val1-val2)/val2 ELSE x END AS valColumn FROM table;

# ? May 10, 2011 20:53

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Is it possible to combine LEFT JOIN and comma syntax for joins in MySQL? For example, instead of doing...

SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id LEFT JOIN shazbot ON foo.shaz = shazbot.shaz

or doing...

SELECT * FROM foo, bar, shazbot WHERE foo.id = bar.id AND foo.shaz = shazbot.shaz

... is it possible to do this?

SELECT * FROM (foo LEFT JOIN bar ON foo.id = bar.id) AS fnord, shazbot WHERE shazbot.shaz = fnord.shaz

or something similar?

# ? May 10, 2011 22:39

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Golbez posted:

Is it possible to combine LEFT JOIN and comma syntax for joins in MySQL? For example, instead of doing...

SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id LEFT JOIN shazbot ON foo.shaz = shazbot.shaz

or doing...

SELECT * FROM foo, bar, shazbot WHERE foo.id = bar.id AND foo.shaz = shazbot.shaz

... is it possible to do this?

SELECT * FROM (foo LEFT JOIN bar ON foo.id = bar.id) AS fnord, shazbot WHERE shazbot.shaz = fnord.shaz

or something similar?

Have you tried it?

Based on this page (paragraph starting with "Previously, the comma operator...") it looks like they treat a comma inside the FROM clause as meaning the same thing as JOIN, so based on that my guess would be yes you can do that.

Personal opinion: Better to always use explicit join syntax.

# ? May 10, 2011 23:04

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Golbez posted:

Is it possible to combine LEFT JOIN and comma syntax for joins in MySQL? For example, instead of doing...

SELECT * FROM foo LEFT JOIN bar ON foo.id = bar.id LEFT JOIN shazbot ON foo.shaz = shazbot.shaz

or doing...

SELECT * FROM foo, bar, shazbot WHERE foo.id = bar.id AND foo.shaz = shazbot.shaz

... is it possible to do this?

SELECT * FROM (foo LEFT JOIN bar ON foo.id = bar.id) AS fnord, shazbot WHERE shazbot.shaz = fnord.shaz

or something similar?

Kinda but then its called a SubSelect and you have to specificy each field its not really a good way to select things.
Select foo.f1, foo.f2, (select bar.f1, bar.f2 from bar where foo.id = bar.id) from foo

I belive the second one is possible.

And there is another way you can do them

Select * from foo
Left join bar
left join shaz
where
bar.id = foo.id
and shaz.id = bar.id

# ? May 11, 2011 01:08

benisntfunny: Dec 2, 2004; I'm Perfect.

Aredna posted:

This is on SQL Server 2008. I'm optimizing a query and want to test the performance of several different indexes. The issue is that the table is loaded into cache after the first run increasing the speed by 50x.

I found some methods of clearing the system cache for benchmarking, but I'm not the only one on the server and don't want to cause slowdowns for others just so I can get a better benchmark.

Without being able to test on a server by myself, what are my best options left?

local server? Obviously it won't be exactly the same but you might be able to get a general idea.

# ? May 11, 2011 01:42

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

Have you tried it?

Based on this page (paragraph starting with "Previously, the comma operator...") it looks like they treat a comma inside the FROM clause as meaning the same thing as JOIN, so based on that my guess would be yes you can do that.

Personal opinion: Better to always use explicit join syntax.

Nope, was at home and it struck me as a possible solution to a query builder I'm making.

Sprawl posted:

And there is another way you can do them

Select * from foo
Left join bar
left join shaz
where
bar.id = foo.id
and shaz.id = bar.id

That would be very useful, actually, if it works... hm.

# ? May 11, 2011 13:28

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

I have a production database whose tables contents I need dumped nightly to a second corporate data warehouse. For various reasons, we cannot use the typical replication methods of snapshots, log shipping or replication so we have decided to use SSIS packages and T-SQL to do the job.

I've written the easy T-SQL that selects all new records in a specific table and inserts them into the corresponding table in the DW. However:

My problem is how do I select records that have been changed in the production database to do an update against the DW table?

This is a vague question, but I don't know enough about the process to know what information I should provide here.

# ? May 12, 2011 21:04

Goat Bastard: Oct 20, 2004

You probably want to use MERGE.

# ? May 12, 2011 21:47

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

I actually said fuckit and am now looking at a product called SQL Data Compare Pro. It does data comparisons between two databases and can be called via command line (helpful for scheduled tasks...)

# ? May 13, 2011 06:11

benisntfunny: Dec 2, 2004; I'm Perfect.

Agrikk posted:

I actually said fuckit and am now looking at a product called SQL Data Compare Pro. It does data comparisons between two databases and can be called via command line (helpful for scheduled tasks...)

Visual Studio Team Data Edition does it. I mean if you guys are interested spending money...

But yeah - from the last guy's point. It sounds like you could probably just use MERGE and an Agent Job. Probably doesn't even need SSIS.

# ? May 13, 2011 17:10

Rick Rickshaw: Feb 21, 2007; I am not disappointed I lost the PGA Championship. Nope, I am not.

I have a problem and a potential solution, but I'm not sure how I can complete the solution.

I have a list of Patients with a count of their medications on a given day, as seen below:

code:

ID    Patient_ID     Med_Count
1       64234            5
2       64234            4
3       73455            10 
4       42624            5
5       42624            7
6       42624            8

I want to run a report that displays the data as seen below:

code:

Patient_ID     Med_Count_Day1     Med_Count_Day2     Med_Count_Day3    
64234            5                      4
73455            10 
42624            5                      7                  8

My potential solution would be to add a "Visit Number" column to all of the data (retroactively), such as this:

code:

ID    Patient_ID     Visit_Num          Med_Count
1       64234            1                  5
2       64234            2                  4
3       73455            1                  10 
4       42624            1                  5
5       42624            2                  7
6       42624            3                  8

If I had this extra identifier, I could do: MAX(SELECT CASE WHEN visit_num = 1 THEN Med_Count ELSE null END) AS Med_Count_Day1 (I'm using MySQL - not sure of the syntax yet, but that's the jist of it). And I'm only interested in coding for up to five days, so the lack of scalability in this solution is not a concern. But I will want to repeat this action on new data as it becomes available. Unfortunately I am not in charge of how the data is being collected.

Is it possible to do an UPDATE to add a "Visit_Number" as proposed above? Google hasn't helped me

Perhaps a FUNCTION needs to be written, but I wouldn't really know where to begin on that.

_{(data used above is not real)}

edit:

code:

DELIMITER $$
DROP PROCEDURE IF EXISTS `curForm`$$
CREATE PROCEDURE `videx`.`curForm`()
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE p_id,r_id,v_id,currentseq,prevseq INT;
	DECLARE c_form CURSOR FOR 
        SELECT `form`.`PatientID`, `form`.`DataID`, 
        `form`.`Visit_Num` FROM `form` ORDER BY `form`.`PatientID`, 
        `form`.`DataID`, `form`.`Visit_Num`;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN c_form;
	REPEAT
		FETCH c_form INTO p_id,r_id,v_id;
		IF done THEN
			SELECT MAX(`form`.`Visit_Num`) INTO prevseq FROM form WHERE `form`.`PatientID`=p_id;
			UPDATE form SET `Visit_Num`=(prevseq+1) WHERE `form`.`PatientID`=p_id AND `form`.`DataID`=r_id;
		END IF;
	UNTIL done END REPEAT;
	CLOSE c_form;
END$$
DELIMITER ;

A friend of mine helped me come up with this, but it doesn't quite function as intended. The patients can repeat up to five times in this set of data, and there's only one patient that does in fact repeat five times. This procedure sets Visit_Num to 1 for the last occurrence of that patient. I have no idea why.

Rick Rickshaw fucked around with this message at 21:03 on May 13, 2011

# ? May 13, 2011 18:24

wellwhoopdedooo: Nov 23, 2007; Pound Trooper!

Rick Rickshaw posted:

Is it possible to do an UPDATE to add a "Visit_Number" as proposed above? Google hasn't helped me Perhaps a FUNCTION needs to be written, but I wouldn't really know where to begin on that.

code:

SELECT
  *,
  (SELECT COUNT(*)
    FROM blarg b2
    WHERE
      b2.Patient_ID =  b1.Patient_ID AND
      b2.ID         <= B1.ID
  ) AS Visit_Num
  FROM blarg b1
;

ID  Patient_ID  Med_Count  Visit_Num
 1       64234          5          1
 2       64234          4          2
 3       73455         10          1
 4       42624          5          1
 5       42624          7          2
 6       42624          8          3

I did this on MS SQL 2k8 R2, dunno if the syntax will work anywhere else (pretty sure it'll work just about anywhere, it's not complex), but you get the idea. If this doesn't work, post the SQL server you'll be using it on.

I'd advise against changing the table at all--you don't need to, and that's a great reason not to. In fact, when running reports, consider the data read-only. If you absolutely, positively need to store data, make a temporary table, but don't change anything during reports, unless it's rptInfo.lastRunDate.

# ? May 13, 2011 20:58

Rick Rickshaw: Feb 21, 2007; I am not disappointed I lost the PGA Championship. Nope, I am not.

wellwhoopdedooo posted:

code:
SELECT
  *,
  (SELECT COUNT(*)
    FROM blarg b2
    WHERE
      b2.Patient_ID =  b1.Patient_ID AND
      b2.ID         <= B1.ID
  ) AS Visit_Num
  FROM blarg b1
;

ID  Patient_ID  Med_Count  Visit_Num
 1       64234          5          1
 2       64234          4          2
 3       73455         10          1
 4       42624          5          1
 5       42624          7          2
 6       42624          8          3
I did this on MS SQL 2k8 R2, dunno if the syntax will work anywhere else (pretty sure it'll work just about anywhere, it's not complex), but you get the idea. If this doesn't work, post the SQL server you'll be using it on.

I'd advise against changing the table at all--you don't need to, and that's a great reason not to. In fact, when running reports, consider the data read-only. If you absolutely, positively need to store data, make a temporary table, but don't change anything during reports, unless it's rptInfo.lastRunDate.

Thank you so much. That's incredible. It worked as-is in MySQL.

You're certainly right about changing the data - I'd prefer not to, but I'm actually running MySQL off of a WAMP installation on my laptop. I'm importing the data from a spreadsheet that's dumped out of a database that I don't even have access to (consultants). It's very sketchy. So adding a column wasn't exactly a huge worry for me. But this is definitely a much better solution.

# ? May 13, 2011 21:22

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Trying to optimize a script that takes a bunch of MySQL MyISAM databases and consolidates them for easy searching... unfortunately, I can't find anywhere to optimize it. My only thought is perhaps, do we need to ANALYZE TABLE right after recreating? This is the basic flow:

CREATE TABLE LIKE ... ALTER TABLE DISABLE KEYS INSERT INTO ... SELECT ... ALTER TABLE ENABLE KEYS ANALYZE TABLE

I'm guessing the analyze here is useful since we just added a few million rows to the index?

# ? May 17, 2011 14:55

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Golbez posted:

Trying to optimize a script that takes a bunch of MySQL MyISAM databases and consolidates them for easy searching... unfortunately, I can't find anywhere to optimize it. My only thought is perhaps, do we need to ANALYZE TABLE right after recreating? This is the basic flow:

CREATE TABLE LIKE ... ALTER TABLE DISABLE KEYS INSERT INTO ... SELECT ... ALTER TABLE ENABLE KEYS ANALYZE TABLE

I'm guessing the analyze here is useful since we just added a few million rows to the index?

Ah yea it would be useful since you had the keys off while inserting you might want to run an optimize on the tables as well.

What do you mean take a bunch of mysql isam database and consolidates them? are all the table names the same?

You would get much faster load times if you use the database export/import functions but that requires access to a shell/command prompt/.net or some other local scripting language.

# ? May 17, 2011 17:47

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Sprawl posted:

What do you mean take a bunch of mysql isam database and consolidates them? are all the table names the same?

Yeah, we have a half dozen identical (in structure) databases, vestiges of multiple mergers and acquisitions, and they have to stay separate - except when we merge them all together for searching joy. (And even that is purely experimental at this point)

Sprawl posted:

You would get much faster load times if you use the database export/import functions but that requires access to a shell/command prompt/.net or some other local scripting language.

I have shell access; what functions do you suggest using in this situation?

# ? May 17, 2011 17:56

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Golbez posted:

Yeah, we have a half dozen identical (in structure) databases, vestiges of multiple mergers and acquisitions, and they have to stay separate - except when we merge them all together for searching joy. (And even that is purely experimental at this point)

I have shell access; what functions do you suggest using in this situation?

The mysqldump function. It would need a large amount of swtiches to get it just right like, no table creates, the --compact one, and there are some to even do it directly to another mysql server if the instances are separate.

# ? May 17, 2011 18:17

Internet Explorer: Jun 1, 2005

Okay SQL megathread. I have not read the entire thread and the first post isn't much of a Megathread info post, so I am sorry if this question has been asked before. We have some front-office / back-office software that is used for a lot of financial reporting and that sort of thing to the higher ups who run the company. The problem is, my boss is the only "DBA" and he does everything by hand. Every time they need some numbers he types of an SQL query or uses an old query and edits it. There has to be some piece of software out there that will help him build reports that are essentially templates, or are easy enough for the end-user to find the data they need with dropdown boxes, etc. Something like Crystal Reports, I guess? I do not have much experience in this department. Please recommend me something because it sucks having a boss who is supposed to be managing things and spends his entire day reinventing the wheel. Thank you!

[Edit: MSSQL 2008 if it matters.]

# ? May 20, 2011 21:16

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Crystal Reports would work fine yes and its reasonably priced if you get the "dev" edition.

# ? May 20, 2011 21:53

benisntfunny: Dec 2, 2004; I'm Perfect.

Internet Explorer posted:

Okay SQL megathread. I have not read the entire thread and the first post isn't much of a Megathread info post, so I am sorry if this question has been asked before. We have some front-office / back-office software that is used for a lot of financial reporting and that sort of thing to the higher ups who run the company. The problem is, my boss is the only "DBA" and he does everything by hand. Every time they need some numbers he types of an SQL query or uses an old query and edits it. There has to be some piece of software out there that will help him build reports that are essentially templates, or are easy enough for the end-user to find the data they need with dropdown boxes, etc. Something like Crystal Reports, I guess? I do not have much experience in this department. Please recommend me something because it sucks having a boss who is supposed to be managing things and spends his entire day reinventing the wheel. Thank you!

[Edit: MSSQL 2008 if it matters.]

I don't really see anything wrong with what your boss is doing in theory. Sounds like he just sucks at SQL.

# ? May 20, 2011 21:56

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Internet Explorer posted:

Okay SQL megathread. I have not read the entire thread and the first post isn't much of a Megathread info post, so I am sorry if this question has been asked before. We have some front-office / back-office software that is used for a lot of financial reporting and that sort of thing to the higher ups who run the company. The problem is, my boss is the only "DBA" and he does everything by hand. Every time they need some numbers he types of an SQL query or uses an old query and edits it. There has to be some piece of software out there that will help him build reports that are essentially templates, or are easy enough for the end-user to find the data they need with dropdown boxes, etc. Something like Crystal Reports, I guess? I do not have much experience in this department. Please recommend me something because it sucks having a boss who is supposed to be managing things and spends his entire day reinventing the wheel. Thank you!

[Edit: MSSQL 2008 if it matters.]

We had a similar situation so we all took an afternoon and wrote a Windows Forms interface for canned reports. It's not very hard, but if you don't already have a web service/data access layer set up there's a bit more work involved.

# ? May 20, 2011 22:37

Thel: Apr 28, 2010

benisntfunny posted:

I don't really see anything wrong with what your boss is doing in theory. Sounds like he just sucks at SQL.

Basically this. You only need Crystal Reports (or similar - you could look into SQL Server Reporting Services, also) if you need the ability to generate 'pretty' reports for people that get scared by Excel.

Your boss will probably continue to suck at reports, but at least they'll be prettier if you get CR or SSRS.

# ? May 21, 2011 08:56

baquerd: Jul 2, 2007; by FactsAreUseless

Thel posted:

Basically this. You only need Crystal Reports (or similar - you could look into SQL Server Reporting Services, also) if you need the ability to generate 'pretty' reports for people that get scared by Excel.

Your boss will probably continue to suck at reports, but at least they'll be prettier if you get CR or SSRS.

Crystal reports also needs licensed upgrades to keep with the times, which aren't cheap. We disintegrated a crystal reports 7 installation with a bunch of reports and migrated into our proprietary reporting system just last year.

# ? May 21, 2011 14:25

Internet Explorer: Jun 1, 2005

Thel posted:

Basically this. You only need Crystal Reports (or similar - you could look into SQL Server Reporting Services, also) if you need the ability to generate 'pretty' reports for people that get scared by Excel.

Your boss will probably continue to suck at reports, but at least they'll be prettier if you get CR or SSRS.

Thanks for the feedback. Basically trying to figure out a way for the end users to get what they need without having to rely on my boss for every little thing. And yeah, these are people scared of Excel, so as pretty as as "easy" as we can make it would be for the best.

# ? May 21, 2011 20:45

Aredna: Mar 17, 2007; Nap Ghost

This is on SQL Server 2008. I received a performance tip to change my queries from the form of "select ... into #temp" to "insert into #temp select ..." because the latter uses fewer locking resources.

I've searched around and found one website that agrees, but all other websites say that "select ... into #temp" will be faster due to less logging taking place.

The obvious answer is to test both on my system, but as a rule of thumb which method does everyone else use? What "locking resources" are being used less and how does that impact overall performance?

# ? May 23, 2011 23:08

benisntfunny: Dec 2, 2004; I'm Perfect.

Aredna posted:

This is on SQL Server 2008. I received a performance tip to change my queries from the form of "select ... into #temp" to "insert into #temp select ..." because the latter uses fewer locking resources.

I've searched around and found one website that agrees, but all other websites say that "select ... into #temp" will be faster due to less logging taking place.

The obvious answer is to test both on my system, but as a rule of thumb which method does everyone else use? What "locking resources" are being used less and how does that impact overall performance?

On SQL Server I've always used INSERT INTO #PandaBear SELECT... on Oracle I do the opposite. Reasoning? None. I also prefer using variables for my temp tables in SQL Server* (i.e. @PandaBear instead).

*Unless it's a metric gently caress ton of data then I'd probably stick with a temp table.

benisntfunny fucked around with this message at 00:19 on May 24, 2011

# ? May 24, 2011 00:17

bewilderment: Nov 22, 2007; man what

I'm a huge newbie to SQL and SQL Server, I did a short course on basic SQL queries and database structure a year ago, and remember most of it, but some things just weren't covered.

Do stored procedures allow you to have if syntax? Specifically I'm trying to do something like this:

Create Procedure getTable
AS
@tname nvarchar(10)
IF @tname = 'foo'
(
Begin
Select * From Foo
End
)
ELSE IF @tname = 'bar'
(
Begin
Select * From Bar
End
)

Any help with the syntax of the thing would be nice. I'd test it right now but I'm not at my own computer at this moment.

# ? May 24, 2011 06:44

Adbot: ADBOT LOVES YOU

# ? May 19, 2024 19:09

Bad Titty Puker: Nov 3, 2007; Soiled Meat

bewilderment posted:

I'm a huge newbie to SQL and SQL Server, I did a short course on basic SQL queries and database structure a year ago, and remember most of it, but some things just weren't covered.

Do stored procedures allow you to have if syntax? Specifically I'm trying to do something like this:

Create Procedure getTable
AS
@tname nvarchar(10)
IF @tname = 'foo'
(
Begin
Select * From Foo
End
)
ELSE IF @tname = 'bar'
(
Begin
Select * From Bar
End
)

Any help with the syntax of the thing would be nice. I'd test it right now but I'm not at my own computer at this moment.

I recommend that you read a good introduction to database concepts and SQL before attempting to write code. What you posted isn't a syntactical issue at all -- it shows that you don't understand the fundamental concepts of relational databases. Learn those first, then look at examples of good SQL code.

# ? May 24, 2011 07:12

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »