SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

mindphlux posted:

The checkbox's data source is the Y/N field in the Owner's table, so I think yes? The datatype for the Y/N is just access's dedicated Yes/No datatype. this sucks.

So all the other fields write out fine?

What version of access are you using?

Are you sure its not being written into the table?

# ? Sep 15, 2010 19:05

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 10:40

c355n4: Jan 3, 2007

This should be a simple question I think. I'm just trying to create a simple view in MSSQL 2008.

code:

SELECT dbo.talumnicategories.alumniemail,
       dbo.talumnicategories.alumnicategoryid,
       dbo.ttypescategory.category,
       dbo.ttypescategory.categoryid,
       dbo.talumnicategories.checked
FROM   dbo.talumnicategories
       RIGHT OUTER JOIN dbo.ttypescategory
         ON dbo.talumnicategories.categoryid = dbo.ttypescategory.categoryid
WHERE  ( dbo.ttypescategory.visible = 1 )

I have two tables that I'm doing an outer join on. I'm trying to figure out how to get the Checked field to be "false" when its NULL. I should be able to do it with a CASE statement I think. Just can't figure out how.

**** Figured it out with some help

code:

SELECT dbo.talumnicategories.alumniemail,
       dbo.talumnicategories.alumnicategoryid,
       dbo.ttypescategory.category,
       dbo.ttypescategory.categoryid,
       dbo.talumnicategories.checked,
       CASE
         WHEN dbo.talumnicategories.checked IS NULL THEN 'false'
         ELSE dbo.talumnicategories.checked
       END AS vchecked
FROM   dbo.talumnicategories
       RIGHT OUTER JOIN dbo.ttypescategory
         ON dbo.talumnicategories.categoryid = dbo.ttypescategory.categoryid
WHERE  ( dbo.ttypescategory.visible = 1 )

c355n4 fucked around with this message at 20:10 on Sep 15, 2010

# ? Sep 15, 2010 19:58

mindphlux: Jan 8, 2004; by R. Guyovich

Sprawl posted:

So all the other fields write out fine?

What version of access are you using?

Are you sure its not being written into the table?

access 2010 - yeah, I'm sure it's not being written in the table. Basically the checkbox on my form has a weird thing where it's filled in with a block - not empty or checked, which I assume means it has no data. If I click the box, it just makes a dinging noise like it can't be altered.

So, I'm not insane? this should be working as is? I thought it would just work if I defined the relationship and then clicked a checkbox, seems like it would write it in the new table.

edit : I just tested it where I added a record in the new table with an existing ownerID, and then on my form the checkbox works like it should. so what, do I need code to check and make sure the record exists in my new table, and create a new record if it doesn't when I bring it up on my form?

edit2 : argh, I did a 'before update' on the Y/N checkbox, with iff([Owners]![OwnerID]=Null,[Owners]![OwnerID]=[Stores]![OwnerID],) but this breaks my entire form and more importantly doesn't initialize an OwnerID entry in my owners table

mindphlux fucked around with this message at 22:07 on Sep 15, 2010

# ? Sep 15, 2010 21:44

Aredna: Mar 17, 2007; Nap Ghost

c355n4 posted:

code:

       CASE
         WHEN dbo.talumnicategories.checked IS NULL THEN 'false'
         ELSE dbo.talumnicategories.checked
       END AS vchecked

You can also use either COALESCE or ISNULL as such:

COALESCE(dbo.talumnicategories.checked,'false')
ISNULL(dbo.talumnicategories.checked,'false')

ISNULL is typically slightly faster, but for many scenarios they behave differently and you may want to use the other. A lot of the differences are discussed here: http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

# ? Sep 16, 2010 00:08

c355n4: Jan 3, 2007

Aredna posted:

You can also use either COALESCE or ISNULL as such:

COALESCE(dbo.talumnicategories.checked,'false')
ISNULL(dbo.talumnicategories.checked,'false')

ISNULL is typically slightly faster, but for many scenarios they behave differently and you may want to use the other. A lot of the differences are discussed here: http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

Ah, thanks. Thats much cleaner.

# ? Sep 16, 2010 00:59

Cowcatcher: Dec 23, 2005; OUR PEOPLE WERE BORN OF THE SKY

Can I cast a value while unpivoting?

I have a table with several columns with different types. I'm unpivoting the columns into Field, Value rows, and obviously they need to be casted into the same type. Is this possible?

code:

DECLARE @email varchar(100)
SET @email=N'wgycrqkgoggncjcxvoyn@hsdnbhdqbm.com'
SELECT UserGUID, Field, Value   
    FROM    
       (SELECT UserGuid, FirstName,LastName,Region,Country,[Language],OptVersion
                FROM [CoreData]
                WHERE Email = @email) p   
        UNPIVOT   
       (Value FOR Field IN (FirstName,LastName,Region,Country, Language],OptVersion))   
    AS unpvt1;

# ? Sep 16, 2010 21:47

Cowcatcher: Dec 23, 2005; OUR PEOPLE WERE BORN OF THE SKY

Nevermind, answered my own question

