CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Sanprof
Sanprof

Reputation: 409

Check data from SELECT and then INSERT new values

So, I have some table with data, I need get a new table with new data based on old data and then insert them into temp table for the next processing.

But I have got some problem with my SQL

DECLARE @PGNPGE float,
            @PGHTTP400PCT float,
            @PGHTTP500PCT float,
            @PGSLPCT float,
            @PGTME float;

    DECLARE @tempTable TABLE (
        PViews int, 
        Http400 int, 
        Http500 int, 
        PTime int, 
        ExclSimul int, 
        DispSimul int, 
        SlowPages int, 
        AESWA int
    );

    IF EXISTS (SELECT COUNT(*) FROM [ApplicationThreshold] WHERE [ApplicationID] = @AppID)
    BEGIN
        SELECT @PGNPGE = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 6;
        SELECT @PGHTTP400PCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 31;
        SELECT @PGHTTP500PCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 12;
        SELECT @PGSLPCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 49;
        SELECT @PGTME = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 72;
    END
    ELSE
    BEGIN
        SELECT @PGNPGE = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 6;
        SELECT @PGHTTP400PCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 31;
        SELECT @PGHTTP500PCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 12;
        SELECT @PGSLPCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 49;
        SELECT @PGTME = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 72;
    END

    INSERT INTO @tempTable (
        PViews, 
        Http400, 
        Http500, 
        PTime, 
        ExclSimul, 
        DispSimul, 
        SlowPages, 
        AESWA )
    SELECT 
        [PViews] = CASE WHEN sel.[PGNPGE] < @PGNPGE THEN 1 ELSE 0 END,
        [Http400] = CASE WHEN sel.[PGHTTP400PCT] > @PGHTTP400PCT THEN 1 ELSE 0 END, 
        [Http500] = CASE WHEN sel.[PGHTTP500PCT] > @PGHTTP500PCT THEN 1 ELSE 0 END, 
        [PTime] = CASE WHEN sel.[PGTME] > @PGTME THEN 1 ELSE 0 END, 
        [ExclSimul] = CASE WHEN ([PViews] + [Http400] + [Http500] + [PTime]) >= 1 THEN 1 ELSE 0 END, 
        [DispSimul] = CASE WHEN ([PViews] + [Http400] + [Http500] + [PTime]) > 1 THEN ([PViews] + [Http400] + [Http500] + [PTime]) ELSE 0 END, 
        [SlowPages] = CASE WHEN sel.[PGSLPCT] > @PGSLPCT THEN 1 ELSE 0 END, 
        [AESWA] = CASE WHEN [ExclSimul] >=1  THEN 0 ELSE [SlowPages] END
    FROM (SELECT [PGNPGE], [PGHTTP400PCT], [PGHTTP500PCT], [PGSLPCT], [PGTME]
            FROM [dbo].[TimeValue]
            WHERE [ApplicationID] = @AppID
            AND [Time] BETWEEN @DateFrom AND @DateTo) sel;

SELECT * FROM @tempTable;

and errors where I'm adding new values

Msg 207, Level 16, State 1, Line 59
Invalid column name 'PViews'.

Msg 207, Level 16, State 1, Line 59
Invalid column name 'Http400'.

Msg 207, Level 16, State 1, Line 59
Invalid column name 'Http500'.

Msg 207, Level 16, State 1, Line 59
Invalid column name 'PTime'.

Msg 207, Level 16, State 1, Line 60
Invalid column name 'PViews'.

Msg 207, Level 16, State 1, Line 60
Invalid column name 'Http400'.

Msg 207, Level 16, State 1, Line 60
Invalid column name 'Http500'.

Msg 207, Level 16, State 1, Line 60
Invalid column name 'PTime'.

Msg 207, Level 16, State 1, Line 60
Invalid column name 'PViews'.

Msg 207, Level 16, State 1, Line 60
Invalid column name 'Http400'.

Msg 207, Level 16, State 1, Line 60
Invalid column name 'Http500'.

Msg 207, Level 16, State 1, Line 60
Invalid column name 'PTime'.

Msg 207, Level 16, State 1, Line 62
Invalid column name 'ExclSimul'.

Msg 207, Level 16, State 1, Line 62
Invalid column name 'SlowPages'.

Upvotes: 0

Views: 128

Answers (1)

chridam
chridam

Reputation: 103365

Try using a CTE to insert data in the table variable:

declare @PGNPGE float,
    @PGHTTP400PCT float,
    @PGHTTP500PCT float,
    @PGSLPCT float,
    @PGTME float;

declare @tempTable TABLE 
(
    PViews int, 
    Http400 int, 
    Http500 int, 
    PTime int, 
    ExclSimul int, 
    DispSimul int, 
    SlowPages int, 
    AESWA int
);

IF EXISTS (SELECT COUNT(*) FROM [ApplicationThreshold] WHERE [ApplicationID] = @AppID)
BEGIN
    SELECT @PGNPGE = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 6;
    SELECT @PGHTTP400PCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 31;
    SELECT @PGHTTP500PCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 12;
    SELECT @PGSLPCT = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 49;
    SELECT @PGTME = Value FROM [ApplicationThreshold] WHERE [ThresholdID] = 72;
END
ELSE
BEGIN
    SELECT @PGNPGE = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 6;
    SELECT @PGHTTP400PCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 31;
    SELECT @PGHTTP500PCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 12;
    SELECT @PGSLPCT = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 49;
    SELECT @PGTME = Value FROM [ApplicationTypeThreshold] WHERE [ThresholdID] = 72;
END

;with ThresholdCTE
as
(
    SELECT 
        case when sel.[PGNPGE] < @PGNPGE then 1 else 0 end as [PViews],
        case when sel.[PGHTTP400PCT] > @PGHTTP400PCT then 1 else 0 end as [Http400], 
        case when sel.[PGHTTP500PCT] > @PGHTTP500PCT then 1 else 0 end as [Http500], 
        case when sel.[PGTME] > @PGTME then 1 else 0 end as [PTime], 
        case when ([PViews] + [Http400] + [Http500] + [PTime]) >= 1 then 1  else 0 end as [ExclSimul], 
        case when ([PViews] + [Http400] + [Http500] + [PTime]) > 1 then ([PViews] + [Http400] + [Http500] + [PTime]) else 0 end as [DispSimul], 
        case when sel.[PGSLPCT] > @PGSLPCT then 1 else 0 end as [SlowPages], 
        case when [ExclSimul] >=1 then 0 else [SlowPages] end as [AESWA]
    from [dbo].[TimeValue]
    where [ApplicationID] = @AppID
          and [Time] between @DateFrom and @DateTo
)

insert into @tempTable 
(
    PViews, 
    Http400, 
    Http500, 
    PTime, 
    ExclSimul, 
    DispSimul, 
    SlowPages, 
    AESWA 
)
select
    PViews, 
    Http400, 
    Http500, 
    PTime, 
    ExclSimul, 
    DispSimul, 
    SlowPages, 
    AESWA 
from [ThresholdCTE]


SELECT * FROM @tempTable;

Upvotes: 0

Related Questions