[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

problem in joiining my query

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.....

SELECT (
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';
0
johndlz
Asked:
johndlz
1 Solution
 
Joe WoodhousePrincipal ConsultantCommented:
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.


Also:

     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.


 
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now