Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: select based on nonexistant value

    Hey all, I'm having an interesting problem, but also keep in mind I am certainly a newbie with DB's and SQL but here's what I got:

    I'm making an application that creates quotes for our customers. I want to create a Quote Number that is based on the largest of the last number in the DB but only based on that company. For example:

    CoID QuoteNum
    123 1
    123 2
    666 1
    123 3
    666 2
    666 3

    so if I created a new quote for CoID: 666, the QuoteNum would be 4. Easy enough. But the problem arises when I create a new quote for a NEW COMPANY, one who's CoID is not already present in the DB. So what I have so far is this:

    SELECT QuoteNum FROM Quote
    WHERE QuoteNum = (SELECT MAX(QuoteNum) FROM Quote
    WHERE CoID='222')

    but since 222 does not yet exist I get an error: "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

    So how do I tell the DB to ignore that error and perform another task should it arise? Any help would greatly be appreciated. THANKS!

  2. #2
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103

    Re: select based on nonexistant value

    DECLARE @defaultNum int
    IF NOT EXISTS (SELECT QuoteNum FROM Quote WHERE CoID='222')
    SELECT @default = 1
    ELSE
    SELECT QuoteNum FROM Quote
    WHERE QuoteNum = (SELECT MAX(QuoteNum) FROM Quote
    WHERE CoID='222')

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    NewQuoteNum =
    ISNULL((SELECT MAX(QuoteNum) FROM Quote WHERE CoID='222'), 0.00)+1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2004
    Posts
    7
    Ok, a little explaination would have been helpful, I am a newbie after all.

    I ended up using a modified version of what AccessUser posted but I took out the variables (after I had to search around and realize that they were variables, since no one bothered to tell me @ means variable in SQL)...

    but despite the lack of communication, you guys still put me on the right track. Thanks!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SELECT MAX(QuoteNum) FROM Quote WHERE CoID='222'

    ...returns the highest existing QuoteNum for the ColID, or Null if it is a new ColID.

    ISNULL(xxxxxx, 0.00)
    ...substitutes a zero if the select statement returned a Null.

    +1
    ...well, you know what +1 does.

    All in one statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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