Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Germany
    Posts
    17

    Unanswered: Adding A default value in a column in a declare statment

    Hi Can anybody help me.

    I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

    DECLARE @Query nVarchar(1000)
    SET @Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
    THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
    THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @Query, N'@level tinyint', @level = 35

    Can anybody please help on this.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by NOODLES101487
    Hi Can anybody help me.

    I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

    DECLARE @Query nVarchar(1000)
    SET @Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
    THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
    THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @Query, N'@level tinyint', @level = 35

    Can anybody please help on this.

    Thanks
    would help us if u could post the ddl for the table

  3. #3
    Join Date
    Jul 2004
    Posts
    60
    what is the dataytpe of [Sales Rep Code]?
    Off the cuff, it looks like your IND value is supposed to be a string.
    Your example would be OK if you use 'IND' as [Sales Rep Code] - with the single quotes around IND.

  4. #4
    Join Date
    Dec 2003
    Location
    Germany
    Posts
    17
    hi,

    The datatype for SalesRep code is not defined as its in a view and I am creating the column [Sales Rep Code] with the default value of IND,

    e.g

    Select 'IND' AS [Sales Rep Code] from test would create a [Sales Rep Code] populated by IND for each row.

    I have tried the 'IND' and it appears that it is incorrect syntax, this is beacuse it is closing the string value of the declare statment at the fist ' but I dont have a way around this.

    Any Ideas

  5. #5
    Join Date
    Dec 2003
    Location
    Germany
    Posts
    17
    Hi thanks for everyones help finally got it working but I still have one problem in that it is over 4000 carachters long and the @test has a maximum of 4000 carachters is there any way around this.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •