What's wrong with this statement?-VBForums
Results 1 to 5 of 5

Thread: What's wrong with this statement?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,600

    What's wrong with this statement?

    I'm trying to select data from a variable into a variable defined as table. I'm not sure what's wrong with this statement. Please see attached image.

    Thanks,
    Attached Images Attached Images  
    Blake

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,185

    Re: What's wrong with this statement?

    In doesn't work like that... It's expecting a list of things. A table isn't a list. But what you can do is this:
    Code:
    and (A.MainID In (select mainID from @tbl))
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,600

    Re: What's wrong with this statement?

    Ok,

    I've changed things around a bit. What I'm trying to do now is SPLIT the @MainID variable. This string variable contains 6 digit numbers in the format of 123456, 234567, 456789, etc. The STRING_SPLIT function does exist, however, I know that I'm using it wrong. It's just for illustration for what I'm wanting to accomplish.

    Thanks,
    Attached Images Attached Images  
    Blake

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,500

    Re: What's wrong with this statement?

    I only see one closing parenthesis after the STRING_SPLIT - see if that fixes it.

    If not try to put the results of the STRING_SPLIT into a variable - and use that simple variable in the VALUES() list. I kind of remember that VALUES() lists have to remain somewhat simple...

    Or just flip it to be: Insert into @Tbl Select String_Split(...)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,185

    Re: What's wrong with this statement?

    Did you check the documentation on string_split? https://docs.microsoft.com/en-us/sql...t-transact-sql
    Specifically the note where it states:
    Quote Originally Posted by MSDN
    The STRING_SPLIT function is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command:
    ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130 +
    Note that compatibility level 120 might be default even in new Azure SQL Databases.
    That's only going to work if you can change the compatibility mode of the database, which may or may not be a good thing to do.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.