Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: Why wouldn't this SQL query run?

    Is there a way to make this SQL statement run so I can get the count? When I pass the value to a parameter, it gives me back a count of ZERO. TIA

    Code:
    DECLARE @MONTHS AS CHAR(50)
    SET @MONTHS="'08 2004','09 2004'"
    PRINT @MONTHS
    SELECT COUNT(*) FROM WORKORDER WHERE VRUTIMELINE IN(@MONTHS) '<-- does not run
    SELECT COUNT(*) FROM WORKORDER WHERE VRUTIMELINE IN('08 2004','09 2004')
    
    
    ------ ANSWER --------
    '08 2004','09 2004'                               
             
    ----------- 
    0
    
    (1 row(s) affected)
    
                
    ----------- 
    16071
    
    (1 row(s) affected)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    EXECUTE ('SELECT COUNT(*) FROM WORKORDER WHERE VRUTIMELINE IN(' + @MONTHS+ ')')
    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SELECT COUNT(*) FROM WORKORDER w
    inner join dbo.fn_fn_ParseString2VarCharTable((@MONTHS) f
    on w.VRUTIMELINE = f.[String]
    Last edited by rdjabarov; 10-04-04 at 16:12.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by Pat Phelan
    I'd use:
    Code:
    EXECUTE ('SELECT COUNT(*) FROM WORKORDER WHERE VRUTIMELINE IN(' + @MONTHS+ ')')
    -PatP
    Yup this worked just fine but it seems it is limited to 128 character long. So when I was building the actual SQL statement, I'm getting an error "The identifier that starts with.....is too long. Maximum length is 128."

    Thanks
    Last edited by ARPRINCE; 10-04-04 at 16:33.

  5. #5
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by rdjabarov
    SELECT COUNT(*) FROM WORKORDER w
    inner join dbo.fn_fn_ParseString2VarCharTable((@MONTHS) f
    on w.VRUTIMELINE = f.[String]
    When I run this one, I'm getting an error:

    "Server: Msg 170, Level 15, State 1, Line 9
    Line 9: Incorrect syntax near '('."

    Thanks

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...dbo.fn_ParseString2VarCharTable('01/01/2001,01/01/2002,01/01/2003', ',')...

    create function dbo.fn_ParseString2VarCharTable (
    @string varchar(8000),
    @delimiter char(1) = ',' ) returns @tbl table (
    RecordID int identity(1,1) not null primary key clustered,
    [String] varchar(8000) not null ) as
    begin
    declare @spos int, @pos int
    --set @string = replace(replace(@string, ', ', @delimiter), ' ,', @delimiter)
    set @string = replace(replace(@string, @delimiter + ' ', @delimiter), ' ' + @delimiter, @delimiter)
    set @spos = 1
    set @pos = 100
    while (@pos) > 0 begin
    set @pos = charindex(@delimiter, @string, @spos)
    insert @tbl ([String])
    select
    ltrim (
    rtrim (
    substring (
    @string, @spos,
    case
    when (@pos - @spos) <= 0 then datalength(@string) + 1
    else @pos
    end - @spos
    )
    )
    )
    set @spos = @pos + 1
    end
    return
    end
    GO
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by ARPRINCE
    Yup this worked just fine but it seems it is limited to 128 character long. So when I was building the actual SQL statement, I'm getting an error "The identifier that starts with.....is too long. Maximum length is 128."

    Thanks
    I think your trouble is with how it is interpreting your strings when you are building the @Months Field...

    How about
    Code:
    DECLARE @MONTHS AS CHAR(50)
    SET @MONTHS='''08 2004''' + ',' + '''09 2004'''
    PRINT @MONTHS
    EXECUTE ('SELECT COUNT(*) FROM WORKORDER WHERE VRUTIMELINE IN(' + @MONTHS+ ')')
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    <10 characters>
    Last edited by TallCowboy0614; 10-04-04 at 17:01. Reason: dup posting
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Quote Originally Posted by TallCowboy0614
    I think your trouble is with how it is interpreting your strings when you are building the @Months Field...

    How about
    Code:
    DECLARE @MONTHS AS CHAR(50)
    SET @MONTHS='''08 2004'',''09 2004'''
    PRINT @MONTHS
    EXECUTE ('SELECT COUNT(*) FROM WORKORDER WHERE VRUTIMELINE IN(' + @MONTHS+ ')')
    The statement actually works if I use this one:

    Code:
    SET QUOTED_IDENTIFIER OFF
    DECLARE @MONTHS AS CHAR(50)
    SET @MONTHS="'08 2004','09 2004'"
    PRINT @MONTHS
    EXECUTE("SELECT COUNT(*) FROM WORKORDER WHERE VRUTIMELINE IN(" + @MONTHS +")")
    The parameter @Months would be an input by the user so having so many "'" wouldn't really work for my project.

    Thanks.

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    yeah, I tried to repost to remove some of my quotes *L* Anyway, it probably would be best to write a little stored proc or function to accept the user input and conver it to the comma-delimited list you need - something I assume you already dealt with...

    My original attempt was just to point out that the problem ("The identifier that starts with.....is too long. Maximum length is 128.") was in the definition of the quoted string, not a limitation of the actual command string. I shoulda left well enough alone, I guess *L* But I never have been much good at doing that
    Last edited by TallCowboy0614; 10-04-04 at 17:12.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    I'm trying to figure out rdjabarov function (i'm not really a SQL expert). Anyways, your inputs are appreciated. Thank you.

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    that function, in a nutshell, just performs an operation along the same lines as I was mentioning that you probably would want. It converts the comma-delimited string into a table containing your months values...then returns the table to the calling command. Then the join can be performed instead of the IN clause.

    Just another way to accomplish the same result you are going for in the IN clause you originally posted.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Then there's the Dr.'s article....

    http://weblogs.sqlteam.com/jeffs/arc...0/04/2167.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Brett Kaiser
    Well...yeah...if you wanna make it EASY...but that just seems somehow WRONG, ya know? I mean, if stuff starts getting too easy to implement, it makes us seem so much less amazingly intellectual, don't you think? It's the ole' "knowledge is power" thang (or was that "baffle 'em with B.S." I always get the two mixed up).

    Howya, Brett?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You been in hiding?

    You should stop by the Corral for a cocktail or 2.

    What's up in Del Ray these daze?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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