|
Factor Mystic posted:Alright, this is making a lot more sense now with the example tables there. I got that I would need a Keyword and a Bookmark table, but I didn't know how to properly link to the two together. So each row of the Bookmark_Keyword table contains exactly one Keyword <-> Bookmark relation. Now I get it. Thank you Just to generalize, this is how you do any N-to-N relation of anything. Tags<->Bookmarks, Students<->Classes, Books<->Customers, anything in which a bunch of Somethings can have a bunch of SomethingElses.
|
# ? Jun 24, 2008 08:20 |
|
|
# ? May 15, 2024 03:07 |
|
I want to return Name, H1, H2 and JobID. I've tried Union and Select In and I don't know what I'm doing I'm stupid.
|
# ? Jun 26, 2008 13:26 |
|
Bruce Hussein Daddy posted:
If you don't have spaces in the field names just remove them below. If you don't have spaces in the names you don't need the square brackets around the field names either, but it won't hurt anything by leaving them. code:
|
# ? Jun 26, 2008 14:18 |
Jesus, I typed all this up, and then on a hunch I googled UNION, and there I was. gently caress. Well, here's the post for posterity. Here's a MySQL question. Say I have two tables, namely: code:
code:
|
|
# ? Jun 26, 2008 15:00 |
|
shopvac4christ posted:Jesus, I typed all this up, and then on a hunch I googled UNION, and there I was. gently caress. Well, here's the post for posterity. You should also look at the difference between UNION ALL and UNION as they will give you different result sets. UNION ALL will return every row from both queries. UNION will merge the results of the queries and if there are any duplicated rows between the unioned queries they will be printed only once.
|
# ? Jun 26, 2008 15:45 |
Aredna posted:You should also look at the difference between UNION ALL and UNION as they will give you different result sets. Awesome, very good to know. Thanks!
|
|
# ? Jun 26, 2008 15:51 |
|
Aredna posted:You should also look at the difference between UNION ALL and UNION as they will give you different result sets.
|
# ? Jun 26, 2008 16:41 |
|
Aredna posted:If you don't have spaces in the field names just remove them below. If you don't have spaces in the names you don't need the square brackets around the field names either, but it won't hurt anything by leaving them.
|
# ? Jun 26, 2008 17:07 |
|
Jethro posted:Just for further clarification, this means that, unless you specifically know that you want to discard duplicates you should use UNION ALL, since UNION forces the RDBMS to check for duplicates, even if there aren't any. And for even more clarification: UNION ALL is O(n) and UNION is O(n ^ 2) (n = number of records in both sets). Why? Because UNION ALL simply takes both sets and pushes into a new table, an O(n) operation. UNION is O(n ^ 2) because for each record it goes to add, it needs to check all the previous records for a match (which takes 1 + 2 + 3 + ... + n - 1 = (n - 1)(n) / 2 operations).
|
# ? Jun 26, 2008 17:17 |
|
Factor Mystic posted:In this case, is 'SELECT DISTINCT ...' the proper way to prevent duplicate rows in the result? Yes. http://www.w3schools.com/sql/sql_select.asp I just did a simple test to reassure myself that DISTINCT applies to all columns in the select clause, which is does. code:
code:
code:
|
# ? Jun 26, 2008 17:23 |
|
Factor Mystic posted:In this case, is 'SELECT DISTINCT ...' the proper way to prevent duplicate rows in the result? Chocojosh's post is a good example, but with that said you should always make sure you understand your data and know why you are getting duplicate rows before you decide to eliminate them. It could be a symptom of a large problem. For this problem, it seems to me logically that ROWNUM and JOBNUM should both be unique fields in thier respective tables in H and JOBS. In table R it seems like the combination of ROWNUM and JOBNUM should be unique as well. If both of these are true then there wouldn't be any duplicate rows. DISTINCT is great to remove extra rows as long as it's not used as a bandaid.
|
# ? Jun 26, 2008 19:28 |
|
Aredna posted:If you don't have spaces in the field names just remove them below. If you don't have spaces in the names you don't need the square brackets around the field names either, but it won't hurt anything by leaving them. and it lists Inner Joins but my query code:
quote:Error: Extra characters at end of query: INNER. (State:37000, Native Code: F3E)
|
# ? Jun 27, 2008 18:13 |
|
Bruce Hussein Daddy posted:Thank you, but I am using this human being Pervasive db and that syntax is no good. Here's the documentation for Joins in Pervasive http://www.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-53.html It shouldn't make a difference, but it looks like the join type is optional in your documentation. You could try it with just JOIN instead of INNER JOIN as that is the default join type. If that doesn't work, hopefully someone with experience using Pervasive has some insight as to why the standard syntax isn't working.
|
# ? Jun 27, 2008 18:50 |
|
I'm runnings MS SQL Server 2005 and have close to 100 .sql files that I need it to run (generated by another program). Is there some way to automatically batch process all these files at one time? Some kind of utility or batch file that I could create to get SQL server to open each file and run it?
|
# ? Jun 28, 2008 17:26 |
|
if all those files arrive in the same dir you could simply put cmd /c type c:\sql\*.sql > c:\sql\out\job.sql in a daily job. Then you end up with only one file with a fixed filename which can be easily scheduled to run from sql server.
|
# ? Jun 28, 2008 19:12 |
|
mezz posted:if all those files arrive in the same dir you could simply put cmd /c type c:\sql\*.sql > c:\sql\out\job.sql in a daily job. Works great! Thanks for the tip.
|
# ? Jun 28, 2008 20:59 |
|
What's the best way to store an MD5 hash in Postgres? char(32) is a terrible way to store a base 16 integer, but it's the only suggestion I've seen (other than the laughably worse varchar and text). I can't find a data type that accepts it on first glance. edit: Decided to use a screwdriver instead of a hammer. PHP's MD5 function has a raw_output option since PHP5, meaning I can have the hash as a binary from the beginning. Not sure if it requires more or less effort on the function to get the hash that way, but it's probably negligible, and at least I know how to store a 16 byte binary. Forzan fucked around with this message at 02:24 on Jun 29, 2008 |
# ? Jun 29, 2008 00:11 |
|
I'm a .NET dev, and only occasionally have to write SQL. I can do the basics, mostly, but nothing comlex. Can you help me write a query? What I'm trying to do is delete rows from one table based on the contents of rows in other tables. This is the "spirit" or what I want to do, but of course this isn't valid SQL: code:
I have no idea how to write a query such as this so that it will work. Green_Machine fucked around with this message at 15:49 on Jun 29, 2008 |
# ? Jun 29, 2008 15:45 |
|
mezz posted:if all those files arrive in the same dir you could simply put cmd /c type c:\sql\*.sql > c:\sql\out\job.sql in a daily job. Is there some way to get this to combine *.sql files that are in subdirectories as well? I've been working at it and came up with doing a copy first for /R %G IN (*.sql) DO copy /Y %G c:\files but even that doesn't work for some reason (I believe it's because for doesn't like long filenames)
|
# ? Jun 29, 2008 15:45 |
|
You just need quotes around %g I think. for /R %G IN (*.sql) DO copy /Y "%G" c:\files e: there's certainly a more elegant way but i can only concentrate on my hangover right now it seems mezz fucked around with this message at 16:04 on Jun 29, 2008 |
# ? Jun 29, 2008 16:00 |
|
Green_Machine posted:I'm a .NET dev, and only occasionally have to write SQL. I can do the basics, mostly, but nothing comlex. Can you help me write a query? You can't do joins in a delete statement, I'm unsure on what DBMS you're using, in oracle you'd do something akin to: code:
|
# ? Jun 29, 2008 16:20 |
|
JingleBells posted:You can't do joins in a delete statement, I'm unsure on what DBMS you're using, in oracle you'd do something akin to: In TSQL you can, I'd assume that's what Green_Machine is using if he's doing .net. The syntax is: code:
|
# ? Jun 29, 2008 16:34 |
|
Thanks JingleBells and Zombywuf -- both of your solutions will do the trick.
|
# ? Jun 29, 2008 17:08 |
|
Zombywuf posted:In TSQL you can, I'd assume that's what Green_Machine is using if he's doing .net. The syntax is: Interesting, I don't think I've seen that type of syntax before
|
# ? Jun 29, 2008 17:27 |
|
This isn't a sql code question, but a general question regarding sql server versions. If this is not the right place for this kind of question my apologies. Our company has a point of sale system that uses a sql database and when we first got the system we installed sql server 2005 express because we didn't think our database would grow larger than 4gb, however one year later it has. We plan on getting sql server 2005 standard, but since our operations have nearly come to a stand still, I was wondering if installing the 180 day trial version of sql 2005 standard that microsoft provides would be a temporary fix, Or does the trial version have limitations that would prevent it from functioning in the same way as a full licensed install? I tried searching google for limitations or similar situations to the one we are in, but no luck.
|
# ? Jun 29, 2008 18:09 |
|
Tomathan posted:This isn't a sql code question, but a general question regarding sql server versions. If this is not the right place for this kind of question my apologies. I am pretty sure it is fully functional, but just grab the phone and call microsoft sales, they should be able to tell you pretty quick.
|
# ? Jun 29, 2008 20:12 |
|
Begby posted:I am pretty sure it is fully functional, but just grab the phone and call microsoft sales, they should be able to tell you pretty quick. Thanks, for the reply. It is in fact fully functional, but the license agreement prohibits its use in a "live operating environment" which i think we fall under. So, that's that. I'll just have to keep shrinking the database until our full copy comes in with all the CAL's
|
# ? Jun 29, 2008 21:09 |
|
No Safe Word posted:I've been working with SSIS for about a year now and the two Wrox books I used to get started: Professional SQL Server 2005 Integration Services and the Expert counterpart were both pretty good. Awesome. I've spent a few hours playing with SSIS and the Professional Wrox book came in from amazon on Friday. I'm starting to understand how it all fits together. The main thing I have left to do now is to figure out how to get the errors from my Data Flow task (recordset destination, datareader destination, variable) to an Execute SQL task. I need to be able to call a stored procedure for each erroneous row (perhaps also call one stored proc for the entire batch of rows). I imagine in the Data Flow I just set output destinations and then I go to the control flow and link my Data Flow task to multiple Execute SQL tasks? Zombywuf: As a coder I much prefer using plain old SQL/.NET to handle this kind of work, although I can see how there is a market for it in suitably large systems.
|
# ? Jul 1, 2008 20:37 |
|
chocojosh posted:
* - unless you use package variables which are scoped to the entire package, but obviously you can only do so much with that
|
# ? Jul 1, 2008 20:39 |
|
No Safe Word posted:Use the OLE DB Command. Getting stuff outside the Data Flow is (as far as I know) impossible*, the control flow has no "data" between its tasks. I may not be able to use an OLE DB command for what I need. This is what I have: Right now in my dataflow I'm joining a CSV file and a database table (to import daily information that we get from an external source). What I'm doing now is that if there are values that exist in one source and not the other (obtained from a FULL-OUTER JOIN and checking for NULL), I want to send an e-mail for each value (record/row) that is not in both lists (there's a few other rules, but that's the gist of it).
|
# ? Jul 1, 2008 20:52 |
|
Found it out
Twlight fucked around with this message at 22:52 on Jul 1, 2008 |
# ? Jul 1, 2008 21:29 |
|
chocojosh posted:I may not be able to use an OLE DB command for what I need. This is what I have:
|
# ? Jul 1, 2008 21:38 |
|
I have a list in TSQL ('1','2','3') and I want to select them as a table so I can compare them, so I'd like to say something like code:
code:
|
# ? Jul 1, 2008 21:40 |
|
Jethro posted:You might be able to use an OLE DB Command for that, but I think your best bet would be to store that information in a different table and then run a SQL task (or whatever) afterwards. What you'd want to do either way is use a multi-cast to create a second copy of the data, then have one copy go to the destination tables as normal, while the other copy goes into a conditional split, which you use to discard the "good" rows. The "bad" rows then either go through your OLE DB Command, or they go to another table for use outside of the Data Flow. If you don't want your "bad" rows to go into the destination tables, then you don't need the multi-cast, just have the conditional split send the good rows on to the destination instead of discarding them. First, I'm a moron for not realizing that the OLE DB Command is in the Data Flow and not in the Control Flow. It *is* exactly what I needed. In this instance I *don't* need the multicast -- currently some people are inputting the data manually every day and there is a CSV file already created. Thanks for your help! Did you find it a bit intimidating at first to learn how to use SSIS? I'm having some trouble because I have to think: *ok, I know how to do a join in SQL, but how do I do it here? --scan the list of tools-- Hmm.. this merge join seems interesting.. let's google it.. oh, that actually is the right thing.. ok.. let's figure out this dialog box now...*.
|
# ? Jul 1, 2008 21:57 |
|
MrHyde posted:I have a list in TSQL ('1','2','3') and I want to select them as a table so I can compare them, so I'd like to say something like Dirty/quick way is to use a temp table. I've seen stuff using recursive CTEs but I've never had to use recursive CTEs before. SET NOCOUNT ON CREATE TABLE #Temp (col1 int) DECLARE @Index int DECLARE @MaxValue int SET @Index = 1 SET @MaxValue = 10 WHILE @Index <= @MaxValue BEGIN INSERT INTO #Temp VALUES (@Index) SET @Index = @Index + 1 END select * from #Temp DROP TABLE #Temp
|
# ? Jul 1, 2008 22:10 |
|
chocojosh posted:Thanks for your help! Did you find it a bit intimidating at first to learn how to use SSIS? I'm having some trouble because I have to think: *ok, I know how to do a join in SQL, but how do I do it here? --scan the list of tools-- Hmm.. this merge join seems interesting.. let's google it.. oh, that actually is the right thing.. ok.. let's figure out this dialog box now...*.
|
# ? Jul 1, 2008 22:11 |
|
chocojosh posted:Dirty/quick way is to use a temp table. I've seen stuff using recursive CTEs but I've never had to use recursive CTEs before. Thanks for the help. I wasn't really clear. I don't want a list of integers, I just want a list of comma separated values I have, a better example would be ('firstVal','2ndval','3rdval') I guess the actual problem is I have a list of values from an outside source and I have a table. I want to figure out which values are in the list, but not in the table.
|
# ? Jul 1, 2008 22:15 |
|
MrHyde posted:I have a list in TSQL ('1','2','3') and I want to select them as a table so I can compare them code:
|
# ? Jul 1, 2008 22:15 |
|
MrHyde posted:Thanks for the help. I wasn't really clear. I don't want a list of integers, I just want a list of comma separated values I have, a better example would be ('firstVal','2ndval','3rdval') If the "outside source" happens to be a file you may be able to use bulk insert to load the data into a (temporary) table. Then you have two tables, your temp containing the values from the list and your actual DB table. At this point you can do an outer join or a subquery with NOT EXISTS to compare the tables.
|
# ? Jul 1, 2008 22:21 |
|
|
# ? May 15, 2024 03:07 |
|
Got it, thanks guys. Ended up using something along the lines of code:
|
# ? Jul 1, 2008 22:27 |