Results 1 to 11 of 11

Thread: error message

  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: error message

    Hello all, I have the following error message in one of my stored procedures but no idea how to rectify it, any help would be brill.

    ERROR
    Msg 207, Level 16, State 1, Procedure spGetZonesType, Line 6(in red)
    Invalid column name 'storeID'


    Stored Procedure
    SELECT
    zoneID
    ,ZoneName
    FROM tblzones
    WHERE zoneID
    IN (SELECT DISTINCT fk_zoneID FROM tblStores2Zones WHERE fk_storeID = (storeID))


    END

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mind_grapes
    ... but no idea how to rectify it, any help would be brill.
    how about if you define your storeID variable before you use it? brill enough?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi r937,

    lol thats brill enough, one thing though, im really new to SQL so where and how would i do that (yup im a dumbo friend)

    Regards
    MG

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi

    Thank you for the link, but I find that msdn site very difficult to understand the terminology. But I'm trying to serach for another article.

    regards
    MG

  6. #6
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Just to let you all know this has been sorted to some extent.

    It now displays four of the records in the database as requested. But, for some reason it returns the same results for all options in a drop down menu i have created. Still working on it.

    SELECT
    zoneID
    ,ZoneName
    ,storeID
    FROM tblzones
    WHERE zoneID
    IN (SELECT DISTINCT Z.fk_zoneID
    FROM tblStores2Zones as Z JOIN tblstores AS s
    ON z.fk_storeID = S.storeID)


    thanks for your help in this matter.

    Onwards and sidewards people.

    Regards
    MG

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mind_grapes
    SELECT
    zoneID
    ,ZoneName
    ,storeID
    FROM tblzones
    WHERE zoneID
    IN (SELECT DISTINCT Z.fk_zoneID
    FROM tblStores2Zones as Z JOIN tblstores AS s
    ON z.fk_storeID = S.storeID)
    that query makes sense only if the tblzones table table has a storeID column

    which, given the names of your table tables, is unlikely, since it appears that the tblStores2Zones table table is a "many-to-many" relationship table table between the tblzones table table and the tblstores table table

    perhaps try this, now that it's a little clearer what your table tables are for --
    Code:
    SELECT z.zoneID
         , z.ZoneName
         , s.storeID
      FROM tblstores AS s
    INNER
      JOIN tblStores2Zones as s2z
        ON s2z.fk_storeID = s.storeID
    INNER
      JOIN tblzones AS z
        ON z.zoneID = s2z.fk_zoneID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hello, thank you for your help.

    You're right "storeID" wasn't in that table, and the many-to-many etc.

    I dont think it helps when im not giving an acurate description of what i'm trying to achieve, and a list of the tables.

    Let me try and explain. I've tried to be as concise as possible.

    I have an ASP page that pulls information from a database.
    This information is displayed to the user is in the form of a drop down menu. This drop down menu should only display records that relate specifically to a certain shop / store.

    This "information" is a list of different types of displays that are currently at certain shops / stores. Displays are things like cabinets, window displays, shelves, gondoals etc.

    so for example, shop number 1 should have 2 window displays, 3 cabinets, 4 gondolas.

    but

    shop number 2 should have 1 window display and 4 cabinets and 1 gondola. Shop 3.... shop 4... and so on.

    originally the drop down menu showed a list of the entire displays available, it simply ran through the entire list in the database for all shops.

    for instance:
    shop 1 = window #1, cabinet #3, gondola #2
    shop 2 = window #1, cabinet #3, gondola #2
    shop 3 = window #1, cabinet #3, gondola #2 etc.
    the info kept duplicating and didnt filter the results.


    However with this code...:

    SELECT
    zoneID
    ,ZoneName
    --,storeID
    FROM tblzones
    WHERE zoneID
    IN (SELECT DISTINCT Z.fk_zoneID
    FROM tblStores2Zones as Z LEFT JOIN tblstores AS s
    ON z.fk_storeID = S.storeID)

    ...the drop down menu reduced what it displayed, but again this reduced list keeps displaying for all shops.

    I cant seem to call info that relates to X shop(s).

    Hope that all made sense, sorry for going on.

    the tables im using are:

    tblzoneTypes
    columns (zoneTypeID | ZoneTypeName)
    1 cabinet
    2 gondola
    3 fish tank
    4 window promo
    5 triple window promo
    6 Memory Display
    7 gondola two
    8 kiosks

    zoneID | ZoneName | fk_zonetypeID
    1 Cabinet one - small 1
    2 Cabinet two - middle 1
    3 Cabinet three - large 1
    4 Cabinet four - double bay 1
    5 WINDOW FRONT LEFT 1 5
    6 WINDOW FRONT LEFT 2 5
    8 Memory Display 6
    9 Goldola 2

    tblStore2Zones
    store2ZoneID | fk_storeID | fk_zoneID | active
    1 1 1 True
    2 1 2 True
    3 1 3 True
    4 1 5 True
    5 2 1 True
    6 2 3 True
    7 2 5 True
    8 3 1 True
    9 3 5 True

    tblstores
    storeID | StoreNumber | storeName
    1 1 First Store
    2 2 second store
    3 3 Third Store
    5 4 fourth
    6 5 fifth store
    7 6 sixth
    8 7 seven
    9 8 eight
    10 9 nine
    11 10 ten
    12 11 eleven

    any help would be great.

    Kind regards
    MG

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try my query in post #7 and add this at the end --
    Code:
     WHERE s.storename = 'X'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi thanks for your post.

    One question though, if i define a store name with:

    "WHERE s.storename = 'X'",

    how would it cycle through the different shops/stores?

    Would it not keep going back to the same store?

    Regards
    MG

  11. #11
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi all,

    With my constant questions on how to get this problems sorted, i felt it only right tell you it has now been sussed.

    The problem I had was because i wasn't passing a parameter through the query.

    the final sql query was:

    CREATE PROCEDURE [dbo].[spGetZonesType]
    @iStoreID DECIMAL
    AS
    BEGIN

    SELECT
    zoneID
    ,ZoneName
    ,ZoneTypeName
    FROM tblZones z
    INNER JOIN tblZoneTypes AS zt
    ON z.zoneID = zt.zoneTypeID
    INNER JOIN tblstores2zones AS s2z
    on z.zoneID = s2z.fk_zoneID
    inner join tblstores AS s
    on s2z.fk_storeID = s.storeID

    WHERE storeID = @iStoreID

    END

    Thanks to all for help in getting it sorted.

    Regards
    MG

Posting Permissions

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