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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy