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 »

toby: Dec 4, 2002

I got this in a PM from geetee, for those interested:

geetee posted:

I can't post , but yes, coalesce works and is better apparently:

quote:
The IFNULL function is a special case of the COALESCE function. IBM recommends using COALESCE in place of IFNULL because COALESCE is included in the ANSI SQL-92 standard.

So there's that, I guess!

# ? Aug 8, 2008 22:43

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 02:09

stack: Nov 28, 2000

toby posted:

(by way of geetee)
COALESCE > IFNULL

Then there you go. I learned a valid reason for using COALESCE() over IFNULL().

# ? Aug 8, 2008 23:13

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

Zoracle Zed posted:

You could also do:
code:
tblemails
  Period: 1, 7, or 30
Which would give the simplicity of #3 but the flexibility (or more) of the first two options.

Zounds, that's a good idea. And then just use code to convert it to 'daily', et.al. for display purposes, but the database entry would be totally unambiguous!

Edit: Except this will run every month, rather than every 30 days. Nuts. (This is to send emails of things that have been updated in the last [x] days) I guess I could make it "31", so there might be a minor overlap but better than losing a day.

Golbez fucked around with this message at 00:10 on Aug 9, 2008

# ? Aug 9, 2008 00:08

DLCinferno: Feb 22, 2003; Happy

LordLobo posted:

This is specifically a SQL Server 2005 question:

Is it possible to query the SYS objects to get the username of someone who created a stored procedure?

Someone wrote something so stupid I need to beat them, severely.

Not without looking at the logs.

# ? Aug 9, 2008 02:11

RagingHematoma: Apr 19, 2004; Goiters can be beautiful too!

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

Some more fuel to the fire.

# ? Aug 9, 2008 05:51

Victor: Jun 18, 2004

I tend to use isnull when I'm clearly returning a column, with a default value so I don't get null back. I use coalesce when I'm pulling the first non-null column, such as when I do a full outer join. That way, you get some semantic use out of the two different ways and, just by happenstance, choose the more efficient method according to the link above with respect to subqueries and default values.

# ? Aug 10, 2008 18:32

chemosh6969

Jul 3, 2004

code:
cat /dev/null > /etc/professionalism

_{_{I am in fact a massive asswagon.

Do not let me touch computer.}}

I'm hoping this is possible but wouldn't be shocked if it wasn't.

I'm wondering if there is a way to link both an oracle database and a mysql database.

If not, if there's some feature in SQL Developer that can do it. I already have the plug-in for mysql databases and have both databases in my connection list.

Or if someone knows of other solutions besides running queries in one, copying the results and using it in another query on the other database.

# ? Aug 13, 2008 00:43

Goat Bastard: Oct 20, 2004

I have a query that (obviously paraphrased) looks like

select t1.value1, t2.value2
from table1 t1, table2 t2
where
t1.value3 = 'something'
and t1.value3 = t2.value3(+)

I'm trying to find out what the "(+)" at the end of the last line does. Google is no help as it apparently can't search for punctuation marks, and no other search terms I use return anything relevant.

If anyone can help me out with an explanation or point me towards a document containing one I'd really appreciate it.

# ? Aug 13, 2008 02:48

atomic johnson: Dec 7, 2000; peeping-tom techie with x-ray eyes

Goat Bastard posted:

I'm trying to find out what the "(+)" at the end of the last line does. Google is no help as it apparently can't search for punctuation marks, and no other search terms I use return anything relevant.

That's Oracle's old OUTER JOIN syntax.

# ? Aug 13, 2008 02:55

Goat Bastard: Oct 20, 2004

atomic johnson posted:

That's Oracle's old OUTER JOIN syntax.

Thank you.

# ? Aug 13, 2008 04:12

Stephen: Feb 6, 2004; Stoned

In MySQL I populate a table of cars from several different feeds. Each feed runs through the vehicles one dealership at a time and inserts them. This is causing me a problem later in my application when the vehicles are being displayed, the first dealerships in the feed are always displayed first in the search results. Since they're paying for the service, I can't have preference based on dealers.

The only solution I could think of is to assign a random number between 1-1000 into an order column for each vehicle and then sort on that in my code.

Is there a way to create a default value for a column of RAND(1-1000) in MySQL? Or is there a better way to have MySQL order results not based on it's primary key?

# ? Aug 13, 2008 17:24

chemosh6969

Jul 3, 2004

code:
cat /dev/null > /etc/professionalism

_{_{I am in fact a massive asswagon.

Do not let me touch computer.}}

Stephen posted:

In MySQL I populate a table of cars from several different feeds. Each feed runs through the vehicles one dealership at a time and inserts them. This is causing me a problem later in my application when the vehicles are being displayed, the first dealerships in the feed are always displayed first in the search results. Since they're paying for the service, I can't have preference based on dealers.

The only solution I could think of is to assign a random number between 1-1000 into an order column for each vehicle and then sort on that in my code.

Is there a way to create a default value for a column of RAND(1-1000) in MySQL? Or is there a better way to have MySQL order results not based on it's primary key?

Couldn't you do an Order By vehicle type

# ? Aug 13, 2008 17:42

Stephen: Feb 6, 2004; Stoned

chemosh6969 posted:

Couldn't you do an Order By vehicle type

Yes, but then I'm giving preference by vehicle type. Ideally I'd like to have a random list of vehicles pulled, however I can't do that with pagination.

# ? Aug 13, 2008 17:46

chemosh6969

Jul 3, 2004

code:
cat /dev/null > /etc/professionalism

_{_{I am in fact a massive asswagon.

Do not let me touch computer.}}

You only said you didn't want preference by dealer.

I did a search for "mysql random number" and found
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

quote:

you can retrieve rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND();

# ? Aug 13, 2008 18:33

Stephen: Feb 6, 2004; Stoned

chemosh6969 posted:

You only said you didn't want preference by dealer.

I did a search for "mysql random number" and found
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

Yes, but since I'm limiting the results per-page, I can't have it returning random rows each time I do the query. Page 1 would have some of the same results as page 2 etc. and I wouldn't be able to link to any pages. Therefore I'll need some type of consistant order to sort by when the table is queried, but one that doesn't give preference based on any of the dealerships, car features etc.

# ? Aug 13, 2008 19:13

Aredna: Mar 17, 2007; Nap Ghost

What about creating a GUID for each row and then ordering by that column? It'll add minimal time during row creation and has all but a 0% chance of providing a duplicate.

It looks like UUID() is what you need for MySQL.
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

Depending on your platform the end may be the MAC address of the machine or a random number.

Edit: I just realized this wouldn't change for new people, so each person would see the same random cars on the main page. You'll probably have to store some per-user settings to pull it off as you intend.

Aredna fucked around with this message at 19:33 on Aug 13, 2008

# ? Aug 13, 2008 19:29

Stephen: Feb 6, 2004; Stoned

chemosh6969 posted:

You only said you didn't want preference by dealer.

I did a search for "mysql random number" and found
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

I didn't realize that RAND() could accept an argument. If I do an ORDER BY RAND(20), the results will be randomly returned, but will always be randomly sorted the same way. This works perfectly now, since each time the user views each page it should show up the same way!

# ? Aug 13, 2008 19:33

stack: Nov 28, 2000

When creating a intermediate table in MySQL is it better to have a PRIMARY KEY index on both values or individual indexes for each column? Or to MySQL is there even a difference?

I asking about simple two column tables where the rows contain two foreign keys.

# ? Aug 14, 2008 22:13

pig-fukr: Nov 3, 2002

var1ety posted:

My personal preference is to use COALESCE/CASE in all situations and ignore our database's large number of null-handling functions.

Oddly enough, you're using CASE either way. In fact, with MSSQL you're ultimately using CASE whether you call ISNULL, COALESCE or straight up CASE. Under the hood the engine does the same exact call.

# ? Aug 15, 2008 00:09

Squashy Nipples: Aug 18, 2007

Here is a neat little sorting trick I just came up with.

What if you need to ORDER BY a (fixed or variable length) string field, but some of the strings start with "THE "? If you want the sorting to ignore the "THE ", you can use this expression in your ORDER BY:

quote:

Name of Field containing string titles: TITLE

( CASE WHEN LEFT( TITLE, 4) = 'THE '
THEN SUBSTR( TITLE, 5)
ELSE TITLE END )

(or, if you want the Titles returned with the "THE "'s removed, stick the expression in your SELECT, give it an alias, and ORDER BY the alias)

I realize that this might be nothing new, but I found it useful, and hopefully someone else will.

# ? Aug 15, 2008 20:26

vagina rodeo: Jul 2, 2005; vulva puppets, that's what

This is for SQL Server 2005. I'm trying to add pagination functionality to a stored procedure that implements the querying abilities of an in-house PHP O/RM. The stored procedure receives valid sql, pre-formatted sort and where clauses which are used to build a statement in the SP which is then executed. In adding paging I'm trying to use common table expressions but they don't seem to allow anything but constant statements (and maybe variable substitutions). Can anyone help me reconcile the way we have implemented this with the several methods of paging that are available?

code:

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[cco_participants_search]
(
	@WHEREClause	varchar(1024),
	@orderColumn	varchar(1024),
	@countySearch	varchar(255),
	@row_start	int,
	@row_end	int
)
as
	DECLARE @ErrorMsgID int
	
	DECLARE @SQLStatement varchar(255)

	DECLARE @pagination_stmt varchar(64)


	if @WHEREClause is NULL
		SELECT @WHEREClause = " WHERE (1 = 1)"

	if @orderColumn = ""
		SELECT @orderColumn = NULL
	if @orderColumn is NULL
		SELECT @orderColumn = "1"
	if @orderColumn <> "last_name"
		SELECT @orderColumn = @orderColumn + ", last_name"



with pagination as
(
		SELECT row_number() over (ORDER BY @orderColumn) as row_no,* FROM cco_participants @WHEREClause
)

	if @row_start = "0" or @row_end = "0"
		SELECT @pagination_stmt = ""
	else
		SELECT @pagination_stmt = "where row_no BETWEEN " + @row_start + " AND " + @row_end

	SELECT @SQLStatement = "SELECT *, (SELECT count(*) from pagination) as totalResults" + @pagination_stmt

	EXEC(@SQLStatement)

# ? Aug 15, 2008 22:30

Victor: Jun 18, 2004

Your CTE also needs to be made into dynamic SQL and executed as such.

# ? Aug 15, 2008 23:13

Nurbs: Aug 31, 2001; Three fries short of a happy meal...Whacko!

sqlserver 2005 here

Is there a statement I can use that will tell me what the the constraints are made up of?

I'm hoping for something like

constraint_name | column_name | constraint_type

-Edit,

Solved my own question by figuring out the Information_Schema.Constraint_column_usage view

Nurbs fucked around with this message at 21:21 on Aug 16, 2008

# ? Aug 16, 2008 11:24

Pussy v. Sperguson: Apr 28, 2003

I recently put down some money for a cheap Dreamhost account for a couple projects I'm working on. Does anyone have some experience working with their system of 'conueries'?

basically,

quote:

"Conueries" is a term we at DreamHost came up with to measure MySQL database usage. The word is a combination of "connections" and "queries", as that is what the unit is derived from! Basically, the number of MySQL queries you have in a month, plus 25 times the number of connections you have is your total "conueries" for a month!

I'm just trying to figure out the most efficient of handling some of the scripts I'm writing; mainly, for monitoring outbound traffic, searching, adding comments, basic CMS stuff like that.

# ? Aug 17, 2008 18:58

ijustam: Jun 20, 2005

code:

SELECT Motherboards.Brand,
       Motherboards.Model,
       DISTINCT(Processors.Brand)  AS procBrand,
       Processors.Model            AS procModel,
       Processors.FSB              AS procFSB,
       Processors.ID               AS procID,
       Processors.Series           AS procSeries
FROM   Motherboards,
       Processors
WHERE  FSB LIKE "%%"
       AND Motherboards.Brand LIKE "%%"
       AND Motherboards.Model LIKE "%%"
       AND Processors.Brand LIKE "%%"
       AND Processors.Series LIKE "%%"
       AND Processors.Model LIKE "%%"
       AND Motherboards.ID IN (SELECT FSB.moboID
                               FROM   FSB);

I'm getting a SQL syntax error around the DISTINCT() part. Am I not allowed to get one column as distinct? Does my query make any sense? To be honest I only partially know what I'm doing, and partially shooting into the dark. :psyduck:

# ? Aug 18, 2008 23:21

Squashy Nipples: Aug 18, 2007

DISTINCT refers to the entire SELECT clause, not just any one Column. Think about it, you are asking for distinct ROWS, not Columns.

The usage is: 'SELECT' or 'SELECT DISTINCT'

Don't put DISTINCT anywhere else in the SELECT clause.

They exception to this is that you can also use DISTINCT inside certain Expressions, such as COUNT, but this is an Expression that defines a Column, not the rows SELECTed. In other words, do you want to COUNT all Rows returned, or just the Rows that have unique values?

# ? Aug 18, 2008 23:38

syphon^2: Sep 22, 2004

This should be a simple question, but I'm a bit of a SQL novice.

I'd like to make a sproc that 'sanitizes' a set of results by stripping out certain characters (comma, single/double quote, things of that like). The raw data still needs to be accessible, but I'd like to create a sproc which returns sanitized data (without modifying the raw data) for programmatic consumption.

I googled it, but all I could find was stuff designed for performing a find+replace on the data source itself.

EDIT: Better yet if I can accomplish this without actually creating a sproc, but simply with a query.

syphon^2 fucked around with this message at 18:24 on Aug 20, 2008

# ? Aug 20, 2008 18:16

Aredna: Mar 17, 2007; Nap Ghost

Depending on the flavor of SQL you can probably do this with a query (or stored view) with regular expressions.

What flavor of SQL are you using?

# ? Aug 20, 2008 18:58

syphon^2: Sep 22, 2004

MSSQL 2005. Sorry, I meant to add that, but apparently I have the attention span of a kitten and forgot.

Looking at the data, I'm wondering if this is possible with a single query. There is one column I need to get rid of a comma, and then another column that needs to be cleansed of commas or quotes.

# ? Aug 20, 2008 19:05

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Victor posted:

Your CTE also needs to be made into dynamic SQL and executed as such.

I don't understand, what is the benefit to using dynamic SQL versus me just stitching a query up client side and then running it? It seems pretty much the same, to me.

# ? Aug 20, 2008 19:39

Aredna: Mar 17, 2007; Nap Ghost

syphon^2 posted:

MSSQL 2005. Sorry, I meant to add that, but apparently I have the attention span of a kitten and forgot.

Looking at the data, I'm wondering if this is possible with a single query. There is one column I need to get rid of a comma, and then another column that needs to be cleansed of commas or quotes.

See if you have the RegExReplace function available. From my limited searching it is not always installed with SQL Server 2005. If you can use it this post (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205) has a few examples and another more effiecient version to use if you need to run it on either on hundreds of thousands of records, or will need to run it a lot. The laptop, yes I know, someone tested it on was about 1k rows per second.

# ? Aug 20, 2008 20:14

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Edit: Wow, not an SQL question...

Triple Tech fucked around with this message at 22:21 on Aug 20, 2008

# ? Aug 20, 2008 21:05

Xae: Jan 19, 2005

Anyone have experience with Teradata's Fastload or Multiload? I'm pretty familiar with Oracle's sqlloader but I need to load a bit of data into a Teradata setup (1.5 million rows). Unfortunately the turn around from the DBA/ETL team is measured in weeks, and I need something very fast.

Both of Teradata's programs are pissing me off to no end, they are seemingly needlessly complex and the documentation is borderline non-existent for them.

All I want to do is load a "|" delimited file with 6 columns into a database, it shouldn't be so loving difficult.

# ? Aug 21, 2008 03:05

syphon^2: Sep 22, 2004

Aredna posted:

See if you have the RegExReplace function available.

FYI, strapping REPLACE() into my SELECT statement did what I needed. I appreciate your help though!

code:

SELECT User, REPLACE(Description, ',', '') AS Description, REPLACE(Customer, ',', ' ') AS Customer, Status
FROM activetickets
WHERE (Status <> 'Resolved')

# ? Aug 21, 2008 11:51

Enderzero: Jun 19, 2001; The snowflake button makes it
cold cold cold
Set temperature makes it
hold hold hold

Thank you Victor, your advice worked very well. I now have it functioning except for one case in which I need to do a join. The error occurs within the if(@countySearch = true) block. Can anyone spot the problem here?

code:

ALTER procedure [dbo].[CCO_PARTICIPANTS_SEARCH]
(
	@WHEREClause	varchar(512),
	@orderColumn	varchar(512),
	@countySearch	varchar(255),
	@row_start	varchar(10),
	@row_end	varchar(10)
)
AS
	DECLARE @ErrorMsgID INT
	DECLARE @pagination_stmt	varchar(128)
	DECLARE @SQLStatement varchar(2048)

	if @WHEREClause is NULL
		SELECT @WHEREClause = "WHERE (1 = 1)"

	if @orderColumn = ""
		SELECT @orderColumn = NULL
	if @orderColumn is NULL
		SELECT @orderColumn = "last_name"
	if @orderColumn <> "last_name"
		SELECT @orderColumn = @orderColumn + ", last_name"


	if @row_start = "0" or @row_end = "0"
		SELECT @pagination_stmt = ""
	ELSE
		SELECT @pagination_stmt = "where row_no BETWEEN " + @row_start + " AND " + @row_end

	if @countySearch = "true"
		SELECT @SQLStatement = "with pagination as
							(
								SELECT row_number() over (ORDER BY " + @orderColumn + ") as row_no,*
 FROM cco_participants c1 INNER JOIN cco_counties ON cco_counties.participant_uid = c1.uid  " + @WHEREClause +
							")
				SELECT *, (SELECT count(*) from pagination) as totalResults FROM pagination " + @pagination_stmt
	ELSE
		SELECT @SQLStatement = "with pagination as
							(
								SELECT row_number() over (ORDER BY " + @orderColumn + ") as row_no,* FROM cco_participants " + @WHEREClause +
							")
				SELECT *, (SELECT count(*) from pagination) as totalResults FROM pagination " + @pagination_stmt
	

	PRINT @SQLStatement
	EXEC(@SQLStatement)

On running this with the countySearch option set to true I get this error message:
"with pagination as
(
SELECT row_number() over (ORDER BY last_name) as row_no,*
FROM cco_participants c1 INNER JOIN cco_counties ON cco_counties.participant_uid = c1.uid WHERE non_cco=0)
SELECT *, (SELECT count(*) from pagination) as totalResults FROM pagination where row_no BETWEEN 1 AND 30
Msg 8156, Level 16, State 1, Line 1
The column 'uid' was specified multiple times for 'pagination'."

I know it's because there are 2 uid fields that are ambiguous, but I'm afraid I can't quite work out the syntax to properly define the two.

# ? Aug 21, 2008 21:24

Enderzero: Jun 19, 2001; The snowflake button makes it
cold cold cold
Set temperature makes it
hold hold hold

Triple Tech posted:

I don't understand, what is the benefit to using dynamic SQL versus me just stitching a query up client side and then running it? It seems pretty much the same, to me.

Well for one, if you want to do pagination you would have to generate a CTE client side, along with the rest of the SQL statement. I inherited this project, so I had assumed it was for proper encapsulation, you know, let SQL server and sprocs deal with the generation of SQL and just send in arguments. Sprocs are not quite as easy to work with as I would like, so I don't know if it's the best decision to do so much dynamic building in the Sprocs, but that's how it is.

# ? Aug 21, 2008 21:29

Victor: Jun 18, 2004

Triple Tech posted:

I don't understand, what is the benefit to using dynamic SQL versus me just stitching a query up client side and then running it? It seems pretty much the same, to me.

That just depends on where you want to put the code. What I was pointing out is that you can't have half-dynamic SQL -- for example, you just can't have a variable containing a table name, and do select * from @hoohoo.

Enderzero posted:

I know it's because there are 2 uid fields that are ambiguous, but I'm afraid I can't quite work out the syntax to properly define the two.

When you write a standard select statement, you can have duplicate columns. However, columns selected inside a CTE (and inside a view) need to be unique. You can't select * -- you can alias one table and do a select wut.*, then specifically select out the columns from the other table, aliasing those that are duplicates.

# ? Aug 22, 2008 03:25

RagingHematoma: Apr 19, 2004; Goiters can be beautiful too!

Enderzero posted:

Well for one, if you want to do pagination you would have to generate a CTE client side, along with the rest of the SQL statement. I inherited this project, so I had assumed it was for proper encapsulation, you know, let SQL server and sprocs deal with the generation of SQL and just send in arguments. Sprocs are not quite as easy to work with as I would like, so I don't know if it's the best decision to do so much dynamic building in the Sprocs, but that's how it is.

The biggest benefit that I get from stored procedures is that it is a single point to do all of my database work. You can do a ton of work in a stored procedure change it a million times, optimize and tune it and as long as the input and output is consistent, you never have to change your application's code. In every environment I have ever worked in, it was always easier to make changes on the database side rather than re-deploy client applications.

Below is stuff that everyone will tell you, but it is tough to see the real world impact of it unless you have some really large applications with tons of users.

One of the benefits of stored procedures is that the code is already compiled. When you send SQL over dynamically through the application, the server has to compile the code before it runs. It may not be too big of a deal in your situation, but in applications with lots of users and transactions it can help performance.

Also, stored procedures offload all of the work from your application to the database server and send less over the network. Again, if you have a lot of users this may be a benefit.

# ? Aug 22, 2008 03:56

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

I don't know much about database design, but I'm not convinced the standard stored procedure benefit still comes through with dynamically generated SQL. It's like eval'ed code in a programming language, it's not semantically relevant up until the point that it's run, because it can always change before then. This isn't something predictable like bind parameters.

That's all I was trying to get at.

# ? Aug 22, 2008 04:30

Adbot: ADBOT LOVES YOU

# ? May 12, 2024 02:09

Victor: Jun 18, 2004

Unless it was actually proven that, for a particular use case, that a sproc was faster than client-side ad-hoc code, I would go with wherever it makes the most place to put the code. If there's insane dynamic sql (building sql statements, which can happen in a sproc or in C# or whatever) happening, then most sql dialects suck.

# ? Aug 22, 2008 04:48

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

«‹›303 »