Post reply

converting statement

  • September 22, 2005 at 5:44 pm

    #87838

    Hi there,

    At the moment, I am happy that the first (test) statement does what I need, but I just can't seem to work why the 2nd won't work (even though the syntax checking is ok.)

    ---------test statement---------------------

    DECLARE @table TABLE(CallID CHAR(8), GroupName VARCHAR(55), CustPendCase CHAR(1))

    INSERT @table(CallID, GroupName, CustPendCase)

     SELECT '00500588','Desktop',Null UNION ALL

     SELECT '00500588','SD Followup',Null UNION ALL

     SELECT '00500600','Server',Null UNION ALL

     SELECT '00500600','Service Desk',Null UNION ALL

     SELECT '00500777','SD Followup',Null UNION ALL

     SELECT '00500777','Network',Null

    SELECT CallID, GroupName, CustPendCase FROM @table

    UPDATE @table

    SET CustPendCase =

     CASE when CallId in (select CallId from @table where GroupName like 'SD Followup') then 'Y'

     ELSE 'N'

    END

    SELECT CallID, GroupName, CustomerPending FROM @table

    ---------implimenting statement----------------

    USE reporting

    go

    DECLARE @table TABLE(CallID VARCHAR(8), GroupName VARCHAR(15), CustPendCase VARCHAR(1))

    INSERT @table(Asgnmnt.CallID, Asgnmnt.GroupName, CustPendCase)

     SELECT CallID, GroupName, NULL UNION ALL

    SELECT CallID, GroupName, CustPendCase FROM @table

    UPDATE @table

    SET CustPendCase =

     CASE

      WHEN CallId in (select CallId from @table where GroupName = 'SD Followup') then 'Y'

      ELSE 'N'

     END

    --------------------------end-------------------

    The error I get when trying to run the implimenting statement is:

    Server: Msg 207, Level 16, State 3, Line 3

    Invalid column name 'CallID'.

    Server: Msg 207, Level 16, State 1, Line 3

    Invalid column name 'GroupName'.

    I've tried including tables names to CallID, GroupName and a few other thigns but no joy yet.

  • September 22, 2005 at 7:41 pm

    #592455

    "INSERT @table(Asgnmnt.CallID, Asgnmnt.GroupName, CustPendCase)

     SELECT CallID, GroupName, NULL UNION ALL

    SELECT CallID, GroupName, CustPendCase FROM @table"

    For the highlighted statement, there is no FROM clause so where are the columns coming from?

     

    --------------------
    Colt 45 - the original point and click interface

  • September 22, 2005 at 11:23 pm

    #592474

    Still a bit lost. Basically I'm trying to substitute the following data:

    SELECT '00500588','Desktop',Null UNION ALL

    SELECT '00500588','SD Followup',Null UNION ALL

    SELECT '00500600','Server',Null UNION ALL

    SELECT '00500600','Service Desk',Null UNION ALL

    SELECT '00500777','SD Followup',Null UNION ALL

    SELECT '00500777','Network',Null

    with the data found in the actual tables, then use UPDATE to add additional information into the 'CustPendCase' column.

    Do I scrap it all and start from the top, or ..?

  • September 22, 2005 at 11:48 pm

    #592477

    "...with the data found in the actual tables..."

    So you would need to reference those table in the FROM clause.

    EG:

    INSERT @table(CallID, GroupName, CustPendCase) 
SELECT CallID, GroupName, NULL
FROM <your table name here>

     

    --------------------
    Colt 45 - the original point and click interface

  • September 24, 2005 at 1:18 am

    #592825

    Code should be like:

    INSERT @table(CallID, GroupName, CustPendCase)

    Select * from (SELECT '00500588' as CallId,'Desktop' as GroupName,Null as CustPendCase UNION ALL

     SELECT '00500588','SD Followup',Null UNION ALL

     SELECT '00500600','Server',Null UNION ALL

     SELECT '00500600','Service Desk',Null UNION ALL

     SELECT '00500777','SD Followup',Null UNION ALL

     SELECT '00500777','Network',Null) a

    Insert expects only one select statement for inserted values. You need to combine all statments in a subquery before submitting it to insert statment.

  • September 25, 2005 at 1:41 pm

    #592873

    Ok, thanks for your input. I have changed the statement to:

    ------------------------------------

    USE reporting

    go

    DECLARE @table TABLE(CallID VARCHAR(8), GroupName VARCHAR(15), CustPendCase VARCHAR(1))

    INSERT @table(CallID, GroupName, CustPendCase)

    SELECT CallID, GroupName, NULL

    FROM Asgnmnt

    -- SELECT CallID, GroupName, CustPendCase FROM @table

    UPDATE @table

    SET CustPendCase =

     CASE

      WHEN CallId in (select CallId from @table where GroupName = 'SD Followup') then 'Y'

      ELSE 'N'

     END

    --------------------------------------

    The syntax checks out ok, when I run the query it says 'x rows 'x row(s) affected', and yet the CustPendCase column is all NULL, not 'Y' or 'N'. If I try altering the line:

    SELECT CallID, GroupName, NULL  ->  SELECT CallID, GroupName

    I get a syntax error.

    Your help appreciated.

  • September 25, 2005 at 4:54 pm

    #592875

    I don't think you actually need the update part. You can include your CASE in the SELECT,

    DECLARE @table TABLE(CallID VARCHAR(8), GroupName VARCHAR(15), CustPendCase VARCHAR(1)) 
INSERT @table(CallID, GroupName, CustPendCase) 
SELECT CallID, GroupName
    ,  CASE 
           WHEN GroupName = 'SD Followup' then 'Y'
           ELSE 'N'
        END as CustPendCase
FROM Asgnmnt

    For the syntax error "SELECT CallID, GroupName, NULL", again you're not saying where the columns are coming from.

    SELECT CallID, GroupName
FROM Asgnmnt
     

    --------------------
    Colt 45 - the original point and click interface

  • September 25, 2005 at 7:07 pm

    #592877

    Hi Phil,

    I tried the following, but no results were returned into the CustPendCase column

    ---------------------------------

    DECLARE @table TABLE(CallID VARCHAR(8), GroupName VARCHAR(15), CustPendCase VARCHAR(1))

    INSERT @table(CallID, GroupName, CustPendCase)

    SELECT CallID, GroupName

        ,  CASE

               WHEN GroupName = 'SD Followup' then 'Y'

               ELSE 'N'

            END as CustPendCase

    FROM Asgnmnt

    -----------------------------------

    Nathan

  • September 25, 2005 at 9:44 pm

    #592886

    What is returned for just the select portion?

    SELECT CallID, GroupName

        ,  CASE

               WHEN GroupName = 'SD Followup' then 'Y'

               ELSE 'N'

            END as CustPendCase

    FROM Asgnmnt

     

    --------------------
    Colt 45 - the original point and click interface

  • September 25, 2005 at 10:19 pm

    #592890

    Hi Phil,

    as sample of what i get back is this:

    CallID                  GroupName         CustPendCase

    ----------           --------------     -------------

    00504164            Field Sup - Vic           N

    00504164            SD Followup              Y

    What I want is for CustPendCase be set to 'Y' for all rows in a particular CallID whenever 'SD Followup" one or more times. i.e. in the example above, I am wanting there to be 'Y' for both rows.

    N.

  • September 25, 2005 at 10:41 pm

    #592894

    Ahhh .... Ok, progressing in leaps and bounds now ... in that case you should do the update.

    SET NOCOUNT ON
    -------------------------
-- create and populate testing source table
DECLARE @Asgnmnt TABLE(CallID CHAR(8), GroupName VARCHAR(55))
    INSERT @Asgnmnt(CallID, GroupName)
 SELECT '00500588','Desktop' UNION ALL
 SELECT '00500588','SD Followup' UNION ALL
 SELECT '00500600','Server' UNION ALL
 SELECT '00500600','Service Desk' UNION ALL
 SELECT '00500777','SD Followup' UNION ALL
 SELECT '00500777','Network'
    SELECT CallID, GroupName FROM @Asgnmnt
--------------------------
    DECLARE @table TABLE(CallID CHAR(8), GroupName VARCHAR(55), CustPendCase CHAR(1))
    INSERT @table(CallID, GroupName, CustPendCase) 
SELECT CallID, GroupName, NULL 
FROM @Asgnmnt
    -- check table contents
SELECT CallID, GroupName, CustPendCase 
FROM @table
    -- Update CustPendCase to Y or N
UPDATE @table
SET CustPendCase =
 CASE 
  WHEN CallId in (select CallId from @table where GroupName = 'SD Followup') then 'Y'
  ELSE 'N'
 END
    -- check table contents again
SELECT CallID, GroupName, CustPendCase 
FROM @table
     

    --------------------
    Colt 45 - the original point and click interface

  • September 26, 2005 at 11:05 pm

    #593167

    Hi Phil,

    So close. This all looks right, except that with the actual updating of the table, the query just runs for 45 mins or more with no actual updating of the table.

    N.

  • September 26, 2005 at 11:33 pm

    #593168

    "...except that with the actual updating of the table..."

    Hmmm ... are you referring to updating your Asgnmnt table, or the table variable that you created?

    Can you post the definition of the Asgnmnt table?

     

    --------------------
    Colt 45 - the original point and click interface

  • September 26, 2005 at 11:54 pm

    #593173

    I was referring to the updating of the 'Asgnmnt' table

    I think you're asking for 'sp_help Asgnmnt' information.

    AssignedBy  varchar no 8             yes no no SQL_Latin1_General_CP1_CI_AS

    DateAssign  smalldatetime no 4       yes (n/a) (n/a) NULL

    TimeAssign  smalldatetime no 4       yes (n/a) (n/a) NULL

    AssignDatetime  datetime no 8       yes (n/a) (n/a) NULL

    GroupName  varchar no 15             yes no no SQL_Latin1_General_CP1_CI_AS

    AsgnCdrGrp  varchar no 1             yes no no SQL_Latin1_General_CP1_CI_AS

    Assignee  varchar no 20             yes no no SQL_Latin1_General_CP1_CI_AS

    Phone   varchar no 14             yes no no SQL_Latin1_General_CP1_CI_AS

    Comments  text no 16             yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

    DateAcknow  smalldatetime no 4       yes (n/a) (n/a) NULL

    TimeAcknow  smalldatetime no 4       yes (n/a) (n/a) NULL

    DateResolv  smalldatetime no 4       yes (n/a) (n/a) NULL

    TimeResolv  smalldatetime no 4       yes (n/a) (n/a) NULL

    ResolvDatetime  datetime no 8       yes (n/a) (n/a) NULL

    Resolution  varchar no 15             yes no no SQL_Latin1_General_CP1_CI_AS

    CallID   varchar no 8             yes no no SQL_Latin1_General_CP1_CI_AS

    HEATSeq   int no 4 10    0     yes (n/a) (n/a) NULL

    EMail   varchar no 35             yes no no SQL_Latin1_General_CP1_CI_AS

    Pager   varchar no 14             yes no no SQL_Latin1_General_CP1_CI_AS

    Available  varchar no 50             yes no no SQL_Latin1_General_CP1_CI_AS

    WhoAcknow  varchar no 8             yes no no SQL_Latin1_General_CP1_CI_AS

    WhoResolv  varchar no 8             yes no no SQL_Latin1_General_CP1_CI_AS

    HEATLogin  varchar no 8             yes no no SQL_Latin1_General_CP1_CI_AS

    PhoneExt  varchar no 4             yes no no SQL_Latin1_General_CP1_CI_AS

    TargetTime  smalldatetime no 4       yes (n/a) (n/a) NULL

    TargetDate  smalldatetime no 4       yes (n/a) (n/a) NULL

    MobileNum  varchar no 14             yes no no SQL_Latin1_General_CP1_CI_AS

    ResolutionDesc  varchar no 50             yes no no SQL_Latin1_General_CP1_CI_AS

    AsgnGrpDesc  varchar no 50             yes no no SQL_Latin1_General_CP1_CI_AS

    Supervisor  varchar no 40             yes no no SQL_Latin1_General_CP1_CI_AS

    SupervisorEmailID varchar no 50             yes no no SQL_Latin1_General_CP1_CI_AS

    DTLastMod  int no 4 10    0     yes (n/a) (n/a) NULL

    CustPendTime  varchar no 1             yes no no SQL_Latin1_General_CP1_CI_AS

    CustPendCase  varchar no 1             yes no no SQL_Latin1_General_CP1_CI_AS

  • September 27, 2005 at 12:52 am

    #593184

    Ahhh ... even bigger jump this time ...

    "...I was referring to the updating of the 'Asgnmnt' table..."

    In that case you don't need to worry about the table variable and it can be done in one query.

    SET NOCOUNT ON
-------------------------
-- create and populate testing source table
    DECLARE @Asgnmnt TABLE(CallID CHAR(8), GroupName VARCHAR(55), CustPendCase CHAR(1))
INSERT @Asgnmnt(CallID, GroupName, CustPendCase) 
SELECT '00500588','Desktop', NULL UNION ALL 
SELECT '00500588','SD Followup', NULL UNION ALL 
SELECT '00500600','Server', NULL UNION ALL 
SELECT '00500600','Service Desk', NULL UNION ALL 
SELECT '00500777','SD Followup', NULL UNION ALL 
SELECT '00500777','Network', NULL
    -- check table contents
SELECT CallID, GroupName, CustPendCase 
FROM @Asgnmnt
    -- Update CustPendCase to Y or N
UPDATE @Asgnmnt
SET CustPendCase = CASE WHEN ISNULL(FlwUp.CallId, '') = '' THEN 'N' ELSE 'Y' END
FROM @Asgnmnt Asgn
    LEFT JOIN (
            SELECT 
                CallId 
            FROM @Asgnmnt
            WHERE GroupName = 'SD Followup'
        ) as FlwUp
    ON Asgn.CallID = FlwUp.CallID
    -- check table contents
SELECT CallID, GroupName, CustPendCase 
FROM @Asgnmnt

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply