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 »

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Having CreatedDate on everything can't hurt (as long as the column has a default value of "now"). Having a ModifiedDate on stuff where you don't need it is probably a bad idea because if you don't need it, you aren't going to be careful to update that column every time you update the row, and then it's going to be misleading.

# ? Jul 16, 2014 18:36

Adbot: ADBOT LOVES YOU

# ? May 29, 2024 02:33

Sedro: Dec 31, 2008

Essential posted:

How important is it to have a CreatedDate & ModifiedDate columns on tables? I've got a guy here telling me that EVERY table, without exception should have those 2 columns. We're adding a couple tables and right now I don't care about CreatedDate/ModifiedDate on a few of the tables and I can't ever see it being needed on those tables. His argument is that what IF at some point in the future I want it and even if I don't, it isn't going to affect anything negatively.

I can see his point but I'd rather not add columns if I don't truly need them.

You should be suspicious when someone tells you should always, without exception do something. You could spend a lifetime doing work that you don't need right now but you might need later.

That said, if you do implement this it should be automated via triggers. Or use the auditing provided by your DBMS if there is one.

# ? Jul 16, 2014 18:50

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Essential posted:

How important is it to have a CreatedDate & ModifiedDate columns on tables? I've got a guy here telling me that EVERY table, without exception should have those 2 columns. We're adding a couple tables and right now I don't care about CreatedDate/ModifiedDate on a few of the tables and I can't ever see it being needed on those tables. His argument is that what IF at some point in the future I want it and even if I don't, it isn't going to affect anything negatively.

I can see his point but I'd rather not add columns if I don't truly need them.

Personally, I like having audit fields on all of my tables (created date, created by, modified date, modified by), even if I don't foresee using that information (and there's been a few times where I'm glad I had it).

# ? Jul 16, 2014 19:05

One Swell Foop: Aug 5, 2010; I'm afraid we have no time for codes and manners.

I'd say that's probably true for data warehouses dimensions and for rows that are actually records of objects, but probably not true for things like data warehouse fact tables and application operational databases.

That said, sometimes design consistency within a database is important in itself; if this is someone else's database design that you're adding to and one of the design principles is that all of their tables must have a created date and a modified date, then create the tables to that spec, but don't feel compelled to follow that principle in your own designs.

# ? Jul 16, 2014 19:11

Essential: Aug 14, 2003

As always, great advice and thank you guys. I'm going to talk with him about the db consistency and I think that's what his concern may be. I really appreciate the advice from everyone!

# ? Jul 16, 2014 21:54

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

One Swell Foop posted:

That said, sometimes design consistency within a database is important in itself; ...

My crystal ball says... He wants it to be compatible with a bevy of existing SOLR web applications, so he can guarantee historical, paginated record selection without ever having to learn "that complicated SQL stuff".

# ? Jul 16, 2014 23:38

No Gravitas: Jun 12, 2013; by FactsAreUseless

Does anyone know if SQLite supports something similar to this: http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert

I'm going to be inserting data into a table which has an autoincrementing row id. I then would like to get back that row id to use as a key for some extra data. I have a ton of threads hitting SQL, so getting the biggest rowid is not going to cut it.

Thanks! (First time using a database, please be gentle to this idiot!)

# ? Jul 17, 2014 05:41

Sedro: Dec 31, 2008

No Gravitas posted:

Does anyone know if SQLite supports something similar to this: http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert

I'm going to be inserting data into a table which has an autoincrementing row id. I then would like to get back that row id to use as a key for some extra data. I have a ton of threads hitting SQL, so getting the biggest rowid is not going to cut it.

Thanks! (First time using a database, please be gentle to this idiot!)

According to the docs, the rowid is local to the connection. You should have one connection per thread and not share them (this is true for most databases).

# ? Jul 17, 2014 06:09

One Swell Foop: Aug 5, 2010; I'm afraid we have no time for codes and manners.

I think the answer probably depends on whether you're using an API or a SQL-type interface... it looks like there's a function lastrowid in some APIs that you can use. AFAIK SQLite is unusual in that it's more often accessed using an API than it is using SQL command + driver, unlike most RDBMSs.

I'm no SQLite expert so here's a stackoverflow link that describes how to use lastrowid with Python, maybe it'll help get you to where you need to be.

# ? Jul 17, 2014 08:40

Workaday Wizard: Oct 23, 2009; by Pragmatica

Hello all, this should be simple but the solution escapes me.

How do I turn this:

pre:

group  player  score
--------------------
1      Adam    2
1      Bob     1
1      Carl    3
1      David   2
2      Edd     3
2      Frank   2
2      George  1
2      Howard  3

Into this:

pre:

group  player  score
--------------------
1      Carl    3
2      Edd     3

In other words how do I get the maximum for a GROUP BY group while also getting the rest of the row?

I am using PostgreSQL if that matters.

# ? Jul 18, 2014 18:09

Pardot: Jul 25, 2001

http://sqlfiddle.com/#!15/c6167/3

SQL code:

=# select distinct on (grp) * from what order by grp, score desc, player;
 grp | player | score
-----+--------+-------
   1 | Carl   |     3
   2 | Edd    |     3
(2 rows)

# ? Jul 18, 2014 18:25

Workaday Wizard: Oct 23, 2009; by Pragmatica

Pardot posted:

http://sqlfiddle.com/#!15/c6167/3

SQL code:

=# select distinct on (grp) * from what order by grp, score desc, player;
 grp | player | score
-----+--------+-------
   1 | Carl   |     3
   2 | Edd    |     3
(2 rows)

I was so fixated on including max() somewhere in the query I forget distinct exists :doh:

Thanks friend :tipshat:

# ? Jul 18, 2014 18:34

Pardot: Jul 25, 2001

Shinku ABOOKEN posted:

I was so fixated on including max() somewhere in the query I forget distinct exists

Thanks friend

Ah, but this is DISTINCT ON, not just DISTINCT, which is uhh distinct from distinct.

# ? Jul 18, 2014 18:37

Workaday Wizard: Oct 23, 2009; by Pragmatica

Pardot posted:

Ah, but this is DISTINCT ON, not just DISTINCT, which is uhh distinct from distinct.

Noted. Thanks.

# ? Jul 18, 2014 18:52

mobby_6kl: Aug 9, 2009; by Fluffdaddy

Pardot posted:

Ah, but this is DISTINCT ON, not just DISTINCT, which is uhh distinct from distinct.

What is this black magic called and does T-SQL have an equivalent? Or are we stuck with window functions there?

# ? Jul 18, 2014 21:24

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

mobby_6kl posted:

What is this black magic called and does T-SQL have an equivalent? Or are we stuck with window functions there?

Nope, looks like hacky workarounds like ROW_NUMBER:
http://stackoverflow.com/questions/5021693/distinct-for-only-one-column

# ? Jul 18, 2014 21:26

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

Pardot posted:

Ah, but this is DISTINCT ON, not just DISTINCT, which is uhh distinct from distinct.

I was also trying to find a way to use that MAX function in there, I had never heard of this DISTINCT ON.

You have taught me something new, thank you sir. :tipshat:

# ? Jul 18, 2014 21:35

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

mobby_6kl posted:

What is this black magic called and does T-SQL have an equivalent? Or are we stuck with window functions there?

If you want groupwise maximums you could do something like this:

http://sqlfiddle.com/#!6/c6167/6

As is, that will give you all players that had the max score in their group, but you could put a GROUP BY on there to get just one.

http://sqlfiddle.com/#!6/870a7/11

# ? Jul 18, 2014 22:13

the: Jul 18, 2004; by Cowcaster

What format do I specify a dateTime Primitive Data Type in Salesforce? I'm trying to restrict a query to objects that were created after a certain date.

I just don't know what's supposed to go in here. Can I even use a greater/less than symbol in a query?

code:

SELECT Name WHERE Account.CreatedDate = ?????

edit: I'm basically trying to figure out if it's MMDDYYYY or MMDDYYHHMMSS or whatever

the fucked around with this message at 21:58 on Jul 20, 2014

# ? Jul 20, 2014 21:52

kloa: Feb 14, 2007

the posted:

What format do I specify a dateTime Primitive Data Type in Salesforce? I'm trying to restrict a query to objects that were created after a certain date.

I just don't know what's supposed to go in here. Can I even use a greater/less than symbol in a query?
code:
SELECT Name WHERE Account.CreatedDate = ?????
edit: I'm basically trying to figure out if it's MMDDYYYY or MMDDYYHHMMSS or whatever

Not sure for Salesforce ( :barf:

) but most SQL software will allow ">" and ">=" for dates.

You could try a general date like '7/20/14' and see if it automatically converts it to the needed format.

# ? Jul 20, 2014 22:17

Pardot: Jul 25, 2001

the posted:

edit: I'm basically trying to figure out if it's MMDDYYYY or MMDDYYHHMMSS or whatever

http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm

YYYY-MM-DD or YYYY-MM-DDThh:mm:ssZ

edit: I've written like a total of 2 salesforce queries, the second being seeing if that date time thing was true just now, but https://workbench.developerforce.com is probably useful for figuring things out if you're not already aware of it.

Pardot fucked around with this message at 05:46 on Jul 21, 2014

# ? Jul 21, 2014 05:43

the: Jul 18, 2004; by Cowcaster

In Salesforce, I have an Opportunity entity that has many different custom fields associated with it. What I'm finding out is that many of the custom fields are throwing errors when I make SOQL requests to them. But some aren't. And I can't figure out what is the difference, since they're all custom fields.

For example, the one I want to grab is called "Training_Contact__c." When I make a request:

code:

SELECT ContactId, Opportunity.Training_Contact__c, Opportunity.Name, IsPrimary 
FROM OpportunityContactRole WHERE Opportunity.RecordType.Name = \'FD - Buying\' 
AND Opportunity.IsClosed = TRUE AND Opportunity.CreatedDate >= 2012-05-01T00:00:00Z

I get the following error:

code:

SoapFaultError: 'INVALID_FIELD' "INVALID_FIELD: SELECT ContactId, 
Opportunity.Training_Contact__c ^ ERROR at Row:1:Column:19 No such column 
'Training_Contact__c' on entity 'Opportunity'. If you are attempting to use 
a custom field, be sure to append the '__c' after the custom field name. 
Please reference your WSDL or the describe call for the appropriate names."

But using the custom field "Time_zone__c" works fine. But then "Renewal_Date__c" doesn't. But then "Range__c" does. I can't figure out what's the difference, since they're all custom fields under the Opportunity entity.

# ? Jul 22, 2014 19:41

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

the posted:

In Salesforce, I have an Opportunity entity that has many different custom fields associated with it. What I'm finding out is that many of the custom fields are throwing errors when I make SOQL requests to them. But some aren't. And I can't figure out what is the difference, since they're all custom fields.

For example, the one I want to grab is called "Training_Contact__c." When I make a request:
code:
SELECT ContactId, Opportunity.Training_Contact__c, Opportunity.Name, IsPrimary 
FROM OpportunityContactRole WHERE Opportunity.RecordType.Name = \'FD - Buying\' 
AND Opportunity.IsClosed = TRUE AND Opportunity.CreatedDate >= 2012-05-01T00:00:00Z
I get the following error:
code:
SoapFaultError: 'INVALID_FIELD' "INVALID_FIELD: SELECT ContactId, 
Opportunity.Training_Contact__c ^ ERROR at Row:1:Column:19 No such column 
'Training_Contact__c' on entity 'Opportunity'. If you are attempting to use 
a custom field, be sure to append the '__c' after the custom field name. 
Please reference your WSDL or the describe call for the appropriate names."
But using the custom field "Time_zone__c" works fine. But then "Renewal_Date__c" doesn't. But then "Range__c" does. I can't figure out what's the difference, since they're all custom fields under the Opportunity entity.

Sounds like a field level security issue. Go to Setup->Manage Users->Users. Find the user you are executing the queries as and click on the name of the Profile. Then scroll down to the "Field-Level Security" section.

# ? Jul 22, 2014 20:06

the: Jul 18, 2004; by Cowcaster

fletcher posted:

Sounds like a field level security issue. Go to Setup->Manage Users->Users. Find the user you are executing the queries as and click on the name of the Profile. Then scroll down to the "Field-Level Security" section.

Yep, that ended up being it! Turns out that literally the only group not given access to that field, out of the two dozen or so groups, was mine :negative:

# ? Jul 22, 2014 20:10

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

the posted:

Yep, that ended up being it! Turns out that literally the only group not given access to that field, out of the two dozen or so groups, was mine

I've been there my friend!

In general, how is your Salesforce stuff going? Making progress on whatever it is you are building?

# ? Jul 22, 2014 20:16

the: Jul 18, 2004; by Cowcaster

Yeah, the main issue I've been running into is twofold:

1. Making sure that the Python queries I make are accurate (i.e. making the same query in Salesforce to prove it)

