Viewing 15 posts - 2,851 through 2,865 (of 7,613 total)
You need a separate table to specify a unique entry for ( [Entity Name], [Entity EIN] ).
(Actually, hopefully EIN by itself would be unique. If it is, use just that. ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 15, 2019 at 6:08 pm
Which specific brand of SQL? MySQL? SQL Server? Oracle (not likely in a 2-person shop, too expensive)?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 15, 2019 at 2:28 pm
You can't pass expressions as parameter values. You need to resolve the expression yourself and pass only a single (scalar) value. For example:
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(MONTH,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 12, 2019 at 3:33 pm
I don't know that you'll be able to fix it, particularly without the details of how the "standard audit dates" are maintained.
It seems like this is an integrity check to...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 12, 2019 at 3:25 pm
Right, good point. I meant to do that. You just do an INNER JOIN to the table of allowed values, with a WHERE clause(s) if needed .
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2019 at 6:55 pm
Quite right. If you wanted to use a trigger, you'd have to put a trigger(s) on the table(s) used within the view, not on the view itself.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2019 at 3:40 pm
Sure. Based on the originally posted query:
SELECT @currentValue, SomeField
FROM SomeTable
WHERE @currentValue = 'Test1'
UNION ALL
--
SELECT @currentValue, SomeField
FROM AnotherTable
WHERE @currentValue = 'Test2'
UNION ALL
--
SELECT @currentValue, SomeField
FROM AThirdTable
WHERE @currentValue = 'Test3'
UNION...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2019 at 3:26 pm
Yes, I should have said "and without a loop".
For me, I'd use UNION ALL rather than a loop for something like this. SQL will have to parse all the SQL...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2019 at 2:30 pm
How would you pull data from at least 4 different tables efficiently without using a UNION ALL?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 8:52 pm
I doubt you need a temp table. You can just UNION ALL all the different query results. If a given query returns no rows, that's OK, it will...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 6:27 pm
Do you want to list inactive data in the report?
If not, you can just join to the other tables including "AccountStatus IN (1)" in the JOIN ON clause, something like...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 6:10 pm
Personally I'd use CROSS APPLY:
SELECT sl.Name, sl.Country, types.type
FROM Sales_Ledger sl
CROSS APPLY ( VALUES([Type 1]), ([Type 2]), ([Type 3]), ([Type 4]) ) AS types(type)
WHERE types.type > ''
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 3:17 pm
Hopefully SQL's split algorithm isn't that poor for bit columns.
But, if it turns out to be, create an index on:
( MyTable_ID, is_MyTable_Text_NULL ).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2019 at 8:22 pm
Add a bit column to indicate null status of [MyTable_Text], then index on that column.
ALTER TABLE [dbo].[MyTable] ADD is_MyTable_Text_NULL AS CAST(CASE WHEN MyTable_Text IS NULL THEN 1 ELSE 0 END...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2019 at 4:50 pm
You don't want index_columns and indexes in the query as it exists, because it will just generate duplicate rows for no reason.
SELECT TOP (1)
st.name [Table Name],
c.name [Column Name],
t.name [Data Type]
FROM...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2019 at 4:43 pm
Viewing 15 posts - 2,851 through 2,865 (of 7,613 total)