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 »

KennyG: Oct 22, 2002; Here to blow my own horn.

quote:

code:

PROCEDURE Import_Relationship_Type (P_Cohab_Type_Name   IN     Subj_Cohab_Type.Cohab_Type_Name%TYPE,
                                        P_Actor             IN     Subj_Cohab_Type.Created_By%TYPE,
                                        P_Cohab_Type_Id        OUT Subj_Cohab_Type.Cohab_Type_Id%TYPE) IS
BEGIN
        MyPackage.Import_Relationship_Type (P_Cohab_Type_Name,
                                         'N',
                                         P_Actor,
                                         P_Cohab_Type_Id);
    END Import_relationship_type;

; IS

# ? May 14, 2013 16:01

Adbot: ADBOT LOVES YOU

# ? Jun 7, 2024 20:19

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

This is another kind of an academic question, since I've already done it but I was wondering if there's a better/different way. I basically need to choose a random record id from a pool of non-incremental ids. This means that I can't just go random range, since each ID has to be valid. What I ended up doing was getting a count of all possible ids, generate a random within that range, and then select the ROW_NUMBER within the counted range, like so (if this looks familiar I've jammed together two very popular yet separate tutorials on random numbers and ROW_NUMBER):

code:

DECLARE @Random int
DECLARE @Upper int
DECLARE @Lower int

SELECT @Upper = COUNT(TableID) FROM Table1 WHERE Criteria=@blah
SELECT @Lower = 1
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

SELECT * FROM
 (SELECT row_number() OVER (ORDER BY Rand() DESC) AS RecNum,
 * FROM Table WHERE Criteria=@blah) T WHERE T.RecNum = @Random

Is there a better way? I'm introducing a bit of overhead this way but it was the first thing I could think of.

# ? May 15, 2013 03:18

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Scaramouche posted:

This is another kind of an academic question, since I've already done it but I was wondering if there's a better/different way. I basically need to choose a random record id from a pool of non-incremental ids. This means that I can't just go random range, since each ID has to be valid. What I ended up doing was getting a count of all possible ids, generate a random within that range, and then select the ROW_NUMBER within the counted range, like so (if this looks familiar I've jammed together two very popular yet separate tutorials on random numbers and ROW_NUMBER):
code:
DECLARE @Random int
DECLARE @Upper int
DECLARE @Lower int

SELECT @Upper = COUNT(TableID) FROM Table1 WHERE Criteria=@blah
SELECT @Lower = 1
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

SELECT * FROM
 (SELECT row_number() OVER (ORDER BY Rand() DESC) AS RecNum,
 * FROM Table WHERE Criteria=@blah) T WHERE T.RecNum = @Random
Is there a better way? I'm introducing a bit of overhead this way but it was the first thing I could think of.

Generate that list of available ids, and select the top 1 ordered by newid()?

# ? May 15, 2013 05:59

Molotov Cock Tale: Jun 30, 2010

Nth Doctor posted:

Generate that list of available ids, and select the top 1 ordered by newid()?

Scaramouche posted:

This is another kind of an academic question, since I've already done it but I was wondering if there's a better/different way. I basically need to choose a random record id from a pool of non-incremental ids. This means that I can't just go random range, since each ID has to be valid. What I ended up doing was getting a count of all possible ids, generate a random within that range, and then select the ROW_NUMBER within the counted range, like so (if this looks familiar I've jammed together two very popular yet separate tutorials on random numbers and ROW_NUMBER):
code:
DECLARE @Random int
DECLARE @Upper int
DECLARE @Lower int

SELECT @Upper = COUNT(TableID) FROM Table1 WHERE Criteria=@blah
SELECT @Lower = 1
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

SELECT * FROM
 (SELECT row_number() OVER (ORDER BY Rand() DESC) AS RecNum,
 * FROM Table WHERE Criteria=@blah) T WHERE T.RecNum = @Random
Is there a better way? I'm introducing a bit of overhead this way but it was the first thing I could think of.

As per Nth Doctor, compact it all down to:

SELECT TOP 1 * FROM Table WHERE Criteria = @blah ORDER BY NEWID()

That is a kind of unexpected nice trick of GUID generation. It's not that good for performance in huge tables though.

# ? May 15, 2013 18:55

Citycop: Apr 11, 2005; Greetings, Rainbow Dash.

I will now sing for you a song that I hope will ease your performance anxiety.

Malcolm XML posted:

I'm assuming that 33N30 means 33 degrees 30 minutes North so just look up the formula for DMS to decimal (which I think is D + M/60 + S/3600 but don't rely on that) and then use the sign depending on direction (+ for N/E and - for S/W)

DMS is degrees minutes seconds. I see two out the the three max in 33N30

# ? May 15, 2013 22:30

ephphatha: Dec 18, 2009

Finer resolution is optional. Taking an example from the site

code:

5020 Solomon Islands BC (BBC relay) 09S24 160E03

Converts to -9.4, 160.05 which pinpoints an exact location just north of the main island of the Solomons.

# ? May 16, 2013 03:00

lunar detritus: May 6, 2009

I'm starting to learn a bit of (relational) database design for a project but I'm having problems getting my head wrapped around getting hierarchical info inside a SQL database.

How would I be able to store at least a couple thousands animals maintaining their taxonomical info (class > order > family > genus > species)?

A Lion would be:
Mammalia > Carnivora > Felidae > Panthera > Panthera leo (Lion).

I want to be able to click on Felidae and get everything under it including cats, tigers, etc.

I was checking out adjacency lists but I'm not sure it would be a good idea to put everything into one table, but maybe I'm wrong?

# ? May 16, 2013 03:36

Night Shade: Jan 13, 2013; Old School

gmq posted:

I'm starting to learn a bit of (relational) database design for a project but I'm having problems getting my head wrapped around getting hierarchical info inside a SQL database.

I was checking out adjacency lists but I'm not sure it would be a good idea to put everything into one table, but maybe I'm wrong?

Adjacency lists ~~suck~~ are terrible for fast reads due to needing horrible recursive CTE's or cursor mangling to work with. Use nested sets instead: http://en.wikipedia.org/wiki/Nested_set_model

They cost a bit to update because you wind up touching a lot of rows but access time is stupid fast because you're performing a range search on an index. Everything in one nested set table suits the usage you described, we're using one at work for something similar except it has dynamic depth so we're also using a dynamically generated unpivot in a stored proc to turn it into a table with one column per level :psyduck:

.

e: vvvv This is true but you'd wind up putting all the hierarchy management code in the application, and I personally dislike big keys because they lead to a fair bit of duplication if you wind up trying to foreign key to them.

e2: In defense of adjacency lists, they don't need you to update on average 50% of the table when you manipulate a record.

Night Shade fucked around with this message at 04:52 on May 16, 2013

# ? May 16, 2013 04:30

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Class / Order / Family and company are amazing natural keys. Unless you can think of any reason you'd want to put them outside of the table with a surrogate key then there's no harm whatsoever in just lumping the entire taxonomic name in the single table and dealing with the hierarchy in the application code.

# ? May 16, 2013 04:31

Quebec Bagnet: Apr 28, 2009; mess with the honk
you get the bonk; Lipstick Apathy

I'd probably do it something like:

code:

Animals
-------
AnimalID int pk
SpeciesID int fk Species.SpeciesID
Name varchar(100)

Species
-------
SpeciesID int
GenusID int fk Genuses.GenusID
Name varchar(100)

...

Orders
------
OrderID int pk
ClassID int fk Classes.ClassID
Name varchar(100)

Classes
-------
ClassID int pk
Name varchar(100)

And so on up the chain. This is sometimes called a has-many relationship, "class has many orders, order has many families", and so on.

e: beaten

# ? May 16, 2013 04:35

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Scaramouche posted:

This is another kind of an academic question, since I've already done it but I was wondering if there's a better/different way. I basically need to choose a random record id from a pool of non-incremental ids. This means that I can't just go random range, since each ID has to be valid. What I ended up doing was getting a count of all possible ids, generate a random within that range, and then select the ROW_NUMBER within the counted range, like so (if this looks familiar I've jammed together two very popular yet separate tutorials on random numbers and ROW_NUMBER):
code:
DECLARE @Random int
DECLARE @Upper int
DECLARE @Lower int

SELECT @Upper = COUNT(TableID) FROM Table1 WHERE Criteria=@blah
SELECT @Lower = 1
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

SELECT * FROM
 (SELECT row_number() OVER (ORDER BY Rand() DESC) AS RecNum,
 * FROM Table WHERE Criteria=@blah) T WHERE T.RecNum = @Random
Is there a better way? I'm introducing a bit of overhead this way but it was the first thing I could think of.

I would expect a true min/max for lower/upper to be faster (generate the random ID in the same way) if coupled with SELECT * FROM T WHERE id>=random ORDER BY id LIMIT 1 ?

# ? May 16, 2013 12:30

Molotov Cock Tale: Jun 30, 2010

gmq posted:

I'm starting to learn a bit of (relational) database design for a project but I'm having problems getting my head wrapped around getting hierarchical info inside a SQL database.

How would I be able to store at least a couple thousands animals maintaining their taxonomical info (class > order > family > genus > species)?

A Lion would be:
Mammalia > Carnivora > Felidae > Panthera > Panthera leo (Lion).

I want to be able to click on Felidae and get everything under it including cats, tigers, etc.

I was checking out adjacency lists but I'm not sure it would be a good idea to put everything into one table, but maybe I'm wrong?

You say SQL but not which RDBMS, if you're on SQL Server 2008/2012 one thing to take a look at might be the new HierarchyId data type: http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx

The set of many to one tables is the traditional way, HierarchyIds would offer the ability to do hierarchy traversal functions in TSQL.

# ? May 16, 2013 19:56

Night Shade: Jan 13, 2013; Old School

Molotov Cock Tale posted:

You say SQL but not which RDBMS, if you're on SQL Server 2008/2012 one thing to take a look at might be the new HierarchyId data type: http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx

The set of many to one tables is the traditional way, HierarchyIds would offer the ability to do hierarchy traversal functions in TSQL.

Oh wow, that's neat. Going to have to introduce that at work.

# ? May 17, 2013 04:32

Macichne Leainig: Jul 26, 2012; by VG

I have a simple select routine that works great on my local machine with MySQL Workbench. However, the same procedure on phpMyAdmin returns only the first result when I'm expecting 9. Running the same select query directly does exactly what I want, and the routine when called in Workbench returns the same values. What weird poo poo is phpMyAdmin doing? It's for a homework assignment so I have to use phpMyAdmin.

e: Oh wow, of course after I post the question on SA, the answer shows up a few google searches later after it's been racking my brain for 20 minutes. If anyone else has this issue, just call the procedure manually in the SQL pane and it will work.

Macichne Leainig fucked around with this message at 06:36 on May 17, 2013

# ? May 17, 2013 06:33

lunar detritus: May 6, 2009

i barely GNU her! posted:

I'd probably do it something like:
code:
...

Orders
------
OrderID int pk
ClassID int fk Classes.ClassID
Name varchar(100)

Classes
-------
ClassID int pk
Name varchar(100)
And so on up the chain. This is sometimes called a has-many relationship, "class has many orders, order has many families", and so on.

e: beaten

This was actually my first thought but I'm not sure how well it would perform considering that there's probably a reason why nested sets and adjacency lists are a thing. :v:

Molotov Cock Tale posted:

You say SQL but not which RDBMS, if you're on SQL Server 2008/2012 one thing to take a look at might be the new HierarchyId data type: http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx

The set of many to one tables is the traditional way, HierarchyIds would offer the ability to do hierarchy traversal functions in TSQL.

Linux server so I can't use SQL server. However, postgreSQL seems to have a similar thing with ltree, does anyone here have experience with that?

# ? May 18, 2013 17:43

Jabor: Jul 16, 2010; #1 Loser at SpaceChem

Are you trying to optimize that solution before checking to see whether it actually needs to be optimized?

Don't you think that's a bit ... premature?

# ? May 19, 2013 01:45

lunar detritus: May 6, 2009

Jabor posted:

Are you trying to optimize that solution before checking to see whether it actually needs to be optimized?

Don't you think that's a bit ... premature?

True. I guess I just wanted to know if there was a 'right' way to do it.

# ? May 19, 2013 01:48

Sedro: Dec 31, 2008

gmq posted:

This was actually my first thought but I'm not sure how well it would perform considering that there's probably a reason why nested sets and adjacency lists are a thing.

Those let you represent hierarchies and graphs with unbounded depth, heterogeneous nodes, cycles, etc. Schemas which are notoriously non-performant in all modern databases. You shouldn't want to be using nested sets or adjacency lists and fortunately you don't have to!

# ? May 19, 2013 01:55

karms: Jan 22, 2006; by Nyc_Tattoo; Yam Slacker

Sedro posted:

Those let you represent hierarchies and graphs with unbounded depth, heterogeneous nodes, cycles, etc. Schemas which are notoriously non-performant in all modern databases. You shouldn't want to be using nested sets or adjacency lists and fortunately you don't have to!

How would you represent a tree structure with a db then if you do want to be performant? Off-load the complexity to whatever language connects to the db, using specific db extensions, or what?

# ? May 19, 2013 13:21

Jabor: Jul 16, 2010; #1 Loser at SpaceChem

KARMA! posted:

How would you represent a tree structure with a db then if you do want to be performant? Off-load the complexity to whatever language connects to the db, using specific db extensions, or what?

If your database supports recursive queries, you'd just have a table of relationships (an adjacency list) and run recursive queries on that. If you don't, it depends on your use case - for a lot of cases (e.g. checking for friend-of-a-friends), just faking the recursion yourself out to a small depth (say, 4) could be entirely sufficient and still performant enough. Or if your data forms a tree and needs to be queried often but not updated very much you might use nested sets.

In general though, if you find yourself running into these problems it's often a sign that you need to re-think your data model. Or perhaps your storage implementation - if your data fits better as a graph than as a set of relations, why not use a graph database instead of a relational one?

# ? May 19, 2013 13:57

X-BUM-RAIDER-X: May 7, 2008

I need a bit of help on understanding how indexing happens when doing a join across tables. Suppose I have the following query:

SQL code:

SELECT * 
FROM   table1 
       INNER JOIN table2 
               ON table1.t2 = table2.id 
WHERE  table1.myField = 'butts' 
       AND table2.myField = 'buttes'

Supposing that there is an index on both myFields, how does the query engine actually deal with this? My understanding is that when you do a SELECT on a single table, the query engine will take the WHERE clause and pick a single index on that table that leftmost contains the columns that are separated by ANDs. In a join however, the WHERE clause contains columns from multiple different tables, and no single index exists for this. I'm just curious what actually happens here, and whether it restricts on each table in the WHERE clause before it joins them together. That would seem like the most optimal solution since it doesn't make much sense creating a temporary table with potentially 10,000 rows when it only needs to be 100.

X-BUM-RAIDER-X fucked around with this message at 12:16 on May 21, 2013

# ? May 21, 2013 12:08

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

OBAMA BIN LinkedIn posted:

I need a bit of help on understanding how indexing happens when doing a join across tables. Suppose I have the following query:
SQL code:
SELECT * 
FROM   table1 
       INNER JOIN table2 
               ON table1.t2 = table2.id 
WHERE  table1.myField = 'butts' 
       AND table2.myField = 'buttes' 
Supposing that there is an index on both myFields, how does the query engine actually deal with this? My understanding is that when you do a SELECT on a single table, the query engine will take the WHERE clause and pick a single index on that table that leftmost contains the columns that are separated by ANDs. In a join however, the WHERE clause contains columns from multiple different tables, and no single index exists for this. I'm just curious what actually happens here, and whether it restricts on each table in the WHERE clause before it joins them together. That would seem like the most optimal solution since it doesn't make much sense creating a temporary table with potentially 10,000 rows when it only needs to be 100.

Use EXPLAIN ANALYZE (postgres) or whatever the equivalent is for your DB and it should output what the query planner has constructed. From a similar query I ran it will likely pull the where clauses inside the join and filter before running if it can prove that the result is the same (which is basically true for the relational algebra but not for sql in general).

Of course there's always some stupid edge case that comes up that breaks the ability of the planner to take shortcuts.

# ? May 21, 2013 12:21

loinburger: Jul 10, 2004; Sweet Sauce Jones

I have the following table:

1. Integer Key (primary key)
2. Varchar Name (indexed)
3. Integer Size (indexed)
4. Blob Data

Given maxSize, I want to select all Data such that Size <= maxSize; however, I also only want one Data per Name, and given two rows with identical Names I want the Data from the row with the greatest Size (such that Size <= maxSize).

For example, with maxSize = 50 and given the rows

{1, name1, 35, data1}
{2, name1, 45, data2}
{3, name1, 55, data3}

I want {data2}; for the same maxSize, given the rows

{1, name1, 35, data1}
{2, name2, 50, data2}
{3, name3, 55, data3}

I want {data1, data2}

Right now I'm just selecting all rows with Size <= maxSize and then selecting the correct Data from the resultset in application code, but it seems like there should be a way to do this in SQL that I'm missing

code:

SELECT name,
       size,
       data
FROM   table
WHERE  size <= maxsize
GROUP  BY name
ORDER  BY size DESC

# ? May 21, 2013 16:54

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

This is a classic application for analytical/windowing functions, assuming the DB you're using supports them. What RDBMS are you using?

For SQL Server 2005+ you'd want something like:

code:

SELECT data
FROM (
SELECT name,
       size,
       data,
       row_number() over (partition by name order by maxSize desc) rnk
FROM   table
WHERE  size <= maxSize) tbl
WHERE rnk = 1

# ? May 21, 2013 20:25

loinburger: Jul 10, 2004; Sweet Sauce Jones

I'm using MySQL, so I'm guessing it'll be something like

code:

SELECT data
FROM (
SELECT name,
       size,
       data,
       @rank:=@rank+1 over (group by name order by maxSize desc) rnk
FROM   table
WHERE  size <= maxSize) tbl
WHERE rnk = 1

# ? May 21, 2013 20:41

Fluue: Jan 2, 2008

I'm trying to run a MySQL report that will:

1. Get all ticket holders (Table 'sales')
2. Match ticket holders with guests by a JOIN on CWID (for the ticket holders table 'sales') and seniorCWID (for guest table 'guests')

Does this require a sub-select? Right now just running an inner join gets me the ticket holders who -have- guests, but I want the ticket holder information AND the guest information

edit: nevermind, got what I wanted using a simple join

Fluue fucked around with this message at 23:14 on May 21, 2013

# ? May 21, 2013 22:50

Above Our Own: Jun 24, 2009; by Shine

MySQL question here, god my comprehension of joins is abysmal.

Given Table A {id} and Table B {id, value} where B.id is a foreign key referencing A.id, how can I construct a query that will pull all rows from A that either have no record in B or do have a record in B but have a B.value of X?

Above Our Own fucked around with this message at 00:37 on May 26, 2013

# ? May 26, 2013 00:34

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

Above Our Own posted:

MySQL question here, god my comprehension of joins is abysmal.

Given Table A {id} and Table B {id, value} where B.id is a foreign key referencing A.id, how can I construct a query that will pull all rows from A that either have no record in B or do have a record in B but have a B.value of X?

SQL code:

SELECT
   A.id
FROM
   A LEFT JOIN B ON A.id = B.id
WHERE
   B.value IS NULL OR B.value = X

# ? May 26, 2013 01:28

roomforthetuna: Mar 22, 2005; I don't need to know anything about virii! My CUSTOM PROGRAM keeps me protected! It's not like they'll try to come in through the Internet or something!

Should really be "WHERE b.id IS NULL", to account for the case where b exists and has a NULL contained in value, which doesn't want to be selected by the criteria given. (Though in practice this distinction is unlikely to matter.)

# ? May 26, 2013 02:29

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

roomforthetuna posted:

Should really be "WHERE b.id IS NULL", to account for the case where b exists and has a NULL contained in value, which doesn't want to be selected by the criteria given. (Though in practice this distinction is unlikely to matter.)

Oh, you're right. I guess I inferred that the columns were all NOT NULL, but there's nothing in the question to support that.

# ? May 26, 2013 03:30

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

I could have sworn I've done this in the past but I can't find anything in my notes, and Google is coming up with solutions that split lists into columns, not process a list into rows. I'm working on MSSQL 2008. What I basically want to do is convert a single value INSERT statement from:

code:

@PID int,
@SID int

INSERT INTO Table (@PID,@SID)
VALUES (@PID,@SID)

Into something that will accept a comma delimited list in the second parameter, but use the first parameter for every row. This is wrong, but in my mind it looked like:

code:

@PID int,
@SID nvarchar(4000) -- e.g. '1,2,3,4'

INSERT Into Table (@PID,@SID)
SELECT @PID,SplitFunction(@SID,',')

However when I do that I get the dreaded 'Subquery returned more than 1 value' error. The alternative is to built a control loop (e.g. WHILE MAX(@SID)>blah etc etc) but I could have sworn there was a more elegant way to do this?

# ? May 27, 2013 23:01

Quebec Bagnet: Apr 28, 2009; mess with the honk
you get the bonk; Lipstick Apathy

Not that I know anything about MERGE but I think it will work for you.

# ? May 27, 2013 23:38

PastaSky: Dec 15, 2009

I'm new to mysql and I am trying to do something like this but I don't know how...

code:

UPDATE new_items n,
       old_items o
SET    n.new = true
WHERE  n.item_id = o.item_id
       AND Count(DISTINCT o.item_state) = 2

Basically I have two tables, new_items and old_items. If the number of distinct item_states
for an item_id in old_items is 2, I want to set the corresponding item's new flag to true.

# ? May 28, 2013 00:49

Sedro: Dec 31, 2008

You probably just have to re-state the query so you are not updating two tables

SQL code:

update new_items n
set n.new = true
where exists
(
    select *
    from old_items o
    where n.item_id = o.item_id
          and count(distinct o.item_state) = 2
)

# ? May 28, 2013 01:35

zerofunk: Apr 24, 2004

Scaramouche posted:

code:
@PID int,
@SID nvarchar(4000) -- e.g. '1,2,3,4'

INSERT Into Table (@PID,@SID)
SELECT @PID,SplitFunction(@SID,',')
However when I do that I get the dreaded 'Subquery returned more than 1 value' error. The alternative is to built a control loop (e.g. WHILE MAX(@SID)>blah etc etc) but I could have sworn there was a more elegant way to do this?

What are you using for the split function? I think you'll want a table valued function and then you'd select from it. Something like...

code:

INSERT INTO Table (pid, sid)
SELECT @PID, result FROM SplitFunction(@SID, ',')

Assuming result is the name of a column in the table returned by split function.

# ? May 28, 2013 08:25

hewerman: Jun 5, 2008

Scaramouche posted:

I could have sworn I've done this in the past but I can't find anything in my notes, and Google is coming up with solutions that split lists into columns, not process a list into rows. I'm working on MSSQL 2008. What I basically want to do is convert a single value INSERT statement from:
code:
@PID int,
@SID int

INSERT INTO Table (@PID,@SID)
VALUES (@PID,@SID)
Into something that will accept a comma delimited list in the second parameter, but use the first parameter for every row. This is wrong, but in my mind it looked like:
code:
@PID int,
@SID nvarchar(4000) -- e.g. '1,2,3,4'

INSERT Into Table (@PID,@SID)
SELECT @PID,SplitFunction(@SID,',')
However when I do that I get the dreaded 'Subquery returned more than 1 value' error. The alternative is to built a control loop (e.g. WHILE MAX(@SID)>blah etc etc) but I could have sworn there was a more elegant way to do this?

Why not create a view that selects from your function, and use that in your insert? Is your function table valued or scalar?

# ? May 28, 2013 13:13

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Can anyone see why this join does not return any results from the subquery?

http://privatepaste.com/146024dbb3

Edit: Could it be because I didn't select any of the columns from it? :v:

celestial teapot fucked around with this message at 20:55 on May 31, 2013

# ? May 31, 2013 20:50

Trabant: Nov 26, 2011; All systems nominal.

The main SQL guy is on vacation and my background is in semiconductors, so this could be a truly :downs:

question.

I wrote a query which retrieves the following character string:

code:

1, ABC;2, DEF;3, GHI

and need to convert it into a table like:

code:

Col_1	Col_2
1	ABC
2	DEF
3	GHI

And after reading the web for help, I realized that I'm utterly out of my depth when it comes to anything beyond simple retrieval. Hoping this is a simple question for experts among you... Any thoughts?

# ? May 31, 2013 23:02

Pardot: Jul 25, 2001

SQL code:

 select split_part(r, ',', 1) as left, split_part(r, ',', 2) as right
 from regexp_split_to_table('1, ABC;2, DEF;3, GHI', ';') r;
 
 left | right
------+-------
 1    |  ABC
 2    |  DEF
 3    |  GHI
(3 rows)

# ? May 31, 2013 23:06

Adbot: ADBOT LOVES YOU

# ? Jun 7, 2024 20:19

Trabant: Nov 26, 2011; All systems nominal.

Pardot posted:

SQL code:

 select split_part(r, ',', 1) as left, split_part(r, ',', 2) as right
 from regexp_split_to_table('1, ABC;2, DEF;3, GHI', ';') r;
 
 left | right
------+-------
 1    |  ABC
 2    |  DEF
 3    |  GHI
(3 rows)

Holy hell that was fast -- really appreciate the effort. Unfortunately, I'm working in MS SQL Server Management Studio, and it doesn't seem to recognize split_part functions. Is there a way to import the functions or approximate them using vanilla SQL?

# ? Jun 1, 2013 00:47

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

«‹›303 »