|
Could Customers be a view?
|
![]() |
|
![]()
|
# ? Jun 24, 2024 16:38 |
|
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.
|
![]() |
|
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.
|
![]() |
|
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 |
![]() |
|
Sebbe posted:Eclipse has a pretty good sample database for BIRT. This looks perfect, thanks.
|
![]() |
|
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.
|
![]() |
|
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:
|
![]() |
|
Alternatively, you could use a stored procedure:code:
|
![]() |
|
Squashy Nipples posted:I've just been given a query in Oracle SQL, and I'm having trouble parsing it.
|
![]() |
|
With SQLite, what is the most efficient way to either insert a row or increment its counter it if it already exits?
|
![]() |
|
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.
|
![]() |
|
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 |
![]() |
|
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.
|
![]() |
|
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:
|
![]() |
|
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?
|
![]() |
|
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.
|
![]() |
|
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
|
![]() |
|
Sebbe posted:Something like this, perhaps? ![]()
|
![]() |
|
Whats the best software you guys have found for SQL development in OSX?
|
![]() |
|
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 ![]()
|
![]() |
|
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
|
![]() |
|
I had to change my indexes but I figured it out, thanks.
|
![]() |
|
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?
|
![]() |
|
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 |
![]() |
|
edit nvm
|
![]() |
|
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.
|
![]() |
|
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.
|
![]() |
|
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.)
|
![]() |
|
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
|
![]() |
|
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 |
![]() |
|
|
![]() |
|
Victor posted:
Sorry ![]() code:
code:
Thirteenth Step fucked around with this message at 23:22 on Apr 22, 2010 |
![]() |
|
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!
|
![]() |
|
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 ![]() ![]()
|
![]() |
|
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.
|
![]() |
|
rugbert posted:I was thinking of something like: Is one of those ANDs supposed to be an OR?
|
![]() |
|
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.
|
![]() |
|
You need commas at the end of your MOVE lines.
|
![]() |
|
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
|
![]() |
|
![]()
|
# ? Jun 24, 2024 16:38 |
|
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:
|
![]() |