PhantomOfTheCopier posted:

Which database?

PhantomOfTheCopier posted:

I don't know that I can answer without sounding utterly biased. First, I knew you were going to say MySQL... probably based on the wording in the original question. In all honesty, you might as well just make a single varchar(random number) like every other MySQL user and call it good, because everything is likely to be handled, ultimately, in the application, so why bother with all that noise in the database if it doesn't gain you anything.

Now then, one choice is to store the data that way, or encoded in such a way that the application does most of the interpretation with no effect on database performance because you aren't running optimized queries over those fields anyway. Encoding could be anything from a blob to custom formats, markup, etcetera. This pushes all the integrity off to the application. Indeed, one of the main issues with your proposal is integrity: Nothing prevents all three from being non-null.

As there is no programmatic union (a la C, "store multiple things in the same space"), you either need to use a database that's custom and gives it to you, or, lacking that, if you decide you must have a union type, you can create a custom data type. In that case, as in your proposed structure, you'd really need to store another field identifying the actual type. This is no different than a union, where the data stored is fundamentally useless for decoding purposes unless you know whether it's an integer, float, character, pointer, whatever. Presumably, you would then be able to select on specific types, or wrap CASE statements around each field to enforce NULL based on the registered type for that row. Even if the application "accidentally" loaded (different) values into all three fields, you'd have a unique result for the row.

A third approach is to separate the fields into their own tables, only inserting a referenced row into the one appropriate table. While this provides some optimization on queries for subtypes, it seems a bit ridiculous for the application you've described. It seems highly unlikely that your primary use case is "searching for test_dec between 0.73 and 0.81", whence it's unlikely that you'd even much care about indexing. If all queries you perform are already restricted to groups/classes/quarters, a table scan of the relevant rows will be fast enough.

Please don't tell us this was a homework question.

Jethro posted:

One service request table with three supplemental tables is better than three service request tables, but if it's just a handful of extra fields, then you could also just have one table with all the fields and then just leave them empty for the service requests that don't need them.

eriddy posted:

kumba posted:

I know in MSSQL you can group by a case statement, can you do that in MYSQL?

pseudo-code:

code:

select thing, sum(value)
from mytable
group by
     case when thing not in ('thing1','thing2') then 'other'
     else thing
     end
order by thing

i think that works?

kumba posted:

Try this:

code:

SELECT
    CASE WHEN COALESCE(`product_subclass`,'Grand Total') IN ('IMAGERY', 'SAMPLING','Grand Total') THEN COALESCE(`product_subclass`,'Grand Total') ELSE 'OTHER' END AS `name`,
    ROUND(SUM(`previous_sales`), 2) AS `previous_sales`,
    ROUND(SUM(`previous_gp`), 2) AS `previous_gp`,
    ROUND(SUM(CASE WHEN `unit` = 'ACRE' THEN `previous_quantity` ELSE 0 END), 2) AS `previous_quantity`,
    ROUND((SUM(`previous_gp`) / SUM(`previous_sales`)) * 100, 2) AS `previous_percent`,
    ROUND(SUM(`current_sales`), 2) AS `current_sales`,
    ROUND(SUM(`current_gp`), 2) AS `current_gp`,
    ROUND(SUM(CASE WHEN `unit` = 'ACRE' THEN `current_quantity` ELSE 0 END), 2) AS `current_quantity`,
    ROUND((SUM(`current_gp`) / SUM(`current_sales`)) * 100, 2) AS `current_percent`
FROM `metrics`
INNER JOIN `locations` ON `metrics`.`location_id` = `locations`.`id`
WHERE `product_subgroup` = 'FERTILIZER.SERVICES'
    AND `product_class` = 'LARESCO'
    AND `locations`.`id` = '5'
GROUP BY
    CASE WHEN `product_subclass` IN ('IMAGERY', 'SAMPLING')
    THEN `product_subclass`
    ELSE 'OTHER' END ASC WITH ROLLUP

That rollup row is NULL for Purchase type, so your case expression for the name is doing something funky with it I think. Give that a shot

e: you will probably have to fix some apostrophes because microsoft

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.

Ruggan posted:

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

karms posted:

Kinda poo poo that he did not put a date on it, but oh well.

Shy posted:

DataGrip is nice.

TheFluff posted:

If I'm understanding you correctly you can just use a left join and put the filter on instructions.type in the join clause instead of in the where clause, like so:

SQL code:

select
  orders.id,
  instructions.notes
from
  orders
  left join instructions on (
    orders.id = instructions.order_id
    and instructions.type = "A"
  )

For inner joins there is no difference between putting a condition in the join clause vs putting it in the where clause, but for left joins it matters - if you put instructions.type = "A" in the where clause instead you won't get order id 3 in the resultset.

kiwid posted:

Been a while since I've had to write any SQL since being spoiled with an ORM.

I need to join a specific related row in a one-to-many relationship. For example:

orders

code:

| id |
|----|
| 1  |
| 2  |
| 3  |

instructions

code:

| order_id | type | notes |
|----------|------|-------|
| 1        | A    | blue   |
| 1        | B    | red   |
| 1        | C    | yellow   |
| 2        | A    | green   |
| 3        | C    | purple   |

I want a column in my result with only instructions with type A. For example:

code:

| id | notes |
|----|-------|
| 1  | blue  |
| 2  | green |
| 3  | null  |

Is the only way to do this by using a subquery on the join? Also type and order_id are a unique index and so there can't be multiple of the same type for an order.

Heavy_D posted:

In the pivot approach, if you add something like:

code:

LEFT JOIN
(
   (SELECT order_id, instruction 
      FROM instructions) AS instr_rows
     PIVOT
   (MIN(instruction) 
    FOR instruction IN ([A],[B],[C])) AS instr_cols
)
ON instr_cols.order_id = orderlog.orderlog_nbr

You should have access to columns instr_cols.[A], instr_cols.[B] and instr_cols.[C] in your main query, which you can test for being null/non-null using a case statement in the same manner as your strip_test column.

quote:

[2021-06-07 15:44:27] production.ERROR: SQLSTATE[40001]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 75) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Nth Doctor posted:

Our app is built around accepting dirty reads as okay, and squeezing every ounce of performance out of the database server. For years, using (NOLOCK) was a near-mandatory standard. Several years back, we discovered SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED aka STILRU which is basically batch level NOLOCK and is the norm now.

We know dirty reads are a thing, but in the interest of deadlock dodging, we accept the risks. It's not the greatest because of the reads issue, and a slightly more conservative SET TRANSACTION ISOLATION LEVEL SNAPSHOT ISOLATION.

Here's an article I skimmed: https://www.sqlshack.com/snapshot-isolation-in-sql-server/

Instant edit:
I would also do some profiling to see why your CRUD is longrunning enough to cause deadlock issues. Maybe its nothing and has to do with the locks that SELECTs are taking on underlying rows, but it's worth a look.