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 »

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Let's say I have the following tables:
Control, with BatchID and Source columns
Translation, with Source and Translate columns
Raw, with BatchID and Data columns

I want the following logic, but in T-SQL:

code:

for each row in Translation {
  for each row in Control {
    if Translation.Source == Control.Source {
      UPDATE Raw SET Data = Translate WHERE Raw.BatchID = Control.BatchID
    }
  }
}

I know there's a FOR and an IF in T-SQL, but I know there's a better, SQL way to do this.

Thermopyle posted:

What's a good book or other resource on practical SQL? Preferably something that doesn't spend eons of time on how to do basic poo poo that every programmer who has to touch any SQL already knows how to do...

This is also what I am interested in.

# ? Jun 20, 2018 18:54

Adbot: ADBOT LOVES YOU

# ? Jun 3, 2024 12:38

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

I really enjoyed reading SQL Antipatterns, it's very focused on how to not gently caress UP your database design. But it's not going to teach you advanced SQL mojo if that's what you're looking for.

# ? Jun 20, 2018 18:58

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Janitor Prime posted:

I really enjoyed reading SQL Antipatterns, it's very focused on how to not gently caress UP your database design. But it's not going to teach you advanced SQL mojo if that's what you're looking for.

Maybe something like like Introductory SQL Mojo

# ? Jun 20, 2018 19:00

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

anthonypants posted:

Let's say I have the following tables:
Control, with BatchID and Source columns
Translation, with Source and Translate columns
Raw, with BatchID and Data columns

I want the following logic, but in T-SQL:
code:
for each row in Translation {
  for each row in Control {
    if Translation.Source == Control.Source {
      UPDATE Raw SET Data = Translate WHERE Raw.BatchID = Control.BatchID
    }
  }
}
I know there's a FOR and an IF in T-SQL, but I know there's a better, SQL way to do this.
This is also what I am interested in.

You don't need T-SQL:

code:

UPDATE
 Raw r1
SET
 r1.Data = t1.Translate
From
 Raw r1 JOIN 
 Control c1 ON 
 r1.BatchId = c1.BatchId JOIN
 Translation t1 ON
 c1.Source = t1.Source

This is joining Raw to Control on BatchId, and Control to Translation on Source, which matches your looping.

# ? Jun 20, 2018 19:13

spiritual bypass: Feb 19, 2008; Grimey Drawer

Thermopyle posted:

What's a good book or other resource on practical SQL? Preferably something that doesn't spend eons of time on how to do basic poo poo that every programmer who has to touch any SQL already knows how to do...

The Art Of SQL is a superb book on querying efficiently.

# ? Jun 20, 2018 19:19

ElehemEare: May 20, 2001; I am an omnipotent penguin.

Moonwolf posted:

You don't need T-SQL:
code:
UPDATE
 Raw r1
SET
 r1.Data = t1.Translate
From
 Raw r1 JOIN 
 Control c1 ON 
 r1.BatchId = c1.BatchId JOIN
 Translation t1 ON
 c1.Source = t1.Source
This is joining Raw to Control on BatchId, and Control to Translation on Source, which matches your looping.

This also illustrates the difference between RBAR and set-based operations. Generally avoid things like loops and cursors when possible; the set-based approach almost always exists and is almost always the best solution.

# ? Jun 20, 2018 19:45

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

Moonwolf posted:

The reason your other way doesn't work is, having done it on the actually super useful Oracle livesql (https://livesql.oracle.com) is hard to show because the data explodes out, but with 5 players, who each kill each other once, you end up with 150 rows with joining for kills and deaths at the same time. You get for each kill row, every possible death row, because they're independent rows joined in.

Link to worksheet so you can play with it:
https://livesql.oracle.com/apex/livesql/s/guwqntt2shpfia6s7u1fbb5d3

Oracle's accounts are free for this.

Edit: They're expensive for everything though.

Also, on the topic of your query, if you have it, COALESCE or NVL would save you needing the players table, you can join the two subqueries like so:
code:
SELECT
  NVL(KillerName, VictimName) PlayerName,
  NVL(Kills, 0) Kills,
  NVL(Deaths, 0) Deaths
FROM
  (
    SELECT
      KillerName,
      count(*) as Kills
    FROM
      KillInfo
    GROUP BY
      KillerName
  ) KillCounts ON  PlayerName = KillerName FULL OUTER JOIN
  (
    SELECT
      VictimName,
      count(*) as Deaths
    FROM
      KillInfo
    GROUP BY
      VictimName
  ) DeathCounts ON KillerName= VictimName
Which saves a join.

This is pretty slick, but MySQL doesn't have FULL OUTER JOINs. The recommended MySQL workaround for a lack of FULL OUTER is doing a UNION of a LEFT JOIN and a RIGHT JOIN, so we are back to three joins.

# ? Jun 20, 2018 21:37

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

Yeah, if you don't have it then just left join the subqueries onto the core table.

On the cursors thing, I'd say only use them if you're going to do more than 1 discrete thing per output row. Anything where you're only generating one set of output can be done in a single statement and doesn't need them.

# ? Jun 20, 2018 23:30

Minus Pants: Jul 18, 2004

I've got a table that tracks start/end dates, and I'd like to get a "usage-days" sum on a monthly basis. I have it working for a date range, but I'm stuck figuring out how to get it by month. It's not a simple as a group by since each month's end date needs to be accounted for.

Here's the range version (for 2017-04-28 to 2018-01-01). The LEAST clause covers the cases where the real_end_date is after the query range, otherwise I wouldn't need the CTE.

code:

WITH dates AS (
	SELECT
		start_date,
		LEAST ( DATE'2018-01-01', real_end_date ) AS end_date 
	FROM
		"usage" 
	WHERE
		start_date <= DATE'2017-04-28' 
	) 
SELECT
	start_date::DATE,
	end_date::DATE,
	( end_date :: DATE - start_date :: DATE ) + 1 AS days 
FROM
	dates;

That gives:

code:

start_date	end_date	days
2017-04-29	2017-05-02	4
2017-11-03	2017-11-22	20

What I want is that broken down by month, covering overlapping months:

code:

month		days
2017-04-01	2
2017-05-01	2
2017-06-01	0
...
2017-11-01	20

From Googling, I think I need a calendar table, but it's not clear to me how to structure the join.

# ? Jul 2, 2018 05:46

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Take a look at the EXTRACT function.

# ? Jul 2, 2018 07:02

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

Minus Pants posted:

I've got a table that tracks start/end dates, and I'd like to get a "usage-days" sum on a monthly basis. I have it working for a date range, but I'm stuck figuring out how to get it by month. It's not a simple as a group by since each month's end date needs to be accounted for.

Here's the range version (for 2017-04-28 to 2018-01-01). The LEAST clause covers the cases where the real_end_date is after the query range, otherwise I wouldn't need the CTE.
code:
WITH dates AS (
	SELECT
		start_date,
		LEAST ( DATE'2018-01-01', real_end_date ) AS end_date 
	FROM
		"usage" 
	WHERE
		start_date <= DATE'2017-04-28' 
	) 
SELECT
	start_date::DATE,
	end_date::DATE,
	( end_date :: DATE - start_date :: DATE ) + 1 AS days 
FROM
	dates;
That gives:
code:
start_date	end_date	days
2017-04-29	2017-05-02	4
2017-11-03	2017-11-22	20
What I want is that broken down by month, covering overlapping months:
code:
month		days
2017-04-01	2
2017-05-01	2
2017-06-01	0
...
2017-11-01	20
From Googling, I think I need a calendar table, but it's not clear to me how to structure the join.

Yeah - calendar table is the way to go. Calendar tables generally have a row per day and can have tons of columns that represent properties of that date. Examples of possible columns are DayOfWeek, MonthNumber, FirstDayOfMonth, IsHoliday, etc. Some of those columns can be trivial to calculate manually and are just nice to have (MonthNumber), others not so much (IsHoliday). Anyway, you can probably get away with a two column table: CalendarDt which represents the row�s date and MonthDt which represents the first day of the month for that month.

To solve your problem you join your usage table to the calendar table using the between operator, then group on MonthDt. Pseudo (phone) code:

SELECT MonthDt, COUNT(*) Days FROM usage u INNER JOIN calendar c ON c.CalendarDt BETWEEN u.start_date and u.end_date GROUP BY MonthDt

# ? Jul 2, 2018 16:33

Minus Pants: Jul 18, 2004

Ruggan posted:

SELECT MonthDt, COUNT(*) Days FROM usage u INNER JOIN calendar c ON c.CalendarDt BETWEEN u.start_date and u.end_date GROUP BY MonthDt

Ahhh that makes sense. Thanks!

# ? Jul 2, 2018 16:43

Shugojin: Sep 6, 2007; THE TAIL THAT BURNS TWICE AS BRIGHT...

So I used T-SQL's LAG() function to get the previous day's entry for some reporting and it's doing something... real weird?

The code is like this:

code:

lag([expression],1,0) over(partition by LOCATION order by DATE) AS Prior

Works great in the span of one month. As soon as I change months it... wraps around and takes the first entry in whatever date range I had. If I change the expression to just show me the date then it's 1/1/1900.

Was lag just extremely the wrong function to use or what

# ? Jul 3, 2018 11:19

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

Shugojin posted:

So I used T-SQL's LAG() function to get the previous day's entry for some reporting and it's doing something... real weird?

The code is like this:
code:
lag([expression],1,0) over(partition by LOCATION order by DATE) AS Prior
Works great in the span of one month. As soon as I change months it... wraps around and takes the first entry in whatever date range I had. If I change the expression to just show me the date then it's 1/1/1900.

Was lag just extremely the wrong function to use or what

Why are you supplying the 2nd and 3rd parameters of lag? Those are optional and in 99% of use cases you don�t need them.

The 2nd parameter is offset, which defaults to 1. That�s how many rows to look back. You�re passing the default.

The 3rd parameter is default, and is what is causing your odd behavior. This parameter specifies what should be returned when your lag function returns null. 0 converted to a date is 1/1/1900, which is why your query randomly returns that value.

What I can�t tell you without more info on your dataset is why your lag function is returning NULL. But what that does mean is there is no prior value based on your partition/order OR that prior value is NULL.

# ? Jul 3, 2018 14:08

Shugojin: Sep 6, 2007; THE TAIL THAT BURNS TWICE AS BRIGHT...

I was supplying the parameters because the documentation had them supplied :kiddo:

It also had the DAY(date) in an example similar to what I was doing that I had forgotten when posting that from home and was causing sql to re-sort everything weirdly, if I ditch the DAY it sorts everything properly

# ? Jul 3, 2018 14:38

Mark Larson: Dec 27, 2003; Interesting...

I'm trying to use week numbers to filter for the last few weeks (WHERE clause), since the date_column in the database is of YYYYWW (ISO week) type and it of course has a problem with periods in the beginning of the year. Using Redshift (PostgreSQL 8.3 compatible)

code:

select CONCAT(EXTRACT(YEAR from current_date), EXTRACT(WEEK from current_date))-4;
 ?column?
----------
   201823
(1 row)

This works well since we are in the middle of the year, but this:

code:

select CONCAT(EXTRACT(YEAR from '2018-01-01'::date), EXTRACT(WEEK from '2018-01-01'::date))-4;
 ?column?
----------
    20177
(1 row)

select CONCAT(EXTRACT(YEAR from '2018-01-01'::date), EXTRACT(WEEK from '2018-01-01'::date));
 concat
--------
 20181
(1 row)

... doesn't.

Any ideas? Maybe the best thing is to somehow do a date_trunc('week', date_column) on the date_column.

# ? Jul 3, 2018 14:46

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

Shugojin posted:

I was supplying the parameters because the documentation had them supplied

It also had the DAY(date) in an example similar to what I was doing that I had forgotten when posting that from home and was causing sql to re-sort everything weirdly, if I ditch the DAY it sorts everything properly

Yeah DAY(Date) would probably give you really weird results if your data spans more than a month. :shobon:

Here's an experiment I did for you:

code:

;WITH Number_List AS (
  SELECT 1 AS Number
  UNION ALL
  SELECT N.Number + 1
  FROM Number_List AS N
  WHERE N.Number < 35
)
SELECT
  CONVERT(VARCHAR(10), DATEADD(DAY, N.Number, '1/1/2018'), 101) AS Date_Col,
  N.Number,
  DAY(DATEADD(DAY, N.Number, '1/1/2018')) AS Day_Value,
  LAG(N.Number, 1, 0) OVER(ORDER BY DAY(DATEADD(DAY, N.Number, '1/1/2018'))) AS Original_Lag_Record,
  LAG(N.Number, 1) OVER(ORDER BY DAY(DATEADD(DAY, N.Number, '1/1/2018'))) AS Lag_Record,
  LAG(N.Number, 1) OVER(ORDER BY DATEADD(DAY, N.Number, '1/1/2018')) AS Corrected_Lag_Record
FROM Number_List AS N
ORDER BY N.Number;

pre:

Date_Col   Number      Day_Value   Original_Lag_Record Lag_Record  Corrected_Lag_Record
---------- ----------- ----------- ------------------- ----------- --------------------
01/02/2018 1           2           32                  32          NULL
01/03/2018 2           3           1                   1           1
01/04/2018 3           4           34                  34          2
01/05/2018 4           5           3                   3           3
01/06/2018 5           6           35                  35          4
01/07/2018 6           7           5                   5           5
01/08/2018 7           8           6                   6           6
01/09/2018 8           9           7                   7           7
01/10/2018 9           10          8                   8           8
01/11/2018 10          11          9                   9           9
01/12/2018 11          12          10                  10          10
01/13/2018 12          13          11                  11          11
01/14/2018 13          14          12                  12          12
01/15/2018 14          15          13                  13          13
01/16/2018 15          16          14                  14          14
01/17/2018 16          17          15                  15          15
01/18/2018 17          18          16                  16          16
01/19/2018 18          19          17                  17          17
01/20/2018 19          20          18                  18          18
01/21/2018 20          21          19                  19          19
01/22/2018 21          22          20                  20          20
01/23/2018 22          23          21                  21          21
01/24/2018 23          24          22                  22          22
01/25/2018 24          25          23                  23          23
01/26/2018 25          26          24                  24          24
01/27/2018 26          27          25                  25          25
01/28/2018 27          28          26                  26          26
01/29/2018 28          29          27                  27          27
01/30/2018 29          30          28                  28          28
01/31/2018 30          31          29                  29          29
02/01/2018 31          1           0                   NULL        30
02/02/2018 32          2           31                  31          31
02/03/2018 33          3           2                   2           32
02/04/2018 34          4           33                  33          33
02/05/2018 35          5           4                   4           34

(35 row(s) affected)

Since you're ordering just by the DAY portion of the date, SQL Server can't see a difference between February 2nd and January 2nd, for example. Also peep how February 1st ends up as the record without any LAG when ordering by Date, because DAY('2/1/2018') < DAY('1/2/2018')

An excess of NULLs in your lag column could mean, as Ruggan stated, either NULLs in your data, or a lot of partitions in your data, since the first record in your partition can't have a LAG from the previous row.

# ? Jul 3, 2018 14:58

Shugojin: Sep 6, 2007; THE TAIL THAT BURNS TWICE AS BRIGHT...

Yeah, currently the only nulls I am getting are just for the first record in each partition and that's not total garbage. I will probably muck about with it more today.

# ? Jul 3, 2018 16:08

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Why would a SELECT ... statement inside of a cursor generate results I want, but a SELECT @var = ... doesn't, and includes results not generated by the first statement?

# ? Jul 5, 2018 20:39

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

anthonypants posted:

Why would a SELECT ... statement inside of a cursor generate results I want, but a SELECT @var = ... doesn't, and includes results not generated by the first statement?

The second case sounds like you're getting PHANTOM READS, and I can only assume that a higher transaction isolation level is being used in the first case. Can you share more information about the context in which each of them is being called?

# ? Jul 6, 2018 00:08

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Janitor Prime posted:

The second case sounds like you're getting PHANTOM READS, and I can only assume that a higher transaction isolation level is being used in the first case. Can you share more information about the context in which each of them is being called?

Okay, but I need to preface this: Yes, I know this sucks. Yes, it should be in a CLR. Eventually I might get to write it that way, because it looks like this poo poo isn't going to work. On the plus side, I learned that cursors can loop infinitely.

This cursor goes through a table and generates an UPDATE statement for each row. This UPDATE statement performs an XQuery modify(), and since modify() only accepts string literals, I need to generate a unique UPDATE statement for each row in this table. It looks like this:

SQL code:

DECLARE cursor CURSOR LOCAL FAST_FORWARD
FOR SELECT dl.a1, dl.a2, dl.a3, t.b1, t.b2, t.b3
    FROM master.dbo.t AS t
    JOIN master.dbo.dl AS dl
        ON dl.ID = t.ID

OPEN cursor
FETCH NEXT FROM cursor INTO @a1, @a2, @a3, @b1, @b2, @b3

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT N'UPDATE #Temp SET XML.modify(''replace value of (/Message/' +
        @a1 +
        '/' + @a1 + '.' + @a2 +
        '/' + @a1 + '.' + @a2 + '.' + @a3 +
        '/text())[1] with (/Message/' +
        @b1 +
        '/' + @b1 + '.' + @b2 +
        '/' + @b1 + '.' + @b2 + '.' + @b3 +
        '/text())'') ' +
        'WHERE xID = ' + CAST(r.xID AS NVARCHAR(10))
            FROM #Temp AS r
            JOIN master.dbo.dc AS dc
                ON dc.xID = r.xID
            WHERE dc.Source IN ( SELECT Source FROM master.dbo.t )
                AND CAST(XML AS NVARCHAR(MAX)) LIKE N'%' + @b1 + '.' + @b2 + '.' + @b3 + '%'
                AND CAST(XML AS NVARCHAR(MAX)) NOT LIKE N'%<' + @b1 + '.' + @b2 + '.' + @b3 + '/>%'
    
    FETCH NEXT FROM cursor INTO @a1, @a2, @a3, @b1, @b2, @b3
