March 26, 2011 at 3:15 pm
I am running MSSQL 2000 sp4.
Can someone please tell me how I can get the value of "count(*) AS line_count from activity_line_item" to display as a column in my result based on the following query?
select activity_code, [Description], register_count
from activity
where (select count(*) AS line_count --<<<<<<<<<<<<<<<<<
from activity_line_item
where activity_line_item.activity_code = activity.activity_code
and activity_line_item.inactive_flag = 0)
<> register_count
order by activity.activity_code
I have tried all sort of things but obviously it either can't be done or I do not possess the knowledge to figure out the solution.
Any help will be appreciated.
Howard
March 26, 2011 at 3:30 pm
hmbtx (3/26/2011)
I am running MSSQL 2000 sp4.
That's good to know. FYI, you posted in the 2k8 forum. You wanted the 7,2000 one that's further down in the list. Don't repost it though, we can help you here.
Can someone please tell me how I can get the value of "count(*) AS line_count from activity_line_item" to display as a column in my result based on the following query?
Not as you've written it, no, but let's see if we can help you get there. CROSS APPLY is usually the first goto for something trying to control itself by where clause, but this is 2k, not available. I'm not sure that would be more efficient in this case, anyway. You've also thought in terms of rows instead of columns and tables, which is probably why you were banging your head on the desk.
We'll just use a generic subquery instead. 🙂
EDIT: Code edited to remove typo.
SELECT
a.activity_code,
a.[Description],
a.register_count,
drv.line_count
FROM
activity AS a
JOIN
( SELECTali.Activity_Code, COUNT(*) AS line_count
FROMactivity_line_item AS ali
GROUP BYali.Activity_code
) AS drv
ONa.activity_code = drv.activity_code
WHERE
drv.line_count <> a.register_count
ORDER BY
activity.activity_code
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 26, 2011 at 3:34 pm
hmbtx
Please post table definition(s), sample data for each table and required results from the sample data in an easy to use format. To accomplish this please click on the first link in my signature block and follow the instructions in the article, and use the T-SQL code also in the article to do all this quickly and easily. This is requested so that those who want to help you can do so quickly and easily with a tested solution.
March 26, 2011 at 3:40 pm
Craig:
Thank you for the fast response.
I get the following errors when I run your query.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'AS'.
I realize that it is difficult to create a query when you do not have the data to test it.
Any ideas as to what the problem may be?
Ron has asked me to post table definition(s), sample data for each table and required results from the sample data in an easy to use format. I will start to work on that now. Sorry that I did not do this prior to posting the question. My apologies to all. It won't happen again.
Thanks,
Howard
March 26, 2011 at 3:58 pm
hmbtx (3/26/2011)
Server: Msg 156, Level 15, State 1, Line 8Incorrect syntax near the keyword 'FROM'.
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'AS'.
2 Q's:
1) Are you running my query in a SSMS window by itself, or is there other code in the same script?
2) Is the code you're running a direct copy and paste from the post above, or were there minor tweaks you needed to make? If there were, please copy/paste you code exactly here.
I don't see anything... offhand... that should make that fail. It's pointing at the select clause and whatever was following that for some reason in the derived query. Not quite sure why.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 26, 2011 at 3:59 pm
*FACEPALM*
I'm blind, sorry. It's currently stating FROM table FROM (SELECT ...)
Yeaaah... turn that into FROM table JOIN (SELECT...)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 26, 2011 at 4:04 pm
I am copying your script and pasting it into Query Analyzer and running it there.
I made the change as you suggested (see below) but now I get the following error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'activity' does not match with a table name or alias name used in the query.
select
a.activity_code,
a.[Description],
a.register_count,
drv.line_count
from
activity AS a
join
( SELECT ali.Activity_Code, COUNT(*) AS line_count
FROM activity_line_item AS ali
GROUP BY ali.Activity_code
) AS drv
ON a.activity_code = drv.activity_code
where
drv.line_count <> a.register_count
order by
activity.activity_code
I am working on getting you a script to create some test data.
Thanks
Howard
March 26, 2011 at 4:19 pm
hmbtx (3/26/2011)
I am copying your script and pasting it into Query Analyzer and running it there.I made the change as you suggested (see below) but now I get the following error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'activity' does not match with a table name or alias name used in the query.
selecta.activity_code,
a.[Description],
a.register_count,
drv.line_count
from
activity AS a
join
( SELECT ali.Activity_Code, COUNT(*) AS line_count
FROM activity_line_item AS ali
GROUP BY ali.Activity_code
) AS drv
ON a.activity_code = drv.activity_code
where
drv.line_count <> a.register_count
order by
activity.activity_code
I am working on getting you a script to create some test data.
Thanks
Howard
No worries. It's this line because I used aliasing:
activity.activity_code
make it:
a.activity_code
(also, side note, check out the code="sql" wrapper I used in your quote to make it easier to read)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 26, 2011 at 4:19 pm
I have quickly created a script that creates two tables, inserts values, and then runs the query that contains the count(*).
I hope that this will help. If not, please let me know what you may need.
--Drop table if it exists
IF OBJECT_ID ('A_Header') IS NOT NULL
DROP TABLE A_Header
GO
--Create table.
CREATE TABLE A_Header
(Activity_Code CHAR(12)NOT null PRIMARY KEY,
Register_Count SMALLINT)
go
--Add rows to table.
INSERT INTO A_Header (Activity_Code,Register_Count) VALUES ('1234567890',0)
INSERT INTO A_Header (Activity_Code,Register_Count) VALUES ('0987654321',1)
GO
--Drop table if it exists
IF OBJECT_ID ('A_Line') IS NOT NULL
DROP TABLE A_Line
GO
--Create table.
CREATE TABLE A_Line
(nt_Key INT IDENTITY(1,1) PRIMARY KEY,
Activity_Code CHAR(12) NOT NULL)
go
--Add rows to table.
INSERT INTO A_Line (Activity_Code) VALUES ('0987654321')
INSERT INTO A_Line (Activity_Code) VALUES ('0987654321')
GO
--List TABLE ROWS.
SELECT * FROM A_Header
SELECT * FROM A_Line
go
--LIST A_Header Count that does not equal the number of matching Activity_Code in A_Line rows.
select register_count,activity_code
from A_Header
where (select count(*) from A_Line
where A_Line.activity_code = A_Header.activity_code)
<> register_count
go
The result from the query lists two columns:
Activity_Code and Register Count
I would also like a column returned to show the value of count(*) from A_Line.
I hope that this makes some sense.
I really appreciate the help.
Thanks
Howard
March 26, 2011 at 4:23 pm
This runs against your sample.
SELECT
a.activity_code,
--a.[Description],
a.register_count,
drv.line_count
FROM
A_Header AS a
JOIN
( SELECTali.Activity_Code, COUNT(*) AS line_count
FROMA_Line AS ali
GROUP BYali.Activity_code
) AS drv
ONa.activity_code = drv.activity_code
where
drv.line_count <> a.register_count
order by
a.activity_code
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 26, 2011 at 4:27 pm
Craig:
That is it! Works perfectly.
Thanks for your help as I would not have ever figured this out on my own.
I am sorry that I did not provide you with the sample code to start with. I hope that I did not waste too much of your time on this.
Howard
March 26, 2011 at 4:36 pm
hmbtx (3/26/2011)
Craig:That is it! Works perfectly.
Thanks for your help as I would not have ever figured this out on my own.
I am sorry that I did not provide you with the sample code to start with. I hope that I did not waste too much of your time on this.
Howard
Glad to help. No worries, you'll know for next time. Also, if you do that for yourself when you're just starting to try to puzzle a problem out, it'll help you isolate the issue in your own mind, too. Sometimes the data volume gets in the way.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 26, 2011 at 5:05 pm
Running Craig Farrell code Posted Today @ 6:23 PM
On my SQL Server 2000
activity_code register_count line_count
------------- -------------- -----------
0987654321 1 2
Does Craig's code satisfy your requirements?
March 26, 2011 at 5:07 pm
Line added at 7:08 pm
Whoops took me too long to get my old system up and running.
bitbucket-25253 (3/26/2011)
Running Craig Farrell code Posted Today @ 6:23 PMOn my SQL Server 2000
activity_code register_count line_count------------- -------------- -----------
0987654321 1 2
Does Craig's code satisfy your requirements?
March 27, 2011 at 10:30 am
Ron:
Yes, Craig's code satisfied my requirements?
If it is okay, I would like to add one additional question based on Craig's solution and that is how can I change the script so that it will correct the regsiter_count in A_Header.
Example: Update A_Header set A_Header.register_count = drv.line_count.
Thanks
Howard
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply