Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

problem in joining queries

2 posts in General Discussion Last posting was on 2006-05-10 11:11:44.0Z
john Posted on 2006-05-10 05:22:32.0Z
From: john <johndalde@biz.phisl.net>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: problem in joining queries
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 203.215.95.78
Message-ID: <44617888@forums-2-dub>
X-Original-Trace: 9 May 2006 22:22:16 -0700, 203.215.95.78
Lines: 15
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 9 May 2006 22:22:17 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 9 May 2006 22:22:32 -0700
X-Trace: forums-1-dub 1147238552 10.22.108.75 (9 May 2006 22:22:32 -0700)
X-Original-Trace: 9 May 2006 22:22:32 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5327
Article PK: 1380

somebody please help me i have problem in this queries. the first error
is result returned is non deterministic then after i hit oK another
error will occur.QT which is the value of prefix cannot be converted
into numeric 30,6 can somebody 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';


Breck Carter [Team iAnywhere] Posted on 2006-05-10 11:11:44.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: problem in joining queries
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <qbi36210pn6e1189dqqrc904291ae5ps1n@4ax.com>
References: <44617888@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 10 May 2006 04:11:25 -0700, bcarter.sentex.ca
Lines: 43
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 May 2006 04:11:26 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 May 2006 04:11:44 -0700
X-Trace: forums-1-dub 1147259504 10.22.108.75 (10 May 2006 04:11:44 -0700)
X-Original-Trace: 10 May 2006 04:11:44 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5328
Article PK: 1381

If this subquery returns only one row

select top 1 customeraccountsID from customeraccounts where
customercode = 'T-00045'

then you do not need the "top 1". If it does return more than 1 row,
you do need the "top 1" and you can add ORDER BY to get rid of the
non-deterministic error (the ORDER BY tells it *which* one of the 2 or
more rows the "top 1" applies to):

select top 1 customeraccountsID from customeraccounts where
customercode = 'T-00045' order by customercode

I do not understand what you mean by "QT which is the value of
prefix..."

Breck

On 9 May 2006 22:22:32 -0700, john <johndalde@biz.phisl.net> wrote:

>somebody please help me i have problem in this queries. the first error
> is result returned is non deterministic then after i hit oK another
>error will occur.QT which is the value of prefix cannot be converted
>into numeric 30,6 can somebody 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';

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com