END

The results look like this, and is the command I want to run:

SQL code:

UPDATE #Temp SET XML.modify('replace value of (/Message/a1/a1.a2/a1.a2.a3/text())[1] with (/Message/b1/b1.b2/b1.b2.b3/text())') WHERE xID = 12345678

There are currently three combinations of "b1.b2.b3". This gets me three "tables" full of UPDATE statements, for which the XML tag <b1.b2.b3> in the XML column of the #Temp table both exists, and is not blank, and each row in these "tables" contains a unique xID.

If I change it to the following, then I get one "table", where the UPDATE command is run for all three combinations of "b1.b2.b3", regardless of what's in the XML tag, and for only one xID:

SQL code:

DECLARE cursor CURSOR LOCAL FAST_FORWARD
FOR SELECT dl.a1, dl.a2, dl.a3, t.b1, t.b2, t.b3
    FROM master.dbo.t AS t
    JOIN master.dbo.dl AS dl
        ON dl.ID = t.ID

OPEN cursor
FETCH NEXT FROM cursor INTO @a1, @a2, @a3, @b1, @b2, @b3

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @var = N'UPDATE #Temp SET XML.modify(''replace value of (/Message/' +
        @a1 +
        '/' + @a1 + '.' + @a2 +
        '/' + @a1 + '.' + @a2 + '.' + @a3 +
        '/text())[1] with (/Message/' +
        @b1 +
        '/' + @b1 + '.' + @b2 +
        '/' + @b1 + '.' + @b2 + '.' + @b3 +
        '/text())'') ' +
        'WHERE xID = ' + CAST(r.xID AS NVARCHAR(10))
            FROM #Temp AS r
            JOIN master.dbo.dc AS dc
                ON dc.xID = r.xID
            WHERE dc.Source IN ( SELECT Source FROM master.dbo.t )
                AND CAST(XML AS NVARCHAR(MAX)) LIKE N'%' + @b1 + '.' + @b2 + '.' + @b3 + '%'
                AND CAST(XML AS NVARCHAR(MAX)) NOT LIKE N'%<' + @b1 + '.' + @b2 + '.' + @b3 + '/>%'
    SELECT @var
    
    FETCH NEXT FROM cursor INTO @a1, @a2, @a3, @b1, @b2, @b3
END

The third "b1.b2.b3" combination has no matches, which is why its "table" from the first cursor returned zero rows, and therefore it shouldn't return anything in this cursor. At most, I should get two UPDATE statements for a single xID, but not three. And before anyone asks, I can't just run the cursor once through each of the three "b1.b2.b3" combinations and do a bulk update, because some of the associated XML values are blank, and XQuery's replace will only replace with blanks if you explicitly replace with "()".

Additionally, I have no idea how to execute these commands. I can't replace the SELECT with EXEC or EXEC sp_executesql because of the CAST()s, and even if I try to do SELECT @var = ...EXEC @var it throws an error because it truncates @var after 129 characters for some reason. PRINT @var or SELECT @var don't show it as being truncated.

# ? Jul 6, 2018 01:02

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

So wait, is there anything stopping you from doing something like this?

code:

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL = @SQL + N'UPDATE #Temp SET XML.modify(''replace value of (/Message/' +
  dl.a1 +
  '/' + dl.a1 + '.' + dl.a2 +
  '/' + dl.a1 + '.' + dl.a2 + '.' + dl.a3 +
  '/text())[1] with (/Message/' +
  t.b1 +
  '/' + t.b1 + '.' + t.b2 +
  '/' + t.b1 + '.' + t.b2 + '.' + t.b3 +
  '/text())'') ' +
  'WHERE xID = ' + (
    SELECT CAST(r.xID AS NVARCHAR(10))
    FROM #Temp AS r
    JOIN master.dbo.dc AS dc
      ON dc.xID = r.xID
    WHERE dc.Source IN ( SELECT Source FROM master.dbo.t )
      AND CAST(XML AS NVARCHAR(MAX)) LIKE N'%' + t.b1 + '.' + t.b2 + '.' + t.b3 + '%'
      AND CAST(XML AS NVARCHAR(MAX)) NOT LIKE N'%<' + t.b1 + '.' + t.b2 + '.' + t.b3 + '/>%'
  ) + ';'
FROM master.dbo.t AS t
JOIN master.dbo.dl AS dl
  ON dl.ID = t.ID;


PRINT @SQL;

This probably doesn't help you solve your problem all that much, but seriously: you most likely don't need a cusror for what you're trying to do. Unless something weird is going on with the x.id query, you can do the whole shebang as a set based operation.

A spot for further cleanup is that subquery selecting r.xID. Is there any way we can relate that directly to dbo.t or dbo.dl? Also is the r.xID query ALWAYS expected to return a single value?

# ? Jul 6, 2018 05:22

Bobcats: Aug 5, 2004; Oh

Is there a sql white space/tab standard that doesn�t cause suicidal ideation?

# ? Jul 6, 2018 14:47

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Nth Doctor posted:

So wait, is there anything stopping you from doing something like this?
code:
DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL = @SQL + N'UPDATE #Temp SET XML.modify(''replace value of (/Message/' +
  dl.a1 +
  '/' + dl.a1 + '.' + dl.a2 +
  '/' + dl.a1 + '.' + dl.a2 + '.' + dl.a3 +
  '/text())[1] with (/Message/' +
  t.b1 +
  '/' + t.b1 + '.' + t.b2 +
  '/' + t.b1 + '.' + t.b2 + '.' + t.b3 +
  '/text())'') ' +
  'WHERE xID = ' + (
    SELECT CAST(r.xID AS NVARCHAR(10))
    FROM #Temp AS r
    JOIN master.dbo.dc AS dc
      ON dc.xID = r.xID
    WHERE dc.Source IN ( SELECT Source FROM master.dbo.t )
      AND CAST(XML AS NVARCHAR(MAX)) LIKE N'%' + t.b1 + '.' + t.b2 + '.' + t.b3 + '%'
      AND CAST(XML AS NVARCHAR(MAX)) NOT LIKE N'%<' + t.b1 + '.' + t.b2 + '.' + t.b3 + '/>%'
  ) + ';'
FROM master.dbo.t AS t
JOIN master.dbo.dl AS dl
  ON dl.ID = t.ID;


PRINT @SQL;
This probably doesn't help you solve your problem all that much, but seriously: you most likely don't need a cusror for what you're trying to do. Unless something weird is going on with the x.id query, you can do the whole shebang as a set based operation.

A spot for further cleanup is that subquery selecting r.xID. Is there any way we can relate that directly to dbo.t or dbo.dl? Also is the r.xID query ALWAYS expected to return a single value?

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

The xID is a row-unique ID, so, yes?

e: Here's a version that works:

SQL code:

EXEC (N'UPDATE #Temp SET XML.modify(''replace value of (/Message/' +
@a1 +
'/' + @a1 + '.' + @a2 +
'/' + @a1 + '.' + @a2 + '.' + @a3 +
'/text())[1] with (/Message/' +
@b1 +
'/' + @b2 + '.' + @b3 +
'/' + @b1 + '.' + @b2 + '.' + @b3 +
'/text())'')
FROM #Temp AS r
JOIN master.dbo.dc AS dc
ON dc.xID = r.xID
WHERE dc.Source IN ( SELECT Source FROM master.dbo.t )
AND CAST(XML AS NVARCHAR(MAX)) LIKE ''%' + @b1 + '.' + @b2 + '.' + @b3 + '%''
AND CAST(XML AS NVARCHAR(MAX)) NOT LIKE ''%<' + @b1 + '.' + @b2 + '.' + @b3 + '/>%''')

anthonypants fucked around with this message at 23:38 on Jul 6, 2018

# ? Jul 6, 2018 16:08

Sheep: Jul 24, 2003

Are there any small programs that will let me generate a visualization of the relationships in a Postgres database from the command line in Linux? Something that doesn't require me to install java, a JDBC driver, or a bunch of random PHP modules. Everything I've run into can't hit one of those three requirements and those are the kind of things that are verboten on that side of the network.

Bonus points if the program itself doesn't require X windows and just dumps the visualization to some sort of file that I can sendmail to the project manager.

Sheep fucked around with this message at 17:55 on Jul 6, 2018

# ? Jul 6, 2018 17:46

DELETE CASCADE: Oct 25, 2017; i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

if those are really your requirements then why don't you hack it yourself? pg_dump the table and constraint definitions, do some regex to get them in graphviz dot form, then you can generate a pdf/png from the command line

# ? Jul 6, 2018 21:44

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

Bobcats posted:

Is there a sql white space/tab standard that doesn�t cause suicidal ideation?

2 space tabs. Any serious reporting query will otherwise end up so indented it linewraps.

# ? Jul 7, 2018 01:06

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

Moonwolf posted:

2 space tabs. Any serious reporting query will otherwise end up so indented it linewraps.

# ? Jul 7, 2018 02:33

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Bobcats posted:

Is there a sql white space/tab standard that doesn�t cause suicidal ideation?

Tabs work well for indentation; you can set the desirable fixed-width expansion in your editor. Spaces make sense for alignment if you need it.

Perhaps instead you meant:

code:

INSERT INTO widget (a,b,c) VALUES
	 (1,2,3)
	,(4,5,6)
	,(7,8,9)
; -- note carefully which whitespace characters are in use

to avoid annoyances like superfluous line changes in code repositories when another row is added (silly example, but consider CREATE TABLE).

# ? Jul 7, 2018 20:17

Hughmoris: Apr 21, 2007; Let's go to the abyss!

I have a beginner T-SQL question that is tripping me up:

I have a table:

code:

ORDER, PERSON, TRANSACTION
aaa, John, 2018-07-02 13:00
aaa, Bill, 2018-07-02 09:00
aaa,Carl, 2018-07-02 18:00
bbb, Sarah, 2018-07-03 19:00
bbb, Julie, 2018-07-03 22:00
bbb, Phil, 2018-07-03 20:15

What I want to end up with is a table that shows: for each order, what is the earliest transaction time and person, and what is the last transaction time and person.
So my end result is ideally something like this:

code:

ORDER, FIRST_TRANSACTION, FIRST_PERSON, LAST_TRANSACTION, LAST_PERSON
aaa, 2018-07-02 09:00, Bill, 2018-07-02 18:00, Carl
bbb, 2018-07-03 19:00, Sarah, 2018-07-03 22:00, Julie

I'm flailing a bit at this point. I've tried using MIN, MAX but I end up getting a row for each person. I remember seeing somewhere a method where you ORDER BY a sub query and LIMIT 1 to get the first or last result but I'm positive what that would look like.

# ? Jul 15, 2018 01:40

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

Hughmoris posted:

I have a beginner T-SQL question that is tripping me up:

I have a table:
code:
ORDER, PERSON, TRANSACTION
aaa, John, 2018-07-02 13:00
aaa, Bill, 2018-07-02 09:00
aaa,Carl, 2018-07-02 18:00
bbb, Sarah, 2018-07-03 19:00
bbb, Julie, 2018-07-03 22:00
bbb, Phil, 2018-07-03 20:15
What I want to end up with is a table that shows: for each order, what is the earliest transaction time and person, and what is the last transaction time and person.
So my end result is ideally something like this:
code:
ORDER, FIRST_TRANSACTION, FIRST_PERSON, LAST_TRANSACTION, LAST_PERSON
aaa, 2018-07-02 09:00, Bill, 2018-07-02 18:00, Carl
bbb, 2018-07-03 19:00, Sarah, 2018-07-03 22:00, Julie
I'm flailing a bit at this point. I've tried using MIN, MAX but I end up getting a row for each person. I remember seeing somewhere a method where you ORDER BY a sub query and LIMIT 1 to get the first or last result but I'm positive what that would look like.

There are multiple ways of achieving this sort of result IIRC. One way to do it is to construct a subquery that gets the minimum and maximum for each order, then join it against two copies of the original table so as to get the PERSON data from that table.

code:

SELECT
    Subquery.ORDER,
    Subquery.FIRST_TRANSACTION,
    MyTable_First.PERSON AS FIRST_PERSON,
    Subquery.LAST_TRANSACTION,
    MyTable_Last.PERSON AS LAST_PERSON
FROM
    (
        SELECT
            ORDER,
            MIN(TRANSACTION) AS FIRST_TRANSACTION,
            MAX(TRANSACTION) AS LAST_TRANSACTION
        FROM
            MyTable
        GROUP BY
            ORDER
    ) AS Subquery
    JOIN MyTable AS MyTable_First ON
        Subquery.ORDER = MyTable_First.ORDER AND
        Subquery.FIRST_TRANSACTION = MyTable_First.TRANSACTION
    JOIN MyTable AS MyTable_Last ON
        Subquery.ORDER = MyTable_Last.ORDER AND
        Subquery.LAST_TRANSACTION = MyTable_Last.TRANSACTION

# ? Jul 15, 2018 12:57

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Hammerite posted:

code:

SELECT
    Subquery.ORDER,
    Subquery.FIRST_TRANSACTION,
    MyTable_First.PERSON AS FIRST_PERSON,
    Subquery.LAST_TRANSACTION,
    MyTable_Last.PERSON AS LAST_PERSON
FROM
    (
        SELECT
            ORDER,
            MIN(TRANSACTION) AS FIRST_TRANSACTION,
            MAX(TRANSACTION) AS LAST_TRANSACTION
        FROM
            MyTable
        GROUP BY
            ORDER
    ) AS Subquery
    JOIN MyTable AS MyTable_First ON
        Subquery.ORDER = MyTable_First.ORDER AND
        Subquery.FIRST_TRANSACTION = MyTable_First.TRANSACTION
    JOIN MyTable AS MyTable_Last ON
        Subquery.ORDER = MyTable_Last.ORDER AND
        Subquery.LAST_TRANSACTION = MyTable_Last.TRANSACTION

I was able to apply this concept to my problem and it worked like a champ. Thank you!

# ? Jul 15, 2018 20:21

kiwid: Sep 30, 2013

I keep being told I should stop using MySQL and use a "real" database.

What do people mean by this? I always ask for more clarification and then conversation stops.

# ? Jul 17, 2018 18:15

pangstrom: Jan 25, 2003; Wedge Regret

They're probably either telling you to use PostgreSQL or a noSQL and you will know for sure which if you ask them which they use.

# ? Jul 17, 2018 18:31

wolrah: May 8, 2006; what?

kiwid posted:

I keep being told I should stop using MySQL and use a "real" database.

What do people mean by this? I always ask for more clarification and then conversation stops.

Like PHP, MySQL was a lot shittier in previous versions and that reputation lives on. AFAIK the largest problems these days are actually bad defaults, that it's possible to configure it to be decent enough but the default settings do a lot of insane things like silently dropping or modifying data.

# ? Jul 17, 2018 18:33

kiwid: Sep 30, 2013

pangstrom posted:

They're probably either telling you to use PostgreSQL or a noSQL and you will know for sure which if you ask them which they use.

I believe they're referring to MSSQL, Oracle, and Postgres.

MSSQL and Oracle are two I'll likely never be interested in because I work primarily in PHP and Python. Should I however invest time into learning Postgres over MySQL?

kiwid fucked around with this message at 18:54 on Jul 17, 2018

# ? Jul 17, 2018 18:42

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

kiwid posted:

I believe they're referring to MSSQL, Oracle, and Postgres.

MSSQL and Oracle are two I'll likely never be interested in because I work primarily in PHP and Python. Should I however invest time into learning Postgres over MySQL?

Doesn�t MSSQL integrate just fine with PHP and Python? I think there are drivers for both.

# ? Jul 17, 2018 19:42

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Ruggan posted:

Doesn�t MSSQL integrate just fine with PHP and Python? I think there are drivers for both.

There are.
https://docs.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server
https://docs.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server

# ? Jul 17, 2018 19:56

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

kiwid posted:

I believe they're referring to MSSQL, Oracle, and Postgres.

MSSQL and Oracle are two I'll likely never be interested in because I work primarily in PHP and Python. Should I however invest time into learning Postgres over MySQL?

Be solid on the relational part of a database, then look how other db's differ. It's better to have a good base before switching since that is the most important bit. Mysql is good enough for this, as is postgres, mssql, oracle, etc etc etc

# ? Jul 17, 2018 20:45

Adbot: ADBOT LOVES YOU

# ? Jun 3, 2024 12:38

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

Maybe it's just the nerd in me being overly strict, but I have zero tolerance for MySQL's default behavior. I don't know if this has changed, but the last time I used it with a complicated GROUP BY query that included some columns that weren't part of the GROUP BY clause it just happily returned some random data from the matching rows instead of throwing an error like Postgres and Oracle do. This lead to a subtle bug in production that wasn't caught during testing because our test data always happened to return the same rows and nothing seemed amiss.

Another stupid behavior I ran into was where date columns that contained NULL values would return 0000-00-00 as the value instead of NULL and breaking our ORM mapping. You can fix it by setting a property on the connection string or turning on Strict mode in the server config, but this is something that's so rear end backwards it boggles my mind. Then there's all the historically broken Unicode support that it shipped with.

The final nail in the coffin was all the licensing hassle that Oracle tried to impose on us. Oracle licenses the JDBC drivers under the GPL and when we tried to purchase an enterprise license to ship the driver with our product their legal team went crazy claiming that the way we used the product would require us to be a 3rd party reseller and wanted us to have minimum commitments and other crap like that.

# ? Jul 17, 2018 22:00

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

«‹›303 »