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 »

Fuck them: Jan 21, 2011; and their bullshit

Got a bit of a hiccup, including the fact that no, this is a 2005 server, not 2008

It's a MSSQL Server and I have a table of entry IDs (not unique) which can join on a table which also has revision IDs, which are very much unique.

Well joining them is easy enough. But once I've done that, I want to kick out rows with duplicate EntryIDs based on the fact that I only want the most recent (so, highest valued) RevisionID.

:downs:

Edit: My procedural brain is saying "Order by Entry ID. If more than one exists, then go pick the highest valued RevisionID and the others that match should be discarded."

But I don't want to just kick this poo poo out and do a loop before passing it along the rest of the way, I want to do it the right way in SQL.

How should I be thinking about this?

Edit2: Welp here's a lead

ANSWER:

I used a subselect, rank(), and OVER.

Basically:

code:

SELECT * 
FROM   (SELECT SI.entryid, 
               foo, 
               bar,
	       baz,
               Rank() 
                 OVER ( 
                   partition BY SI.entryid 
                   ORDER BY revisionid DESC ) N 
        FROM   dbo.index SI 
               INNER JOIN dbo.revisions SR 
                       ON SI.entryid = SR.entryid) M 
WHERE  n = 1

Could I do better?

Fuck them fucked around with this message at 21:00 on Sep 26, 2014

# ? Sep 26, 2014 20:28

Adbot: ADBOT LOVES YOU

# ? Apr 25, 2024 10:41

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

gently caress them posted:

ANSWER:

I used a subselect, rank(), and OVER.

Basically:

code:

SELECT * 
FROM   (SELECT SI.entryid, 
               foo, 
               bar,
	       baz,
               Rank() 
                 OVER ( 
                   partition BY SI.entryid 
                   ORDER BY revisionid DESC ) N 
        FROM   dbo.index SI 
               INNER JOIN dbo.revisions SR 
                       ON SI.entryid = SR.entryid) M 
WHERE  n = 1

Could I do better?

99% of the time, when your answer contains "I used a subselect", it could be done better. Subselects are bad for readability, even if not bad for performance. And often bad for both. For the purposes of what you're doing, you shouldn't need any meta columns (RANK, ROW_NUMBER, etc). Assuming a clustered PK on ID columns, I would write...

code:

WITH cte(EntryID, MaxRevisionID)
AS (SELECT si.EntryID,
	   MAX(RevisionID) AS MaxRevisionID
    FROM dbo.index si
	JOIN dbo.revisions r
		ON si.EntryID = r.EntryID)
SELECT s.EntryID,
       foo,
       bar,
       baz
FROM cte c
	JOIN dbo.index s
		ON c.EntryID = s.EntryID
	JOIN dbo.revisions r
		ON s.EntryID = r.EntryID
WHERE RevisionID = MaxRevisionID

Without knowing your schema exactly, I would expect that to be more efficient. MAX(etc) is a really fast aggregate if done with proper indexes, and you're not making SQL compile a bunch of poo poo you don't care about (foo, bar, baz) for results you don't need -- only what you do need.

# ? Sep 27, 2014 20:30

Vesi: Jan 12, 2005; pikachu looking at?

Working with Sqlite:
I have a set of decks each containing 0-n cards, and I want to select a random card from a certain subset of "checked" decks

code:

CREATE TABLE cards(card_id INTEGER, deck_id INTEGER);
CREATE TABLE decks(deck_id INTEGER, checked INTEGER DEFAULT 1);

I can select a random card easily enough if I don't take into account the checked status of the deck:

code:

SELECT card_id FROM cards ORDER BY RANDOM() LIMIT 1;

but I'm struggling to find an elegant solution to selecting a card from a checked (checked not 0) but non-empty deck (empty meaning there's zero cards.deck_id matching decks.deck_id)

I'd rather not store the number of cards in the decks table, or checked status in the cards table as that would violate normalization.

Vesi fucked around with this message at 13:59 on Sep 28, 2014

# ? Sep 28, 2014 13:57

Nam Taf: Jun 25, 2005; I am Fat Man, hear me roar!

I don't know if I'm misunderstanding, but are you trying to:

SELECT card_id
FROM cards
WHERE card_id not in (
SELECT card_id
FROM decks
WHERE checked not 0)

?

idk if this works but logically is that what you're trying to do?

# ? Sep 28, 2014 15:52

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

Vesi posted:

Working with Sqlite:
I have a set of decks each containing 0-n cards, and I want to select a random card from a certain subset of "checked" decks
code:
CREATE TABLE cards(card_id INTEGER, deck_id INTEGER);
CREATE TABLE decks(deck_id INTEGER, checked INTEGER DEFAULT 1);
I can select a random card easily enough if I don't take into account the checked status of the deck:
code:
SELECT card_id FROM cards ORDER BY RANDOM() LIMIT 1;
but I'm struggling to find an elegant solution to selecting a card from a checked (checked not 0) but non-empty deck (empty meaning there's zero cards.deck_id matching decks.deck_id)

I'd rather not store the number of cards in the decks table, or checked status in the cards table as that would violate normalization.

Does this not work?

code:

SELECT c.card_id
FROM decks d
    JOIN cards c
        ON d.deck_id = c.deck_id
WHERE d.checked = 1
ORDER BY RANDOM()
LIMIT 1

# ? Sep 28, 2014 19:42

Vesi: Jan 12, 2005; pikachu looking at?

Anaxandrides posted:

Does this not work?

code:

SELECT c.card_id
FROM decks d
    JOIN cards c
        ON d.deck_id = c.deck_id
WHERE d.checked = 1
ORDER BY RANDOM()
LIMIT 1

that works perfectly thank you!

# ? Sep 28, 2014 20:10

Master_Odin: Apr 15, 2010; My spear never misses its mark...

ladies

I have two tables:
Table A
- a_id
- a_number
- a_code

Table B
- b_id
- a_id
- b_checkpoint
- b_value
- user_id

Table B will have multiple rows per a_id per user. Sometimes, there is no rows in B for a_id for a particular user (but the user will have other ones).

My base query is:

code:

SELECT user_id,sum(b_value) FROM Table_B GROUP BY a_id, user_id ORDER BY user_id

and it works, except requires code to check to see what ids are missing from the response and deal with it.

I get I need to use a LEFT JOIN, but I'm a bit lost on how I want to do this.

# ? Sep 29, 2014 21:54

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

Master_Odin posted:

I have two tables:
Table A
- a_id
- a_number
- a_code

Table B
- b_id
- a_id
- b_checkpoint
- b_value
- user_id

Table B will have multiple rows per a_id per user. Sometimes, there is no rows in B for a_id for a particular user (but the user will have other ones).

My base query is:
code:
SELECT user_id,sum(b_value) FROM Table_B GROUP BY a_id, user_id ORDER BY user_id
and it works, except requires code to check to see what ids are missing from the response and deal with it.

I get I need to use a LEFT JOIN, but I'm a bit lost on how I want to do this.

Using MSSQL?

code:

SELECT a_id,
       bs.TotalValue
FROM table_a a
OUTER APPLY
(
    SELECT SUM(b_value) AS TotalValue
    FROM table_b b
    WHERE bs.a_id= a.a_id
) bs

# ? Sep 29, 2014 22:18

Master_Odin: Apr 15, 2010; My spear never misses its mark...

ladies

Anaxandrides posted:

Using MSSQL?

Whoops, left that out.

It's Postgres so no Outer Apply. Also, I'd want to join a and b on a_id, not b.user_id=a.a_id

# ? Sep 29, 2014 22:23

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

Master_Odin posted:

Whoops, left that out.

It's Postgres so no Outer Apply. Also, I'd want to join a and b on a_id, not b.user_id=a.a_id

Yeah, I edited the join afted posting. For Postgres, it looks like this would work (per their docs):

code:

SELECT a_id,
       bs.TotalValue
FROM table_a a
LEFT JOIN LATERAL
each(
    SELECT SUM(b_value) AS TotalValue
    FROM table_b b
    WHERE b.a_id= a.a_id
) AS bs
     ON a.a_id = bs.a_id

I haven't worked that much with Postgres, though, so take it with a grain of salt.

# ? Sep 29, 2014 22:59

FieryBalrog: Apr 7, 2010; Grimey Drawer

Gatac posted:

Hey guys, got a newbie question regarding PL/SQL. Suppose I've got a procedure that's got a fairly complex SELECT to query the data I want to work on, and I'd like to store the results for the runtime of the procedure so I can then run further queries on that. I've done some googling about temporary tables and such and supposedly on an MS system, building temporary tables at runtime is no big deal, but Oracle doesn't support it. The consensus pretty much seems to be "It's not supposed to work that way, don't do it", followed by (what looks to me) horribly kludge-y ways to get it done anyway by explicitly creating, filling and then dropping a table.

I could just have the explicit query in there multiple times, but that seems inelegant when I really just need a way to say "Okay, remember this poo poo we did at the beginning? We're working with that again." Or wrap the whole thing up in an FOR loop so I can refer back to that outer query inside the loop however I want. But it feels like there's probably a better solution than what a newbie with Google can figure out. But even if there isn't, at this stage I'm mostly just genuinely curious: 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?

This is the worst thing about PL/SQL.

"We don't need easy temp tables! Write better SQL!" *implements half a dozen kludgy temp-table workarounds*

I spent more time than I care to admit trying to get Oracle's lovely implementation of nested tables to work. The following basic query is completely unreliable in terms of performance:

code:

select * into x_abc_row from view_abc where some_id in (select column_value from table(x_nested_table))

Where the nested table is meant to be the driver. It's a complete crapshoot whether the optimizer decides to treat this as a good driving set or whether it will make some nonsensical assumption about the cardinality of the nested table and gently caress up its access path. Or just fail to push the predicate through the view because of (insert a number of arbitrary reasons). Sometimes it will push the predicate if its a regular table join but not if it's a pseudo-table join like this one. We have sometimes had to resort to creating dynamic SQL to generate literal in-list predicates (yes, really) so that the optimizer doesn't throw a tantrum.

This comes up all the time when like you said, a stored procedure might have an initial complex query that produces a working set of rows, and subsequent logic in the procedure will operate on that working set only. So it would, in theory, be very helpful to restrict the access/updates/operations of that subsequent logic to the set of primary keys in the working set, i.e. to precompute a series of accesses. In practice, Oracle DB often fails to make proper use of information like that if its not presented as a literal in-list (as in, actually use the info to drive access).

FieryBalrog fucked around with this message at 13:29 on Oct 6, 2014

# ? Oct 6, 2014 13:15

murkdotdoc: Oct 26, 2005

I've been tasked with converting a client's current Oracle based system into our SQL Server 2008 system. I'm much stronger in SQL Server and quite weak in Oracle so any input would be great.

The Oracle system was exported using the expdp utility from an 11g database. We have a license for the Oracle Standard Edition. My plan is to use the impdp utility to pull in the schema/tables/data only then use SQL Server Migration Assistant to pull the tables/data into SQL Server and work on the mapping with the client. Sounds simple enough, but what am I missing? Here's the pieced together command I'm working on in a Windows Server 2008 environment:

impdp <user>/<pass>@orcl directory=data_pump_dir dumpfile=export01.dmp,export02.dmp logfile=import.log full=y

I'm worried the character set was different (and potentially other settings) and also that the old Oracle environment was on a Unix platform instead of Windows. Is there any way I can pull in only the tables/data? Is there a better approach to this?

# ? Oct 7, 2014 04:12

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

murkdotdoc posted:

I've been tasked with converting a client's current Oracle based system into our SQL Server 2008 system. I'm much stronger in SQL Server and quite weak in Oracle so any input would be great.

The Oracle system was exported using the expdp utility from an 11g database. We have a license for the Oracle Standard Edition. My plan is to use the impdp utility to pull in the schema/tables/data only then use SQL Server Migration Assistant to pull the tables/data into SQL Server and work on the mapping with the client. Sounds simple enough, but what am I missing? Here's the pieced together command I'm working on in a Windows Server 2008 environment:

impdp <user>/<pass>@orcl directory=data_pump_dir dumpfile=export01.dmp,export02.dmp logfile=import.log full=y

I'm worried the character set was different (and potentially other settings) and also that the old Oracle environment was on a Unix platform instead of Windows. Is there any way I can pull in only the tables/data? Is there a better approach to this?

Honestly, I'd use the SSIS Import/Export Data wizard to pull from Oracle to MSSQL. It'll create the tables for you on-the-fly, and pull the data right along with it.

# ? Oct 7, 2014 18:35

murkdotdoc: Oct 26, 2005

Anaxandrides posted:

Honestly, I'd use the SSIS Import/Export Data wizard to pull from Oracle to MSSQL. It'll create the tables for you on-the-fly, and pull the data right along with it.

I usually use SSIS for flat file, Excel or Access imports and it works great. All I have are two .dmp files from the Oracle database though - I'm assuming the data source would be "Microsoft OLE DB Provider for Oracle"? I'll check around to see what I need to install to make this work but I get a feeling there's more to it than that. Or is there something in SSIS I'm missing?

Edit: I think what you're saying is get the data into Oracle and use SSIS to pull the data from the Oracle db, not import the dump files directly into MSSQL - sorry about that.

murkdotdoc fucked around with this message at 19:08 on Oct 7, 2014

# ? Oct 7, 2014 19:02

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

murkdotdoc posted:

I usually use SSIS for flat file, Excel or Access imports and it works great. All I have are two .dmp files from the Oracle database though - I'm assuming the data source would be "Microsoft OLE DB Provider for Oracle"? I'll check around to see what I need to install to make this work but I get a feeling there's more to it than that. Or is there something in SSIS I'm missing?

Edit: I think what you're saying is get the data into Oracle and use SSIS to pull the data from the Oracle db, not import the dump files directly into MSSQL - sorry about that.

Yeah, I assumed it was already in Oracle, not present in two .dmp files.If you don't have an Oracle setup at all, I'm not aware of a magic bullet.

# ? Oct 8, 2014 02:20

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

Another stupid out-there newbie question. Using PL/SQL Developer 10, I've been messing around with substitution variables in a script so I get a nice data entry popup at runtime. Now there's plenty of options to configure that (drop lists, checkboxes, value restrictions, required fields etc), but I'm wondering: is it possible to rig this up in a way that I can lock out one variable depending on the selection in another field?

For example, say I have this:

&<name="Date">

and

&<name="Cutoff by Date"
list="0, No, 1, Yes"
description="yes"
restricted="yes">

If I enter a date, I want to be able to choose whether to use it as cutoff or not. However, if I leave it blank, the choice is meaningless and I'd prefer to lock it to No. I have seen the options to make a variable read-only and to supply a default value, but these can only be predefined, not made conditional. I've also found that you can base the selection for one variable on another by using a SELECT with a bind variable, but unfortunately, PL/SQL Developer only executes this query when there's actually something in that bind variable - if it's a NULL (by leaving the field blank) the SELECT doesn't run. If it did, I'm pretty sure I could figure out how to build the selection so there's only one option, which would work well enough for my purposes, but as it is I'm a little stumped.

It's not essential by any means - I'm honestly positively surprised at how indepth the existing options are - but if it's possible I'd like to know how.

# ? Oct 8, 2014 17:33

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I'm struggling to compare some rate records in our database and need a nudge in the right direction. I think it can be solved joining the table with itself but as I am a total novice (I've written a few nested selects and that's about the extent of my experience, so for this question Type is most certainly equal to Stupid) I'm at a loss how to proceed! Essentially I'm trying to pull a list of accounts where our customer has lapsed their contracted rate for some amount of time, so instead of receiving a contracted rate they instead receive a default. Rate table structure is (roughly) below:

code:


RateTypeKey (PK)  |  Account#  |  EffBeginDate  |  EffEndDate  | RateType
       1             12345        2013-01-01       2013-12-31    Fixed
       3             12345        2014-01-01       2050-12-31    Default
       5             12345        2014-02-01       2015-01-31    Fixed
       2             67890        2013-01-01       2013-12-31    Fixed
       4             67890        2014-01-01       2050-12-31    Default
       6             67890        2014-01-01       2014-12-31    Fixed

(obviously there's more but I don't much care about it)

Since 12345's newest fixed rate begins a full month after the previous expired, I'd like it to be pulled, and since 67890's started immediately after the previous one, it would need to be excluded, regardless of the fact that 67890 still has that default in there. My problem is I'm not sure how to set it up to compare the dates.

code:

SELECT * 
FROM   dbo.ratetype rt1 
JOIN   dbo.ratetype rt2 
ON     rt1.ratetypekey = rt2.ratetypekey 
WHERE  rt1.ratetype != 'Default' 
AND    ???

Am I even headed in the right direction or is there some much easier way to do this? Obviously I can't just do something like add an "and rt2.EffBeginDate > rt1.EffEndDate" as that's just comparing the record with itself but poo poo I feel like this shouldn't be this hard!! Thank you in advance for anyone who can shed some light!

# ? Oct 13, 2014 21:03

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

Kumbamontu posted:

code:
SELECT * 
FROM   dbo.ratetype rt1 
JOIN   dbo.ratetype rt2 
ON     rt1.ratetypekey = rt2.ratetypekey 
WHERE  rt1.ratetype != 'Default' 
AND    ???
Am I even headed in the right direction or is there some much easier way to do this? Obviously I can't just do something like add an "and rt2.EffBeginDate > rt1.EffEndDate" as that's just comparing the record with itself but poo poo I feel like this shouldn't be this hard!! Thank you in advance for anyone who can shed some light!

Try this:

code:

;with cte (Account#, EffBeginDate, EffEndDate, DateRank)
AS (SELECT Account#, EffBeginDate, EffEndDate, DENSE_RANK() OVER(PARTITION BY Account# ORDER BY EffBeginDate)
    FROM dbo.RateType
    WHERE RateType = 'Fixed')
SELECT
FROM cte c
    JOIN cte c2
        ON c.Account# = c2.Account#
WHERE c.EffEndDate < c2.EffBeginDate
       AND c.DateRank = c2.DateRank -1

# ? Oct 14, 2014 18:22

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Anaxandrides posted:

Try this:

code:

;with cte (Account#, EffBeginDate, EffEndDate, DateRank)
AS (SELECT Account#, EffBeginDate, EffEndDate, DENSE_RANK() OVER(PARTITION BY Account# ORDER BY EffBeginDate)
    FROM dbo.RateType
    WHERE RateType = 'Fixed')
SELECT
FROM cte c
    JOIN cte c2
        ON c.Account# = c2.Account#
WHERE c.EffEndDate < c2.EffBeginDate
       AND c.DateRank = c2.DateRank -1

This was the nudge I needed in the right direction, thank you! What I ultimately ended up going with was (account# is ieinum and is actually in a different table hence the extra join):

code:

USE ny; 

WITH ratecomparison (ieinum, effbegindate, effenddate, daterank) 
     AS (SELECT DISTINCT sa.ieinum, 
                         rt.effbegindate, 
                         rt.effenddate, 
                         Dense_rank() 
                           OVER( 
                             partition BY sa.ieinum 
                             ORDER BY effbegindate) 
         FROM   dbo.ratetype rt 
                JOIN dbo.serviceaddr sa 
                  ON rt.serviceaddrid = sa.serviceaddrkey 
         WHERE  rt.ratetype NOT IN ( 'Default', 'Intro', 'VEREM2M', 'VEPTM2M' )) 
SELECT * 
FROM   ratecomparison rc1 
       JOIN ratecomparison rc2 
         ON rc1.ieinum = rc2.ieinum 
WHERE  rc1.effenddate + 1 < rc2.effbegindate 
       AND rc1.daterank = rc2.daterank - 1 
ORDER  BY rc2.effbegindate DESC, 
          rc2.ieinum

One other newbie question: the above query runs in 2 seconds if I omit the DESC in the order by clause, but with DESC it takes 22 seconds. Is there a better less intensive way to get the results like I want them? This is mostly an academic exercise as this isn't going to be a continually run query so I don't particularly care about the 20 seconds, but I'm interested why it's such a performance hit.

# ? Oct 14, 2014 21:30

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

Kumbamontu posted:

One other newbie question: the above query runs in 2 seconds if I omit the DESC in the order by clause, but with DESC it takes 22 seconds. Is there a better less intensive way to get the results like I want them? This is mostly an academic exercise as this isn't going to be a continually run query so I don't particularly care about the 20 seconds, but I'm interested why it's such a performance hit.

Broadly speaking, sorting is expensive for SQL to do, and moreso the larger the record set is. Specifically, in this case, you have one join operating on the common table expression (think of this as a @table being held in memory), and another operating on a second in-memory copy of that table, ordered in a different way. Since it's a @table being held in memory, it has no indexes, and it gets to sort the slow way. There can be no indexes to help it sort, and dates are a complex type. Sorting by an INT DESC, for example, would be really fast.

That's my gut feeling on this one, anyway. It's akin to memorizing a book, then having someone tell you "Now read it backwards and forwards at the same time, and tell me which sentences are the same".

Edit: To tack on, if you want the results ordered in DESC order, my suggestion would be to take the first result set and put it into a #table, then select from that ORDER BY EffBeginDate DESC. I would expect that to be pretty fast, since it's a different execution step at that point,

Anaxandrides fucked around with this message at 23:57 on Oct 14, 2014

# ? Oct 14, 2014 23:55

atmakaraka: Mar 3, 2005; When I said 'we', officer, I was referring to myself, the four young ladies, and, of course, the goat.

So I got assigned the fun task of using 11 year old report building software to build a report for some patient data at work. After a couple weeks of wrangling the software to do what I need, I have it all ready to go, with one exception.

There are a few places where, instead of straight text strings, the information I need is codified as a series of 0s and 1s. ex: 0010111. I can't output that because there's no actual data there, just 0s and 1s (obviously). So I want to turn that into text, where a 0 is blank and a 1 represents a word or string. ex: 0010111 = c, e, f, g or 1101000 = a, b, d.

I'm not familiar enough with SQL to build an expression more complicated than a basic CASE statement. I know that the use of substring(table, 1, 1) = 1 is probably going to be used, but my research isn't giving me anything definitive.

Any ideas?

# ? Oct 15, 2014 17:51

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

atmakaraka posted:

So I got assigned the fun task of using 11 year old report building software to build a report for some patient data at work. After a couple weeks of wrangling the software to do what I need, I have it all ready to go, with one exception.

There are a few places where, instead of straight text strings, the information I need is codified as a series of 0s and 1s. ex: 0010111. I can't output that because there's no actual data there, just 0s and 1s (obviously). So I want to turn that into text, where a 0 is blank and a 1 represents a word or string. ex: 0010111 = c, e, f, g or 1101000 = a, b, d.

I'm not familiar enough with SQL to build an expression more complicated than a basic CASE statement. I know that the use of substring(table, 1, 1) = 1 is probably going to be used, but my research isn't giving me anything definitive.

Any ideas?

What kind of SQL are you using? If it was heavy duty processing then I'd be tempted to create a function to convert the binary string into an integer value and then use bitwise AND to pull out the data values, but if it's not too processor intensive, then you could probably use something like the below (assuming MS T-SQL):

code:

SELECT 	CASE WHEN SUBSTRING('100111',1,1) = '1' THEN 'a,' else '' END +
		CASE WHEN SUBSTRING('100111',2,1) = '1' THEN ' b,' else '' END +
		CASE WHEN SUBSTRING('100111',3,1) = '1' THEN ' c,' else '' END +
		CASE WHEN SUBSTRING('100111',4,1) = '1' THEN ' d,' else '' END +
		CASE WHEN SUBSTRING('100111',5,1) = '1' THEN ' e,' else '' END +
		CASE WHEN SUBSTRING('100111',6,1) = '1' THEN ' f,' else '' END +
		CASE WHEN SUBSTRING('100111',7,1) = '1' THEN ' g,' else '' END AS StringVal
;

Where obviously you'd replace '100111' with the name of your binary string column.

That does leave you with a trailing comma on the string field though; if that's a problem then you're probably looking at something a little more complex like setting up the above as an inline function and doing something like

code:

SELECT SUBSTRING(myBinaryFunc(myString), 1, LEN(myBinaryFunc(myString))-1) AS StringVal;

# ? Oct 15, 2014 18:19

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

If I have a table whose layout is [timestamp],[accountID],[balance] where there are each account has a balance on a given date, what is the most efficient method to produce the following layout:

code:

timestamp  account1   account2  account3
day1         $100       $200      $400
day2         $100       $250      $400
day3         $95        $350      $600

I could create a query that selects distinct timestamps and then runs a subquery for each account that produces a column that I could join back to the timestamp column, but that seems cumbersome and doesn't scale well.

Is there another, faster method?

# ? Oct 15, 2014 18:38

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

Agrikk posted:

If I have a table whose layout is [timestamp],[accountID],[balance] where there are each account has a balance on a given date, what is the most efficient method to produce the following layout:
code:
timestamp  account1   account2  account3
day1         $100       $200      $400
day2         $100       $250      $400
day3         $95        $350      $600
I could create a query that selects distinct timestamps and then runs a subquery for each account that produces a column that I could join back to the timestamp column, but that seems cumbersome and doesn't scale well.

Is there another, faster method?

Is this for an arbitrary amount of accounts, or a known amount?

# ? Oct 15, 2014 18:48

atmakaraka: Mar 3, 2005; When I said 'we', officer, I was referring to myself, the four young ladies, and, of course, the goat.

One Swell Foop posted:

What kind of SQL are you using? If it was heavy duty processing then I'd be tempted to create a function to convert the binary string into an integer value and then use bitwise AND to pull out the data values, but if it's not too processor intensive, then you could probably use something like the below (assuming MS T-SQL):
code:
SELECT 	CASE WHEN SUBSTRING('100111',1,1) = '1' THEN 'a,' else '' END +
		CASE WHEN SUBSTRING('100111',2,1) = '1' THEN ' b,' else '' END +
		CASE WHEN SUBSTRING('100111',3,1) = '1' THEN ' c,' else '' END +
		CASE WHEN SUBSTRING('100111',4,1) = '1' THEN ' d,' else '' END +
		CASE WHEN SUBSTRING('100111',5,1) = '1' THEN ' e,' else '' END +
		CASE WHEN SUBSTRING('100111',6,1) = '1' THEN ' f,' else '' END +
		CASE WHEN SUBSTRING('100111',7,1) = '1' THEN ' g,' else '' END AS StringVal
;
Where obviously you'd replace '100111' with the name of your binary string column.

That does leave you with a trailing comma on the string field though; if that's a problem then you're probably looking at something a little more complex like setting up the above as an inline function and doing something like
code:
SELECT SUBSTRING(myBinaryFunc(myString), 1, LEN(myBinaryFunc(myString))-1) AS StringVal;

That worked flawlessly. Thank you!

We're on SQL Server 2012, but the report building frontend of the EMR software we use was last updated in 2003. In the next month I'll be getting CrystalReports and some paid training, so I'm excited for not having to use the lovely built in stuff.

# ? Oct 15, 2014 19:05

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

Anaxandrides posted:

Is this for an arbitrary amount of accounts, or a known amount?

The initial number is known (~200) but it is expected to grow randomly.

# ? Oct 15, 2014 22:09

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Agrikk posted:

Is there another, faster method?

http://www.postgresql.org/docs/9.1/static/tutorial-window.html

See also, cumulative sum.

vvvv Sorry, I completely misread his original post. Phone doesn't display those code sections well, but it's been my only post/read approach these days. At least it prompted someone to give an answer!

PhantomOfTheCopier fucked around with this message at 05:15 on Oct 16, 2014

# ? Oct 15, 2014 23:32

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

PhantomOfTheCopier posted:

http://www.postgresql.org/docs/9.1/static/tutorial-window.html

See also, cumulative sum.

Not what he's looking for -- he wants a PIVOT, if I understand things correctly.

Agrikk, what flavor of SQL are you using? Postgre, MSSQL, Oracle, MySQL, etc?

# ? Oct 16, 2014 00:50

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

Anaxandrides posted:

Not what he's looking for -- he wants a PIVOT, if I understand things correctly.

Agrikk, what flavor of SQL are you using? Postgre, MSSQL, Oracle, MySQL, etc?

MSSQL. Yeah, from a casual google, a PIVOT is what I'm looking for.

# ? Oct 16, 2014 02:11

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

atmakaraka posted:

That worked flawlessly. Thank you!

We're on SQL Server 2012, but the report building frontend of the EMR software we use was last updated in 2003. In the next month I'll be getting CrystalReports and some paid training, so I'm excited for not having to use the lovely built in stuff.

I thought about this for a little while and on reflection I'd probably use a view to separate the binary digits out into separate columns in this case, but glad it worked out for you.

Agrikk posted:

The initial number is known (~200) but it is expected to grow randomly.

Agrikk posted:

MSSQL. Yeah, from a casual google, a PIVOT is what I'm looking for.

With a regular PIVOT you need to know the account (column) names when you're writing the query, so it's not good (as it stands) for situations when the accounts can change. It is possible to write a Dynamic SQL Pivot to derive the SQL query from the data, but that kind of application (dynamic crosstabs) is getting more into the realms of OLAP (SSAS, on SQL Server).

# ? Oct 16, 2014 06:35

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

So the pseudo bianary guy made me think. At what point do you say gently caress it and write something in java/c# etc rather than huge and complex sql statements. Is there a point? Is there ever a reason to (beyond the absurd)?

# ? Oct 16, 2014 07:37

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

Cast_No_Shadow posted:

So the pseudo bianary guy made me think. At what point do you say gently caress it and write something in java/c# etc rather than huge and complex sql statements. Is there a point? Is there ever a reason to (beyond the absurd)?

Depends on the DB platform and how it integrates with the language of choice. SQL as a language is clunky but under the hood the optimizers are some of the most sophisticated software in the world, so for large data sets it's difficult to beat (although some NoSQL platformw will easily outperform it in specific tasks). That said, for complex logic you're probably better off looking at a higher-level SQL concept like a view, an inline function, or a stored procedure (as they typically still make use of the SQL optimization engine). If you need more complexity than that, then you could go to a higher level language, sure; but it's not always easy to integrate procedural languages efficiently into the set-based paradigm of SQL, especially for large data sets. Also it's often better to stick with a solution that can easily be debugged and optimized using a single platform's tools.

Additionally, it sounds like the binary situation was part of a reporting tool that says 'you can add some custom SQL in here if you're not getting what you want from the defaults', as a lot of reporting tools do; in that case it's easy to one-off code a slightly long-winded SQL command than to try and integrate another language or reporting environment. A lot of business tools understand SQL or a derivative so it's easy to use in-place.

That said, if performance isn't an issue, you're not tied to a reporting tool, you don't need your DBAs to support it, and you're already familiar with Java or C#, then feel free to grab the data from SQL and process it however you see fit.

E:

Moogle posted:

I know this has already been answered, but I saw this after waking up and wanted to put it in one line because reasons.

SELECT STUFF((SELECT ',' + CHAR(96 + (NULLIF(SUBSTRING('10110',n,1) & 1,0) * n)) FROM (VALUES (0),(1),(2),(3),(4)) [mask] (n) FOR XML PATH('')),1,1,'')

That's assuming left to right, but you can just reverse the VALUES figures if you'd like it the other way.

That's really a very pretty solution, and much better than mine for the problem as stated, but it locks you into the a, b, c mapping and I think you'd need a case statement to allow arbitrary strings.

One Swell Foop fucked around with this message at 08:21 on Oct 16, 2014

# ? Oct 16, 2014 08:09

Salt n Reba McEntire: Nov 14, 2000; Kuparp.

atmakaraka posted:

So I got assigned the fun task of using 11 year old report building software to build a report for some patient data at work. After a couple weeks of wrangling the software to do what I need, I have it all ready to go, with one exception.

There are a few places where, instead of straight text strings, the information I need is codified as a series of 0s and 1s. ex: 0010111. I can't output that because there's no actual data there, just 0s and 1s (obviously). So I want to turn that into text, where a 0 is blank and a 1 represents a word or string. ex: 0010111 = c, e, f, g or 1101000 = a, b, d.

I'm not familiar enough with SQL to build an expression more complicated than a basic CASE statement. I know that the use of substring(table, 1, 1) = 1 is probably going to be used, but my research isn't giving me anything definitive.

Any ideas?

I know this has already been answered, but I saw this after waking up and wanted to put it in one line because reasons.

SELECT STUFF((SELECT ',' + CHAR(96 + (NULLIF(SUBSTRING('1011011',n,1) & 1,0) * n)) FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) [mask] (n) FOR XML PATH('')),1,1,'')

That's assuming left to right, but you can just reverse the VALUES figures if you'd like it the other way.

Salt n Reba McEntire fucked around with this message at 08:15 on Oct 16, 2014

# ? Oct 16, 2014 08:13

thegasman2000: Feb 12, 2005; Update my TFLC log? BOLLOCKS!
/

This isnt a DB issue.. I am retarded.

thegasman2000 fucked around with this message at 21:53 on Oct 20, 2014

# ? Oct 20, 2014 21:19

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Are there any best practices to keep in mind when setting up a read-only user account? There should be no difference between using the db_datareader group and "GRANT SELECT TO John", am I correct?

# ? Oct 20, 2014 21:52

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

Pretty much. DB_datareader is a database level permission, GRANT can be super fine-grained if you need it to be. One consideration is that DB_datareader (IIRC) gives read access on the database's system tables and views, so it's probably slightly better practice to create a schema for user tables and "GRANT SELECT ON userschema TO JOHN".

One Swell Foop fucked around with this message at 22:28 on Oct 20, 2014

# ? Oct 20, 2014 22:25

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Thanks. I think I actually want read access on the system stuff in this case, so I can call sys.fn_my_permissions to verify that the credentials being used have been configured correctly (and halt the program otherwise).

# ? Oct 20, 2014 23:41

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

That'd be a "GRANT EXECUTE ON sys.fn_my_permissions". Generally speaking you could also just check to see if the items you need exist and can be read from (wrap "SELECT top 1 'test' AS TEST FROM mytable" in a try/catch). That'd be slightly better practice than providing access to system functions; in fact on reflection it's probably best practice to just wrap every SQL query that you run in a try/catch and handle it that way, as at some point your SQL server isn't going to be available and then at least you've got your handlers in place already.

# ? Oct 21, 2014 00:00

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

One Swell Foop posted:

That'd be a "GRANT EXECUTE ON sys.fn_my_permissions". Generally speaking you could also just check to see if the items you need exist and can be read from (wrap "SELECT top 1 'test' AS TEST FROM mytable" in a try/catch). That'd be slightly better practice than providing access to system functions; in fact on reflection it's probably best practice to just wrap every SQL query that you run in a try/catch and handle it that way, as at some point your SQL server isn't going to be available and then at least you've got your handlers in place already.

Well the primary concern is to make sure that INSERT/UPDATE/DELETE do not work, so running a test SELECT query wouldn't help with that. (I would have to try inserting/updating/deleting a dummy entry in each table and check the error I get - probably not a great idea!)

FWIW, the scenario is that we need to add an audit access option to an existing application, and said access must be 100% read-only for legal reasons (minus log entries). While we're also going to intercept the DB-writing functions in the program itself (so we can display the appropriate "NO YOU CAN'T DO THAT" messages instead of the usual "database error" ones), I want to play it safe by giving the audit user read-only SQL credentials as well, and check that they are read-only before they can do anything.

# ? Oct 21, 2014 00:26

Adbot: ADBOT LOVES YOU

# ? Apr 25, 2024 10:41

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

NihilCredo posted:

Well the primary concern is to make sure that INSERT/UPDATE/DELETE do not work, so running a test SELECT query wouldn't help with that. (I would have to try inserting/updating/deleting a dummy entry in each table and check the error I get - probably not a great idea!)

FWIW, the scenario is that we need to add an audit access option to an existing application, and said access must be 100% read-only for legal reasons (minus log entries). While we're also going to intercept the DB-writing functions in the program itself (so we can display the appropriate "NO YOU CAN'T DO THAT" messages instead of the usual "database error" ones), I want to play it safe by giving the audit user read-only SQL credentials as well, and check that they are read-only before they can do anything.

If your tables aren't in a schema, you can GRANT SELECT ON SCHEMA::dbo TO MyUser. Also, I really wouldn't put your own functions in the sys schema.

# ? Oct 21, 2014 18:43

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

«‹›303 »