SELECT UserGUID, CAST(Field as nvarchar(100) as Field, ...

# ? Sep 16, 2010 22:15

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

What are the limitations of COALESCE? I have this query:

code:

SELECT SUM(amount) FROM payments WHERE payment_id = $i GROUP BY payment_id

This works, when there's a payment_id with that number. Otherwise, it delivers a null, because there's nothing to sum. I tried to change it to SELECT COALESCE(SUM(amount), 0) to force it to give some result, but no dice. Even when I change the 0 to a 1, it still gives no rows, leading me to think this is either some limitation of COALESCE, or I'm misunderstanding what's going on here.

Even weirder(?), when I change it to COALESCE("a", SUM(amount)), Navicat tells me it's returning a blob, but when I save it to disk, the blob is an empty file.

Edit: I found a workaround, I suppose: Wrapping the whole thing in COALESCE.

code:

SELECT COALESCE((SELECT SUM(amount) FROM payments WHERE payment_id = $i GROUP BY payment_id), 0)

It works, but I'm still wondering why the first one doesn't work. When I get rid of the GROUP BY, it works.

Of course, now I wonder if it's faster to do that (with the subquery), or just run a mysql_num_rows and return '0' if it's 0. And that reeks of premature optimization. But alas, I wonder.

Golbez fucked around with this message at 22:32 on Sep 20, 2010

# ? Sep 20, 2010 22:10

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

It doesn't work because you're trying to GROUP an empty result set. It has nothing to do with COALESCE

# ? Sep 20, 2010 22:36

Atimo: Feb 21, 2007; Lurking since '03; Fun Shoe

I need to generate gobs of test data for an application's sql server. The database is rather legacy and I can't really change the schema very much.

This means I have almost no constraints and very few tables even have identity columns, or even proper unique constraints.

I've look at the red gate data generator but it doesn't seem to be flexible enough for my needs.

I've spent a few days working on my own custom tool to do the job where I can manage table relationships in a bit more custom fashion.

I finally have the thing mostly working, but I was wondering if anyone knew of a professional tool that might do the job instead of continuing work on my tool.

Any thoughts?

# ? Sep 24, 2010 02:58

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Atimo posted:

I need to generate gobs of test data for an application's sql server. The database is rather legacy and I can't really change the schema very much.

This means I have almost no constraints and very few tables even have identity columns, or even proper unique constraints.

I've look at the red gate data generator but it doesn't seem to be flexible enough for my needs.

I've spent a few days working on my own custom tool to do the job where I can manage table relationships in a bit more custom fashion.

I finally have the thing mostly working, but I was wondering if anyone knew of a professional tool that might do the job instead of continuing work on my tool.

Any thoughts?

I use EMS Sql Manager to do a lot of poo poo, and they do have a commercial tool available that will generate test data for sql server, mysql, and postgres. Never used it though

http://www.sqlmanager.net/en/products/mssql/datagenerator

I am not familiar with red gate, so I dunno how this compares. But I am sure there has got to be other companies with similar products.

# ? Sep 24, 2010 14:41

Beseiged: Aug 27, 2007; Random Text Under A Picture

I have a table in which I want to dynamically update a column for a particular account. Mostly this table will be used to store the state of windows or panes when the user logged in or out. I should also state this is SQL Server 2008.

code:

exec server.account_settings_update 12, 'north_open', 42

code: http://adampaulberg.com/code.php

When I execute the above command in SQL Management Studio, no errors are returned.
When I pass and incorrect or non-existent account id, the correct error message is logged.
When the column name is non-existent or is AID then the correct error message is logged.

Funky part:
When all the information is right, it does make it down to "EXECUTE sp_executesql...". No errors are return. When I check the data it never updated the table.

All that is output is the message printed to the screen:

code:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Beseiged fucked around with this message at 16:35 on Sep 24, 2010

# ? Sep 24, 2010 16:33

Atimo: Feb 21, 2007; Lurking since '03; Fun Shoe

Begby posted:

I use EMS Sql Manager to do a lot of poo poo, and they do have a commercial tool available that will generate test data for sql server, mysql, and postgres. Never used it though

http://www.sqlmanager.net/en/products/mssql/datagenerator

I am not familiar with red gate, so I dunno how this compares. But I am sure there has got to be other companies with similar products.

Thanks, I'll look into this product further. At first glance though it does appear to be mapping table relationships with contraints. If I can't set it manually it won't be of much help.

# ? Sep 25, 2010 00:40

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

First off I should say I feel dumb for this one because it seems like it should be obvious and I am rusty at SQL.

I am designing a query to do a current inventory for a rental company. In the rental section I have it designed to have a delivery and pick up date. I might be going about it wrong, but here's the failing design.

WHERE not (((([CURRENT_TIMESTAMP])<Rentals.[Date of Pickup] And Not ([CURRENT_TIMESTAMP])<Rentals.[Date of delivery])=False) And ((Equipment.sale)=False));

So it takes all the equipment rom the rental form, and excludes all the products that do not have a pick up/delivery date, and not have been sold. It still shows a few products though that are currently delivered. Is there a better way to go about it, or am I just missing something. I can't stop going in circles over it.

EDIT: DECIDED TO DO IT AS A CASE but I get a format error on it. Here's what I got.

SELECT Equipment.[Serial Number], Equipment.Product, Equipment.Brand, Equipment.[Product Type], Equipment.[Purchase Date],Rentals.[Date of pickup],
CASE = 'status'
WHEN [CURRENT_TIMESTAMP]<=[Rentals].[Date of Pickup] then 'Not Available'
Else 'Available'
end
FROM Equipment INNER JOIN (Clients INNER JOIN Rentals ON Clients.[Client ID]=Rentals.[Client ID]) ON Equipment.[Serial Number]=Rentals.[Serial Number];

This should work right?

Veskit fucked around with this message at 20:33 on Sep 25, 2010

# ? Sep 25, 2010 10:09

Kekekela: Oct 28, 2004

Veskit posted:

EDIT: DECIDED TO DO IT AS A CASE but I get a format error on it. Here's what I got.

SELECT Equipment.[Serial Number], Equipment.Product, Equipment.Brand, Equipment.[Product Type], Equipment.[Purchase Date],Rentals.[Date of pickup],
CASE = 'status'
WHEN [CURRENT_TIMESTAMP]<=[Rentals].[Date of Pickup] then 'Not Available'
Else 'Available'
end
FROM Equipment INNER JOIN (Clients INNER JOIN Rentals ON Clients.[Client ID]=Rentals.[Client ID]) ON Equipment.[Serial Number]=Rentals.[Serial Number];

This should work right?

Looks like your case statement is off. Not in front of a sql ide so my syntax may be off as well but it should be something more like:

'STATUS' = CASE
WHERE CURRENT_TIMESTAMP <= [Rentals].[Date of Pickup] then 'Not Available'
Else 'Available' END

# ? Sep 25, 2010 22:52

slartibartfast: Nov 13, 2002

Veskit posted:

This should work right?

Looks right, but you might need to alias the CASE statement, i.e. "END AS columnAlias".

# ? Sep 26, 2010 20:57

Kreeblah: May 17, 2004; INSERT QUACK TO CONTINUE; Taco Defender

So, total newbie here, but is there a good resource for learning database design? I'm just starting a project I've been thinking about doing for a while now (if you're familiar with PARANOIA, it'd be a web-based game-running tool), but I want to make sure I don't completely gently caress myself down the line. Right now, I've just got a simple user creation and login system set up to make sure I can figure out the whole PHP/SQL thing, so I'd really like to spend some time doing design now to hopefully avoid having severe performance issues if I start getting any kind of regular traffic or problems adding features I may not initially start with.

# ? Sep 28, 2010 03:23

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Kekekela posted:

Looks like your case statement is off. Not in front of a sql ide so my syntax may be off as well but it should be something more like:

'STATUS' = CASE
WHERE CURRENT_TIMESTAMP <= [Rentals].[Date of Pickup] then 'Not Available'
Else 'Available' END

I am still getting horrendous amounts of Syntax errors trying it this way. Did I write this incorrectly or is Access just a giant pain in the rear end?

# ? Sep 28, 2010 03:25

Kekekela: Oct 28, 2004

Veskit posted:

I am still getting horrendous amounts of Syntax errors trying it this way. Did I write this incorrectly or is Access just a giant pain in the rear end?

This might be it:

CASE WHERE CURRENT_TIMESTAMP <= [Rentals].[Date of Pickup] then 'Not Available'
Else 'Available' END AS Status

Its been a while since I've worked with Access though, didn't realize that's what you were using.

Kekekela fucked around with this message at 14:57 on Sep 28, 2010

# ? Sep 28, 2010 05:46

baquerd: Jul 2, 2007; by FactsAreUseless

Kreeblah posted:

So, total newbie here, but is there a good resource for learning database design? I'm just starting a project I've been thinking about doing for a while now (if you're familiar with PARANOIA, it'd be a web-based game-running tool), but I want to make sure I don't completely gently caress myself down the line. Right now, I've just got a simple user creation and login system set up to make sure I can figure out the whole PHP/SQL thing, so I'd really like to spend some time doing design now to hopefully avoid having severe performance issues if I start getting any kind of regular traffic or problems adding features I may not initially start with.

To be brutally honest, you are so far from having a usable game that people will want to use (literally years if you know nothing about database design or programming right now) that performance considerations aren't important if finishing the project is the goal, especially not database ones.

That said, if you're interested in learning and applying the concepts involved, read up on primary and foreign keys, database normalization, indexes, and entity-relationship diagrams to start. Once you grasp the theory basics, take a look here: http://dev.mysql.com/doc/refman/5.0/en/explain.html for a diagnostic tool to help you out.

Normally, to get a good handle on the basics of database optimization I would expect 3 or more graduate level CS courses after an undergrad sequence of 1-3 general courses.

# ? Sep 28, 2010 07:22

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

baquerd posted:

Normally, to get a good handle on the basics of database optimization I would expect 3 or more graduate level CS courses after an undergrad sequence of 1-3 general courses.

You're joking aren't you? Granted it takes some learning but it's nowhere near as much of a black art as you make it sound.

baquerd posted:

To be brutally honest, you are so far from having a usable game that people will want to use (literally years if you know nothing about database design or programming right now) that performance considerations aren't important if finishing the project is the goal, especially not database ones.

Aside from the "literally years" bit (which I doubt - months or a year maybe, depending on how much time he intends to dedicate to it), I think he's just aiming to create some automated player aids or something. The game he's talking about is a role-playing game, so I don't imagine he intends his project to actually run a game. My impression is that RPGs have far too much in the way of subjective elements for that to be possible.

Kreeblah -

I don't really know any essential resources to recommend, but I think the best way to pick up what is and what isn't a good database design is to look at lots of examples, along with the reasons knowledgable people have given as to why those examples show good or bad design. Don't get me wrong, do look up what the various levels of normalisation are, and apply those ideas to examples you come across, but it's easiest to learn by getting a "feel" for what is right and what is wrong.

I wouldn't extend this advice to other aspects of creating a web app. There are some core things you definitely need to read up on, but good database design is really very easy once you "get it". Just make sure you do indeed "get it", because it is true that a badly-designed database is a headache to deal with.

# ? Sep 28, 2010 09:40

uXs: May 3, 2005; Mark it zero!

I have a question about transactions:

Say I want to insert a record in a table, read it out again, and then update that same record. Only after the update is the new record really complete, so I don't want any other query to see my new record until after it has been updated.

I think that I need to wrap those three operations in a READ COMMITTED transaction. Is that correct?

# ? Sep 28, 2010 09:59

baquerd: Jul 2, 2007; by FactsAreUseless

Hammerite posted:

You're joking aren't you? Granted it takes some learning but it's nowhere near as much of a black art as you make it sound.

I'm not joking. While most day to day cases are simple, really complex queries can take a very difficult and fundamental understanding of the database to fully optimize. People make careers off of doing this well, it's not something you just pick up in school and you're good to go.

quote:

Aside from the "literally years" bit (which I doubt - months or a year maybe, depending on how much time he intends to dedicate to it), I think he's just aiming to create some automated player aids or something. The game he's talking about is a role-playing game, so I don't imagine he intends his project to actually run a game. My impression is that RPGs have far too much in the way of subjective elements for that to be possible.

While I understood his scope to be more in line of a full implementation of quantifiable mechanics, when I've taught computer science, students who've never been exposed to the materials sometimes take months of actual classes with instruction and direct examples to even learn boolean logic fully, let alone understand OOS design techniques and understand the bit of the API that can interact with a database meaningfully. Those of us (yes I'm assuming) who have been doing this forever often have a really warped perspective of what beginners should be capable of, and we likely took to it very easily ourselves as well.

I see CS majors walk out of undergrad who would be unable to write a program to connect to a database and output the results to the screen without Google and an hour.

# ? Sep 28, 2010 10:02

Sub Par: Jul 18, 2001; Dinosaur Gum

Veskit posted:

SELECT Equipment.[Serial Number], Equipment.Product, Equipment.Brand, Equipment.[Product Type], Equipment.[Purchase Date],Rentals.[Date of pickup],
CASE = 'status'
WHEN [CURRENT_TIMESTAMP]<=[Rentals].[Date of Pickup] then 'Not Available'
Else 'Available'
end
FROM Equipment INNER JOIN (Clients INNER JOIN Rentals ON Clients.[Client ID]=Rentals.[Client ID]) ON Equipment.[Serial Number]=Rentals.[Serial Number];

This should work right?

You cannot use CASE statements in Access SQL. You will need to use IIF instead:

code:

SELECT Equipment.[Serial Number], 
Equipment.Product, 
Equipment.Brand, 
Equipment.[Product Type], 
Equipment.[Purchase Date],
Rentals.[Date of pickup],
iif([CURRENT_TIMESTAMP]<=[Rentals].[Date of Pickup], 'Not Available', 'Available') as status
FROM Equipment INNER JOIN (Clients INNER JOIN Rentals ON Clients.[Client ID]=Rentals.[Client ID]) ON Equipment.[Serial Number]=Rentals.[Serial Number];

Is CURRENT_TIMESTAMP a form field or something? Unless it is (or a field in one of your tables for some reason) Access is going to prompt you for a value there.

# ? Sep 28, 2010 14:55

Aredna: Mar 17, 2007; Nap Ghost

baquerd posted:

I'm not joking. While most day to day cases are simple, really complex queries can take a very difficult and fundamental understanding of the database to fully optimize. People make careers off of doing this well, it's not something you just pick up in school and you're good to go.

Writing basic SQL is something that he won't need that many courses to pick up. I know because I've taught coworkers with no computer science or programming background how to pull the data they need. Most of them have only limited Excel and Access experience. The ones with no Access take quite a bit longer to teach, but they still get it.

I'll give you that their queries aren't perfectly optimized and sometimes even simple changes can speed them up by an order of magnitude, but they are fast enough and getting the correct results.

baquerd posted:

While I understood his scope to be more in line of a full implementation of quantifiable mechanics, when I've taught computer science, students who've never been exposed to the materials sometimes take months of actual classes with instruction and direct examples to even learn boolean logic fully, let alone understand OOS design techniques and understand the bit of the API that can interact with a database meaningfully. Those of us (yes I'm assuming) who have been doing this forever often have a really warped perspective of what beginners should be capable of, and we likely took to it very easily ourselves as well.

I see CS majors walk out of undergrad who would be unable to write a program to connect to a database and output the results to the screen without Google and an hour.

From what I saw coming out of my computer science classes, being able to do what you requested with Google and an hour would easily put someone in the top 10%. I can't say I've seen anything better in the real world.

# ? Sep 28, 2010 15:16

guch: Mar 10, 2003; fuck quake. lets do art.

I'm trying to create a table in Oracle Database Express Edition with a foreign key that references another table, but I keep getting the "ORA-00907: missing right parenthesis" error. Does anyone know what I'm doing wrong here?

create table order_item(
order_id varchar2(50) primary key not null,
order_item_seq_id varchar2(50) not null,
qty integer not null,
unit_price varchar2(50) not null,
estimated_delivery_date date not null,
product_id varchar2(50) foreign key references customer(cust_id)
)

Thanks for any help!

# ? Sep 28, 2010 16:27

Kreeblah: May 17, 2004; INSERT QUACK TO CONTINUE; Taco Defender

baquerd posted:

To be brutally honest, you are so far from having a usable game that people will want to use (literally years if you know nothing about database design or programming right now) that performance considerations aren't important if finishing the project is the goal, especially not database ones.

That said, if you're interested in learning and applying the concepts involved, read up on primary and foreign keys, database normalization, indexes, and entity-relationship diagrams to start. Once you grasp the theory basics, take a look here: http://dev.mysql.com/doc/refman/5.0/en/explain.html for a diagnostic tool to help you out.

Normally, to get a good handle on the basics of database optimization I would expect 3 or more graduate level CS courses after an undergrad sequence of 1-3 general courses.

Well, Hammerite is right about it being more of a tool than an actual game, so I'm expecting that will help simplify things quite a bit. For background (and I probably should have mentioned this), I have about 80% of a computer engineering degree, but zero database experience. I'm actually hoping to eventually be able to use this as an example of what I can do to help try to land a job with a schedule that'll let me finish my degree, as I haven't had much luck looking so far and not having any database experience seems to be one of the reasons why.

Even though this is going to be a first project in this field for me, I do have at least some familiarity with OO design, data structures, design processes, etc. from my classes, which should make things easier to get the logic side of things going once I get a handle on this. I just never had any courses on databases and I've never had to use them for anything myself, so I need to find an excuse to get my hands dirty and this is at least something I've wanted to do for a while for my own use. I do want to come away with good habits, though, which is why I was hoping to find some resources to help develop them.

I did check up on primary/foreign keys and database normalization before posting and they were easy enough to grasp (at least up to 3NF; I'm going to need to do some more reading before I feel comfortable saying I understand what the differences are among the higher normal forms), but I wasn't sure what else I was missing so I'll look into indexes and entity-relationship diagrams and then check out that link.

Hammerite posted:

I don't really know any essential resources to recommend, but I think the best way to pick up what is and what isn't a good database design is to look at lots of examples, along with the reasons knowledgable people have given as to why those examples show good or bad design. Don't get me wrong, do look up what the various levels of normalisation are, and apply those ideas to examples you come across, but it's easiest to learn by getting a "feel" for what is right and what is wrong.

I wouldn't extend this advice to other aspects of creating a web app. There are some core things you definitely need to read up on, but good database design is really very easy once you "get it". Just make sure you do indeed "get it", because it is true that a badly-designed database is a headache to deal with.

Alright. I'll see what I can find for examples, then. And, yeah, I do want to have something usable come out of this, especially since the existing tools for it aren't very good and it's something I'd actually use myself, but if it ends up just being an excuse for gaining some experience with SQL/database design/web app design, I'd be OK with that. I can always finish it later if it comes down to it.

Anyway, thanks to the both of you for your help. I really wasn't sure where to get started, so I do appreciate the advice.

# ? Sep 28, 2010 18:15

MoNsTeR: Jun 29, 2002

guch posted:

I'm trying to create a table in Oracle Database Express Edition with a foreign key that references another table, but I keep getting the "ORA-00907: missing right parenthesis" error. Does anyone know what I'm doing wrong here?

create table order_item(
order_id varchar2(50) primary key not null,
order_item_seq_id varchar2(50) not null,
qty integer not null,
unit_price varchar2(50) not null,
estimated_delivery_date date not null,
product_id varchar2(50)
)

Thanks for any help!

The constraint needs a name:

create table order_item(
order_id varchar2(50) primary key not null,
order_item_seq_id varchar2(50) not null,
qty integer not null,
unit_price varchar2(50) not null,
estimated_delivery_date date not null,
product_id varchar2(50) constraint order_item_customer_fk foreign key references customer(cust_id)
)

or

create table order_item(
order_id varchar2(50) primary key not null,
order_item_seq_id varchar2(50) not null,
qty integer not null,
unit_price varchar2(50) not null,
estimated_delivery_date date not null,
product_id varchar2(50)
);

alter table order_item
add constraint order_item_customer_fk
foreign key (product_id)
references customer(cust_id);

I'll assume the fact that product_id keys to cust_id is due to a hastily constructed example and not completely insane design

Also, I keep this page bookmarked for questions like this http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/toc.htm (probably requires OTN account, which is free)

MoNsTeR fucked around with this message at 20:13 on Sep 28, 2010

# ? Sep 28, 2010 20:07

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Sub Par posted:

You cannot use CASE statements in Access SQL. You will need to use IIF instead:
code:
SELECT Equipment.[Serial Number], 
Equipment.Product, 
Equipment.Brand, 
Equipment.[Product Type], 
Equipment.[Purchase Date],
Rentals.[Date of pickup],
iif([CURRENT_TIMESTAMP]<=[Rentals].[Date of Pickup], 'Not Available', 'Available') as status
FROM Equipment INNER JOIN (Clients INNER JOIN Rentals ON Clients.[Client ID]=Rentals.[Client ID]) ON Equipment.[Serial Number]=Rentals.[Serial Number];
Is CURRENT_TIMESTAMP a form field or something? Unless it is (or a field in one of your tables for some reason) Access is going to prompt you for a value there.

Thannnnkkkk you so much for this.

Also yeah it's a form field so that the client can see on what specific date the deliveries/pickups are in the future and such.

# ? Sep 28, 2010 21:10

MikeRabsitch: Aug 23, 2004; Show us what you got, what you got

In Oracle, you can prompt for values with the following code:

code:

accept myValue char prompt 'Enter your value: ';

Is this possible in Postgres at all?

# ? Sep 28, 2010 22:33

MoNsTeR: Jun 29, 2002

baquerd posted:

I'm not joking. While most day to day cases are simple, really complex queries can take a very difficult and fundamental understanding of the database to fully optimize. People make careers off of doing this well, it's not something you just pick up in school and you're good to go.

You won't pick it up in school. At all. Ever. To get that level of understanding of how a database works you need to actually work with them, preferably in a high-pressure enterprise environment where your designs and implementations actually matter. No "3 graduate CS courses" are going to get you there, or even halfway there.

That said, the basics of relational theory that you do need can be had in one semester of junior-level material. That's all I ever had. Or the oft-recommended manga guide (which is shockingly good) is a good alternative if a college course isn't a reasonable option.

# ? Sep 29, 2010 00:56

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

In MySQL, I've been using FROM ... LEFT JOIN ... USING (...) style for a while, but I've become very paranoid about using multiple ones. An example:

code:

SELECT users.name, companies.company_name, locations.state
FROM users
LEFT JOIN companies ON users.company_id = companies.company_id
LEFT JOIN locations ON companies.location_d = locations.location_id

This, I have no problem with. When I convert it to USING, like so...

code:

SELECT users.name, companies.company_name, locations.state
FROM users
LEFT JOIN companies USING (company_id)
LEFT JOIN locations USING (location_id)

I'm assuming it's smart enough to know location_id is from companies, not from users. I'm assuming - I'm no expert on database design - that the right table on that second join is locations, and the left table is the up-to-now combination of users and companies. Right?

So what happens if location_id also exists in users? Even if it's equal to the one in companies? Now, before, I would have done the first query above and it would have worked fine, completely unambiguous. But with USING I'm not entirely sure. In a case with any possible ambiguity, should I abandon USING, or is it safe when I know the data is the same?

Another question, less about paranoia and more about how this works. Let's say I'm pulling in things from two tables on the same ID:

code:

SELECT users.name, userlog.last_entry, employees.foo
FROM users
LEFT JOIN userlog ON users.user_id = userlog.user_id
LEFT JOIN employees ON users.user_id = employees.user_id

If I wanted to convert this to USING, could I simply do...

code:

SELECT users.name, userlog.last_entry, employees.foo
FROM users
LEFT JOIN (userlog, employees) USING (user_id)

# ? Sep 29, 2010 18:05

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Golbez posted:

In MySQL, I've been using FROM ... LEFT JOIN ... USING (...) style for a while, but I've become very paranoid about using multiple ones. An example:
code:
SELECT users.name, companies.company_name, locations.state
FROM users
LEFT JOIN companies ON users.company_id = companies.company_id
LEFT JOIN locations ON companies.location_d = locations.location_id
This, I have no problem with. When I convert it to USING, like so...
code:
SELECT users.name, companies.company_name, locations.state
FROM users
LEFT JOIN companies USING (company_id)
LEFT JOIN locations USING (location_id)
I'm assuming it's smart enough to know location_id is from companies, not from users. I'm assuming - I'm no expert on database design - that the right table on that second join is locations, and the left table is the up-to-now combination of users and companies. Right?

So what happens if location_id also exists in users? Even if it's equal to the one in companies? Now, before, I would have done the first query above and it would have worked fine, completely unambiguous. But with USING I'm not entirely sure. In a case with any possible ambiguity, should I abandon USING, or is it safe when I know the data is the same?

Not 100% on this, but the way I read the documentation on JOIN, the columns in the USING clause have to exist in both the table (strictly "relation" I guess) on the left and the table on the right, the table being joined. So yes it is smart enough, I reckon.

As for the second question, I don't know. Seems ambiguous on the face of it. The documentation might explain it, but I don't care to take the time to read it closely and find out.

I prefer using JOIN ... ON personally because it's completely explicit about the criteria for the join.

The unquoted part of your post I've no idea

# ? Sep 29, 2010 18:19

Avarkx: Feb 11, 2003; :):):):):)

Golbez posted:

So what happens if location_id also exists in users? Even if it's equal to the one in companies? Now, before, I would have done the first query above and it would have worked fine, completely unambiguous. But with USING I'm not entirely sure. In a case with any possible ambiguity, should I abandon USING, or is it safe when I know the data is the same?

I'm going to act like I'm not at work and just start assuming poo poo arbitrarily, like you're at least using MySQL 5.0.12. This is important because the using clause behaves differently in previous versions.

code:

-- Pseudocode
SELECT *
  FROM ( C1, C2 ) t1 
  LEFT JOIN ( C1, C3 ) t2
       USING ( C1 ) 
> t1LJt2 = ( C1, C2, C3 ) -- In MySQL 5.0.12+
> t1LJt2 = ( C1, C2, C1, C3 ) -- Previous versions

So, to address your question, the using clause performs a coalesce operation on common columns of the tables. That is, if we were to extend the definition of our second table to have a common column with T1 outside of the join criteria, the following occurs:

code:

-- More pseudocode
SELECT *
  FROM ( C1, C2 ) t1
  LEFT JOIN ( C1, C2, C3 ) t2
       USING ( C1 )
> t1LJt2 = ( COALESCE( t1.C1, t2.C1 ), COALESCE( t1.C2, t2.C2 ), t2.C3 )
         = ( t1.C1, CASE WHEN t1.C2 IS NULL THEN t2.C2 ELSE t1.C2 END, t2.C3 )

This means that the using clause favors the value found in the leading table except in the case of null values, in which case it will select from the trailing table, even if it is also null. In the case of your users table containing a location_id scenario, the using clause creates a statement meaning "Select a user's company and location, but if the user does not have a location, select the company location instead."

code:

SELECT users.name, companies.company_name, locations.state
  FROM users
  LEFT JOIN companies USING (company_id)
  LEFT JOIN locations USING (location_id)

-- Is equivalent to:
SELECT users.name
      ,companies.company_name
      ,locations.state
  FROM ( SELECT users.name
               ,companies.company_name
               ,COALESCE( users.location_id
                         ,companies.location_id ) location_id
              /* =
               ,CASE 
                  WHEN users.location_id IS NULL 
                    THEN companies.location_id
                  ELSE users.location_id
                END location_id
               */
           FROM users
           LEFT JOIN companies
                ON users.user_id = companies.user_id ) uc
  LEFT JOIN locations
       ON uc.location_id = locations.location_id

USING is a clause with a specific application, namely auto-coalescing common columns for you. Use ON if you don't need the functionality of USING. Abusing the USING clause will make it difficult to read intent later on, not unlike abuse of the HAVING clause might make code cryptic when a perfectly good WHERE case exists for the same results.

Golbez posted:

Another question, less about paranoia and more about how this works. Let's say I'm pulling in things from two tables on the same ID:
code:
SELECT users.name, userlog.last_entry, employees.foo
FROM users
LEFT JOIN userlog ON users.user_id = userlog.user_id
LEFT JOIN employees ON users.user_id = employees.user_id
If I wanted to convert this to USING, could I simply do...
code:
SELECT users.name, userlog.last_entry, employees.foo
FROM users
LEFT JOIN (userlog, employees) USING (user_id)
?

