how to modify 2 different sets with single query

  • 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?

  • 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

  • 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".

  • 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