|
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') A pattern I've used with Oracle in the past that should be portable is to use a dummy table that is known to have more rows than elements in your input string to parse the string with substr/instr, using your current row number as an offset. You will need to find a way to number the rows, possibly using row_number() or rank(), in the case you do not have a rownum pseudo-column. See the following (the input string 'a,b,c,d' would be passed in and duplicated in several spots in the query). code:
code:
|
# ? Jul 1, 2008 22:28 |
|
|
# ? May 31, 2024 07:35 |
|
(mysql 5) I have a list of names (application side). I need to populate a table with the ids that refer to rows in the names table. Except some of these names may not yet exist in the names table and should be created. What I'd like to do is: 1. Figure out what names are in my list (application side) that are NOT in the names table yet. 2. Add them to the names table. 3. Do the relatively simple query to add the ids corresponding the my list of names to the other table. I can't figure out how to do (1) without either creating a temporary table to hold my list of names that I have application side (how do you select data from a database that ISN'T in the database?), or by finding a list that IS in the database, and computing the inverse application side. So is there as way of doing (1) easily? Should I go with a temporary table? Or should I do it application side? Or is there some other method of accomplishing my goal that I'm not thinking of? (making the name column UNIQUE and just inserting everything does not count!)
|
# ? Jul 2, 2008 01:14 |
|
crazypenguin posted:(mysql 5) I have a list of names (application side). I need to populate a table with the ids that refer to rows in the names table. Except some of these names may not yet exist in the names table and should be created. If your names list is small you should pass it to your application. If it's large then a good solution might be to wrap the checking/creation SQL in a function which does nothing if the name exists, and otherwise it creates it. Then you would call this function N times, once for each name in your list.
|
# ? Jul 2, 2008 03:13 |
|
Is it possible to modify a User_type in Sybase?code:
|
# ? Jul 2, 2008 13:24 |
|
PostgreSQL 8.2 I'm using: code:
I have 34 characters instead of the 6 listed above so changing them all to [Aa][Bb], etc would make it much larger. It also will be harder to read and maintain should the prefixes change. I could also just do upper(a) and not worry about it, but this will be ran millions of time (although in a reporting environment, not a production environment), so I want to make sure it's as fast as possible. I know with regexs in postgres you can use ~* instead of *, or even use flags with regex replace to specify what you want, but I can't find a way to do it with a substring using a regex. Any suggestions? edit: forgot to mention it's 8.2 so I can't use regexp_matches that was added in 8.3 Aredna fucked around with this message at 19:28 on Jul 3, 2008 |
# ? Jul 3, 2008 19:24 |
|
Try prepending your regex with (?i).
|
# ? Jul 3, 2008 19:30 |
|
Victor posted:Try prepending your regex with (?i). beautiful... thank you!
|
# ? Jul 3, 2008 19:42 |
|
This week i started looking at some application made in Visual FoxPro 5.0 and for some reason everytime i want to change a date field in a registry on a .dbf file foxpro just shits in my mouth and laughs at me, so if i want to change the date to 01/01/2005 it automatically shortens it to 01/01/20 despite the fact that the date lenght is invariably 8. I tried to use SQL but to no avail? Can someone help me or tell me how to fix this poo poo.
|
# ? Jul 5, 2008 00:24 |
|
Super Mario Shoeshine posted:This week i started looking at some application made in Visual FoxPro 5.0 and for some reason everytime i want to change a date field in a registry on a .dbf file foxpro just shits in my mouth and laughs at me, so if i want to change the date to 01/01/2005 it automatically shortens it to 01/01/20 despite the fact that the date lenght is invariably 8. I tried to use SQL but to no avail? Can someone help me or tell me how to fix this poo poo. Foxpro's ADO DB interface (assuming that's what you're using) is very particular about what it accepts as dates. Unfortunately I can't remember what it likes, but I think it's something along the lines of '${YYYY-MM-DD}'. Googling "Foxpro date format" might help.
|
# ? Jul 5, 2008 15:01 |
|
Zombywuf posted:Foxpro's ADO DB interface (assuming that's what you're using) is very particular about what it accepts as dates. Unfortunately I can't remember what it likes, but I think it's something along the lines of '${YYYY-MM-DD}'. Googling "Foxpro date format" might help. More accuarately, if STRICTDATE is set to 1, the date format has to be {^YYYY-MM-DD}. If you set it to 0, it can be in the format {MM-DD-YYYY}, but an invalid date will still update, but with a blank date. And I don't know how the CENTURY option would affect any of this. This is from help for SET CENTURY: quote:In Visual FoxPro 5.0, issuing SET CENTURY TO without additional parameters sets the century to the current century –1 and rollover to zero if the two-digit year of the system date is less than 50. Century is set to the current century if the two-digit system date is greater than 50. For example, if the current year were 1998, nYear would be 48, the last two digits of 2048 (1998 + 50). It sounds like Foxpro 5.0 is retarded when it comes to 4 digit years. I've only used 6 and up so I don't really know.
|
# ? Jul 5, 2008 16:05 |
|
I have the free SQLEXPRESS 2005 version of MSSQL server loaded on an extra computer here at home that is also running windows server '03. I would really like to be able to make a connection with the SQL server via asp pages that I want to run locally. I have been working on this most of the afternoon and I still have no idea what I'm doing wrong. The SQL server is set up for windows authentication. I have a separate connection file that I am using that contains this code: <% prices_connection_string = "Provider=SQLOLEDB; Data Source = KILLBOT\SQLEXPRESS; Initial Catalog = prices; user id = [windows login]; password = [windows password]" %> I have tried a ton of different combinations for the user id and so far all I get is: Microsoft OLE DB Provider for SQL Server error '80004005' Login failed for user '[windows login]'. The user is not associated with a trusted SQL Server connection. /pricessite/index.asp, line 6 Using the SQL Surface Area Manager there is a link to add a new administrator but all that takes me to is a page where I can set privileges for the local machine user. If anyone knows of a way to set the user name and password for the sql server or knows what I am doing wrong with the windows authentication user id and password I would love to hear it. Thanks!
|
# ? Jul 6, 2008 01:51 |
|
Windows authentication with SQL Server is based on Kerberos or NTLM; in neither of these cases do you specify the username/password in the connection string. Instead, you put "Trusted_Connection=Yes" in the connection string; here are some details.
|
# ? Jul 6, 2008 02:39 |
|
That worked wonders. Thanks to you all
|
# ? Jul 6, 2008 04:05 |
|
I'm not sure if this necessarily goes in here, but I didn't want to start a new thread and this seemed like the most likely 'small questions' type thread to ask in. Basically, I want to start a mailing list. I've got lots of e-mail addresses of people who've been to gigs, and what gig they went to see. I am allowed to use these addresses for commercial purposes. I want to group them all, I'm guessing in some sort of database, so that I can easily select a subset of them relevant to an upcoming gig. For example, if De La Soul are coming to town, I want to be able to tell all the people who have been to a hip hop gig. I'm not really sure how to go about doing this. I have a little bit of experience with Microsoft Access, but I don't know how great that is for this sort of thing? Could anyone maybe point me in the right direction? Cheers peeps!
|
# ? Jul 7, 2008 00:30 |
|
Situation: I have a table where multiple fields each contain an id to a row in another table. Simplified example tables:code:
code:
code:
code:
E: VVV Bingo, that will do it. Thanks! Factor Mystic fucked around with this message at 01:13 on Jul 7, 2008 |
# ? Jul 7, 2008 00:54 |
|
Factor Mystic posted:Situation: I have a table where multiple fields each contain an id to a row in another table. (Discussed on synirc, posting for posterity: ) code:
Anyone?
|
# ? Jul 7, 2008 01:12 |
|
This is probably a really dumb question, but here goes: I need to re-index a table so that the primary key id's are sequential, is there some administrative command that does this, or would I have to do something like: for each record in table id = position of next record - 1 ? Thanks in advance.
|
# ? Jul 7, 2008 01:33 |
|
SHODAN posted:This is probably a really dumb question, but here goes: The easiest thing would be to drop the primary key field, then add in a new primary autonumber key field. However, this begs the question, why do you want to do this? You should never ever need to change a primary key value in a properly designed database.
|
# ? Jul 7, 2008 02:24 |
|
epswing posted:(Discussed on synirc, posting for posterity: ) Using 3 joins is, in my opinion, the appropriate solution to this problem (you will need to use outer joins since the offers table can have nulls). With indexes the look-up against the names table should be very efficient. Here's a single table join Tom Kyte has suggested for "attribute" tables (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:31263576751669) that will work in this situation. code:
|
# ? Jul 7, 2008 15:55 |
|
If Factor Mystic is using SQL 2005:code:
code:
|
# ? Jul 7, 2008 20:25 |
|
Will someone please help me figure out what's syntactically wrong about this MS SQL query?code:
|
# ? Jul 8, 2008 00:57 |
|
moww posted:Will someone please help me figure out what's syntactically wrong about this MS SQL query? Are you missing a comma after the 0? Everything else looks fine.
|
# ? Jul 8, 2008 01:18 |
|
Reaction Cat posted:Are you missing a comma after the 0? Everything else looks fine. The comma doesn't seem to be the problem. The error I get is code:
code:
|
# ? Jul 8, 2008 01:23 |
|
moww posted:The comma doesn't seem to be the problem. I'm pretty sure it is. Try putting in a comma after the 0 and re-running it. Or take out the 0 and leave it as just "SELECT pieces.display_image FROM pieces ...."
|
# ? Jul 8, 2008 02:20 |
|
moww posted:Will someone please help me figure out what's syntactically wrong about this MS SQL query? You're missing the JOIN criteria for users. I think it's optional in some dialects (ie, it just looks for identically named fields), but if nothing else I always like having it there. It may be required in T-SQL. edit: and yes also the comma missing
|
# ? Jul 8, 2008 02:23 |
|
No Safe Word posted:You're missing the JOIN criteria for users. I think it's optional in some dialects (ie, it just looks for identically named fields), but if nothing else I always like having it there. It may be required in T-SQL. Thanks for the help so far. I tried adding a comma but I still get the same syntax error. As for the JOIN criteria for users, is it not covered in code:
EDIT: Grimace, you are a true guru! I thought SQL was a lot more standardized before I started using MS SQL... moww fucked around with this message at 04:43 on Jul 8, 2008 |
# ? Jul 8, 2008 03:04 |
|
moww posted:Thanks for the help so far. I tried adding a comma but I still get the same syntax error. As for the JOIN criteria for users, is it not covered in You have to list the join statements together in TSQL, and they need to be in order. Try this: code:
|
# ? Jul 8, 2008 04:13 |
|
Is there a way to change a column from decimal 5 to decimal 5, 3 in one command or do I have to grow it to 8, 3, divide by 1000, and then shrink it?
|
# ? Jul 9, 2008 03:16 |
|
In SSIS what is the best way to connect two different data flow tasks. Basic googling said I cannot use a recordset destination/source to share data and that I should instead use a raw file. I'd prefer not to use a raw file as I don't see why I should have to write to disk when I just want to store a bit of data between data flow tasks. Edit: Another simple SSIS questions: I have a CSV file that I import. On each line of the CSV file I store the same value in the last field (auto-generated from a program). I want to store that value into a variable so that I can use it elsewhere that may not have access to the CSV file. How can I do that? Would I need to first open the file with an execute script task? chocojosh fucked around with this message at 15:52 on Jul 9, 2008 |
# ? Jul 9, 2008 15:21 |
|
chocojosh posted:In SSIS what is the best way to connect two different data flow tasks. Basic googling said I cannot use a recordset destination/source to share data and that I should instead use a raw file. I'd prefer not to use a raw file as I don't see why I should have to write to disk when I just want to store a bit of data between data flow tasks. The first question is "why does this have to be two DFTs?", though I'm sure you considered that. The crappy but pretty reliable way is to use global temp tables and setting your connection's RetainSameConnection property (under the Expressions) to True. Little muss, little fuss (except that you have to create the table in order to use it as an OLE DB src if you don't want to write a query to query it, and that you have to drop the table at the end of the job). The "standard" way is raw files, but that involves getting file space somewhere. Another way, kind of like the first one, is a permanent "staging" table, which is basically like the global temp table except it's a real table that you keep between runs. Obviously you can truncate it as needed to keep the space usage down. I'm a fan of this one where it's feasible.
|
# ? Jul 9, 2008 16:01 |
|
No Safe Word posted:The first question is "why does this have to be two DFTs?", though I'm sure you considered that. Why is the first way crappy if I may ask? I think I'd prefer to avoid creating a permanent table in our DB (over 500 tables for entire application) and I'd rather not create a raw file (may be irrational of me, I have my SSIS package divided into three tasks. Task 1: Import and filter errors (each error will have an e-mail sent so the appropriate person can fix it manually) Task 2: Insert records (two out of three cases) Task 3: Insert the final set of records (these records depend on data from task 2). I admit that I could combine task 1 and task 2 but my supervisor found my first task a little bit complex as we're both new to SSIS (~20 elements [mainly error handling]) and suggested that I separate it. While it may not be the SSIS way, I do prefer smaller functions/tasks that are modulated. If I'm doing something horribly wrong please let me know. BTW, another silly quick question -- is there a way to easily REMOVE columns from the flow? I have a bunch of derived columns each creating the same column (text of an e-mail) then I have a union all to combine all of these derived columns. After the derived column is created I *don't* want any of the other columns in the union all (as I've already created the error e-mail text). Many, many, thanks for your help in figuring out how to actually *do* things.
|
# ? Jul 9, 2008 16:18 |
|
chocojosh posted:Why is the first way crappy if I may ask? I think I'd prefer to avoid creating a permanent table in our DB (over 500 tables for entire application) and I'd rather not create a raw file (may be irrational of me, chocojosh posted:I have my SSIS package divided into three tasks. chocojosh posted:BTW, another silly quick question -- is there a way to easily REMOVE columns from the flow? I have a bunch of derived columns each creating the same column (text of an e-mail) then I have a union all to combine all of these derived columns. After the derived column is created I *don't* want any of the other columns in the union all (as I've already created the error e-mail text). 2) You can choose which columns you want to bring together in the Union. Right-click a row for a given data column and just choose "Delete" and it won't be passed through the Union. Even if you didn't already have one I sometimes just put in a one-input Union to do nothing but eliminate unnecessary rows so I don't get 6-10 warning messages in my logging db every time I process a row because not all the columns are used.
|
# ? Jul 9, 2008 19:05 |
|
No Safe Word posted:Because global temp tables are icky to me. Temp tables in general are icky, global ones even more so. Supervisor didn't like any of the three solutions that much for passing data between flows (flat out refused a global temp table and is hesitant for similar reasons as me to use temp raw file). We may just end up shoving it all in one task (the error handling is, as always, the biggest part of the code ironically). It's a huge help though to be able to tell him "here are your options, pick one".. even if we don't like any of them! Thanks for the union tip. I can't believe I missed that (or why they couldn't put a drat button called delete as they do elsewhere).
|
# ? Jul 9, 2008 19:21 |
|
Alright, I'm sure this is really simple, but how can I select the rows from table A that do not have associated rows in table B?
|
# ? Jul 10, 2008 17:35 |
|
Randomosity posted:Alright, I'm sure this is really simple, but how can I select the rows from table A that do not have associated rows in table B? Use a LEFT OUTER JOIN and only SELECT where B.join_column is NULL. e.g. code:
|
# ? Jul 10, 2008 17:43 |
|
My learning of SSIS goes from "Hmm.. this tool is pretty neat and I love the concept but they need to get the interface improved and a few bugs fixed" down to "why the hell can't I do something so basic?". I have a small stored procedure that I have a few custom RAISERROR calls or that an error can be thrown when attempting to insert (constraint being invalidated). I want to get the error message so that I can e-mail a detailed error message to the business analysts (not developers) who can use the UI to make a quick change on production. Attempt 1: Use an OLE DB Command and set the error to redirect row Result: The row gets redirected but I can't access the error description. I can access an integer error code that I don't need. I found this later on (after attempt 3) but it doesn't give detailed error messages. For a unique constraint failing it said something along the lines of "Integrity constraint failed". Attempt 2: Use an OLE DB Command and set the error to fail component Result: It would fail the component completely which we don't want (we want to attempt to insert as much data as possible and send error e-mails for those that fail. Also, if I add an onerror handler to send an e-mail, I would get nice e-mails about how a worker thread failed; a great thing to send off to a business analyst. Alright.. I walked away and took a break for 20 minutes and then thought of another trick.. let's use a stored procedure output parameter. Attempt 3: Use an OLE DB Command and use an error message output parameter with a conditional split Result: Couldn't read the output parameter. I've read online reports that it does and doesn't work. I gave up after an hour and a half of playing with my stored proc and SSIS. Attempt 4: Use google and hope like hell I can find something. I end up finding a blog post that describes a way to get the output parameter from a script task. Result: I had to do some fiddling with the connections but it *worked* (for some reason my Script Task requires an ADO.NET connection whereas my OLE Sources / Destinations need an OLE destination to the exact same database). Final solution: However, I don't like using output parameters for error conditions -- that's why we have exception handling. Consequently, I decided to use an exception. Modifying the script just slightly I catch the exception in the script task and then set it to the read/write variable of the script task instead of using the output parameter. In summary I've spent about 1.5 days just trying to answer the problem of "How can I send a nice error message to my business analyst/future developers so they know exactly what to fix". What a major pain in the rear end.
|
# ? Jul 10, 2008 21:12 |
|
Not much to add except, "yeah, that sounds about right". It's not my tool of choice, I just happen to be pretty well versed in it these days. Tomorrow's my last day with SSIS for a while though (new project at work!), huzzah.
|
# ? Jul 10, 2008 21:35 |
|
No Safe Word posted:Not much to add except, "yeah, that sounds about right". Well, thank you! I feel much better knowing that I'm not incompetent for spending a day and a half to figure out such a small thing (and luckily my boss is very laid back).
|
# ? Jul 10, 2008 21:58 |
|
Some hatred of SSIS, nicely packaged.
|
# ? Jul 11, 2008 00:14 |
|
|
# ? May 31, 2024 07:35 |
|
sorry for being a pest but i need some advice on how to separate a char field like "QUAKE\QUIT\JOHN" to 3 differents fields. (I am trying to separate the names field on a database by surnames and names.
|
# ? Jul 11, 2008 17:59 |