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 »

var1ety: Jul 26, 2004

SLOSifl posted:

Just update all the columns if any of them have changed...or do you have triggers on the table that care if certain columns were touched?

In Oracle, you'll generate undo and redo for the rewritten records, fire any triggers, and update all the indexes (including the primary key) - and could generate unexpected issues with child table locking (even if no values change). This scenario usually comes up when you use a ROWTYPE variable to push updates into a table (these statements are transformed into an update against every column).

In practice, I'm not sure how much this will impact concurrency, but it result in some unexpected and difficult to debug behavior that should be weighed against convenience.

# ? Sep 9, 2014 14:16

Adbot: ADBOT LOVES YOU

# ? Apr 27, 2024 00:09

Gatac: Apr 22, 2008; Fifty Cent's next biopic.

Yeah, that’s my worry. I guess I'll go with multiple UPDATE clauses then.

# ? Sep 9, 2014 14:25

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Based on the questions you're asking, you'll soon be at the point where the question actually becomes, "What actual problem are you trying to solve?". The trouble with these speculations is that you're not just using them as a theoretical information, but that you're presenting what you think are the solutions when they likely aren't.

Gatac posted:

"Okay, remember this poo poo we did at the beginning? We're working with that again."...: why isn't it done like this? Is this supposed to be one of those cases where the language wants to force you to clean up your thoughts (and program flow) to the point where you no longer need temp tables to get it done? Or am I just looking in the wrong place?

What you say is partially true. Most viable databases are very good at doing what they're supposed to do, and are seemingly awful --- in the sense of optimization --- at doing other things. It can be very common that a hundredfold increase results from using a "proper query" instead of something someone just slapped together "because the right answer appeared". Here, however, it sounds like you're confusing where the processing occurs.

The database has to find the rows when it runs the query (which is not necessarily the same time it builds it, and not necessarily all at once). If your purpose is to update said rows, it can be something of a waste of time to do this in two steps. "Find all rows with property X then update them" might make sense getting file folders out of a drawer, but the database is generally smart enough to know to "Find a row with property X and update it before finding the next". You can make this worse with ORDER BY statements and such, but the point is still the same: Don't do things to short-circuit the system that best knows how to interact with the database.

Based on your question, it sounds like you want the data to cross over to the application before the processing occurs. While there are situations where this is the only viable approach, it can be relatively painful because of all the networking time that gets added, along with the process and session creations on worker threads. Depending on the application and data sizes, things like memory become doubly troublesome because you get to optimize queries and the handler and everything in the middle. For such a scenario, particularly when there's little choice, you can make use of things like SELECT FOR UPDATE RETURNING. With temporary tables, you may well have issues with naming conflicts, dependent on the database, and there are things like CURSORs, which bring their own headaches. In most cases, however, even with the atomicity requirements in most applications, you can get away with return clauses or UPDATE...SELECT. If you can do things with stored procedures, you'll still likely save over an external application.

Gatac posted:

Hmm, okay. Different question, is it possible to construct an UPDATE that doesn't actually change a field? I know it's possible to just have it write back what's already in there, but is there a way to avoid any change at all? Basically, I'd like to update a record, but certain fields only need to be changed under certain circumstances. (Using CASE statements when assigning the values.) I could just externalize those into separate UPDATEs wrapped in IF blocks, of course, but is it possible to centralize this into one neat UPDATE?

Again, what are you trying to do? I think others have beaten me to it, but touching the row, even with the same value, is bad for transaction logging, triggers, and so forth. You seem to want everything in a single statement, but you should be thinking about things that need to occur "within a single transaction". Multiple updates can appear in a single transaction and you can still roll them all back at once. (There are plenty of examples where that's a bad idea, but yeah.)

You're trying to do many things here that are "fighting the database"; this almost always means that you're doing the wrong things. You're taking a few keywords and trying to slew them until they seem to fit a scenario. Instead, think about the problems you're trying to solve and determine how the database permits you to solve them. I don't know how else to describe this, but it just sounds like you're looking at things sideways.

# ? Sep 10, 2014 00:40

Gatac: Apr 22, 2008; Fifty Cent's next biopic.

PhantomOfTheCopier posted:

You're trying to do many things here that are "fighting the database"; this almost always means that you're doing the wrong things. You're taking a few keywords and trying to slew them until they seem to fit a scenario. Instead, think about the problems you're trying to solve and determine how the database permits you to solve them. I don't know how else to describe this, but it just sounds like you're looking at things sideways.

Yeah, fair enough. Like I said, I'm a newb at this, and fortunately for everyone involved very much not responsible for any serious work on the database - mostly just curious about this and that as I'm trying to wrap my head around some of the paradigms. This is the thread for stupid questions, right?

Still, thanks for the responses, everyone.

# ? Sep 10, 2014 16:08

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

I have the following piece of Oracle SQL that I need to convert to PostreSQL:

code:

SELECT blah blah
WHERE REQUEST_DAY BETWEEN trunc(trunc(sysdate,MM)-1,MM) AND (trunc(sysdate,MM)-1)

I'm getting an error because I think the "trunc" function does different things in Oracle than Postgres: Near as I can tell ORackle's trunc handles dates, but Posgres' only accepts decimal numbers.

Problem is, I don't really understand what this WHERE clause is trying to accomplish. I get that it's trying to establish two dates for a range, but the nested trunc functions is throwing me.

Can someone help convert this to Postgres and/or explain what this clause does?

# ? Sep 11, 2014 21:12

var1ety: Jul 26, 2004

Agrikk posted:

I have the following piece of Oracle SQL that I need to convert to PostreSQL:
code:
SELECT blah blah
WHERE REQUEST_DAY BETWEEN trunc(trunc(sysdate,MM)-1,MM) AND (trunc(sysdate,MM)-1)
I'm getting an error because I think the "trunc" function does different things in Oracle than Postgres: Near as I can tell ORackle's trunc handles dates, but Posgres' only accepts decimal numbers.

Problem is, I don't really understand what this WHERE clause is trying to accomplish. I get that it's trying to establish two dates for a range, but the nested trunc functions is throwing me.

Can someone help convert this to Postgres and/or explain what this clause does?

Gets anything with a REQUEST_DAY in the previous month - running it today would return August.

# ? Sep 11, 2014 21:17

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

var1ety posted:

Gets anything with a REQUEST_DAY in the previous month - running it today would return August.

So trunc(trunc(sysdate,MM)-1,MM) returns August 1 and (trunc(sysdate,MM)-1) returns August 31?

# ? Sep 11, 2014 21:38

var1ety: Jul 26, 2004

Agrikk posted:

So trunc(trunc(sysdate,MM)-1,MM) returns August 1 and (trunc(sysdate,MM)-1) returns August 31?

Yes, as long as the "MM" is a quoted literal and is not a column from your source object.

# ? Sep 11, 2014 21:50

Sedro: Dec 31, 2008

Agrikk posted:

So trunc(trunc(sysdate,MM)-1,MM) returns August 1 and (trunc(sysdate,MM)-1) returns August 31?

Yep

Here's the first expression computed out

pre:

trunc(trunc(sysdate,MM)-1,MM)
trunc(trunc('2014-09-11',MM)-1,MM)
trunc('2014-09-01'-1,MM)
trunc('2014-08-31',MM)
'2014-08-01'

# ? Sep 11, 2014 21:51

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Agrikk posted:

I have the following piece of Oracle SQL that I need to convert to PostreSQL:
code:
SELECT blah blah
WHERE REQUEST_DAY BETWEEN trunc(trunc(sysdate,MM)-1,MM) AND (trunc(sysdate,MM)-1)
I'm getting an error because I think the "trunc" function does different things in Oracle than Postgres: Near as I can tell ORackle's trunc handles dates, but Posgres' only accepts decimal numbers...

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

date_trunc?

# ? Sep 11, 2014 23:32

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

Thanks for the responses, guys.

Turns out just a few more links down google results taugh me the postgresql version of Oracle's trunc is date_trunc. Go figure.

The result was:

code:

BETWEEN date_trunc('month',date_trunc('month',sysdate)-1) AND (date_trunc('month', sysdate)-1)

Duh.

...aaaand beaten

# ? Sep 11, 2014 23:34

Workaday Wizard: Oct 23, 2009; by Pragmatica

When I insert multiple rows with "INSERT INTO table (col1, col2) VALUES (blah, blah), (blah, blah), (blah, blah);" can another database user read the new rows before they are completely inserted?

In other words if I insert three new rows, can another user read row1 before i finish inserting row3?

I am using PostgreSQL.

# ? Sep 15, 2014 21:30

Chill Callahan: Nov 14, 2012

Shinku ABOOKEN posted:

When I insert multiple rows with "INSERT INTO table (col1, col2) VALUES (blah, blah), (blah, blah), (blah, blah);" can another database user read the new rows before they are completely inserted?

In other words if I insert three new rows, can another user read row1 before i finish inserting row3?

I am using PostgreSQL.

I wouldn't chance it being atomic, so I would just wrap it in a transaction.

# ? Sep 15, 2014 22:04

Pardot: Jul 25, 2001

Shinku ABOOKEN posted:

When I insert multiple rows with "INSERT INTO table (col1, col2) VALUES (blah, blah), (blah, blah), (blah, blah);" can another database user read the new rows before they are completely inserted?

In other words if I insert three new rows, can another user read row1 before i finish inserting row3?

I am using PostgreSQL.

In general, no other connections won't see it until that statement commits.

There is an notion of a read uncommitted isolation level, but Postgres doesn't actually have it. It does have neat things like serializable snapshot isolation which turns hard to detect race conditions into errors http://www.postgresql.org/docs/9.3/static/transaction-iso.html

# ? Sep 15, 2014 22:44

Workaday Wizard: Oct 23, 2009; by Pragmatica

Pardot posted:

In general, no other connections won't see it until that statement commits.

There is an notion of a read uncommitted isolation level, but Postgres doesn't actually have it. It does have neat things like serializable snapshot isolation which turns hard to detect race conditions into errors http://www.postgresql.org/docs/9.3/static/transaction-iso.html

Sounds good for my purpose.

I will try to change the data producer to use transactions later. (I can't do that now because it's a live data scraper).

Thanks all.

# ? Sep 16, 2014 07:11

Weaponized Autism: Mar 26, 2006; All aboard the Gravy train!; Hair Elf

How do I query on text between two known strings?

I have IIS Logs stored on a database and I need to pull out the userid portion of it. Let's say I have data that looks like this:

XXXXX;+userid=LOGIN;abcdefg

My goal is to pull out the text that exists between +userid= and ;. So in this case I would have LOGIN as my result. Assuming the column name is LOGININFO, I have attempted to write this:

code:

SELECT substring(LOGININFO, charindex('+userid=', LOGININFO) + len('+userid='), charindex(';', LOGININFO) - charindex('+userid=', LOGININFO) - len('+userid='))

FROM TABLENAME

WHERE LOGININFO IS NOT NULL

However, even after loving around with the query I keep getting errors:
Invalid length parameter passed to the LEFT or SUBSTRING function.

# ? Sep 17, 2014 00:39

Sedro: Dec 31, 2008

quote:

charindex(';', LOGININFO)

That's going to resolve to the location of the first semicolon, so your resulting length is probably negative.

Edit: I would recommend solving this problem with baby steps. For example, confirm that your start index is correct

SQL code:

SELECT charindex('+userid=', LOGININFO) + len('+userid=')
FROM TABLENAME
WHERE LOGININFO = 'SOME KNOWN VALUE'

Then run a query that returns the length, then start putting it all together.

Edit2: then fix all your off-by-one errors

Sedro fucked around with this message at 01:45 on Sep 17, 2014

# ? Sep 17, 2014 01:40

BabyFur Denny: Mar 18, 2003

substring_index(substring_index(LOGININFO,'+userid=',-1),';',1)

# ? Sep 17, 2014 11:35

Necc0: Jun 30, 2005; by exmarx; Broken Cake

I'm an SQL scrub who's starting to write somewhat detailed reports. This is quickly getting into things more complex than your basic select and join statements so I need some help. The point of this query is to look at all records in the past three months divided into individual months, then further grouped into each unique combination of sub-business and importer. It will then find a percentage of which of those are of one value vs. another value. Right now just summing up how many of them are 'HOLD' should be fine.

This is the query I have now:

code:

select TO_CHAR(TO_DATE(extract(MONTH FROM TX.CREATED_DATE), 'MM'), 'MONTH') MONTH_NAME, de2.flexfield_var3, de2.flexfield_var7, 
sum(case when tx.IM_STATUS = 'HOLD' then 1 else 0 end) AS HOLDS
from GETAADM.mdi_tx tx
inner join GETAADM.mdi_tx_de de2 on tx.alt_key_tx = de2.alt_key_tx and de2.group_name ='IMPL_TX:DEFAULT' AND de2.flexfield_var3 is not null AND de2.flexfield_var7 is not null
where tx.TX_CATEGORY = 'SHIPMENT' AND extract(MONTH FROM tx.CREATED_DATE) >= extract(MONTH FROM SYSDATE)-3
GROUP BY tx.CREATED_DATE, de2.flexfield_var3, de2.flexfield_var7;

I understand that my problem is the grouping function is ONLY looking at the 'HOLDS' column so all I'm getting is a bunch of 1s and 0s. How do I get it to sum up all the 'HOLD' values where the month, and var3/var7 fields are equal?

# ? Sep 17, 2014 14:46

Withnail: Feb 11, 2004

Our Oracle databases are set up to lock accounts after a certain number of failed attempts. This means that anyone who can find the database connection information (without the password) can take down all of our production systems. Is this pretty standard or should there be some other security measure to block the person trying to login, instead of locking the account.

# ? Sep 17, 2014 15:50

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

~~System~~Service accounts should have a custom database password profile that sets FAILED_LOGIN_ATTEMPTS to a high or UNLIMITED number. You should also enable auditing of failed login attempts and ensure that PASSWORD_VERIFY_FUNCTION is set to an appropriately complex level for those accounts.

One Swell Foop fucked around with this message at 16:44 on Sep 17, 2014

# ? Sep 17, 2014 16:25

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

Necc0 posted:

I understand that my problem is the grouping function is ONLY looking at the 'HOLDS' column so all I'm getting is a bunch of 1s and 0s. How do I get it to sum up all the 'HOLD' values where the month, and var3/var7 fields are equal?

code:

select TO_CHAR(TO_DATE(extract(MONTH FROM TX.CREATED_DATE), 'MM'), 'MONTH') MONTH_NAME, 
	de2.flexfield_var3, 
	de2.flexfield_var7, 
	sum(case when tx.IM_STATUS = 'HOLD' then 1 else 0 end) AS HOLDS
from GETAADM.mdi_tx tx
	inner join GETAADM.mdi_tx_de de2 
		on tx.alt_key_tx = de2.alt_key_tx 
where tx.TX_CATEGORY = 'SHIPMENT' 
	AND extract(MONTH FROM tx.CREATED_DATE) >= extract(MONTH FROM SYSDATE)-3
	and de2.group_name ='IMPL_TX:DEFAULT' 
	AND de2.flexfield_var3 is not null 
	AND de2.flexfield_var7 is not null
-- next line changed to use exact column from the select clause in group clause
GROUP BY TO_CHAR(TO_DATE(extract(MONTH FROM TX.CREATED_DATE), 'MM'), 'MONTH'), 
	de2.flexfield_var3, 
	de2.flexfield_var7;

The join conditions in the JOIN clause are clearer if they're put into the WHERE clause instead. Most databases will apply the where clauses to the second table before the join so it comes out the same way but is more readable and SQL-y this way.

I'm not 100% sure but I think the grouping condition on your calculated month field could be the issue, can you try the change above?

# ? Sep 17, 2014 16:36

Necc0: Jun 30, 2005; by exmarx; Broken Cake

One Swell Foop posted:

code:
select TO_CHAR(TO_DATE(extract(MONTH FROM TX.CREATED_DATE), 'MM'), 'MONTH') MONTH_NAME, 
	de2.flexfield_var3, 
	de2.flexfield_var7, 
	sum(case when tx.IM_STATUS = 'HOLD' then 1 else 0 end) AS HOLDS
from GETAADM.mdi_tx tx
	inner join GETAADM.mdi_tx_de de2 
		on tx.alt_key_tx = de2.alt_key_tx 
where tx.TX_CATEGORY = 'SHIPMENT' 
	AND extract(MONTH FROM tx.CREATED_DATE) >= extract(MONTH FROM SYSDATE)-3
	and de2.group_name ='IMPL_TX:DEFAULT' 
	AND de2.flexfield_var3 is not null 
	AND de2.flexfield_var7 is not null
-- next line changed to use exact column from the select clause in group clause
GROUP BY TO_CHAR(TO_DATE(extract(MONTH FROM TX.CREATED_DATE), 'MM'), 'MONTH'), 
	de2.flexfield_var3, 
	de2.flexfield_var7;
The join conditions in the JOIN clause are clearer if they're put into the WHERE clause instead. Most databases will apply the where clauses to the second table before the join so it comes out the same way but is more readable and SQL-y this way.

I'm not 100% sure but I think the grouping condition on your calculated month field could be the issue, can you try the change above?

Exactly what I needed, thanks a bunch :toot:

# ? Sep 17, 2014 16:38

raej: Sep 25, 2003; "Being drunk is the worst feeling of all. Except for all those other feelings."

Is there a way to concatenate a string onto a count(*)?

For instance:

code:

SELECT 'There are ' + count(*) + ' error(s)' FROM table WHERE errors='Y';

Desired output would be "There are 7 error(s)"

# ? Sep 17, 2014 21:06

Necc0: Jun 30, 2005; by exmarx; Broken Cake

raej posted:

Is there a way to concatenate a string onto a count(*)?

For instance:
code:
SELECT 'There are ' + count(*) + ' error(s)' FROM table WHERE errors='Y'; 
Desired output would be "There are 7 error(s)"

Use pipes || to concatenate strings. Or you can use a concat() function depending on what you're using.

# ? Sep 17, 2014 21:08

raej: Sep 25, 2003; "Being drunk is the worst feeling of all. Except for all those other feelings."

Perfect, thanks!

# ? Sep 17, 2014 21:26

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

raej posted:

Is there a way to concatenate a string onto a count(*)?

For instance:
code:
SELECT 'There are ' + count(*) + ' error(s)' FROM table WHERE errors='Y'; 
Desired output would be "There are 7 error(s)"

If you want to get all anal (mssql where the + operator works but have to convert):

code:

select
 CASE WHEN COUNT(*)=1 THEN 'There is ' + CONVERT(varchar(16),COUNT(*)) + ' error.'
 ELSE 'There are ' + CONVERT(varchar(16),COUNT(*)) + ' errors.' END
 from Table

# ? Sep 17, 2014 22:46

Weaponized Autism: Mar 26, 2006; All aboard the Gravy train!; Hair Elf

BabyFur Denny posted:

substring_index(substring_index(LOGININFO,'+userid=',-1),';',1)

Sorry should have mentioned this is SQL Server, substring_index doesn't work.

Sedro posted:

That's going to resolve to the location of the first semicolon, so your resulting length is probably negative.

Edit: I would recommend solving this problem with baby steps. For example, confirm that your start index is correct
SQL code:
SELECT charindex('+userid=', LOGININFO) + len('+userid=')
FROM TABLENAME
WHERE LOGININFO = 'SOME KNOWN VALUE'
Then run a query that returns the length, then start putting it all together.

Edit2: then fix all your off-by-one errors

I managed to get this to work for certain data with a function I wrote. Turns out the "userid" is not always in the same spot and in those cases I kept getting errors. Since I am importing this data into SQL Server via LogParser (as I am working with IIS logs), LogParser actually provides a really simple way of extracting out the userid from the cookie column (LOGININFO in my case). Makes things simpler if I just make an entirely new column for USERID.

Oddly enough, this still does not always work! In cases where "userid" is the first text in the cookie column, LogParser just ignores it and a NULL gets written to my new USERID column. Luckily in those cases, since I know where userid is going to be in the data set, I can use charindex/substring to pull out the information and use a simple UPDATE to populate my column. A combination of using LogParser for the bulk of the user_ids and a manual fix for the remaining problematic ones ultimately gives me what I want.

# ? Sep 18, 2014 00:13

joebuddah: Jan 30, 2005

Is there an easier way of storing this data?
I'm working on a database for my family. I want to store the people who rsvped would be assigned a certain thing ex:
Event: Fourth of July
year: 2099
Rsvp Supplies

John Doe. paper plates
Peggy Sue. soda

I currently have a table for each holiday ( fourth of July, Christmas etc)
An rsvp and supply table for each holiday as the number of people invited depends on the holiday
.
Currently I query the holiday by year, then merge the rsvp and supply

# ? Sep 18, 2014 09:16

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

joebuddah posted:

Is there an easier way of storing this data?
I'm working on a database for my family. I want to store the people who rsvped would be assigned a certain thing ex:
Event: Fourth of July
year: 2099
Rsvp Supplies

John Doe. paper plates
Peggy Sue. soda

I currently have a table for each holiday ( fourth of July, Christmas etc)
An rsvp and supply table for each holiday as the number of people invited depends on the holiday
.
Currently I query the holiday by year, then merge the rsvp and supply

When you say "table for each holiday", do you mean there is a table for the Fourth of July and then another table for Christmas? Because this is wrong. There should be one table that stores all of the holidays and one table that stores all of the RSVPs (with the columns being holiday and person, and with one row per person per holiday). Supplies can be stored as a column in the RSVP table if either Supplies is just a free text field, i.e. one person might have "paper plates" and another person might have "soda, napkins, other crap", or each person can only have one supply assigned to them.

# ? Sep 18, 2014 14:57

IAmKale: Jun 7, 2007; やらないか; Fun Shoe

I'm working on an internal, mysql-powered database for one of our users to log workplace accidents. I have the database up and running, and I have the tables and query all set up and good to go. Before I resort to writing my own frontend, I wanted to see if there's anything pre-existing I could use to generate one. Ideally it'd be something simple enough for the (somewhat older) user to get used to. And since the query utilizes several JOINs, it'd be great if the "Add Entry" window of whatever frontend showed the JOINed table's Name column instead of a bunch of IDs. Worst case scenario I'll have to write something up myself.

The trickiest part is getting the information into the database. The user has expressed a desire to generate charts and graphs in Excel so I'll use the ODBC drivers to map a connection they can use for that purpose.

# ? Sep 18, 2014 22:39

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

Karthe posted:

I'm working on an internal, mysql-powered database for one of our users to log workplace accidents. I have the database up and running, and I have the tables and query all set up and good to go. Before I resort to writing my own frontend, I wanted to see if there's anything pre-existing I could use to generate one.

I haven't used either myself but Xataface and VFront both look like they could be good free options for a web UI over tables.

Karthe posted:

And since the query utilizes several JOINs, it'd be great if the "Add Entry" window of whatever frontend showed the JOINed table's Name column instead of a bunch of IDs. Worst case scenario I'll have to write something up myself.

I think MySQL supports writable views, so you should be able to define a front end over a view rather than just a table to get this behaviour.

# ? Sep 18, 2014 23:59

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

If I have data that consists of an email address, a severity (low, medium, high, critical) and contact type (phone, chat, email):

code:

email                           severity     type
[email]me@home.com[/email]      low          chat
[email]me@home.com[/email]      low          email
[email]you@gmail.com[/email]    high         phone
[email]you@gmail.com[/email]    critical     phone
[email]you@gmail.com[/email]    low          email
[email]hello@qwe.com[/email]    high         email
[email]hello@qwe.com[/email]    high         chat
[email]hello@qwe.com[/email]    critical     phone
[email]bob@qwerty.com[/email]   low          phone
[email]bob@qwerty.com[/email]   low          phone
[email]bob@qwerty.com[/email]   high         chat

What is the most efficient way to create a nice tabular result that groups by email and severity with columns for each contact type?

code:

email                           severity    chat     phone    email
[email]me@home.com[/email]      low          1        0         1
[email]you@gmail.com[/email]    high         0        1        0
[email]you@gmail.com[/email]    critical     0        1        0
[email]you@gmail.com[/email]    low          0        0        1
[email]hello@qwe.com[/email]    high         1        0        1
[email]hello@qwe.com[/email]    critical     0        1        0
[email]bob@qwerty.com[/email]   low          0        2        0
[email]bob@qwerty.com[/email]   high         1        0        0

Because I'm lazy, I'd normally run three queries (select email, severity, count(1) as TYPE where type = "TYPE"), dump the results into a temporary table and then join all three on email, severity to generate the three columns. However, the database I'm working on as an end user prohibits temp tables so I need to write the query like a grownup.

Help?

Agrikk fucked around with this message at 19:26 on Sep 19, 2014

# ? Sep 19, 2014 19:23

BabyFur Denny: Mar 18, 2003

a sum(type='chat') does the trick.

# ? Sep 19, 2014 19:37

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

BabyFur Denny posted:

a sum(type='chat') does the trick.

So is it as simple as

code:

select email, severity, sum(type='chat') as chat, sum(type='email') as email, sum(type='phone') as phone
from table
group by email, severity

# ? Sep 19, 2014 19:49

BabyFur Denny: Mar 18, 2003

Exactly. Some stricter versions of SQL than mySQL might require you to convert the boolean to an int first, dunno. A sum(if(type='chat',1,0)) would work in any case.

# ? Sep 19, 2014 20:09

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

Well drat... that worked perfectly. Easy as pie.

Thanks!

# ? Sep 19, 2014 20:54

PapFinn: Jul 15, 2003; I am Ron Pearlman's illigitimate love child.

I am looking for the proper way, if one exists, to join an application users table and HR table to determine if terminated users have an active application account.

The issue is that the HR system has multiple records per user for each job. For users with multiple jobs,
1. If both are active, return either record.
2. If both are terminated, return either record.
3. If there is one active and one termed record, return the active record.

This is currently accomplished by exporting the HR table to excel, sorting by Status, and then using a VLOOKUP to pull the needed records. I need to see NULL matches as well, since there are also non-employee users in the application who will not be in the HR table.

Obviously a LEFT JOIN will not work since I am then duplicating records for users with multiple jobs like so:

code:

USER_CODE	USER_NAME	GROUP_CODE	NetworkID	FirstName	LastName	EmploymentStatus	TerminationDate
jdoe99		John Doe	grp1		jdoe99		John		Doe		ACTIVE			NULL
jdoe99		John Doe	grp1		jdoe99		John		Doe		Terminated		12/17/2013
jdoe99		John Doe	grp2		jdoe99		John		Doe		ACTIVE			NULL
jdoe99		John Doe	grp2		jdoe99		John		Doe		Terminated		12/17/2013

I would only want to see 2 records, one for each unique GROUP_CODE with both showing the ACTIVE Status from HR. I'm not really looking for the exact code as much as some advice on whether or not there is even a valid solution to this and what that might be. I'm looking at temp tables and sub-query solutions and keep running into a wall.

# ? Sep 23, 2014 19:51

Anaxandrides: Jun 7, 2012; Every summer day in the desert is like a vacation in hell.

PapFinn posted:

I am looking for the proper way, if one exists, to join an application users table and HR table to determine if terminated users have an active application account.

The issue is that the HR system has multiple records per user for each job. For users with multiple jobs,
1. If both are active, return either record.
2. If both are terminated, return either record.
3. If there is one active and one termed record, return the active record...

It looks to me like you can solve this pretty easily. Throw this into a cte (Common Table Expression) or a temp table, and slap a RANK() OVER(PARTITION BY USER_CODE, GROUP_CODE, ORDER BY EmploymentStatus), then select WHERE Rank = 1.

# ? Sep 23, 2014 20:39

Adbot: ADBOT LOVES YOU

# ? Apr 27, 2024 00:09

PapFinn: Jul 15, 2003; I am Ron Pearlman's illigitimate love child.

Anaxandrides posted:

It looks to me like you can solve this pretty easily. Throw this into a cte (Common Table Expression) or a temp table, and slap a RANK() OVER(PARTITION BY USER_CODE, GROUP_CODE, ORDER BY EmploymentStatus), then select WHERE Rank = 1.

Awesome. I'll look into a solution using this method.

# ? Sep 23, 2014 22:48

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

«‹›303 »