If this doesn't throw a syntax error (and I would be seriously surprised if it didn't), go for it. I don't have access to a MySQL instance right now so I can't check this myself or I would, just to see.

# ? Sep 30, 2010 15:53

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Avarkx posted:

If this doesn't throw a syntax error (and I would be seriously surprised if it didn't), go for it. I don't have access to a MySQL instance right now so I can't check this myself or I would, just to see.

The manual includes this:

quote:

This is a conservative extension if we consider each comma in a list of table_reference items as equivalent to an inner join. For example:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

is equivalent to:

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other).

So I was wondering if it was possible to combine that comma notation in the LEFT JOIN with USING. Though the whole cross/inner join thing scares me a little.

# ? Sep 30, 2010 16:18

Avarkx: Feb 11, 2003; :):):):):)

Golbez posted:

The manual includes this:

So I was wondering if it was possible to combine that comma notation in the LEFT JOIN with USING. Though the whole cross/inner join thing scares me a little.

Oh! That's sort of neat I guess, definitely good to know...

Edit: Everything I said is a lie, I'm really confused about the INNER JOIN = CROSS JOIN claim the manual is making, I'll repost here after I check something out...

Avarkx fucked around with this message at 17:41 on Sep 30, 2010

# ? Sep 30, 2010 16:59

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Avarkx posted:

