Hi
when i try to run a query using linked servers, i get the following
error.
Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.
I do have more than 10 case statements, it works fine when it is less
than 10. can anyone tell me if there is a way to have more than 10
case statements. thanks alot.
Jay
my query
Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
Then 'N'
When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x
Where KEEP = 'Y'
Group By CATEGORY, YR 2 3108
"Jay" <we**********@yahoo.com> wrote in message
news:95**************************@posting.google.c om... Hi when i try to run a query using linked servers, i get the following error.
Server: Msg 125, Level 15, State 1, Line 1 Case expressions may only be nested to level 10.
I do have more than 10 case statements, it works fine when it is less than 10. can anyone tell me if there is a way to have more than 10 case statements. thanks alot.
Jay
my query Select category, val, Sum(QTY) As QTY , yr From ( Select val, QTY2 As QTY, KEEP = Case When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11)) Then 'N' When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12)) Then 'N' Else 'Y' End From amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM Where CATEGORY In ('1') And CODE In ('001','003','004','005') And b.YR Between 2003 And 2004 And b.MON <= 1 ) x Where KEEP = 'Y' Group By CATEGORY, YR
Since most of your conditions are the same, have you tried something like
this?
Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code in ('004', '005, '003', '017', /* etc. */)
And ((YR > 2003) Or (YR = 2003 And MON > 12))
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x
Simon
hi
thanks for the suggestion. I have a problem, this is one of the query
where it is all the same, in a few others it varies a lot. i want to
know if the limitaion exists in sql using linked servers ( since it
works fine if i dont use linked servers and have them in the same
server). i want to get aroud this, so that i dont have to change all my
existing queries, and would hamper my using linked server. thanks.. any
suggestion?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jevon |
last post by:
I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL...
|
by: mahajan.sanjeev |
last post by:
Hi All,
I am accessing a linked server (also a SQL Server) from a stored
procedure. There is an insert statement that I run on a table in the
linked server. This statement causes the Internal...
|
by: Lyn Duong |
last post by:
Hi,
I have tried creating a linked server in microsoft sql 2000 to a db2
for vm database. I use a db2 v8 admin client, configure it via client
configuration setup and then in Microsoft SQL...
|
by: Philippa |
last post by:
I'm trying to access data in vba using the openrecordset command. The
data in on a SQL Server 2000 database, and I have linked tables to
that data. the Table I'm trying to access is one of these...
|
by: annie |
last post by:
Hi all,
I have recently ported my Access 2000 app to SQL Server, keeping the
Access client as the front end using linked tables.
I am also using triggers on my SQL tables to trap orphan...
|
by: Charles Wilt |
last post by:
I have a IBM iSeries (aka AS-400) running v5r3 of OS/400 that I access
via a linked server from SQL Server 2000.
The following select works fine:
select * from...
|
by: holysmokes99 |
last post by:
I have a VB.Net (1.1) that uses SQL-DMO via COM Interop to create a
linked server. The code throws an exception, as it should, when the
linked server I am trying to create already exists on the...
|
by: gshawn3 |
last post by:
Hi,
I am having a hard time creating a Trigger to update an Oracle
database. I am using a SQL Server 2005 Express database on a Win XP Pro
SP2 desktop, linked to an Oracle 10g database on a...
|
by: technocraze |
last post by:
Hi guys,
I have encountered this error when updating the values to the MS Acess table.
Error : Update on linked table failed. ODBC sql server error Timeout expired. MS Acess is my front end and...
|
by: sqlgirl |
last post by:
Hi,
We have 2 servers. Server1 is a 2000 box, with SP3 (yes I know it is
not up to date). Server2 is a 2005 box, SP2.
I set up Server1 (2000) to have a linked server to Server2 (2005). The...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |