|
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: So there's that, I guess!
|
# ? Aug 8, 2008 22:43 |
|
|
# ? May 14, 2024 16:02 |
|
toby posted:(by way of geetee) Then there you go. I learned a valid reason for using COALESCE() over IFNULL().
|
# ? Aug 8, 2008 23:13 |
|
Zoracle Zed posted:You could also do: 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 |
|
LordLobo posted:This is specifically a SQL Server 2005 question:
|
# ? Aug 9, 2008 02:11 |
|
http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html Some more fuel to the fire.
|
# ? Aug 9, 2008 05:51 |
|
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 |
|
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 |
|
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 |
|
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 |
|
atomic johnson posted:That's Oracle's old OUTER JOIN syntax. Thank you.
|
# ? Aug 13, 2008 04:12 |
|
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 |
|
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. Couldn't you do an Order By vehicle type
|
# ? Aug 13, 2008 17:42 |
|
chemosh6969 posted:Couldn't you do an Order By vehicle type
|
# ? Aug 13, 2008 17:46 |
|
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:
|
# ? Aug 13, 2008 18:33 |
|
chemosh6969 posted:You only said you didn't want preference by dealer.
|
# ? Aug 13, 2008 19:13 |
|
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 |
|
chemosh6969 posted:You only said you didn't want preference by dealer. 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 |
|
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 |
|
var1ety posted:
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 |
|
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:
(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 |
|
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:
|
# ? Aug 15, 2008 22:30 |
|
Your CTE also needs to be made into dynamic SQL and executed as such.
|
# ? Aug 15, 2008 23:13 |
|
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 |
|
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 |
|
code:
|
# ? Aug 18, 2008 23:21 |
|
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 |
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 |
|
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 |
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 |
|
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 |
|
syphon^2 posted:MSSQL 2005. Sorry, I meant to add that, but apparently I have the attention span of a kitten and forgot. 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 |
|
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 |
|
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 |
Aredna posted:See if you have the RegExReplace function available. code:
|
|
# ? Aug 21, 2008 11:51 |
|
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:
"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 |
|
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 |
|
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. 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.
|
# ? Aug 22, 2008 03:25 |
|
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 |
|
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 |
|
|
# ? May 14, 2024 16:02 |
|
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 |