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.

Duplicate rows while retriving from table

3 posts in General Discussion (old) Last posting was on 2008-06-24 02:02:09.0Z
Biswajit Kar Posted on 2008-06-23 03:37:44.0Z
From: "Biswajit Kar" <BiswajitK@xsysys.com>
Newsgroups: sybase.public.sqlanywhere
Organization: 124.30.116.5
X-Newsreader: AspNNTP 1.50 (JodoHost)
Subject: Duplicate rows while retriving from table
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <485f1a88@forums-1-dub>
Date: 22 Jun 2008 20:37:44 -0700
X-Trace: forums-1-dub 1214192264 10.22.241.152 (22 Jun 2008 20:37:44 -0700)
X-Original-Trace: 22 Jun 2008 20:37:44 -0700, vip152.sybase.com
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:88
Article PK: 866537

i am getting duplicate rows while retrieving data from tables in ASA.
I am presently using ASA 8.0.2 with patch #4542
But the above query works fine with ASA 8.0.2 with patch #4398.
the SElect query contains 30-40 casting expresstions, 2 temporary tables and few
sub queries.
on the previous patch it was retriving distinct records but now i am getting
duplicate rows for the same query.
Can anyone confirm me what is this problem regarding ? Why i am getting thios
type of errors. Needs a quick reply because i stuck in bet my devlovement.
---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp


Breck Carter [sqlanywhere.blogspot.com] Posted on 2008-06-23 10:16:13.0Z
From: "Breck Carter [sqlanywhere.blogspot.com]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: Duplicate rows while retriving from table
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <nutu541lb8aie3t92qtrbv7q56qj1c6p8j@4ax.com>
References: <485f1a88@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 23 Jun 2008 03:16:13 -0700
X-Trace: forums-1-dub 1214216173 10.22.241.152 (23 Jun 2008 03:16:13 -0700)
X-Original-Trace: 23 Jun 2008 03:16:13 -0700, vip152.sybase.com
Lines: 25
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:89
Article PK: 866538

Please show us the query, and the table definitions (CREATE TABLE is
best).

Breck

On 22 Jun 2008 20:37:44 -0700, "Biswajit Kar" <BiswajitK@xsysys.com>

wrote:

>i am getting duplicate rows while retrieving data from tables in ASA.
>I am presently using ASA 8.0.2 with patch #4542
>But the above query works fine with ASA 8.0.2 with patch #4398.
>the SElect query contains 30-40 casting expresstions, 2 temporary tables and few
>sub queries.
>on the previous patch it was retriving distinct records but now i am getting
>duplicate rows for the same query.
>Can anyone confirm me what is this problem regarding ? Why i am getting thios
>type of errors. Needs a quick reply because i stuck in bet my devlovement.
>---== Posted via the PFCGuide Web Newsreader ==---
>http://www.pfcguide.com/_newsgroups/group_list.asp

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Biswajit Kar Posted on 2008-06-24 02:02:09.0Z
From: "Biswajit Kar" <BiswajitK@xsysys.com>
Newsgroups: sybase.public.sqlanywhere
Organization: 124.30.116.5
References: <485f1a88@forums-1-dub> <nutu541lb8aie3t92qtrbv7q56qj1c6p8j@4ax.com>
X-Newsreader: AspNNTP 1.50 (JodoHost)
Subject: Re: Duplicate rows while retriving from table
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <486055a1$1@forums-1-dub>
Date: 23 Jun 2008 19:02:09 -0700
X-Trace: forums-1-dub 1214272929 10.22.241.152 (23 Jun 2008 19:02:09 -0700)
X-Original-Trace: 23 Jun 2008 19:02:09 -0700, vip152.sybase.com
Lines: 255
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:90
Article PK: 866539

Select
dummy.dummy_code ,
'' ords_detail_empl_code,
' ' ords_detail_order_line_id,'' ords_detail_dummy_code,
dummy.seq,
dummy.seq_2 ,
cast(dummy_code as char(50)) description,
(SELECT string(size3)+'&'+string(up)+'$'+string(ou) FROM dummy_spacemgt where
dummy_spacemgt.dummy_code = dummy.dummy_code) cc_dummy_space_info,
cast((substr(cc_dummy_space_info,1, patindex('%&%',cc_dummy_space_info)-1)) as
numeric) dummy_spacemgt_size3,
cast((substr(cc_dummy_space_info, patindex('%&%',cc_dummy_space_info)+1 ,
(patindex('%$%',cc_dummy_space_info) -
1)-(patindex('%&%',cc_dummy_space_info))))as numeric) dummy_spacemgt_up,
cast((substr(cc_dummy_space_info, patindex('%$%',cc_dummy_space_info)+1)) as
numeric) dummy_spacemgt_ou,
(select
load_history+'$'+stock_central+'}'+default_location_id+']'+kg_price_check+'*'+co
nd_gr_id+'&'+IsNull(own_stock_box,'0')+IsNull(own_stock_units,'0') from
p_order_type_table where order_type_id = 'ORD02') cc_order_type_info,
(IF '001' = 'NO' THEN
(substr(cc_order_type_info, patindex('%}%',cc_order_type_info)+1 ,
(patindex('%]%',cc_order_type_info) - 1)-(patindex('%}%',cc_order_type_info))))
ELSE
'001'
ENDIF) cc_location_id ,
(substr(cc_order_type_info,
patindex('%*%',cc_order_type_info)+1,(patindex('%&%',cc_order_type_info) -
1)-(patindex('%*%',cc_order_type_info)))) cc_cond_gr_id,
'' AS cc_kg_price_check,
(IF LEFT(cc_order_type_info,1) = '1' then
(select
string(vishis.date_1)+')'+string(vishis.ass_in_1)+'|'+string(vishis.qty_1)+'>'+
string(vishis.date_2)+','+string(vishis.ass_in_2)+'?'+
string(vishis.qty_2)+'/'+ string(vishis.date_3)+'}'+
string(vishis.ass_in_3)+'!'+string(vishis.qty_3)+'@'+string(vishis.date_4)+'#'+s
tring(vishis.ass_in_4)+'$'+

string(vishis.qty_4)+'{'+string(vishis.seq_ord_01)+'^'+string(vishis.seq_ord_02)
+'&'+string(vishis.seq_ord_03)+'*'+string(vishis.seq_ord_04)+'('+
string(vishis.seq_ord_05) from
vishis where vishis.dummy_code = dummy.dummy_code and vishis.acc_code =
'000401001' and
vishis.empl_code = '99999' and vishis.entity_level_id = 'CUSTO')
else
' '
endif) cc_hist_info,
cast((substr(cc_hist_info, patindex('%#%',cc_hist_info)+1 ,
(patindex('%$%',cc_hist_info) - 1)-(patindex('%#%',cc_hist_info))))as integer)
ass_in_4,
cast((substr(cc_hist_info, patindex('%}%',cc_hist_info)+1 ,
(patindex('%!%',cc_hist_info) - 1)-(patindex('%}%',cc_hist_info))))as integer)
ass_in_3,
cast((substr(cc_hist_info, patindex('%,%',cc_hist_info)+1 ,
(patindex('%?%',cc_hist_info) - 1)-(patindex('%,%',cc_hist_info))))as integer)
ass_in_2,
cast((substr(cc_hist_info, patindex('%)%',cc_hist_info)+1 ,
(patindex('%|%',cc_hist_info) - 1)-(patindex('%)%',cc_hist_info))))as integer)
ass_in_1,
(IF Len(Trim(substr(cc_order_type_info, patindex('%*%',cc_order_type_info)+1 ,
(patindex('%&%',cc_order_type_info) -
1)-(patindex('%*%',cc_order_type_info)))))>0 THEN
(SELECT list_price from cond_gr_dummy where cond_gr_dummy.dummy_code =
dummy.dummy_code and cond_gr_dummy.Ass_group_id = cc_cond_gr_id and
cond_gr_dummy.type_amAS = 'COND')
ELSE
0
ENDIF) LIST_PRICE,
(Case When cast(substr(cc_order_type_info,
patindex('%&%',cc_order_type_info)+1) as numeric) > 0
Then (Select String(IsNull(qty_box,0))+'$'+String(IsNull(qty_units,0)) From
stock where acc_code = '000301648'
and entity_level_id = 'CUSTO' and dummy_code = dummy.dummy_code and
record_status ='1')
Else
''
End) cc_own_stock,
Cast(IsNull(substr(cc_own_stock,1,patindex('%$%',cc_own_stock) -1),'0') as
Numeric) cc_own_stock_box,
Cast(IsNull(substr(cc_own_stock,patindex('%$%',cc_own_stock)+1),'0') as
Numeric) cc_own_stock_units,

(IF 'N' = 'YES' THEN
(select price_ans_temp.shop_pres from price_ans_temp where
price_ans_temp.acc_code = '000401001' and price_ans_temp.dummy_code =
dummy.dummy_code and
price_ans_temp.transfer_id = '99' and
price_ans_temp.entity_level_id = 'CUSTO' and price_ans_temp.visit_seq IS NULL)
ELSE
' '
ENDIF) present,
(IF substr(cc_order_type_info, patindex('%$%',cc_order_type_info)+1,
((patindex('%}%',cc_order_type_info)-1) - (patindex('%$%',cc_order_type_info))))
= '1' THEN
(SELECT qty from stock_central where dummy_code = dummy.dummy_code and
location_id = cc_location_id and record_status = '1')
ELSE
0
ENDIF) qty ,
dummy.record_status ,
'' new_mode ,
(select Trim(Isnull(priority_id,'')) from promo_assor_dummy where
promo_assor_dummy.ass_group_id = 'AY002' and promo_assor_dummy.type_amas =
'PROM2' and dummy.dummy_code = promo_assor_dummy.dummy_code and
promo_assor_dummy.record_status = '1') AS promo_assor_dummy_priority_id ,
promo_assor_dummy_priority_id AS p_rgb_priority_id ,
'16777215' AS p_rgb_background ,
CAST(0.00 AS decimal(2,0)) AS qty_box ,
'' order_code,
CAST(0.00 AS decimal(2,0)) AS qty_units ,
CAST(0.00 AS decimal(2,0)) AS del_box ,
CAST(0.00 AS decimal(2,0)) AS del_units ,
CAST(0.00 AS decimal(2,0)) AS rec_qty_box,
CAST(0.00 AS decimal(2,0)) AS rec_qty_units,
' ' return_reason_id ,
CAST(0.00 AS decimal(2,0)) AS free_box ,
CAST(0.00 AS decimal(2,0)) AS free_units ,
CAST(0.00 AS decimal(2,0)) AS whole_free_box ,
CAST(0.00 AS decimal(2,0)) AS whole_free_units,
' ' reason_id ,
CAST(0.00 AS decimal(4,2)) AS disc_per,
CAST(NULL AS decimal(2,0)) AS stock ,
CAST(0.00 AS decimal(8,4)) AS disc_lc,
CAST(0.00 AS decimal(8,4)) AS disc_eu,
CAST(0.00 AS decimal(8,2)) AS net_price_lc,
CAST(0.00 AS decimal(8,2)) AS net_price_eu,
CAST('' AS char(100)) AS ords_detail_remarks,
CAST(0.00 AS decimal(18,2)) AS ords_detail_price_calcul,
CAST(0.00 AS decimal(14,2)) AS st_sales,
(IF 'PROM2' <> 'NO' THEN
(select IsNull(promo_assor_dummy.dummy_code_whole,'') + '$$' +
String(IsNull(promo_assor_dummy.cons_price,0))+'&'+
String(IsNull(promo_assor_dummy.list_price,0))+'#'+IsNull(promo_assor_dummy.rema
rks,'')
from promo_assor_dummy where dummy.dummy_code = promo_assor_dummy.dummy_code and
promo_assor_dummy.ass_group_id = 'AY002' and promo_assor_dummy.type_amas =
'PROM2'
and promo_assor_dummy.record_status = '1')
ELSE
''
ENDIF) cc_promo_info,
(IF 'PROM2' <> 'NO' THEN
(select String(IsNull(promo_assor_dummy.quantity,0)) + '$$' +
String(IsNull(promo_assor_dummy.free,0))+'&'+
String(IsNull(promo_assor_dummy.discount,0))
from promo_assor_dummy where dummy.dummy_code = promo_assor_dummy.dummy_code and
promo_assor_dummy.ass_group_id = 'AY002' and promo_assor_dummy.type_amas =
'PROM2'
and promo_assor_dummy.record_status = '1')
ELSE
''
ENDIF) cc_promo_info2,

CAST((substr(cc_promo_info, patindex('%$$%',cc_promo_info)+2 ,
(patindex('%&%',cc_promo_info) - 1)-(patindex('%$$%',cc_promo_info) + 1))) as
decimal(17,2)) as cc_cons_price,
CAST((substr(cc_promo_info, patindex('%&%',cc_promo_info)+1 ,
(patindex('%#%',cc_promo_info) - 1)-(patindex('%&%',cc_promo_info)))) as
decimal(17,2)) as cc_list_price,

CAST(0 AS Numeric(10,0)) as qty_1,
CAST(0 AS Numeric(10,0)) as qty_2,
CAST(0 AS Numeric(10,0)) as qty_3,
CAST(0 AS Numeric(10,0)) as qty_4,
CAST(0 AS Numeric(10,0)) as qty_5,
CAST(0 AS Numeric(10,0)) as qty_6,
CAST(0 AS Numeric(10,0)) as qty_7,
CAST(0 AS Numeric(10,0)) as qty_8,
CAST(0 AS Numeric(10,0)) as qty_9,
CAST(0 AS Numeric(10,0)) as qty_10,
CAST(0 AS Numeric(10,0)) as qty_11,
CAST(0 AS Numeric(10,0)) as qty_12,
CAST(0 AS Numeric(10,0)) as qty_13,
CAST(0 AS Numeric(10,0)) as qty_14,
CAST(0 AS Numeric(10,0)) as qty_15,
CAST(0 AS Numeric(10,0)) as qty_16,
CAST(0 AS Numeric(10,0)) as qty_17,
CAST(0 AS Numeric(10,0)) as qty_18,
CAST(0 AS Numeric(10,0)) as qty_19,
CAST(0 AS Numeric(10,0)) as qty_20,
(CAST('1900/01/01' as DateTime)) valid_from,
(CAST('2999/12/31' as DateTime)) valid_to,
0 as valid_col,
(select temp_dummy_catalogue.display_item_nr from temp_dummy_catalogue where
temp_dummy_catalogue.dummy_code = dummy.dummy_code AND
temp_dummy_catalogue.transfer_id = '99') item_nr,
(select temp_dummy_catalogue.item_nr from temp_dummy_catalogue where
temp_dummy_catalogue.dummy_code = dummy.dummy_code AND
temp_dummy_catalogue.transfer_id = '99') cc_item_nr,
dummy.ean_code
from
dummy where dummy.dummy_code in(
if('CAT_ASSOR' = 'ASSORT') then
(select promo_assor_dummy.dummy_code from promo_assor_dummy where
dummy.dummy_code = promo_assor_dummy.dummy_code and
promo_assor_dummy.ass_group_id = 'AY002' and promo_assor_dummy.type_amas =
'PROM2' and promo_assor_dummy.record_status = '1')
else if('CAT_ASSOR' = 'ALL') then
(select temp_for_all.dummy_dummy_code from temp_for_all where
dummy.dummy_code = temp_for_all.dummy_dummy_code)
else if('CAT_ASSOR' = 'WHOLE') then
(select promo_assor_dummy.dummy_code from promo_assor_dummy where
dummy.dummy_code = promo_assor_dummy.dummy_code and
promo_assor_dummy.ass_group_id = 'AY002' and promo_assor_dummy.type_amas =
'WHOLE' and promo_assor_dummy.record_status = '1')
else if('CAT_ASSOR' = 'CUST') then
(select vishis.dummy_code from vishis where dummy.dummy_code =
vishis.dummy_code and vishis.acc_code = '000401001' and vishis.empl_code =
'99999' and vishis.entity_level_id = 'CUSTO' and vishis.record_status = '1')
else if('CAT_ASSOR' = 'CAT_ASSOR') then
(select temp_dummy_catalogue.dummy_code from temp_dummy_catalogue where
dummy.dummy_code = temp_dummy_catalogue.dummy_code AND
temp_dummy_catalogue.transfer_id = '99')
endif
endif
endif
endif
endif) AND
( "dummy"."dummy_type_id" like '%' ) AND
"dummy"."dummy_code" in
( SELECT "dummy_structure"."dummy_code" FROM "dummy_structure"
WHERE ( Trim(String("dummy_structure"."tree_nr")) like '%' ) AND
( Trim(String("dummy_structure"."lvl_seq_nr")) like '%' ) AND
( "dummy_structure"."lvl_value_id" in ( SELECT "t_value"."value_id"
FROM "t_value" ))) ;

On 23 Jun 2008 03:16:13 -0700,
in sybase.public.sqlanywhere

Breck Carter [sqlanywhere.blogspot.com] <NOSPAM__breck.carter@gmail.com> wrote:
>Please show us the query, and the table definitions (CREATE TABLE is
>best).
>
>Breck
>
>On 22 Jun 2008 20:37:44 -0700, "Biswajit Kar" <BiswajitK@xsysys.com>
>wrote:
>
>>i am getting duplicate rows while retrieving data from tables in ASA.
>>I am presently using ASA 8.0.2 with patch #4542
>>But the above query works fine with ASA 8.0.2 with patch #4398.
>>the SElect query contains 30-40 casting expresstions, 2 temporary tables and
few
>>sub queries.
>>on the previous patch it was retriving distinct records but now i am getting
>>duplicate rows for the same query.
>>Can anyone confirm me what is this problem regarding ? Why i am getting thios
>>type of errors. Needs a quick reply because i stuck in bet my devlovement.
>>---== Posted via the PFCGuide Web Newsreader ==---
>>http://www.pfcguide.com/_newsgroups/group_list.asp
>
>--
>Breck Carter http://sqlanywhere.blogspot.com/
>
>RisingRoad SQL Anywhere and MobiLink Professional Services
>breck.carter@risingroad.com