2. Showing why using Python is better than running Salesforce reports (which #1 tends to sometimes go against).

# ? Jul 22, 2014 20:25

Pardot: Jul 25, 2001

the posted:

Yeah, the main issue I've been running into is twofold:

1. Making sure that the Python queries I make are accurate (i.e. making the same query in Salesforce to prove it)

2. Showing why using Python is better than running Salesforce reports (which #1 tends to sometimes go against).

You could also use https://www.heroku.com/connect which two way syncs the salesforce stuff with a proper postgres database, so you can use normal tools.

# ? Jul 22, 2014 20:48

the: Jul 18, 2004; by Cowcaster

fletcher, while you're here, I'm trying to query a list of Opportunities and find out whether or not they have a Primary Contact. I don't see that there's a specific field for that. It looks like I can query OpportunityContactRole to see if a contact is a Primary Contact, but the problem there is that there are some Opportunities that don't have Contacts at all, so I'd have to grab a list of all Opportunities, then a list of all OpportunityContactRoles, and then filter out what doesn't match.

Searching around it looks like this is a frequenty requested feature.

# ? Jul 22, 2014 20:55

BirdOfPlay: Feb 19, 2012; THUNDERDOME LOSER

I'm working a little program to manage full tournaments that can take on a number of forms. Since I'm defaulting to fencing tournaments as the default/base case for design purposes, I'll be working with a preliminary round that seeds a final direct elimination bracket. I'll be using local databases to manage the roster of participants and will be storing the final results there as well.

I'm at a very low-level for just SQL in general and am crap at database design, so I'm just looking for some minor input on how I should structure the tables. Since I'll have a table for the roster, is it bad design to store their place initially, after the preliminary round, and the final place in the tournament in the roster table and just call SUBTABLE (I think?) to get a table for which results are needed?

I'm hesitant to do this because mixing data of different types/purposes is usually a big no-no in other things, such as the division of MCV.

# ? Jul 22, 2014 22:03

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

the posted:

Yeah, the main issue I've been running into is twofold:

1. Making sure that the Python queries I make are accurate (i.e. making the same query in Salesforce to prove it)

2. Showing why using Python is better than running Salesforce reports (which #1 tends to sometimes go against).

We also ran into limitations of what we could do with Salesforce reports and ended up going down the route of Java + SOAP API + MySQL to accomplish what we needed to do. Salesforce reports are nice but sometimes you need to break out of them for more flexibility.

the posted:

fletcher, while you're here, I'm trying to query a list of Opportunities and find out whether or not they have a Primary Contact. I don't see that there's a specific field for that. It looks like I can query OpportunityContactRole to see if a contact is a Primary Contact, but the problem there is that there are some Opportunities that don't have Contacts at all, so I'd have to grab a list of all Opportunities, then a list of all OpportunityContactRoles, and then filter out what doesn't match.

Searching around it looks like this is a frequenty requested feature.

Sounds like you might be able to use a subquery to accomplish this? Something like:

code:

SELECT AccountId, (SELECT IsPrimary FROM OpportunityContactRoles) FROM Opportunity

Not sure if you've used that relationship name before, but here's where you can find it in workbench:

# ? Jul 22, 2014 22:08

the: Jul 18, 2004; by Cowcaster

Yeah I was thinking of doing that, but the problem is that there are Opportunities that exist without contacts at all.

So, I have three types here:

A. Opportunity with no contact
B. Opportunity with OpportunityContactRole and IsPrimary = False
C. Opportunity with OpportunityContactRole and IsPrimary = True

And I'm trying to find a query that gets me A + B. If there was a "Does it contain a Primary Contact?" query, that would seem to solve it. But the problem is that if I make a query as you suggested I would be eliminating A which I also need.

It would be like trying to find all people without red hats, but your query is "Do you have a hat? Is it red?" Would exclude people without hats in the first place.

the fucked around with this message at 01:45 on Jul 23, 2014

# ? Jul 23, 2014 01:42

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

I think that query handles A, B, and C though. For case A, the subquery would simply have a row count of 0.

# ? Jul 23, 2014 01:51

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

BirdOfPlay posted:

I'm working a little program to manage full tournaments that can take on a number of forms. Since I'm defaulting to fencing tournaments as the default/base case for design purposes, I'll be working with a preliminary round that seeds a final direct elimination bracket. I'll be using local databases to manage the roster of participants and will be storing the final results there as well.

I'm at a very low-level for just SQL in general and am crap at database design, so I'm just looking for some minor input on how I should structure the tables. Since I'll have a table for the roster, is it bad design to store their place initially, after the preliminary round, and the final place in the tournament in the roster table and just call SUBTABLE (I think?) to get a table for which results are needed?

I'm hesitant to do this because mixing data of different types/purposes is usually a big no-no in other things, such as the division of MCV.

You have answered your own question. Putting anything not pertinent to the roster is likely to complicate matters unnecessarily, and by that I mean "Make it difficult to achieve possibly even the most basic of things" later. In all likelihood, your roster is sufficiently complicated on its own, that it shouldn't be concerned with the details of the brackets. You already have to deal with storing multiple tournaments, and people on the roster may appear on other rosters, so you have enough joins to handle on that side.

Relational databases are notably (theoretically) bad at tree data, so you probably don't want to start off trying to create a query that generates the full tree for the brackets, but as you'll be dealing with a fixed number of rounds after the roster is created, you should not have any troubles. If your bracket table stores the tournament, the round, and the pairings, you should be fine.

After you get your basic structure, think about what manner of referential integrity you want from the database. Indeed, it sounds like the roster is "calculated" from the bracket (with round=0), so perhaps you shouldn't store it at all; it depends entirely on what is needed on the roster.

# ? Jul 23, 2014 04:28

Malcolm XML: Aug 8, 2009; I always knew it would end like ｔｈｉｓ．

the posted:

Yeah I was thinking of doing that, but the problem is that there are Opportunities that exist without contacts at all.

So, I have three types here:

A. Opportunity with no contact
B. Opportunity with OpportunityContactRole and IsPrimary = False
C. Opportunity with OpportunityContactRole and IsPrimary = True

And I'm trying to find a query that gets me A + B. If there was a "Does it contain a Primary Contact?" query, that would seem to solve it. But the problem is that if I make a query as you suggested I would be eliminating A which I also need.

It would be like trying to find all people without red hats, but your query is "Do you have a hat? Is it red?" Would exclude people without hats in the first place.

Left join that poo poo son

# ? Jul 23, 2014 09:15

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Malcolm XML posted:

Left join that poo poo son

This is Salesforce, there is no JOIN keyword. The subquery version that I posted should be the equivalent of a LEFT OUTER JOIN

# ? Jul 23, 2014 18:49

the: Jul 18, 2004; by Cowcaster

fletcher posted:

This is Salesforce, there is no JOIN keyword. The subquery version that I posted should be the equivalent of a LEFT OUTER JOIN

Yes, it worked, thanks again for your help.

# ? Jul 23, 2014 18:57

Malcolm XML: Aug 8, 2009; I always knew it would end like ｔｈｉｓ．

fletcher posted:

This is Salesforce, there is no JOIN keyword. The subquery version that I posted should be the equivalent of a LEFT OUTER JOIN

If it doesn't have join it's not relational

Bastardized sql like dialects heraus :mad:

# ? Jul 23, 2014 19:01

Workaday Wizard: Oct 23, 2009; by Pragmatica

Yo! Got some question marks for you.

1- If I'm not using the column in a WHERE clause, is it better to parse a string column to number in the SQL query or do that in my code?
2- Is there anything wrong with ODBC? I read some post dreading using "the horror that is ODBC" and now I'm curious (I forgot where I read that, probably yospos).

# ? Jul 23, 2014 19:07

kloa: Feb 14, 2007

Shinku ABOOKEN posted:

Yo! Got some question marks for you.

1- If I'm not using the column in a WHERE clause, is it better to parse a string column to number in the SQL query or do that in my code?
2- Is there anything wrong with ODBC? I read some post dreading using "the horror that is ODBC" and now I'm curious (I forgot where I read that, probably yospos).

1. Is this a once in a while query or something that will run frequently?
2. If it came from yospos then :ohdear:

# ? Jul 23, 2014 19:21

Adbot: ADBOT LOVES YOU

# ? May 29, 2024 02:33

Workaday Wizard: Oct 23, 2009; by Pragmatica

kloa posted:

1. Is this a once in a while query or something that will run frequently?
2. If it came from yospos then

It's a common query that shows stuff to the user. It will be running frequently.

# ? Jul 23, 2014 19:42

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

«‹›303 »