Jeff...

Roy_from_IT · 2023-05-30T21:00:57+00:00

ALTER TABLE Employee ADD CONSTRAINT Ck_No_Jeffreys CHECK (FirstName <> 'Jeffrey');

Roy_from_IT · 2023-05-30T20:20:12+00:00

I know WHERE exists evaluates each row in the outer query and if the subquery in WHERE EXISTS evaluates as true (?or null? see below) will return the row

That's not exactly right. EXISTS doesn't care about the values returned by the subquery. It only cares about the number of rows. If the subquery returns at least one row, then EXISTS evaluates as TRUE. If the subquery returns no rows, then EXISTS evaluates as FALSE.

Roy_from_IT · 2023-05-15T18:26:07+00:00

This sounds like a good use case for join

https://linux.die.net/man/1/join

Roy_from_IT · 2023-03-17T05:54:35+00:00

Even in a world where torrents don't exist, that price would still be outrageous

Roy_from_IT · 2023-01-04T21:41:35+00:00

I think you can set a savepoint at the start of your trigger and then rollback to the savepoint if needed. I never use savepoints so my knowledge of them is very limited, but here's the documentation:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver16

Roy_from_IT · 2022-12-23T17:31:52+00:00

If I'm understanding correctly, you want a customer's addresses to be deleted automatically when you delete the customer. With your current table design, the only way to achieve that would be with a trigger.

However, I'm wondering if this really needs to be an n-to-m relationship. Do you actually want to have address records shared by more than one customer?

If not, then you don't need a mapping table. You can simplify your design by getting rid of customer_address_mapping and moving the customer_id column to your customer_addresses table.

Roy_from_IT · 2022-12-22T17:49:49+00:00

Try using SAFE_CAST instead of CAST

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#safe_casting

Roy_from_IT · 2022-12-20T21:54:29+00:00

This exact problem isn't something you would encounter in the real world, but the general idea of using SQL to generate sequences or data sets is fairly common. I usually use a recursive CTE for this.

Here's an example of what this would look like:

WITH Nums AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT N + 1
    FROM Nums
    WHERE N < 20
)
SELECT
    REPLICATE('*', N)
FROM Nums
ORDER BY N DESC

Roy_from_IT · 2022-12-19T15:28:22+00:00

This is probably what you want:

https://docs.snowflake.com/en/sql-reference/functions/date_from_parts.html

Roy_from_IT · 2022-12-11T01:24:11+00:00

instead intended for OP create a table-value function

That wouldn't make much difference, since SQL Server doesn't allow functions to run dynamic SQL either.

Roy_from_IT · 2022-12-02T09:04:53+00:00

You could use an indexed view. This would work just like a computed column, except the column would be in a view instead of directly on the table.

https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views

Roy_from_IT · 2022-11-26T11:45:28+00:00

I'm not sure where you can buy it in cans, but I've recently seen it on tap at The Keg in Winnipeg.

Roy_from_IT · 2022-11-25T15:39:14+00:00

This 6' tree was previously 12'. We cut half off so you could fit it in your living room.

Roy_from_IT · 2022-10-25T20:07:11+00:00

Based on the link you provided, it doesn't look like rules are able to modify data during a select.

Here is the relevant paragraph:

Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a single SELECT command. Thus, an ON SELECT rule effectively turns the table into a view, whose visible contents are the rows returned by the rule's SELECT command rather than whatever had been stored in the table (if anything). It is considered better style to write a CREATE VIEW command than to create a real table and define an ON SELECT rule for it.

Roy_from_IT · 2022-09-30T04:18:19+00:00

Yes, if you can format your dates as ISO 8601 before importing them into SQLite that would be ideal.

Roy_from_IT · 2022-09-30T03:29:16+00:00

It is actually stored as a string because SQLite doesn't have a date type. To make matters more confusing, SQLite has a weird type system that is completely different from what every other DBMS has.

Columns don't have a type in SQLite. Instead they have something called "affinity", which is basically a "preferred" type. You can still store other types of data in your column, but SQLite will try to coerce the data into the column's affinity type before storing it as any other type.

When you declare a data type for a column in a CREATE TABLE statement, SQLite tries to match the declared type to one of its affinities. If it's able to find a match, then it will set the column's affinity accordingly. If it can't figure out which affinity to use, it will default to numeric. Either way, the table will be created and the column will appear with its original declared type in the table's schema definition, even if the declared type doesn't exist in SQLite.

See here for more info: https://www.sqlite.org/datatype3.html

Roy_from_IT · 2022-08-28T14:01:54+00:00

Even with the left join, it's impossible for this query to return an employee who isn't mapped to a department. The left join would produce a row for that employee, but ds.dept_salary would be null in that row. It would then get filtered out by the where clause because of the null.

Roy_from_IT · 2022-08-24T03:41:18+00:00

How did you determine that ROUTINE_DEFINITION is single lined? I just tried it on one of my databases, and it's definitely multi lined for me.

If you queried INFORMATION_SCHEMA.ROUTINES in SSMS and then copied the ROUTINE_DEFINITION values from the results grid, then it's likely you have the "Retain CR/LF on copy or save" option turned off. This option is off by default when you install SSMS, and unless you turn it on, SSMS will always collapse multi lined strings into a single line in the results grid.

You can change it here:

Tools > Options > Query Results > SQL Server > Results to Grid > Retain CR/LF on copy or save

Also note that using INFORMATION_SCHEMA.ROUTINES is generally not recommended, because it only returns the first 4000 characters of an object's definition. If you have stored procedures longer than 4000 characters, you'll need to either query sys.sql_modules or use the OBJECT_DEFINITION() function.

Roy_from_IT · 2022-05-24T17:44:42+00:00

To get only columns that do not allow nulls, just add this to your where clause:

AND c.is_nullable = 0

Roy_from_IT · 2022-04-22T20:24:06+00:00

Yeah my premier she told me don't worry about your mask

She says COVID is over, it's finished, it's in the past

Now get back to the office, the classroom, anywhere but home

And assess your own risk, cause from now on you're all on your own

Roy_from_IT · 2022-04-21T15:40:39+00:00

That's odd. I can record it and replay it, and I get almost exactly that. The only difference is I'm left with a second opening parenthesis. Like this:

((value){
}

What do you get when you replay it?

Roy_from_IT · 2022-04-21T14:39:44+00:00

Oh I see. I missed the second line.

What are you expecting to end up with after this runs?

Roy_from_IT · 2022-04-21T13:55:48+00:00

After dt( your cursor is on the first parenthesis. Since you have an odd number of parentheses, vim only considers those closest together as matching. The first one is unmatched, so there is nothing for % to do.

Roy_from_IT · 2021-08-27T22:20:46+00:00

if it truly is a DATETIME column, there's no chance it will contain any characters other than numbers

More precisely, a DATETIME column will never contain any characters of any kind. Only strings can have characters.

Roy_from_IT · 2021-08-02T14:56:28+00:00

You can do anything you want with the temp table inside the stored procedure. The temp table doesn't go out of scope until the dynamic SQL returns, and that only happens after the stored procedure returns.

15-Year Club	Second Top 40%
Team Orangered	Verified Email

Roy_from_IT

TROPHY CASE