|
Could Customers be a view?
|
# ? Apr 12, 2010 20:55 |
|
|
# ? May 27, 2024 02:47 |
|
Hammerite posted:Could Customers be a view? Possibly... I'm pulling this SQL from Siebol Analytics, and it keeps referring to "Subject Areas". "Customers" is a Subject Area.
|
# ? Apr 12, 2010 21:11 |
|
I need to get my SQL chops back up, but of course it's pointless to just read a book without having a database to muck about with and try things out. Is there a preferred database sandbox that I could connect to or download to play around with? I'd prefer not to create something from the ground up if I don't have to.
|
# ? Apr 12, 2010 23:39 |
|
stubblyhead posted:I need to get my SQL chops back up, but of course it's pointless to just read a book without having a database to muck about with and try things out. Is there a preferred database sandbox that I could connect to or download to play around with? I'd prefer not to create something from the ground up if I don't have to. Eclipse has a pretty good sample database for BIRT. quote:The BIRT sample database provides a simple set of tables and data that form the basis for BIRT sample reports. The schema is for Classic Models, a retailer of scale models of classic cars. The database contains typical business data such as customers, orders, order line items, products and so on. Sebbe fucked around with this message at 23:57 on Apr 12, 2010 |
# ? Apr 12, 2010 23:50 |
|
Sebbe posted:Eclipse has a pretty good sample database for BIRT. This looks perfect, thanks.
|
# ? Apr 12, 2010 23:59 |
|
I've figured out how to do this in MySQL before, but I'm buggered if I can remember how. I've got a table that has product IDs, category IDs, and a "displayorder" field that sets the order that the products should be displayed in that category. code:
code:
Does anyone know the answer? I've done it before and meant to save the query as I knew I'd forget the chubbing thing, but never mind.
|
# ? Apr 13, 2010 09:38 |
|
GazChap posted:I've figured out how to do this in MySQL before, but I'm buggered if I can remember how. Something like this, perhaps? code:
|
# ? Apr 13, 2010 14:34 |
|
Alternatively, you could use a stored procedure:code:
|
# ? Apr 13, 2010 17:38 |
|
Squashy Nipples posted:I've just been given a query in Oracle SQL, and I'm having trouble parsing it.
|
# ? Apr 13, 2010 20:09 |
|
With SQLite, what is the most efficient way to either insert a row or increment its counter it if it already exits?
|
# ? Apr 13, 2010 22:51 |
|
Scaevolus posted:With SQLite, what is the most efficient way to either insert a row or increment its counter it if it already exits? You could potentially use ON CONFLICT REPLACE depending on the use-case.
|
# ? Apr 14, 2010 00:31 |
|
I'm not sure this is the best place to ask, but I'm trying to put together an extended stored procedure in C# and can't for the life of me figure out how to get input parameters... I'd assumed it was just a case of using:code:
Edit2: Nevermind! There's a SQLChars object that gets strings as char arrays. Flamadiddle fucked around with this message at 11:09 on Apr 14, 2010 |
# ? Apr 14, 2010 09:54 |
|
MoNsTeR posted:If this came from Siebel Analytics a.k.a. Oracle Business Intelligence Enterprise Edition, and Customers is a subject area, then the "query" is useless to you. The objects it references exist only within OBIEE and will not be visible to someone with only direct database access. You would need to look at the OBIEE repository within the administration tool to figure out what real database objects these identifiers represent, and how they relate to each other. Yeah, this is exactly what is going on, thank you for the confirmation! My boss gave me two different queries, each from a different "subject area", and asked me to combine them. However, it doesn't look like there is anyway to join the subject areas, so it can't be done. Hopefully, whoever set up the DB will allow me to set up an ODBC connection, so I can access the underlying data directly. I've already had my Fill of Analytics, I really don't want to learn another reporting engine.
|
# ? Apr 14, 2010 14:56 |
|
I need a little help analyzing this query. I've been told to optimize it (I am not a DBA), but the beauty of my job is that you can never tell if whoever wrote this (also not a DBA) is doing something extremely intelligent, or loving retarded. So here is the (simplified - change columns to *) query: code:
|
# ? Apr 14, 2010 16:55 |
|
So its grabbing the x newest rows and the sorting them by the oldest picked up which doesn't really make sense unless that's how he's displaying them to the user or something. So he's either being lazy on his application side or its for a view?
|
# ? Apr 14, 2010 18:25 |
|
Sprawl posted:So its grabbing the x newest rows and the sorting them by the oldest picked up which doesn't really make sense unless that's how he's displaying them to the user or something. Yeah, I have a feeling its for a view. Im guessing its because there is a lack of a time_modified column so he needs something to get the newest rows and it is unfortunately the row he needs to sort the opposite way. I don't personally see the reason for showing your latest reservations backwards but thats why I'm a dev I guess. Thanks though, I'll suggest that we let the front end sort it instead.
|
# ? Apr 14, 2010 18:42 |
|
I also saw some things about Fetch First that can slow down queries and things if your keys aren't setup right Did you run it though an explain and see if its using keys properly? Because i'm not really sure how DB2 uses keys in that way but check and see if it has a key just for these 2 fields. PICKUP_DATE , PROFILE_ID
|
# ? Apr 14, 2010 18:49 |
|
Sebbe posted:Something like this, perhaps?
|
# ? Apr 15, 2010 09:37 |
|
Whats the best software you guys have found for SQL development in OSX?
|
# ? Apr 20, 2010 02:19 |
|
I don't know how to get this query to stop "using filesort." Anybody have any ideas?code:
code:
quote:id select_type table type possible_keys key key_len ref rows Extra Please help me figure this out
|
# ? Apr 20, 2010 04:06 |
|
drcru posted:I don't know how to get this query to stop "using filesort." Anybody have any ideas? KEY `game_id_lastmove` (`last_move`,`game`,`id`) should do it i do belive
|
# ? Apr 20, 2010 06:55 |
|
I had to change my indexes but I figured it out, thanks.
|
# ? Apr 20, 2010 12:06 |
|
Is there an idiots guide to SQL recovey/repair? I am running into what I am sure are all sorts of very basic errors when rebuilding logs/recovering from bak/importing and exporting data Does anyone have any resources or suggestions?
|
# ? Apr 20, 2010 22:20 |
|
Kind of a higher-level design discussion: I'm currently dealing with an ASP.NET/MSSQL application where sproc overspecialization is starting to (I think) get out of control. For example, retrieving one very commonly-used type of object (which contains references to various other objects) results in a pretty oversized sproc being run, and via joins and multiple (4) table returns, that object and a good chunk of its dependencies are also returned. Problem of course is what fields are filled in in those other objects (and what references are filled in) are somewhat arbitrary, leading to a lot of trial-and-error to find out what is and isn't there and what needs to call additional sprocs to grab stuff from the DB. I'm trying to make a hard push to end this, but I need an alternative. The best I can think of now is trying to get code-side objects to be 1:1 with DB rows (there is no inheritance of objects that pull from DB data so that's a non-issue), make object sprocs only store or retrieve data from one table each, resolve references on demand, cache objects code-side, and periodically flush the cache to the DB. Is there a better overarching approach to this? OneEightHundred fucked around with this message at 19:26 on Apr 21, 2010 |
# ? Apr 21, 2010 17:27 |
|
edit nvm
|
# ? Apr 22, 2010 04:37 |
|
OneEightHundred, that sounds like an insane situation. However, it's pretty hard to give advice with the level of detail you've provided — I'm not sure I could say anything except for your own ideas on improving things. Is there any way you could come up with a mock schema that would reasonably mirror your actual schema? Moreover, how are the data returned actually being used in code? By the way, you're going to want to be able to turn caching off, and you will probably want to be able to force a cache flush of all sessions. You know about table-valued UDFs, right? Those may be useful in your situation. Also remember cross apply.
|
# ? Apr 22, 2010 21:48 |
|
I'm having a horrible time creating a cascade delete between tables. (using phpmyadmin) I have 2 tables, 'staff' and 'staff_shift'. 'staff' contains details of staff members and 'staff_shift' is a link table between 'staff' and 'shift' and references staff members against shifts worked all tables are innoDB. What i'm trying to do is create a cascade delete so that when a staff member is deleted, all records of shifts that have are deleted from the staff_shift table but im getting the following error; quote:#1452 - Cannot add or update a child row: a foreign key constraint fails (`database`.`#sql-d10_452`, CONSTRAINT `#sql-d10_452_ibfk_1` FOREIGN KEY (`staffid`) REFERENCES `staff_shift` (`staffid`) ON DELETE CASCADE) Im going into the staff table --> relation view and then selecting staff_shift.staffid from the staffid field. I don't suppose anyone knows whats going on? That literally the best I can describe it. Sorry.
|
# ? Apr 22, 2010 22:07 |
|
Give us the SQL that sets this all up. Three create tables, the necessary foreign keys, some inserts with dummy data, and then the offending delete. (Feel free to omit most of the columns in the table definitions.)
|
# ? Apr 22, 2010 22:14 |
|
Thirteenth Step posted:#1452 - Cannot add or update a child row: a foreign key constraint fails (`database`.`#sql-d10_452`, CONSTRAINT `#sql-d10_452_ibfk_1` FOREIGN KEY (`staffid`) REFERENCES `staff_shift` (`staffid`) ON DELETE CASCADE) I think you have declared your foreign key "the wrong way round", i.e. in the wrong table. Your CREATE TABLE statement for staff_shift should look something like this code:
i.e. the reason you are getting this error when trying to delete a staff member is MySQL sees that there are entries in staff_shift corresponding to that staff member and concludes that the row should not be deleted
|
# ? Apr 22, 2010 22:28 |
|
Victor posted:Give us the SQL that sets this all up. Three create tables, the necessary foreign keys, some inserts with dummy data, and then the offending delete. (Feel free to omit most of the columns in the table definitions.) Ok here goes; (edited out SQL dump, fixed in next post) Thats my dump of my SQL export, seems like there's a bit missing... that's all I can find. Sorry about not having a clue, im fairly new to this, havent touched phpmyadmin in a good 2 years. Thirteenth Step fucked around with this message at 22:51 on Apr 22, 2010 |
# ? Apr 22, 2010 22:30 |
|
|
# ? Apr 22, 2010 22:41 |
|
Victor posted:
Sorry code:
code:
Thirteenth Step fucked around with this message at 23:22 on Apr 22, 2010 |
# ? Apr 22, 2010 22:56 |
|
Nope, those KEY statements are apparently indexes, according to MySQL's CREATE TABLE documentation. Think about it: your KEY statements aren't referencing the primary tables!
|
# ? Apr 22, 2010 23:01 |
|
Hammerite posted:I think you have declared your foreign key "the wrong way round", i.e. in the wrong table. Your CREATE TABLE statement for staff_shift should look something like this I did just this thanks a lot
|
# ? Apr 22, 2010 23:22 |
|
I've just started learning SQL and PHP and I was wondering how to pull info from my tables based on some specific criteria. Like, heres my table:code:
So far when I try searching for multiple things like that it get empty sets even tho I know I have them. I was thinking of something like: SELECT * FROM wp_postmeta WHERE (meta_key = 'Price' AND meta_key BETWEEN 1 AND 900) AND (meta_key = 'Bedrooms' AND meta_key = '1'); but that gives me an empty set.
|
# ? Apr 22, 2010 23:40 |
|
rugbert posted:I was thinking of something like: Is one of those ANDs supposed to be an OR?
|
# ? Apr 22, 2010 23:45 |
|
I'm new to managing a SQL infrastructure and am trying to automate some tasks. I'm attempting to restore (overwrite) a database and keep getting an error. code:
Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'MOVE'. I've done some googling and can't seem to pin down why I'm retarded. Any help would be awesome.
|
# ? Apr 22, 2010 23:59 |
|
You need commas at the end of your MOVE lines.
|
# ? Apr 23, 2010 00:01 |
|
Hammerite posted:Is one of those ANDs supposed to be an OR? Um, yea changed to OR. Ok so now it gave me a code:
Im trying to pass 60 and 73 into a variable so that way I can get all data where post_id=60 or 73
|
# ? Apr 23, 2010 00:05 |
|
|
# ? May 27, 2024 02:47 |
|
rugbert posted:So in that table, a couple entries have the same IDs, 60 and 73 so those are all matches for my search. How can I get those IDs out? So are you saying that you're only interested in the values in post_id and you want to get distinct values back? Change SELECT * to SELECT DISTINCT post_id. From your second paragraph it sounds like you want to identify post_ids found in rows satisfying particular criteria, then get back all of the rows in which those post_ids appear irrespective of whether they caused the post_id to be included. One way you could do this is by issuing code:
|
# ? Apr 23, 2010 00:24 |