August 15, 2019 at 7:37 pm
CREATE TABLE #KjType(
[Id] [nvarchar](50) NOT NULL,
[Date] date NOT NULL,
[Money] [decimal](32,8) NOT NULL)
INSERT INTO #KjType
([Id]
[Date]
,[Money])
SELECT DISTINCT D.Id
,D.Date
,D.Money
FROM dbo.Department D
INNER JOIN dbo.Employee L ON D.empid = L.empid
INNER JOIN dbo.people j on D.PId = j.PId
WHERE j.Grp = 'C1' AND NOT J.Code = 'L3'
INSERT INTO #KjType
([Id]
[Date]
,[Money])
SELECT DISTINCT D.Id
,D.Date
,D.Money
FROM dbo.Department D
INNER JOIN dbo.Employee L ON D.empid = L.empid
INNER JOIN dbo.people j on D.PId = j.PId
WHERE j.Grp <> 'C1' AND NOT J.Code = 'L3'
I have 2 different sets of select statement; and i am trying to insert different sets of data into single temp table
the only difference is j.Grp columnl first set including and second is excluding.
Is there a way can we use case statement in the select list to make it only single query instead of 2 different sets?
August 15, 2019 at 7:48 pm
Looks to me as though you can simplify your WHERE clause to
WHERE j.Code <> 'L3'
and do the whole thing as a single INSERT.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 15, 2019 at 8:13 pm
Technically, if j.Grp is NULLable, I think you'd need to use:
WHERE j.Grp IS NOT NULL AND j.Code <> 'L3'
to insure the same results as the original code.
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".
August 15, 2019 at 9:05 pm
There are no generic "id" in RDBMS, or generic dates, etc. A table has to have a key. Queries do not modify anything. MONEY is an old vague reserved word in T-SQL. Do you have more than one department? The term "People" is never valid in RDBMS; a person plays a role in a data model, such as an employee.
CREATE TABLE Foobars
(foo_id NVARCHAR(50) NOT NULL PRIMARY KEY, -- my guess!!!
foo_date DATE DEFAUT CURRENT_TIMESTAMP NOT NULL,
foobar_cost DECIMAL(32,8) NOT NULL);
SELECT DISTINCT D.department_id , D.something_date, D.department_cost, J.something_grp
FROM Departments AS D, Personnel AS L,
People AS J -- never use this table!
WHERE D.emp_id = L.emp_id
AND J.postal_code <> 'L3';
>> I have 2 different sets of a select statement, and I am trying to insert different sets of data into single temp table the only difference is J.grp column first set including and second is excluding <<
Why? An SQL programmer would create a VIEW. But an old punchcard programmer would do it like you are! Just show your something_grp column (CASE something_grp WHEN 'C1' THEN 'C1' ELSE 'not C1' END)
>>> Is there a way can we use a CASE statement in the select list to make it only single query instead of 2 different sets? <<
CASE is an expression in SQL, not a statement.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply