problem in joiining my query

Posted on 2006-05-09
Last Modified: 2010-05-18
hi i have problem in my query,
 the problem i encountered here is" result returned is non deterministic"
but when i hit OK another problem will occur " QT cannot be converted into numeric 30,6." QT is the value of prefix. please help me.....

ifnull(prefix,'',cast(prefix as char(5))) +
cast(select top 1 customeraccountsID from customeraccounts where customercode = 'T-00045') as varchar(12))  +
'-'+ cast(dateformat(getdate(),'yyMMdd') as varchar(12)) +'-'
+ REPLACE( STR(ifnull(valuecount,'',valuecount)+step, digits, 0 ),' ',IFNULL(ReplaceBlank,'',ReplaceBlank)) )
 AS lcID
    FROM autonumbers
   WHERE UCASE(keyname) = 'Quotation_NO';
Question by:johndlz
    1 Comment
    LVL 24

    Accepted Solution

    I think this is your problem:

        cast(select top 1 customeraccountsID from customeraccounts where customercode = 'T-00045') as varchar(12))

    Looks like misplaced brackets here. You're missing a left bracket around the subquery. Try:

         cast((select top 1 customeraccountsID from customeraccounts where customercode = 'T-00045') as varchar(12))

    Mind you, I'm not sure why this is a subquery at all, since it will return the same results each time it is run for a row in the result set. You could run it once and save it in a local variable instead.


         WHERE UCASE(keyname) = 'Quotation_NO'

    This WHERE clause can never be true. You're forcing keyname to be uppercase, then comparing it with a string containing lowercase characters.


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
    David Varnum recently wrote up his impressions of PRTG, based on a presentation by my colleague Christian at Tech Field Day at VMworld in Barcelona. Thanks David, for your detailed and honest evaluation!
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now