February 22, 2008 at 1:48 pm
update T1
set C1=CASE WHEN exists(select saleid
from T1
where clientid in
(select clientid from T1
where saleid =sl.thelist)
and saleid<>sl.thelist)
THEN 0 ELSE 1 END
from T1 s,
dbo.GenFunc_SeparatedList(@salelist,',') sl
where s.saleid = sl.thelist
and s.merchantid = @merchantid
and salestatusid =1
I just want to know if this script will work properly or not... and if someone can read it properly ... then just tell me what is it doing...
Although i have created this script ... but I have never used case when with subqueries inside a subquery ...
Function dbo.GenFunc_SeparatedList (@salelist, ',')
basically returns a table with one column and the sales value in each row of the column....
What I intend to do is ... I'm sending "saleid" in a list ... and want to check that the column "clientid" corresponding to that "saleid" in the table exists in the other saleids or not....If it does // than set C1 column to 0 else set it to 1
Please let me know if this script is gonna do what i intend to do...
February 22, 2008 at 2:13 pm
There may be a better way to accomplish what you want, but to do that we really need to know the structure (or at least the relevent portions) of the tables involved. This includes the table acessed by your function. We also could use some test data, preferable made up but consistant with your actual data.
This article will provide you with excellent guidance on what we need and how to put it together:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
😎
February 22, 2008 at 2:27 pm
Ok ...
I should tell you in detail now
I have a table say Table1 ....
it has columns ... saleid,clientid,merchantid,affiliateid,salestatus,firsttransaction...
saleid is unique...
any sale that is made... is assigned a saleid... with salestatus pending...
Now say i had sale today and their saleids are(5,6,7,8).. The rows have been inserted in the table ... Now i need to update few columns as follows...
Each saleid has client id associated to it.. we can see in the columns I mentioned...
I need to update firsttransaction column ... for sale ids 5,6,7,8
to values 0 or 1 ...
But to assign it a value .. 0 or 1 ... I need to check if the saleids that i sent to the procedure(that are already in the table) has clientid different from all the other sales in the table... (ofcourse other than the one that i sent)
If the client id is new for this sale id ... I have to tag firsttransaction column as 1 else 0...
The Update statement that i sent in my previous post ... has a basic structure... which i'm not sure of ... is correct or not...
Please help me in this 🙂
Thanks
Smita
February 22, 2008 at 3:46 pm
Your description is still too broad to make a guess at what you are trying to do. As Lynn suggested, read the best practices article for posting and revamp your post to include table DDL and insert statements with sample data showing the before and after results that you are looking for.
It seems like you may also have some normalization problems in your table Table1. Don't you have some sort of date column for the sale? If so, you don't need a column to 'flag' the transaction as the first for a specific client. Instead, you query the data to show which sale was the first for the client:
SELECT t1.*
FROM Table1 t1
INNER JOIN (
SELECT ClientID, MIN(SaleDate) as FirstDate
FROM Table1
GROUP BY ClientID
) t2
ON t1.ClientID = t2.ClientID AND t1.SaleDate = t2.FirstDate
Keep in mind that I have not yet seen your table DDL and I did not test this. My point is, I assume that you are flagging the row as the first transaction for some other process or report that you are planning on running for those rows later downstream. Why not just query the data for the first transactions for each client as you need it? Using a flag column is a crutch. I hope that I am making sense here.
You are going through quite a bit of trouble to get it updated, so what is the FirstTransaction column being used for?
February 22, 2008 at 3:51 pm
Also, if you are using this column as a flag and later scanning this column for whatever reason, you are going to get poor performance out of the scan. In fact, you'll probably get a table scan each time you access the table and filter on this column. Sure, you could try adding an index to it, but depending on the frequency of customer re-orders, the selectivity of the values in this column could make the index useless.
I could be jumping to the wrong conclusion here, but the fact is---we need more info to help you.
February 23, 2008 at 7:02 pm
John Rowan (2/22/2008)
Also, if you are using this column as a flag and later scanning this column for whatever reason, you are going to get poor performance out of the scan. In fact, you'll probably get a table scan each time you access the table and filter on this column. Sure, you could try adding an index to it, but depending on the frequency of customer re-orders, the selectivity of the values in this column could make the index useless.I could be jumping to the wrong conclusion here, but the fact is---we need more info to help you.
I understand that there are many flaws in the database... But the company I'm working for ... already had this database before me getting recruited here... this is too early for me to make changes as the database is online ... up and running...
It is not Normalized ... and I know it... but this is the most that i can do for now...
I know scanning would take long... but the report that is being generated needs the first transaction of the client to be tagged...
this is basically a utility that we want to provide merchants the first real transaction made by a customer...
The Update statement above... Could u please tell me if its syntactically correct or not... and will it actually tag the column value as 1 if the sales list that i sent in the procedure is processed for client id compared to the other records in the table.
February 23, 2008 at 11:02 pm
We are trying to help you. If you take the time to read the article I pointed you to, you would see what we need to be able to help you with your issue. I can't tell you if the query you provided will do what you want, as I don't have the your table(s) or data. You need to provide us with the information needed to help you.
We may also be able to provide you with a much better solution if you give us more info. If you are unable to provide actual data, then you need to create fake data that mimics your actual data.
😎
February 25, 2008 at 1:56 pm
smitavarma (2/22/2008)
update T1set C1=CASE WHEN exists(select saleid
from T1
where clientid in
(select clientid from T1
where saleid =sl.thelist)
and saleid<>sl.thelist)
THEN 0 ELSE 1 END
from T1 s,
dbo.GenFunc_SeparatedList(@salelist,',') sl
where s.saleid = sl.thelist
and s.merchantid = @merchantid
and salestatusid =1
Not sure why you expect us to read your post when you won't read the one suggested. The other post makes this process a lot more efficient.
Some comments on your SQL.
exists(select saleid
from T1...)
You don't need to select the SALEID in an exists
EXISTS(SELECT 1 FROM T1...) instead.
from T1 s,
dbo.GenFunc_SeparatedList(@salelist,',') sl
where s.saleid = sl.thelist
change to new syntax for readability for one
FROM dbo.GenFunc_SeparatedList(@salelist,',') AS sl
INNER JOIN T1 AS s
ON s.saleid = sl.thelist
As for it being syntacally correct.
Why don't you try it on a test system? Copy data to temp tables. SubSet of data? there are lots of ways to do this? Spend 15 minutes and you will get a whole lot of help.
February 27, 2008 at 12:44 pm
exists(select saleid
from T1...)
You don't need to select the SALEID in an exists
EXISTS(SELECT 1 FROM T1...) instead.
from T1 s,
dbo.GenFunc_SeparatedList(@salelist,',') sl
where s.saleid = sl.thelist
change to new syntax for readability for one
FROM dbo.GenFunc_SeparatedList(@salelist,',') AS sl
INNER JOIN T1 AS s
ON s.saleid = sl.thelist
The query runs fine 🙂
Thanks for your help guys ...
February 27, 2008 at 12:46 pm
MIT (2/27/2008)
exists(select saleidfrom T1...)
You don't need to select the SALEID in an exists
EXISTS(SELECT 1 FROM T1...) instead.
from T1 s,
dbo.GenFunc_SeparatedList(@salelist,',') sl
where s.saleid = sl.thelist
change to new syntax for readability for one
FROM dbo.GenFunc_SeparatedList(@salelist,',') AS sl
INNER JOIN T1 AS s
ON s.saleid = sl.thelist
My query runs fine 🙂
Thanks for your help guys ...
February 27, 2008 at 2:23 pm
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Obvious you still didn't read that. Not only are we trying to help you, but also to help others.
I do appreciate letting us know that it worked, but it would be nice to know some of the following too.
So what did you do to get it to work?
Did you do anything?
Did you test it?
Did you incorporate any of the suggestions?
Did you learn anything that you would like to share?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply