Different Results using ODBC and DB-Library

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Different Results using ODBC and DB-Library

Author

Topic

colin.graham@stylo.co.uk
Starting Member

8 Posts

Posted - 2009-01-09 : 09:25:56

I am experiencing a problem with results froma view. I have a view which populates a varchar variable by a series of concatenated CASE statements (the view is included at the bottom of this message.

When I access the view via Microsoft DB-Library the contents of the column WebSites is shown correctly (i.e. with the various WebSite Numbers present separated by a semicolon, but when I access the view via ODBC the same column is shown as NULL.

Sample Output when connected via from MS-DB Library:
TD LineNum WebSites WebSite1 WebSite2 WebSite3
SH02 00/110/0366 0485;0487; NULL 0485 0487

Sample Output when connected ODBC:
TD LineNum WebSites WebSite1 WebSite2 WebSite3
SH02 00/110/0366 NULL NULL 0485 0487

Can anyone tell me what's going on please?

Here's the View:
================
select
p.traddiv as 'TD',
p.prodcode as 'LineNum',
p.proddesc as 'LineName',
'TandS' = CASE
WHEN (p.traddiv = 'BAR01' and p.contcode = '74') THEN 'Y'
WHEN (p.traddiv = 'BAR02' and p.contcode = '05') THEN 'Y'
ELSE 'N'
END,
'Approved' = CASE
WHEN a.Approved = 'Y' THEN 'Y'
ELSE 'N'
END,
p.psize as 'Size',
s.wh_stock as 'TotalWHStock',
s.allocated_stock as 'AllocWHStock',
(s.wh_stock-s.allocated_stock) as 'AvailWHStock',
s.ordered_stock as 'WebStockOnOrder',
s.br_stock as 'DFS_BRStock',
'Websites' =
CASE
WHEN a.WebSite1 is not null THEN a.website1+';'
END +
CASE
WHEN a.WebSite2 is not null THEN a.website2+';'
END +
CASE
WHEN a.WebSite3 is not null THEN a.website3+';'
END +
CASE
WHEN a.WebSite4 is not null THEN a.website4+';'
END +
CASE
WHEN a.WebSite5 is not null THEN a.website5+';'
END +
CASE
WHEN a.WebSite6 is not null THEN a.website6+';'
END +
CASE
WHEN a.WebSite7 is not null THEN a.website7+';'
END +
CASE
WHEN a.WebSite8 is not null THEN a.website8+';'
END +
CASE
WHEN a.WebSite9 is not null THEN a.website9+';'
END +
CASE
WHEN a.WebSite10 is not null THEN a.website10+';'
END +
CASE
WHEN a.WebSite11 is not null THEN a.website11+';'
END +
CASE
WHEN a.WebSite12 is not null THEN a.website12+';'
END +
CASE
WHEN a.WebSite13 is not null THEN a.website13+';'
END +
CASE
WHEN a.WebSite14 is not null THEN a.website14+';'
END +
CASE
WHEN a.WebSite15 is not null THEN a.website15+';'
END +
CASE
WHEN a.WebSite16 is not null THEN a.website16+';'
END +
CASE
WHEN a.WebSite17 is not null THEN a.website17+';'
END +
CASE
WHEN a.WebSite18 is not null THEN a.website18+';'
END +
CASE
WHEN a.WebSite19 is not null THEN a.website19+';'
END +
CASE
WHEN a.WebSite20 is not null THEN a.website20+';'
END,
a.WebSite1,
a.website2,
a.website3,
a.website4,
a.website5,
a.website6,
a.website7,
a.website8,
a.website9,
a.website10,
a.website11,
a.website12,
a.website13,
a.website14,
a.website15,
a.website16,
a.website17,
a.website18,
a.website19,
a.website20
from
rowan.stylo.dbo.ec_full_stock s inner join rowan.rsalive.dbo.stm_prodinterim p on s.variantcode = p.variantcode
left outer join rowan.stylo.dbo.ec_WebSite_Products_Array a on substring(s.variantcode,1,11) = a.LineNum

Colin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 11:44:17

can you check what's datatype of WebSite1 in both?

colin.graham@stylo.co.uk
Starting Member

8 Posts

Posted - 2009-01-10 : 03:59:24

In both cases, WebSites is defined as varchar(100).

Colin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources