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 »

Busy Bee: Jul 13, 2004

code:

SELECT ProductName
  FROM Products
 WHERE ProductID = ALL (SELECT ProductID FROM Orders WHERE Status = 'Shipped')

This is where I am confused. If you go to this link here and look at the last example where they use ALL and if you run the query https://www.w3schools.com/sql/sql_any_all.asp there are no orders in quantity 10 that come up and the results are 0. Rather, if I change "ALL" to "ANY", then all the orders where quantity = 10 pops up. So in your code above, what is the advantage of using "ALL" instead of "ANY"? Would ANY show all the ProductID's where the Status is 'Shipped'?

code:

SELECT ProductName,
       ProductID,
       ProductInventor,
       ProductDateOfInvention,
       X.OrderCount
  FROM Products
  JOIN (SELECT count(1) as OrderCount,
	       ProductID,
          FROM Orders
	 GROUP BY ProductID) X
    ON Products.ProductID = X.ProductID

Can there be a subquery after a JOIN? I have a very basic understanding of SQL but putting a subquery after JOIN does not make sense to me. Shouldn't it just be FROM Products JOIN X ON Products.ProdictID = X.ProductID?

# ? Dec 3, 2017 08:09

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 21:46

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

Busy Bee posted:

This is where I am confused. If you go to this link here and look at the last example where they use ALL and if you run the query https://www.w3schools.com/sql/sql_any_all.asp there are no orders in quantity 10 that come up and the results are 0. Rather, if I change "ALL" to "ANY", then all the orders where quantity = 10 pops up. So in your code above, what is the advantage of using "ALL" instead of "ANY"? Would ANY show all the ProductID's where the Status is 'Shipped'?

Don't think of it in terms of "advantage". ALL and ANY do different things, the question is what you want to achieve. ANY is true if there is at least one matching result from the subquery. ALL is true only if all results from the subquery match, or - put another way - if there are no results from the subquery that don't match. Like I said in my example, if there was one order for that product that wasn't in the status 'shipped', that product would not be shown in the results. I understand if you're thinking "But why would you want to query for something like that?", but assuming you do need that kind of query, ALL is one way to achieve it.

That said, I wouldn't get too hung up on this particular issue.

quote:

Can there be a subquery after a JOIN? I have a very basic understanding of SQL but putting a subquery after JOIN does not make sense to me. Shouldn't it just be FROM Products JOIN X ON Products.ProdictID = X.ProductID?

Yes, there can be a subquery after a JOIN; indeed, there can also be one after a FROM. That's an inline view. I recommend you read up on views in general. One of the big strengths of SQL imho is being able to build queries by nestling subqueries like this; it's not always pretty, admittedly, but it allows you to tackle some very tricky queries by building them from the inside out.

# ? Dec 3, 2017 08:33

mearn: Aug 2, 2011; Kevin Harvick's #1 Fan!

I'm an idiot in over my head trying to find a way to improve my current situation.

I've got a table full of financial data, the relevant columns for the query I'm running are:
data_date - the date the data was published
expiration - the date the data is no longer useful
symbol - an identifier that follows the same data through it's lifespan
delta - a value I'm attempting to filter the data by.

Using PostgreSQL, I want to have target values for expiration and delta and return the symbol that's closest to my target values for each specific date. For example, I might be looking for a value that expires 45 days into the future and has a delta value of -0.5. It's unlikely both of those values will exist inside my dataset so I'm finding the closest values to those targets. Finding the target date is more important than the target delta. Right now I have a query that gets me the result I'm looking for when I specify a specific date but I'd much rather find a way to group by the data_date and return values for every distinct data_date in one query.

code:

SELECT 
  option_symbol, 
  data_date, 
  expiration, 
  delta,
  (data_date + 45) AS "target_date",
  ABS((data_date + 45)-expiration) as "date_gap",
  ABS(delta + 0.5) as "delta_gap"
FROM 
  spydata
WHERE 
  data_date = '2017-10-02' AND
  ABS((data_date + 45)-expiration) = (SELECT(MIN(ABS((data_date + 45)-expiration))) FROM spydata WHERE data_date = '2017-10-02')
ORDER BY
  "delta_gap"
LIMIT 1

This query takes about 3.3 seconds to run and I have about 3200 distinct dates in this table and I'd prefer not to loop through each date for three hours.

# ? Dec 3, 2017 16:49

Busy Bee: Jul 13, 2004

Thank you for the clarification. I mentioned earlier in this thread that I have a 30 min SQL tech interview next week so I am trying to prepare for it. Not sure how complicated it will be but it will be done all on paper.

Another question:

code:

SELECT contactname, orderdate FROM c.customers 
JOIN o.orders ON c.customerid = o.customerid
WHERE orderdate = (SELECT MAX(orderdate) from oo.orders WHERE oo.customerID = c.customerID)

https://www.youtube.com/watch?v=ueULSny-sWU Around the 7 minute mark near the end, why did the person do "oo.orders" instead of o.orders?

# ? Dec 3, 2017 16:55

Shy: Mar 20, 2010

It's a subquery, he selects from the same table again, as if selecting from two different tables.

# ? Dec 3, 2017 17:01

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

mearn posted:

This query takes about 3.3 seconds to run and I have about 3200 distinct dates in this table and I'd prefer not to loop through each date for three hours.

I suspect the subquery is the culprit, and it also seems very superfluous if you just want the top record from the same tables you're querying.

This query should be equivalent (also simpler), does it speed things up?

code:

SELECT 
  option_symbol, 
  data_date, 
  expiration, 
  delta,
  (data_date + 45) AS "target_date",
  ABS((data_date + 45)-expiration) as "date_gap",
  ABS(delta + 0.5) as "delta_gap"
FROM 
  spydata
WHERE 
  data_date = '2017-10-02' 
ORDER BY
  "date_gap",
  "delta_gap"
LIMIT 1

# ? Dec 3, 2017 18:05

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

enjoy the ride; Lipstick Apathy

Busy Bee posted:

Thank you for the clarification. I mentioned earlier in this thread that I have a 30 min SQL tech interview next week so I am trying to prepare for it. Not sure how complicated it will be but it will be done all on paper.

Another question:
code:
SELECT contactname, orderdate FROM c.customers 
JOIN o.orders ON c.customerid = o.customerid
WHERE orderdate = (SELECT MAX(orderdate) from oo.orders WHERE oo.customerID = c.customerID)
https://www.youtube.com/watch?v=ueULSny-sWU Around the 7 minute mark near the end, why did the person do "oo.orders" instead of o.orders?

Small point of clarification: the syntax is actually

code:

SELECT contactname, orderdate FROM customers c
JOIN orders o ON c.customerid = o.customerid
WHERE orderdate = (SELECT MAX(orderdate) from orders oo WHERE oo.customerID = c.customerID)

Your aliases need to come after the db names, not as identifiers before hand.

As to your question: they're using a subquery to pull only the information from the most recent date. here's some pseudocode to illustrate the idea it's simplifying (this code will not run):

code:

SELECT contactname, orderdate FROM c.customers 
JOIN o.orders ON c.customerid = o.customerid
WHERE orderdate = max(o.orderdate)

You can't do it this way, though, since you're not grouping by anything and therefore you cannot find the result of max(o.orderdate) (among other more technical reasons probably). Instead, you use a subquery that selects from the same table as your base query to identify the most recent order date by itself, and use that as a condition in your where clause. Since you're pulling from a second instance of a table you're already selecting from, it needs a distinct alias since the queries are correlated, hence oo (I personally don't use a 2nd of the same letter when naming aliases, I prefer to just do something like o1 and o2 or oOuter and oInner, but eh).

e: small edit for correctness

kumba fucked around with this message at 02:53 on Dec 4, 2017

# ? Dec 3, 2017 19:16

Busy Bee: Jul 13, 2004

kumba posted:

Small point of clarification: the syntax is actually
code:
SELECT contactname, orderdate FROM customers c
JOIN orders o ON c.customerid = o.customerid
WHERE orderdate = (SELECT MAX(orderdate) from orders oo WHERE oo.customerID = c.customerID)
Your aliases need to come after the db names, not as identifiers before hand.

As to your question: they're using a subquery to pull only the information from the most recent date. here's some pseudocode to illustrate the idea it's simplifying (this code will not run):

So in all cases, the aliases need to come AFTER the database names? I swear I was working on a tutorial where the person put the aliases of the databases as identifiers before hand and the query ran fine. I probably interpreted it wrong though. I'll make sure to have the aliases for databases after the name.

And I can put the identifiers in the SELECT section like this without an issue, correct?

code:

SELECT c.contactname, o.orderdate FROM customers c
JOIN orders o ON c.customerid = o.customerid
WHERE orderdate = (SELECT MAX(orderdate) from orders oo WHERE oo.customerID = c.customerID)

Busy Bee fucked around with this message at 02:13 on Dec 4, 2017

# ? Dec 4, 2017 01:40

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

For the sake of the guy after you, explicitly use AS before aliases if you can.

code:


SELECT B.Poop
FROM dbo.Butts AS B
WHERE B.Butt_Owner_ID = 123456

# ? Dec 4, 2017 02:39

Busy Bee: Jul 13, 2004

Okay, will make sure to do that.

Another dumb question:

code:

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE artist IN ('Taylor Swift', 'Usher', 'Ludacris')

Why can't I write it like this?

code:

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE artist IN ('Taylor%', '%sher', 'Ludacri%')

Instead I have to write it all individually if I include the % like this?

code:

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE artist LIKE 'Taylor%' or artist LIKE '%sher' or artist LIKE 'Ludacri%'

# ? Dec 4, 2017 04:24

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

enjoy the ride; Lipstick Apathy

IN is looking for exact matches. LIKE allows for wildcards.

You can't use IN ('%something%') for the same reason you can't use WHERE column = '%something%'

# ? Dec 4, 2017 04:31

Busy Bee: Jul 13, 2004

kumba posted:

IN is looking for exact matches. LIKE allows for wildcards.

You can't use IN ('%something%') for the same reason you can't use WHERE column = '%something%'

But I can't do this either?

code:

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE artist LIKE ('Taylor%', '%sher', 'Ludacri%')

# ? Dec 4, 2017 04:37

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

enjoy the ride; Lipstick Apathy

Busy Bee posted:

But I can't do this either?

code:

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE artist LIKE ('Taylor%', '%sher', 'Ludacri%')

Nope. There's no analogue of IN for LIKE, at least in MSSQL. Not sure about other flavors.

# ? Dec 4, 2017 05:01

TheFluff: Dec 13, 2006; FRIENDS, LISTEN TO ME
I AM A SEAGULL
OF WEALTH AND TASTE

You should be really careful with LIKE anyway. It's a very crude tool and can be really slow. A LIKE condition with a wildcard at the beginning of the string (WHERE column LIKE '%foo') cannot be index optimized IIRC - it'll always need to scan the entire table. If you want fulltext search that actually works and isn't dog slow, look into an external indexer like Lucene, or if you need to combine fulltext search criteria with a complex query, you can try the builtin (in MSSQL) fulltext search engine.

# ? Dec 4, 2017 13:28

BabyFur Denny: Mar 18, 2003

Busy Bee posted:

But I can't do this either?

code:

SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE artist LIKE ('Taylor%', '%sher', 'Ludacri%')

You can do an RLIKE on an regex that matches all your cases. Have fun!

# ? Dec 4, 2017 16:38

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

Busy Bee posted:

Thank you for the clarification.

I moved onto the ANY and ALL operators and I'm having a hard time wrapping my head around it while I prepare for this analyst interview. Here is the resource I am using: https://www.w3schools.com/sql/sql_any_all.asp

I don't understand the use of the ALL operator, especially with a data set. I would assume its not that common for a column of data to ALL be the same where it returns TRUE?

None of the videos on YouTube all the tutorials really explains it well in using ANY / ALL operators.

In addition, why would someone want to use a subquery rather than using JOIN. Are they not similar?

Busy Bee posted:

code:
SELECT ProductName
  FROM Products
 WHERE ProductID = ALL (SELECT ProductID FROM Orders WHERE Status = 'Shipped')
This is where I am confused. If you go to this link here and look at the last example where they use ALL and if you run the query https://www.w3schools.com/sql/sql_any_all.asp there are no orders in quantity 10 that come up and the results are 0. Rather, if I change "ALL" to "ANY", then all the orders where quantity = 10 pops up. So in your code above, what is the advantage of using "ALL" instead of "ANY"? Would ANY show all the ProductID's where the Status is 'Shipped'?

First note: I have been writing SQL almost every working day for the last 10+ years and I have never used or even thought about using the ANY or ALL operators. If anyone brings them up in a tech interview they're probably smug shitlords you wouldn't want to work with.

Second note: The explanation and examples given by w3schools and Gatac^* on ALL are wrong. Here's a better page https://oracle-base.com/articles/misc/all-any-some-comparison-conditions-in-sql. ANY and ALL are about comparing a value to ANY or ALL of the values from a list or subquery. As you surmised, you're almost never going to have a list or subquery return all the same value, so Column =ALL (select value from subquery) is going to be pretty useless. But you can still use other operators, so Column >ALL (select value from subquery) would work just fine, except you'd never do that instead of Column > (select max(value) from subquery), and you'd never use Column =ANY (select value from subquery) instead of Column in (select value from subquery).

_{^*It's not Gatac's fault. ANY and ALL are kinda confusing, not really used all that often, and he was looking at the wrong info from the page you had linked. It is, however, w3schools' fault for giving out bad instruction, as is not uncommon.}

# ? Dec 4, 2017 16:55

duz: Jul 11, 2005; Come on Ilhan, lets go bag us a shitpost

TheFluff posted:

You should be really careful with LIKE anyway. It's a very crude tool and can be really slow. A LIKE condition with a wildcard at the beginning of the string (WHERE column LIKE '%foo') cannot be index optimized IIRC - it'll always need to scan the entire table. If you want fulltext search that actually works and isn't dog slow, look into an external indexer like Lucene, or if you need to combine fulltext search criteria with a complex query, you can try the builtin (in MSSQL) fulltext search engine.

In postgres at least you can index a reversed column, thus making a LIKE '%1234' optimized. You can also index a column for regex searches thanks to a plugin. That said, yes, don't just go throwing wildcards everywhere in if you don't actually need them.

# ? Dec 4, 2017 17:24

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

Jethro posted:

First note: I have been writing SQL almost every working day for the last 10+ years and I have never used or even thought about using the ANY or ALL operators. If anyone brings them up in a tech interview they're probably smug shitlords you wouldn't want to work with.

Same. I had literally forgotten that ANY/ALL existed.

# ? Dec 4, 2017 18:07

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

enjoy the ride; Lipstick Apathy

I don't think I ever knew they existed. I don't recall having ever learned about them

# ? Dec 4, 2017 18:11

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

I knew ANY/ALL existed, but they're in a bin in my mind labelled "pretty well unnecessary to use ever". Not in a bad way, just in a "when would you ever need this" kind of way.

# ? Dec 4, 2017 20:38

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

mearn posted:

I'm an idiot in over my head trying to find a way to improve my current situation.

I've got a table full of financial data, the relevant columns for the query I'm running are:
data_date - the date the data was published
expiration - the date the data is no longer useful
symbol - an identifier that follows the same data through it's lifespan
delta - a value I'm attempting to filter the data by.

Using PostgreSQL, I want to have target values for expiration and delta and return the symbol that's closest to my target values for each specific date. For example, I might be looking for a value that expires 45 days into the future and has a delta value of -0.5. It's unlikely both of those values will exist inside my dataset so I'm finding the closest values to those targets. Finding the target date is more important than the target delta. Right now I have a query that gets me the result I'm looking for when I specify a specific date but I'd much rather find a way to group by the data_date and return values for every distinct data_date in one query.
code:
SELECT 
  option_symbol, 
  data_date, 
  expiration, 
  delta,
  (data_date + 45) AS "target_date",
  ABS((data_date + 45)-expiration) as "date_gap",
  ABS(delta + 0.5) as "delta_gap"
FROM 
  spydata
WHERE 
  data_date = '2017-10-02' AND
  ABS((data_date + 45)-expiration) = (SELECT(MIN(ABS((data_date + 45)-expiration))) FROM spydata WHERE data_date = '2017-10-02')
ORDER BY
  "delta_gap"
LIMIT 1
This query takes about 3.3 seconds to run and I have about 3200 distinct dates in this table and I'd prefer not to loop through each date for three hours.

something like this?

code:

WITH closest_per_data_date AS (
  SELECT data_date, MIN(ABS((data_date + 45)-expiration)) OVER (PARTITION BY data_date) AS closest FROM spydata
)
SELECT 
  option_symbol, 
  data_date, 
  expiration, 
  delta,
  (data_date + 45) AS "target_date",
  ABS((data_date + 45)-expiration) as "date_gap",
  ABS(delta + 0.5) as "delta_gap",
  row_number() OVER (PARTITION BY data_date ORDER BY abs(delta + 0.5)) AS rownum
FROM
  spydata
  JOIN closest_per_data_date USING (data_date)
WHERE 
  ABS((data_date + 45)-expiration) = closest
  AND rownum = 1

i probably hosed it up but hopefully you get the idea?

# ? Dec 4, 2017 22:10

ChickenWing: Jul 22, 2010

Are row locks transitive in Oracle?

I have table A, B, and C.

A has PK foo
B has PK bar

B has an indexed FK foo that references A.foo
C has an indexed FK bar that references B.bar

I'm running SELECT things FROM A FOR UPDATE; which locks all rows returned.

I know that the rows in B referencing the rows in A that are returned by the query are locked.

The question is, are the rows in C referencing the locked rows in B also locked?

Today has been full of fun learning about SQL locks :shepface:

# ? Dec 5, 2017 20:50

Jose: Jul 24, 2007; Adrian Chiles is a broadcaster and writer

Nth Doctor posted:

I've never seen mass ways of doing this, but in SQL Server sp_rename is a thing. What is it you're really trying to do?

I was an idiot and moved all the tables across databases before i renamed them and was hoping I could do it easily after the fact rather than manually do it or transfer it again

# ? Dec 7, 2017 10:52

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

Jose posted:

I was an idiot and moved all the tables across databases before i renamed them and was hoping I could do it easily after the fact rather than manually do it or transfer it again

Hopefully it's easy enough to query sys.tables to write a script to do that for you. I can't be the only dev who uses SQL queries to generate ad-hoc scripts for large sets of data... right? :ohdear:

# ? Dec 7, 2017 14:07

Jose: Jul 24, 2007; Adrian Chiles is a broadcaster and writer

i use the object explorer details to mass generate scripts for me all the time but I think renaming a table full of data is something that it won't do?

On a similar subject though does anyone have a script or something similar to rebuild all fragmented indexes in a database?

edit: on an azure cloud database if that matters

Jose fucked around with this message at 21:04 on Dec 7, 2017

# ? Dec 7, 2017 16:24

AskYourself: May 23, 2005; Donut is for Homer as Asking yourself is to ...

Yes I happen to do that on my Azure SQL DB.

To get the index that need rebuilding I use this function :

code:

FUNCTION [dbo].[fnGetIndexFragmentation]
(
	@DatabaseName nvarchar(128) = NULL,
	@TableName nvarchar(128)	= NULL,
	@IndexName nvarchar(128)	= NULL,
	@MinimumFragmentation int	= 0
)
RETURNS TABLE 
AS
RETURN 
(
SELECT
	ISNULL(ROW_NUMBER() OVER(ORDER BY IndexStats.object_id ASC, Indexes.name ASC), -1) AS Id, 
	OBJECT_NAME(IndexStats.object_id) AS TableName, 
	Indexes.name AS IndexName, 
	avg_fragmentation_in_percent AS Fragmentation
FROM
	sys.dm_db_index_physical_stats(
		CASE WHEN @DatabaseName IS NULL THEN NULL ELSE DB_ID(@DatabaseName) END,
		CASE WHEN @DatabaseName IS NULL OR @TableName IS NULL THEN NULL ELSE OBJECT_ID(@TableName) END,
		NULL, 
		NULL, 
		NULL
		) AS IndexStats 
	INNER JOIN sys.indexes AS Indexes ON IndexStats.object_id = Indexes.object_id AND IndexStats.index_id = Indexes.index_id
WHERE
	(IndexStats.index_id <> 0)
	AND Indexes.name = CASE WHEN @IndexName IS NULL THEN Indexes.name ELSE @IndexName END
	AND IndexStats.avg_fragmentation_in_percent > @MinimumFragmentation
);

And then to rebuild the wanted indexes :

code:

var _Statement = $"ALTER INDEX [{anIndexName}] ON [{aTableName}] REBUILD WITH (ONLINE=ON);";

You'll need a bit more access than read/right to execute that tho. I don't remember the exact access needed tho.

# ? Dec 8, 2017 19:11

Kuule hain nussivan: Nov 27, 2008

Does anyone know if there is a way of applying a certain statement to all values being inserted into a column? I'm trying to think of a simple way to ensure that all values being added to a table have leading zeroes removed.

# ? Dec 11, 2017 15:59

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

Kuule hain nussivan posted:

Does anyone know if there is a way of applying a certain statement to all values being inserted into a column? I'm trying to think of a simple way to ensure that all values being added to a table have leading zeroes removed.

You're looking for a trigger. What you should be doing instead is sanitizing data before it gets to the database because sanitization isn't really the core competency of the database, but if you're dead set on not being able to do that, google "create trigger syntax <insert your database software name here>". You could also use a column constraint that checks for leading zeroes if your database supports it, which would be preferable to a trigger.

# ? Dec 11, 2017 16:14

Kuule hain nussivan: Nov 27, 2008

Munkeymon posted:

You're looking for a trigger. What you should be doing instead is sanitizing data before it gets to the database because sanitization isn't really the core competency of the database, but if you're dead set on not being able to do that, google "create trigger syntax <insert your database software name here>". You could also use a column constraint that checks for leading zeroes if your database supports it, which would be preferable to a trigger.

Most of the data coming in is sanitized, but there are some connections that aren't under our direct control, so people will be people and put in stuff that doesn't abide to data rules. I was trying to avoid triggers, so a check constraint seems to be the way to go. Should be pretty easy to implement as well. Thanks very much.

# ? Dec 12, 2017 10:21

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Sanitizing data prior to insert/amend is desirable in an application, but no substitute for establishing data integrity(*) within the database itself. For the scenario you've described, the check constraint is the best choice (and let us hope you're using a real database that permits such a simple-but-not-too-simple constraint). Always assume some newb with a database console will be modifying the data as they see fit.

* If you don't want to use the features of your database to help maintain your business needs, then remove yourself to the "WordPress with Rails flat files in ten seconds weee!" thread or something. :razz:

# ? Dec 12, 2017 15:48

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

enjoy the ride; Lipstick Apathy

I have a strange scenario that I can't figure out when trying to match a date to a string casted/converted to a date. Here's the following relevant bit:

code:

select
	substring(a.transdata,248,10), 
	r.*
	from #results r
	join locationDetails l
		on r.ServiceAddrID = l.ServiceAddrID 
		and l.TurnOffDate is not null
	join a_transactions a 
		on r.AccountNumber = a.AccountNumber
		and a.TransCode = 'specfic code'
	where
		l.TurnOffDate = cast(substring(a.transdata,248,10) as date)

So here's the deal: transdata is a varchar(max) field from a flatfile from one of our vendors. According to their data specifications, the 10 digit string from position 248 -> 257 is a date in the format 'YYYY-MM-DD' and the field l.TurnOffDate is a date field.

When I run this code, I get the standard error "Conversion failed when converting date and/or time from character string." which leads me to believe that there is some malformed data in the transdata field somewhere. So, I tried this:

code:

select
	substring(a.transdata,248,10), 
        try_cast(substring(a.transdata,248,10) as date),
	r.*
	from #results r
	join locationDetails l
		on r.ServiceAddrID = l.ServiceAddrID 
		and l.TurnOffDate is not null
	join a_transactions a 
		on r.AccountNumber = a.AccountNumber
		and a.TransCode = 'specfic code'
	where
		 try_cast(substring(a.transdata,248,10) as date) is null

This should, in theory, give me the results that are confounding the above query, however it comes back with no results. What gives? I feel like I have to be missing something obvious?

# ? Dec 14, 2017 16:09

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

If this is a quiz, my guess would be that SQL Server is optimizing the query by running the WHERE before the JOIN, so in the second query the JOIN conditions are hiding the results. You could check the execution plan (Ctrl-L) to see if this is the case.

If this is not a quiz, you could have just dropped the WHERE and the JOINS and run a plain

code:

select
	substring(a.transdata,248,10), 
        try_cast(substring(a.transdata,248,10) as date)
from a.transactions a

which would have immediately answered your doubts.

# ? Dec 14, 2017 19:03

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

kumba posted:

I have a strange scenario that I can't figure out when trying to match a date to a string casted/converted to a date. Here's the following relevant bit:
code:
select
	substring(a.transdata,248,10), 
	r.*
	from #results r
	join locationDetails l
		on r.ServiceAddrID = l.ServiceAddrID 
		and l.TurnOffDate is not null
	join a_transactions a 
		on r.AccountNumber = a.AccountNumber
		and a.TransCode = 'specfic code'
	where
		l.TurnOffDate = cast(substring(a.transdata,248,10) as date)
So here's the deal: transdata is a varchar(max) field from a flatfile from one of our vendors. According to their data specifications, the 10 digit string from position 248 -> 257 is a date in the format 'YYYY-MM-DD' and the field l.TurnOffDate is a date field.

When I run this code, I get the standard error "Conversion failed when converting date and/or time from character string." which leads me to believe that there is some malformed data in the transdata field somewhere. So, I tried this:
code:
select
	substring(a.transdata,248,10), 
        try_cast(substring(a.transdata,248,10) as date),
	r.*
	from #results r
	join locationDetails l
		on r.ServiceAddrID = l.ServiceAddrID 
		and l.TurnOffDate is not null
	join a_transactions a 
		on r.AccountNumber = a.AccountNumber
		and a.TransCode = 'specfic code'
	where
		 try_cast(substring(a.transdata,248,10) as date) is null
This should, in theory, give me the results that are confounding the above query, however it comes back with no results. What gives? I feel like I have to be missing something obvious?

Do you know for a fact that locationDetails and a_transactions have matching rows in them? What happens if you flip to a LOJ for those two?

# ? Dec 14, 2017 19:07

Just-In-Timeberlake: Aug 18, 2003

can someone help me out with this pivot, I'm a loving moron when it comes to this

I want to take these results:

code:

name	col	        value
---------------------------------------
guid	name	guid
guid	value	9e1cb05b-a7ba-4283-abb4-29d0212ee303
label	name	label
label	value	Invoice issues
email	name	email
email	value	abc<at>abc.com
format	name	format
format	value	html

and pivot them into this:

code:

guid       label       email      format
------------------------------------------------------
9e1...     Invoi....   abc@...  html

# ? Dec 20, 2017 20:41

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

i don't see how that's going to work, do you have another column that groups all the rows together? if not, how am i supposed to know that the label 'Invoice issues' goes along with the guid 9e1cb05b-a7ba-4283-abb4-29d0212ee303? but if you do have another column to group them, you can use crosstab or jsonb tricks in postgres, or pivot in sql server, or if you're using oracle or mysql then you get to gently caress yourself arseways

# ? Dec 21, 2017 05:06

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

enjoy the ride; Lipstick Apathy

I want to find who designed whatever God forsaken db that data is from and strangle them

Assuming all the info that goes together is clumped you might be able to get away with some clever use of FOR XML PATH and STUFF in mssql but I'm struggling to think it through

e: if you can assume there's always four columns and they're always in the proper order you could do it with nested while loops but that would likely be terribly performant on any meaningfully sized dataset

kumba fucked around with this message at 05:33 on Dec 21, 2017

# ? Dec 21, 2017 05:18

Just-In-Timeberlake: Aug 18, 2003

DELETE CASCADE posted:

i don't see how that's going to work, do you have another column that groups all the rows together? if not, how am i supposed to know that the label 'Invoice issues' goes along with the guid 9e1cb05b-a7ba-4283-abb4-29d0212ee303? but if you do have another column to group them, you can use crosstab or jsonb tricks in postgres, or pivot in sql server, or if you're using oracle or mysql then you get to gently caress yourself arseways

going by this SO post it's doable, I just can't get it to work

I should also clarify that the source data in my example above is the unpivoted data like in the link, I got that far at least.

# ? Dec 21, 2017 14:59

go play outside Skyler: Nov 7, 2005

I have a table called "Parameter", which contains the following columns:

Is there any way for me to get, with a single query, all the Parameters for a given DeviceId, but only the latest ones (sorted by Timestamp)? That means, I only want the latest values in that Params table.

I tried like this, but no dice:

code:

select
            *
            from 
                Parameter
            where
                DeviceId = 1
            group by 
                Type
            order by
                Timestamp asc

I'm sure this is super-simple stuff, but my Relational Databases class was a long time ago!

# ? Dec 21, 2017 15:13

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

enjoy the ride; Lipstick Apathy

go play outside Skyler posted:

I have a table called "Parameter", which contains the following columns:

Is there any way for me to get, with a single query, all the Parameters for a given DeviceId, but only the latest ones (sorted by Timestamp)? That means, I only want the latest values in that Params table.

I tried like this, but no dice:
code:
select
            *
            from 
                Parameter
            where
                DeviceId = 1
            group by 
                Type
            order by
                Timestamp asc
I'm sure this is super-simple stuff, but my Relational Databases class was a long time ago!

If I'm understanding your question correctly, try this:

code:

select *
from Parameter p
where DeviceID = 1
and Timestamp = (
	select MAX(Timestamp)
	from Parameter p2
	where p.DeviceID = p2.DeviceID
	)
order by Timestamp asc

# ? Dec 21, 2017 15:18

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 21:46

go play outside Skyler: Nov 7, 2005

kumba posted:

If I'm understanding your question correctly, try this:

code:

select *
from Parameter p
where DeviceID = 1
and Timestamp = (
	select MAX(Timestamp)
	from Parameter p2
	where p.DeviceID = p2.DeviceID
	)
order by Timestamp asc

Unfortunately that only gives me back one Parameter. They have very chaotic Timestamps.

See for example the "PARAMETER_SWVER" parameter for Device 1. The latest one I have is from 2017-09.07 and its value is 15.5.1120.

See the different Timestamps. I want to get all the different parameters for a certain device, but only the latest ones.

Something like what I did with this query, except it's grouping by the first found Parameter instead of the one with the latest Timestamp (see PARAMETER_SWVER which should have 15.5.1120, from 2017-09-07).

e: sorry for the huge images, retina mbp

go play outside Skyler fucked around with this message at 15:33 on Dec 21, 2017

# ? Dec 21, 2017 15:29

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

«‹›303 »