USING is a clause with a specific application, namely auto-coalescing common columns for you. Use ON if you don't need the functionality of USING.

I thought that USING (c1) was functionally identical, in a simple situation, to "... ON t1.c1 = t2.c1". I'm a little sick at the moment so I might be missing the obvious here, but is what you're saying equal to what I'm saying?

I figure I should be specific and use ON rather than USING for multiple table issues.

Edit: The manual continues, "In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise."

# ? Sep 30, 2010 17:42

Avarkx: Feb 11, 2003; :):):):):)

Golbez posted:

Edit: The manual continues, "In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise."

Okay, this probably what's really messing with me... CROSS JOIN and INNER JOIN should in no way be functionally equivalent, but if MySQL says so, what I originally posted should be more or less correct, so here that is again.

code:

SELECT users.name, userlog.last_entry, employees.foo
FROM users
LEFT JOIN (userlog, employees) USING (user_id)

=

SELECT u1.name
      ,userlog.last_entry
      ,employees.foo
  FROM users u1
  LEFT JOIN userlog ON u1.user_id = userlog.user_id
      ,users u2
  LEFT JOIN employees.foo ON u2.user_id = employees.user_id
 WHERE u1.user_id = u2.user_id

=

SELECT users.name, userlog.last_entry, employees.foo
FROM users
LEFT JOIN userlog ON users.user_id = userlog.user_id
LEFT JOIN employees ON users.user_id = employees.user_id

So yes, it looks to me that those queries would be functionally equivalent in MySQL. It's truly a shame I don't have access to a MySQL instance at the moment however, as I would be quite interested in knowing what the query optimizer would do with the execution plans between your first and second queries.

Also in MySQL, the following is probably also true:

code:

SELECT *
  FROM ( C1, C2 ) t1
      ,( C1, C3 ) t2  
 WHERE t1.C1 = t2.C3

=

SELECT *
  FROM ( C1, C2 ) t1
 INNER JOIN ( C1, C3 ) t2
       ON t1.C1 = t2.C1

=

SELECT *
  FROM ( C1, C2 ) t1
 CROSS JOIN ( C1, C3 ) t2

Normally the difference between an inner join and a cross join is that the inner join operates like a comma separated table list with a WHERE clause that contains a relational association, whereas a cross join operates like a comma separated table list without a relational association.

code:

-- Inner join
SELECT *
  FROM ( C1, C2 ) t1
      ,( C1, C3 ) t2
 WHERE t1.C1 = t2.C1

<>

-- Cross join
SELECT *
  FROM ( C1, C2 ) t1
      ,( C1, C3 ) t2

Golbez posted:

I thought that USING (c1) was functionally identical, in a simple situation, to "... ON t1.c1 = t2.c1". I'm a little sick at the moment so I might be missing the obvious here, but is what you're saying equal to what I'm saying?

