First, thanks to the guys for helping me earlier just to get my XML output
into element form. I'm new to XML, so I appreciate all the help and hope I'm
learning.
My next question has to do with nesting the data that comes out as a result
of several JOIN's
Here is my code:
DECLARE @.t TABLE (id INT)
INSERT INTO @.t VALUES (1)
SELECT
Root.id,
[Order].ID,
[Agent].TextID,
[Buyer].TextID,
[Broker].TextID
FROM
@.t AS Root
JOIN psOrder [Order] ON Root.id = 1
LEFT JOIN Contact [Agent] ON [Order].agent_id = Agent.ID
LEFT JOIN Contact [Buyer] ON [Order].buyer_id = Buyer.ID
LEFT JOIN Contact [Broker] ON [Order].broker_id = Broker.ID
WHERE [Order].ID = 12345
FOR XML AUTO, ELEMENTS
The "Order" table joins to the "Agent", "Buyer", and "Broker" tables.
However, the output I'm getting is nesting the data.
The output I get (incorrectly) is like this:
<Order>
<Agent>
<Buyer>
<Broker>
</Broker>
</Buyer>
</Agent>
</Order>
This is not correct. The Agent, Buyer, and Broker are all children of the
Order, and should not be nested within each other.
The desired output is like this:
<Order>
<Agent>
</Agent>
<Buyer>
</Buyer>
<Broker>
</Broker>
</Order>
I'd would very appreciate some help with this. Perhaps my SQL is not written
properly so that it comes out as desired.
ScottFor each of your LEFT JOIN's, towards the end, add "AND Root.id = 1" to get
the correct nesting. For example:
=====
DECLARE @.t TABLE (id INT)
INSERT INTO @.t VALUES (1)
SELECT
Root.id,
authors.au_id, authors.au_lname, authors.au_fname,
titles.title_id, titles.title
FROM
@.t AS Root
JOIN authors ON Root.id = 1
JOIN titleauthor ON authors.au_id = titleauthor.au_id AND Root.id = 1
JOIN titles ON titleauthor.title_id = titles.title_id AND Root.id = 1
FOR XML AUTO, ELEMENTS
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:umPgdiA8FHA.3388@.TK2MSFTNGP11.phx.gbl...
> First, thanks to the guys for helping me earlier just to get my XML output
> into element form. I'm new to XML, so I appreciate all the help and hope
> I'm
> learning.
> My next question has to do with nesting the data that comes out as a
> result
> of several JOIN's
> Here is my code:
> DECLARE @.t TABLE (id INT)
> INSERT INTO @.t VALUES (1)
> SELECT
> Root.id,
> [Order].ID,
> [Agent].TextID,
> [Buyer].TextID,
> [Broker].TextID
> FROM
> @.t AS Root
> JOIN psOrder [Order] ON Root.id = 1
> LEFT JOIN Contact [Agent] ON [Order].agent_id = Agent.ID
> LEFT JOIN Contact [Buyer] ON [Order].buyer_id = Buyer.ID
> LEFT JOIN Contact [Broker] ON [Order].broker_id = Broker.ID
> WHERE [Order].ID = 12345
> FOR XML AUTO, ELEMENTS
> The "Order" table joins to the "Agent", "Buyer", and "Broker" tables.
> However, the output I'm getting is nesting the data.
> The output I get (incorrectly) is like this:
> <Order>
> <Agent>
> <Buyer>
> <Broker>
> </Broker>
> </Buyer>
> </Agent>
> </Order>
> This is not correct. The Agent, Buyer, and Broker are all children of the
> Order, and should not be nested within each other.
> The desired output is like this:
> <Order>
> <Agent>
> </Agent>
> <Buyer>
> </Buyer>
> <Broker>
> </Broker>
> </Order>
> I'd would very appreciate some help with this. Perhaps my SQL is not
> written
> properly so that it comes out as desired.
> Scott
>|||Thanks for the reply SriSamp. I've added the " AND Root.id = 1" at the end
of each LEFT JOIN, but I'm still getting the nesting problem.
The data is still coming out nested like this (incorrectly):
<Order>
<Agent>
<Buyer>
<Broker>
</Broker>
</Buyer>
</Agent>
</Order>
Just to confirm, I do not want the child table data to nest one within each
other.
The desired output is this:
<Order>
<Agent>
</Agent>
<Buyer>
</Buyer>
<Broker>
</Broker>
</Order>
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:OWdNAvA8FHA.1000@.tk2msftngp13.phx.gbl...
> For each of your LEFT JOIN's, towards the end, add "AND Root.id = 1" to
get
> the correct nesting. For example:
> =====
> DECLARE @.t TABLE (id INT)
> INSERT INTO @.t VALUES (1)
> SELECT
> Root.id,
> authors.au_id, authors.au_lname, authors.au_fname,
> titles.title_id, titles.title
> FROM
> @.t AS Root
> JOIN authors ON Root.id = 1
> JOIN titleauthor ON authors.au_id = titleauthor.au_id AND Root.id = 1
> JOIN titles ON titleauthor.title_id = titles.title_id AND Root.id = 1
> FOR XML AUTO, ELEMENTS
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:umPgdiA8FHA.3388@.TK2MSFTNGP11.phx.gbl...
output
the
>
Showing posts with label statements. Show all posts
Showing posts with label statements. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
Nested IF Statements
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
from dbo.test1Users
When i do that i get this error
Incorrect syntax near the keyword 'from'.
Thank you for your help
You need to use CASE instead of IF. Maybe something like:
Code Snippet
declare @.testUsers table
( id integer,
firstName varchar(25),
lastName varchar(25)
)
insert into @.testUsers
select 1, 'Fred', 'Rubble' union all
select 2, 'Mike', 'Flintstone' union all
select 3, 'Betty', 'Kong' union all
select 4, 'Mike', 'Kong'
--select * from @.testUsers
select id,
case when substring(firstName, 1, 4) = 'Mike'
then replace(firstName, 'Mike', 'MikeTest')
else firstName
end as firstName,
case when substring(lastName, 1, 4) = 'Kong'
then replace(lastName, 'Kong', 'KongTest')
else lastName
end as lastName
from @.testUsers
/*
id firstName lastName
- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty KongTest
4 MikeTest KongTest
*/
Thank you for your replay
Actually I already thought about case statements but it really won’t work and here is why:
I need to have one check in top for example
If(firstName, 1, 4) = 'Mike')
Begin
replace(firstName, 'Mike', 'MikeTest')
If(LatsName, 1, 4) = 'Kong')
Begin
replace(firstName, 'Kong', 'KongTest')
End
End
The reason why ! you might have a guy his first name is “George” and his last name is “Kong”. So George’s Last name should not be changed to kongtest. Case statement won’t work because it will execute each case for example: the output from your case statements was:
id firstName lastName
- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty KongTest
4 MikeTest KongTest
Betty’s last name should not be changed to KongTest
Thanks in advance for your Input
|||That corrected case statement looks something like this:
Code Snippet
declare @.testUsers table
( id integer,
firstName varchar(25),
lastName varchar(25)
)
insert into @.testUsers
select 1, 'Fred', 'Rubble' union all
select 2, 'Mike', 'Flintstone' union all
select 3, 'Betty', 'Kong' union all
select 4, 'Mike', 'Kong'
--select * from @.testUsers
select id,
case when substring(firstName, 1, 4) = 'Mike'
then replace(firstName, 'Mike', 'MikeTest')
else firstName
end as firstName,
case when substring(firstName, 1, 4) = 'Mike'
and substring(lastName, 1, 4) = 'Kong'
then replace(lastName, 'Kong', 'KongTest')
else lastName
end as lastName
from @.testUsers
/*
id firstName lastName
-- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty Kong
4 MikeTest KongTest
*/
Yes this might work only for simple cases, but what I have is First Name, Last Name, Street Address, City,state,, County, Country, zip Code and more info....... Can you see my point?
Thanks
|||What was stated was a specific case; if you will give what your aim is, then I might be able to help you. Yes, I see your rhetorical point.
|||Thank you Kent.
Ok I am tying to convert a query that was written in access to Sql stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
IIf(Mid([proj_name],1,9)='9026-3342','Perspective',
IIf(Mid([proj_name],1,4)='9995','Perspective',
IIf(Mid([proj_name],1,4)='9016','MONITOR Internal',
IIf(Mid([proj_name],1,4)='9011','MONITOR Internal',
IIf(Mid([proj_name],1,4)='9010','MONITOR Internal',
IIf(Mid([proj_name],1,4)>'99',IIf(Mid([proj_name],1,3)='999','MONITOR Internal','Admin'),'Client')
)
)
)
)
)
)
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
Your input will be appreciated
Thank you
Nested IF Statements
Here is what i am trying to do
You can't plant the IF clause in the middle of SELECT like that. Consider using CASE construct instead.
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.......
End
End
end
from dbo.test1Users
When i do that i get this error
Incorrect syntax near the keyword 'from'.
Thank you for your help
Quote:
Originally Posted by goal2007
Here is what i am trying to do
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.......
End
End
end
from dbo.test1Users
When i do that i get this error
Incorrect syntax near the keyword 'from'.
Thank you for your help
You can't plant the IF clause in the middle of SELECT like that. Consider using CASE construct instead.
Nested cursors
I have something kind of bizarre going on and I'm not sure how to
troubleshoot it, I've put print statements all over in my code to try an fin
d
out where it's breaking but it doesn't seem to be breaking at any specific
point, there is no error message, processing just stops about the time the
machine runs out of memery, I suspect it's something with the number of
cursors I have open at one time. I can remove some of the nested cursors and
the problem goes away so I know it's memory related... Anybody have some
design advice.? I'm just moving/transforming data from a legacy database to
a
new system.. I tried to post my code but it's too big, I know that should be
a clue right there but I'm trying to bail a buddy out on a project that is
way overdue so opting for quick and dirty.. I can email the script, shoot me
an email..
Thanks!!
Danhi,
Let me know all that stuff at vtam13@.terra.es
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Alien2_51" wrote:
> I have something kind of bizarre going on and I'm not sure how to
> troubleshoot it, I've put print statements all over in my code to try an f
ind
> out where it's breaking but it doesn't seem to be breaking at any specific
> point, there is no error message, processing just stops about the time the
> machine runs out of memery, I suspect it's something with the number of
> cursors I have open at one time. I can remove some of the nested cursors a
nd
> the problem goes away so I know it's memory related... Anybody have some
> design advice.? I'm just moving/transforming data from a legacy database t
o a
> new system.. I tried to post my code but it's too big, I know that should
be
> a clue right there but I'm trying to bail a buddy out on a project that is
> way overdue so opting for quick and dirty.. I can email the script, shoot
me
> an email..
> Thanks!!
> Dan
>|||It went away with FAST_FORWARD.. hmm.. Maybe someone can tell why the
READ_ONLY is so much more costly on resources...?
"Enric" wrote:
> hi,
> Let me know all that stuff at vtam13@.terra.es
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "Alien2_51" wrote:
>|||Fast_forward is a forward Only read only cursor. If you can move forward an
d
backward in a cursor SQL needs to cache the results... If you use
fast_forward, sql only needs to have in memory ( or in tempdb depending on
the other options you choose), the current row you are looking at... Once yo
u
move off of that row, sql can purge it from memory because you are not
allowed to move backward...
Forward-only, read only cursors are called firehose cursors, and are the
fastest of any cursor...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Alien2_51" wrote:
> It went away with FAST_FORWARD.. hmm.. Maybe someone can tell why the
> READ_ONLY is so much more costly on resources...?
> "Enric" wrote:
>|||Thanks Wayne!!
Dan
"Wayne Snyder" wrote:
> Fast_forward is a forward Only read only cursor. If you can move forward
and
> backward in a cursor SQL needs to cache the results... If you use
> fast_forward, sql only needs to have in memory ( or in tempdb depending on
> the other options you choose), the current row you are looking at... Once
you
> move off of that row, sql can purge it from memory because you are not
> allowed to move backward...
> Forward-only, read only cursors are called firehose cursors, and are the
> fastest of any cursor...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Alien2_51" wrote:
>|||On Tue, 28 Mar 2006 02:26:02 -0800, Alien2_51 wrote:
>I have something kind of bizarre going on and I'm not sure how to
>troubleshoot it, I've put print statements all over in my code to try an fi
nd
>out where it's breaking but it doesn't seem to be breaking at any specific
>point, there is no error message, processing just stops about the time the
>machine runs out of memery, I suspect it's something with the number of
>cursors I have open at one time. I can remove some of the nested cursors an
d
>the problem goes away so I know it's memory related... Anybody have some
>design advice.?
Hi Dan,
Triggers whould be used VERY sparingly in SQL Server. Nested triggers
are a big red waving flag screaming "bad design!! bad design!!"
You should consider rewriting your stored procedure to use set-based
logic only - or at least to reduce the number of triggers.
If you need help with that, you'll really have to post your existing
code. And not only that - we need table structure (as CREATE TABLE
statements), sample data (as INSERT statements) and expected results as
well!
> I tried to post my code but it's too big
Eh? I've posted some prettly lengthy posts here and I've never gotten
any errors on this. Just make sure NOT to post it as a file atachment
(they get filtered out by namy usenet providers); just paste the
complete CREATE PROCEDURE statement in your post.
Of course - the longer the code you post, the harder it is to comment;
we are after all all doing this in our spare time. But if you do post, I
will definitely look at it - that much I promise.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo... I'm not using the cursors in a trigger.. I have a huge
procedural script that transforms data from one data schema to another.. I
have to nest my cursors to preserve all of my data relationships.. Some of
the relationships I'm trying to preserve are four and five levels deep..
~Dan
"Hugo Kornelis" wrote:
> On Tue, 28 Mar 2006 02:26:02 -0800, Alien2_51 wrote:
>
> Hi Dan,
> Triggers whould be used VERY sparingly in SQL Server. Nested triggers
> are a big red waving flag screaming "bad design!! bad design!!"
> You should consider rewriting your stored procedure to use set-based
> logic only - or at least to reduce the number of triggers.
> If you need help with that, you'll really have to post your existing
> code. And not only that - we need table structure (as CREATE TABLE
> statements), sample data (as INSERT statements) and expected results as
> well!
>
> Eh? I've posted some prettly lengthy posts here and I've never gotten
> any errors on this. Just make sure NOT to post it as a file atachment
> (they get filtered out by namy usenet providers); just paste the
> complete CREATE PROCEDURE statement in your post.
> Of course - the longer the code you post, the harder it is to comment;
> we are after all all doing this in our spare time. But if you do post, I
> will definitely look at it - that much I promise.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Tue, 28 Mar 2006 12:12:02 -0800, Alien2_51 wrote:
>Thanks Hugo... I'm not using the cursors in a trigger.. I have a huge
>procedural script that transforms data from one data schema to another.. I
>have to nest my cursors to preserve all of my data relationships.. Some of
>the relationships I'm trying to preserve are four and five levels deep..
Hi Dan,
I made a stupid typo. Where I wrote
I actually intended to write
*Cursors* whould be used VERY sparingly in SQL Server. Nested *cursors*
are a big red waving flag screaming "bad design!! bad design!!"
In most cases you don't need cursors at all. Neither for transforming
data, nor for preserving relationships.
That being said - if this is a one-time conversion job and it works now,
leave it as is!
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Yep! My rule of thumb is that any SQL statement over 10 lines is
suspect. But the real problem is that you used cursors at all! Nested
cursrors scream total incompedency, and old mag tape processing
algorithms.
No good deed goes unpunished! But if it is sooooo bad that you cannot
post to a Newsgroup, then he needs to pay someone to do his job for
him.
Based on 20+ years of fixing this stuff, I would start over with the
spec and do the job from scratch. What I am afraid of is that the
schema is also designed as if it were a mag tape file and thsu cannot
be saved, and probably has already destroyed data integrity.|||>> I have to nest my cursors to preserve all of my data relationships.. Some
of
the relationships I'm trying to preserve are four and five levels
deep.. <<
First, have you looked at ETL tools to do this job?
Again without DDL or code, we can only guess at what you are doing.
But when I have moved data from a "scrub table" to a normalized schema,
I never use cursors. I have found that something this is easier to
write:
BEGIN
..
INSERT INTO HighestReferencedLevel (...)
SELECT ..
FROM ScrubTable
WHERE ..;
INSERT INTO NextHighestReferencedLevel (...)
SELECT ..
FROM ScrubTable
WHERE ..;
etc.
END;
The real problem is what to do about the dirty data that cannot be
moved, like an order for an item that is not in Inventory.
I might have to hit the ScrubTable a few times in the transaction, but
it is still faster than cursors. You might also look at the MERGE
statement in SQL:2003 that other SQL products have.
troubleshoot it, I've put print statements all over in my code to try an fin
d
out where it's breaking but it doesn't seem to be breaking at any specific
point, there is no error message, processing just stops about the time the
machine runs out of memery, I suspect it's something with the number of
cursors I have open at one time. I can remove some of the nested cursors and
the problem goes away so I know it's memory related... Anybody have some
design advice.? I'm just moving/transforming data from a legacy database to
a
new system.. I tried to post my code but it's too big, I know that should be
a clue right there but I'm trying to bail a buddy out on a project that is
way overdue so opting for quick and dirty.. I can email the script, shoot me
an email..
Thanks!!
Danhi,
Let me know all that stuff at vtam13@.terra.es
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Alien2_51" wrote:
> I have something kind of bizarre going on and I'm not sure how to
> troubleshoot it, I've put print statements all over in my code to try an f
ind
> out where it's breaking but it doesn't seem to be breaking at any specific
> point, there is no error message, processing just stops about the time the
> machine runs out of memery, I suspect it's something with the number of
> cursors I have open at one time. I can remove some of the nested cursors a
nd
> the problem goes away so I know it's memory related... Anybody have some
> design advice.? I'm just moving/transforming data from a legacy database t
o a
> new system.. I tried to post my code but it's too big, I know that should
be
> a clue right there but I'm trying to bail a buddy out on a project that is
> way overdue so opting for quick and dirty.. I can email the script, shoot
me
> an email..
> Thanks!!
> Dan
>|||It went away with FAST_FORWARD.. hmm.. Maybe someone can tell why the
READ_ONLY is so much more costly on resources...?
"Enric" wrote:
> hi,
> Let me know all that stuff at vtam13@.terra.es
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "Alien2_51" wrote:
>|||Fast_forward is a forward Only read only cursor. If you can move forward an
d
backward in a cursor SQL needs to cache the results... If you use
fast_forward, sql only needs to have in memory ( or in tempdb depending on
the other options you choose), the current row you are looking at... Once yo
u
move off of that row, sql can purge it from memory because you are not
allowed to move backward...
Forward-only, read only cursors are called firehose cursors, and are the
fastest of any cursor...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Alien2_51" wrote:
> It went away with FAST_FORWARD.. hmm.. Maybe someone can tell why the
> READ_ONLY is so much more costly on resources...?
> "Enric" wrote:
>|||Thanks Wayne!!
Dan
"Wayne Snyder" wrote:
> Fast_forward is a forward Only read only cursor. If you can move forward
and
> backward in a cursor SQL needs to cache the results... If you use
> fast_forward, sql only needs to have in memory ( or in tempdb depending on
> the other options you choose), the current row you are looking at... Once
you
> move off of that row, sql can purge it from memory because you are not
> allowed to move backward...
> Forward-only, read only cursors are called firehose cursors, and are the
> fastest of any cursor...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Alien2_51" wrote:
>|||On Tue, 28 Mar 2006 02:26:02 -0800, Alien2_51 wrote:
>I have something kind of bizarre going on and I'm not sure how to
>troubleshoot it, I've put print statements all over in my code to try an fi
nd
>out where it's breaking but it doesn't seem to be breaking at any specific
>point, there is no error message, processing just stops about the time the
>machine runs out of memery, I suspect it's something with the number of
>cursors I have open at one time. I can remove some of the nested cursors an
d
>the problem goes away so I know it's memory related... Anybody have some
>design advice.?
Hi Dan,
Triggers whould be used VERY sparingly in SQL Server. Nested triggers
are a big red waving flag screaming "bad design!! bad design!!"
You should consider rewriting your stored procedure to use set-based
logic only - or at least to reduce the number of triggers.
If you need help with that, you'll really have to post your existing
code. And not only that - we need table structure (as CREATE TABLE
statements), sample data (as INSERT statements) and expected results as
well!
> I tried to post my code but it's too big
Eh? I've posted some prettly lengthy posts here and I've never gotten
any errors on this. Just make sure NOT to post it as a file atachment
(they get filtered out by namy usenet providers); just paste the
complete CREATE PROCEDURE statement in your post.
Of course - the longer the code you post, the harder it is to comment;
we are after all all doing this in our spare time. But if you do post, I
will definitely look at it - that much I promise.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo... I'm not using the cursors in a trigger.. I have a huge
procedural script that transforms data from one data schema to another.. I
have to nest my cursors to preserve all of my data relationships.. Some of
the relationships I'm trying to preserve are four and five levels deep..
~Dan
"Hugo Kornelis" wrote:
> On Tue, 28 Mar 2006 02:26:02 -0800, Alien2_51 wrote:
>
> Hi Dan,
> Triggers whould be used VERY sparingly in SQL Server. Nested triggers
> are a big red waving flag screaming "bad design!! bad design!!"
> You should consider rewriting your stored procedure to use set-based
> logic only - or at least to reduce the number of triggers.
> If you need help with that, you'll really have to post your existing
> code. And not only that - we need table structure (as CREATE TABLE
> statements), sample data (as INSERT statements) and expected results as
> well!
>
> Eh? I've posted some prettly lengthy posts here and I've never gotten
> any errors on this. Just make sure NOT to post it as a file atachment
> (they get filtered out by namy usenet providers); just paste the
> complete CREATE PROCEDURE statement in your post.
> Of course - the longer the code you post, the harder it is to comment;
> we are after all all doing this in our spare time. But if you do post, I
> will definitely look at it - that much I promise.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Tue, 28 Mar 2006 12:12:02 -0800, Alien2_51 wrote:
>Thanks Hugo... I'm not using the cursors in a trigger.. I have a huge
>procedural script that transforms data from one data schema to another.. I
>have to nest my cursors to preserve all of my data relationships.. Some of
>the relationships I'm trying to preserve are four and five levels deep..
Hi Dan,
I made a stupid typo. Where I wrote
I actually intended to write
*Cursors* whould be used VERY sparingly in SQL Server. Nested *cursors*
are a big red waving flag screaming "bad design!! bad design!!"
In most cases you don't need cursors at all. Neither for transforming
data, nor for preserving relationships.
That being said - if this is a one-time conversion job and it works now,
leave it as is!
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Yep! My rule of thumb is that any SQL statement over 10 lines is
suspect. But the real problem is that you used cursors at all! Nested
cursrors scream total incompedency, and old mag tape processing
algorithms.
No good deed goes unpunished! But if it is sooooo bad that you cannot
post to a Newsgroup, then he needs to pay someone to do his job for
him.
Based on 20+ years of fixing this stuff, I would start over with the
spec and do the job from scratch. What I am afraid of is that the
schema is also designed as if it were a mag tape file and thsu cannot
be saved, and probably has already destroyed data integrity.|||>> I have to nest my cursors to preserve all of my data relationships.. Some
of
the relationships I'm trying to preserve are four and five levels
deep.. <<
First, have you looked at ETL tools to do this job?
Again without DDL or code, we can only guess at what you are doing.
But when I have moved data from a "scrub table" to a normalized schema,
I never use cursors. I have found that something this is easier to
write:
BEGIN
..
INSERT INTO HighestReferencedLevel (...)
SELECT ..
FROM ScrubTable
WHERE ..;
INSERT INTO NextHighestReferencedLevel (...)
SELECT ..
FROM ScrubTable
WHERE ..;
etc.
END;
The real problem is what to do about the dirty data that cannot be
moved, like an order for an item that is not in Inventory.
I might have to hit the ScrubTable a few times in the transaction, but
it is still faster than cursors. You might also look at the MERGE
statement in SQL:2003 that other SQL products have.
Nested CASE statements Problem
I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'
I think there are two problems:
(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE
(b) Each CASE WHEN needs an END
I was not able to completely modify your T-SQL...but your changes should look similar to:
select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end
Peter
|||I think I probably should just use IF statements inside my first CASE statement|||Well, this throws an error also.
The error is:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'IF'.
Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Msg 147, Level 15, State 1, Line 9
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
END
ELSE
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .
case 1 and Case 4 of your outer else are the same. . .
should it have read:
ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
now if your first test in the outer case:
if ArgA > ArgB then this is never true:
ArgA < 0 AND ArgB > 0
then wouldn't all of the first half of your outer case logic translate to
if ArgA>ArgB then ArgA - Abs(ArgB)
now the else part:
if ArgB < ArgA then this is never true:
ArgA > 0 AND ArgB < 0
then wouldn't all of the else half of your outer case logic translate to
ArgB - abs(ArgA)
so simply put, wont this do it -
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax
|||typo on my last statement (change is bolditalic)
yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .
case 1 and Case 4 of your outer else are the same. . .
should it have read:
ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
now if your first test in the outer case:
if ArgA > ArgB then this is never true:
ArgA < 0 AND ArgB > 0
then wouldn't all of the first half of your outer case logic translate to
if ArgA>ArgB then ArgA - Abs(ArgB)
now the else part:
if ArgB > ArgA then this is never true:
ArgA > 0 AND ArgB < 0
then wouldn't all of the else half of your outer case logic translate to
ArgB - abs(ArgA)
so simply put, wont this do it -
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.
otherwise, just take off the case words from inside (to have a correct syntax)
Nested CASE statements Problem
I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'
I think there are two problems:
(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE
(b) Each CASE WHEN needs an END
I was not able to completely modify your T-SQL...but your changes should look similar to:
select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end
Peter
|||I think I probably should just use IF statements inside my first CASE statement|||Well, this throws an error also.
The error is:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'IF'.
Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Msg 147, Level 15, State 1, Line 9
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
END
ELSE
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .
case 1 and Case 4 of your outer else are the same. . .
should it have read:
ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
now if your first test in the outer case:
if ArgA > ArgB then this is never true:
ArgA < 0 AND ArgB > 0
then wouldn't all of the first half of your outer case logic translate to
if ArgA>ArgB then ArgA - Abs(ArgB)
now the else part:
if ArgB < ArgA then this is never true:
ArgA > 0 AND ArgB < 0
then wouldn't all of the else half of your outer case logic translate to
ArgB - abs(ArgA)
so simply put, wont this do it -
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax
|||typo on my last statement (change is bolditalic)
yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .
case 1 and Case 4 of your outer else are the same. . .
should it have read:
ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
now if your first test in the outer case:
if ArgA > ArgB then this is never true:
ArgA < 0 AND ArgB > 0
then wouldn't all of the first half of your outer case logic translate to
if ArgA>ArgB then ArgA - Abs(ArgB)
now the else part:
if ArgB > ArgA then this is never true:
ArgA > 0 AND ArgB < 0
then wouldn't all of the else half of your outer case logic translate to
ArgB - abs(ArgA)
so simply put, wont this do it -
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.
otherwise, just take off the case words from inside (to have a correct syntax)
Wednesday, March 7, 2012
NEED VERY BASIC HELP
At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.
Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.
Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...
NEED VERY BASIC HELP
At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.
Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.
Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...
NEED VERY BASIC HELP
At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.Please see my comments to your other posts.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:uSJS23GwFHA.2808@.TK2MSFTNGP10.phx.gbl...
Subscribe to:
Posts (Atom)