Link to home
Start Free TrialLog in
Avatar of Leogal
LeogalFlag for United States of America

asked on

How to Capture metrics in a SQL script

I am using the code below to capture metrics from a sql script whereby the count of duplicate records is written out to a metrics file.

Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
   Values(Current_timestamp, 'Extract', 'Purch Site', 'Number of Purch Site records extracted', 
	(select count(*) from [dbo].[WorkItemPurch] ) ) ;
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
Values(Current_timestamp, 
	'Extract', 
	'Purch Site', 
	'Duplicates found', 
	(SELECT COUNT(*) FROM ( select count(*) from [dbo].[WorkItemPurch]
	                      group by ItemID   
						  having count(*) > 1  ) ))					    

Open in new window


the problem is that I am apparently incorrect on my syntax... as the second to last closing quote has an error showing in the sytax checker,  ' Incorrect syntax near ')' .  Expecting AS, ID or QUOTED ID.

if I change the code to the following I get a new error:

Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
   Values(Current_timestamp, 'Extract', 'Purch Site', 'Number of Purch Site records extracted', 
	(select count(*) from [dbo].[WorkItemPurch] ) ) ;
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
Values(Current_timestamp, 
	'Extract', 
	'Purch Site', 
	'Duplicates found', 
	(SELECT COUNT(*) FROM ( select count(*) from [dbo].[WorkItemPurch]
	                      group by ItemID   
						  having count(*) > 1  ) as i ))					    
;	

Open in new window


error : Msg 8155, Level 16, State 2, Line 13 No column name was specified for column 1 of 'i'.

A co-worker used this code fine in AS400 SQL and he wants me to adapt it to sql server. I am trying but not successfully.   I understand that the syntax is not exactly the same between the two  sql flavors but I need help.
Can someone help me to fix this syntax?
metrics-file-layout.docx
Avatar of ste5an
ste5an
Flag of Germany image

You need an alias name for the sub-query:

INSERT  INTO [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
VALUES  
	( 
		CURRENT_TIMESTAMP, 
		'Extract', 
		'Purch Site', 
		'Duplicates found', 
        ( SELECT    COUNT(*)
          FROM      ( SELECT    COUNT(*)
                      FROM      [dbo].[WorkItemPurch]
                      GROUP BY  ItemID
                      HAVING    COUNT(*) > 1
                    ) Q
        ) );		

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
A simple change in second INSERT would resolve the issue -

CHANGE -  
select count(*) from [dbo].[WorkItemPurch]
TO
select count(*) AS CountVal from [dbo].[WorkItemPurch]

Anoo
What ScottPletcher provided is a solution, I agree 100%.

Out of curiosity, did you try the solution that I provided and if so did you face any issues on that ?