I figure I should be specific and use ON rather than USING for multiple table issues.

In simple cases, we are talking about the same thing, yes. I'm just attempting to illustrate that in a case involving a possibly ambiguous column, the value of the leading table is favored. That is, the ambiguity is resolved by taking the first non-null value in the common column of the referenced tables (if a value exists, of course). I'm also saying that the USING clause is specific enough for that very reason, but the majority of the time you will be doing yourself (and anybody reading your code later) a service by sticking to the ON clause when the case is in fact, simple.

Avarkx fucked around with this message at 18:31 on Sep 30, 2010

# ? Sep 30, 2010 17:48

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Avarkx posted:

In simple cases, we are talking about the same thing, yes. I'm just attempting to illustrate that in a case involving a possibly ambiguous column, the value of the leading table is favored. That is, the ambiguity is resolved by taking the first non-null value in the common column of the referenced tables (if a value exists, of course). I'm also saying that the USING clause is specific enough for that very reason, but the majority of the time you will be doing yourself (and anybody reading your code later) a service by sticking to the ON clause when the case is in fact, simple.

Thanks so much for your analysis.

# ? Sep 30, 2010 20:18

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 10:40

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Sub Par posted:

You cannot use CASE statements in Access SQL. You will need to use IIF instead:
code:
SELECT Equipment.[Serial Number], 
Equipment.Product, 
Equipment.Brand, 
Equipment.[Product Type], 
Equipment.[Purchase Date],
Rentals.[Date of pickup],
iif([CURRENT_TIMESTAMP]<=[Rentals].[Date of Pickup], 'Not Available', 'Available') as status
FROM Equipment INNER JOIN (Clients INNER JOIN Rentals ON Clients.[Client ID]=Rentals.[Client ID]) ON Equipment.[Serial Number]=Rentals.[Serial Number];
Is CURRENT_TIMESTAMP a form field or something? Unless it is (or a field in one of your tables for some reason) Access is going to prompt you for a value there.

OK ONE LAST THING. I'm getting redundant information in the available column, so how do I get it to exclude all results as available if it already has a serial number in the unavailable column?

# ? Oct 1, 2010 20:45

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »