Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Check for empty group within stored procedure

Author  Topic 

gems
Starting Member

19 Posts
Posted - 2005-11-04 : 09:11:31
Hi,

I created a report in Crystal Report that uses a stored procedure. Stored Procedure is pretty simple where I declare my parameters for the report and my select statement. I have 4 groupings in my report namely -
Region, Sector, SubSector and Interval. The interval group is a formula(created in crystal) that checks the field interval_length and if the values in that field are AA, BB, CC, DD then it puts it under "Less Than Weekly Interval" else "Greater Than Weekly".
Now my problem is that if there are no records found for a particular Interval Group, crystal does not group on it and totally skips that interval. I want to be able to display that interval even if there are no records and display a message saying "No Records Found". My understanding is that I'll have to handle this in my stored procedure. I'll have to somehow create the interval group in the stored procedure and check if it is empty for a particular region, sector, subsector and display the message so that when crystal calls the stored procedure it will grab the interval group even if it is empty. I hope it makes sense. I am totally lost as to how this should be done in the stored procedure.
I'd really appreciate any help.

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2005-11-04 : 09:45:35
Move your interval grouping formula to your stored procedure.
In your procedure, you will use this formula to first create both interval groupings, which you will then LEFT OUTER JOIN to your calculated result set. This will leave a record for each interval, with NULL values in the details if there are no related calculated records. You could then use the ISNULL() or COALESCE() functions to change the NULL value to your default message.
Go to Top of Page

gems
Starting Member

19 Posts
Posted - 2005-11-04 : 14:26:27
Thanks for your reply blilndman. I am fairly new to stored procedures. Can you please help me alittle with the code. I understand what you want me to do. First should the Interval formula go in the procedure. I am posting my stored procedure. I would really appreciate your help.

CREATE PROCEDURE [dbo].[pPMPERDET_ES] @START_DATE DATETIME,
@END_DATE DATETIME,
@FAC_REGION char (2),
@FAC_SECTOR char (2) ,
@FAC_SUBSECTOR char

AS
BEGIN

SELECT

LOGHDR.MPS_FAC_BASE_IDENT, LOGHDR.MPS_FAC_SUFX_IDENT, LOGHDR.ENTRY_YEAR AS HDR_ENTRY_YEAR, LOGHDR.ENTRY_NUMBER AS HDR_ENTRY_NUMBER,
LOGHDR.ENTRY_SEQUENCE AS HDR_ENTRY_SEQUENCE,
LOGHDR.VOID_STATUS, LOGCOM_PMCERT.MPS_FAC_BASE_IDENT AS LOGCOM_MPS_FAC_BASE_IDENT,
LOGCOM_PMCERT.MPS_FAC_SUFX_IDENT AS LOGCOM_MPS_FAC_SUFX_IDENT, LOGCOM_PMCERT.ENTRY_YEAR,
LOGCOM_PMCERT.ENTRY_NUMBER, LOGCOM_PMCERT.ENTRY_SEQUENCE, LOGCOM_PMCERT.DIRECTIVE_LEVEL, LOGCOM_PMCERT.ENTRY_TYPE, LOGCOM_PMCERT.INTERVAL_LENGTH,
LOGLCO.MPS_FAC_BASE_IDENT AS LOGLCO_MPS_FAC_BASE_IDENT, LOGLCO.MPS_FAC_SUFX_IDENT AS LOGLCO_MPS_FAC_SUFX_IDENT,
LOGLCO.COMMENT_SEQUENCE, LOGLCO.COMMENT_DATE_TIME, LOGLCO.COMMENT_TEXT

FROM ((mmssql.dbo.LOGHDR LOGHDR INNER JOIN
mmssql.dbo.LOGCOM_PMCERT LOGCOM_PMCERT ON LOGHDR.MPS_FAC_BASE_IDENT = LOGCOM_PMCERT.MPS_FAC_BASE_IDENT AND
LOGHDR.MPS_FAC_SUFX_IDENT = LOGCOM_PMCERT.MPS_FAC_SUFX_IDENT AND LOGHDR.ENTRY_YEAR = LOGCOM_PMCERT.ENTRY_YEAR AND
LOGHDR.ENTRY_SEQUENCE = LOGCOM_PMCERT.ENTRY_SEQUENCE AND
LOGHDR.ENTRY_NUMBER = LOGCOM_PMCERT.ENTRY_NUMBER)

LEFT OUTER JOIN mmssql.dbo.LOGLCO LOGLCO ON
LOGCOM_PMCERT.MPS_FAC_BASE_IDENT = LOGLCO.MPS_FAC_BASE_IDENT AND
LOGCOM_PMCERT.MPS_FAC_SUFX_IDENT = LOGLCO.MPS_FAC_SUFX_IDENT AND LOGCOM_PMCERT.ENTRY_YEAR = LOGLCO.ENTRY_YEAR AND
LOGCOM_PMCERT.ENTRY_NUMBER = LOGLCO.ENTRY_NUMBER AND LOGCOM_PMCERT.ENTRY_SEQUENCE = LOGLCO.ENTRY_SEQUENCE)

WHERE
-- fixed selection criteria

(LOGCOM_PMCERT.DIRECTIVE_LEVEL = 'N' OR LOGCOM_PMCERT.DIRECTIVE_LEVEL = '')
AND
(LOGCOM_PMCERT.ENTRY_TYPE = 'LPM')
AND
(LOGHDR.VOID_STATUS <> 'V' OR LOGHDR.VOID_STATUS <> '')


ORDER BY
LOGCOM_PMCERT.FAC_REGION ASC,
LOGCOM_PMCERT.FAC_SECTOR ASC,
LOGCOM_PMCERT.FAC_SUBSECTOR ASC

END
GO

My Interval formula in crystal is something like this

If {pPMPERDET_ES.INTERVAL_LENGTH} IN ["DD","DA","SW","SW4"] then "LESS THAN WEEKLY INTERVALS" else
"FOR WEEKLY OR GREATER INTERVALS"

Thanks so much
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2005-11-04 : 14:44:19
This could certainly be cleaned up a lot, but this is the general idea:
CREATE PROCEDURE [dbo].[pPMPERDET_ES] @START_DATE DATETIME, 
@END_DATE DATETIME,
@FAC_REGION char (2), 
@FAC_SECTOR char (2) ,
@FAC_SUBSECTOR char

AS
BEGIN

SELECT	INTERVALGROUPS.INTERVALGROUP,
	RESULTDATA.*
FROM	--INTERVALGROUPS
	(SELECT	'LESS THAN WEEKLY INTERVALS' AS INTERVALGROUP
	UNION SELECT 'FOR WEEKLY OR GREATER INTERVALS') INTERVALGROUPS
	LEFT OUTER JOIN --RESULTDATA
		(SELECT
		CASE WHEN LOGCOM_PMCERT.INTERVAL_LENGTH IN('DD','DA','SW','SW4') THEN 'LESS THAN WEEKLY INTERVALS' ELSE 'FOR WEEKLY OR GREATER INTERVALS' END AS INTERVAL_GROUP,
		LOGHDR.MPS_FAC_BASE_IDENT, LOGHDR.MPS_FAC_SUFX_IDENT, LOGHDR.ENTRY_YEAR AS HDR_ENTRY_YEAR, LOGHDR.ENTRY_NUMBER AS HDR_ENTRY_NUMBER, 
		LOGHDR.ENTRY_SEQUENCE AS HDR_ENTRY_SEQUENCE, 
		LOGHDR.VOID_STATUS, LOGCOM_PMCERT.MPS_FAC_BASE_IDENT AS LOGCOM_MPS_FAC_BASE_IDENT, 
		LOGCOM_PMCERT.MPS_FAC_SUFX_IDENT AS LOGCOM_MPS_FAC_SUFX_IDENT, LOGCOM_PMCERT.ENTRY_YEAR, 
		LOGCOM_PMCERT.ENTRY_NUMBER, LOGCOM_PMCERT.ENTRY_SEQUENCE, LOGCOM_PMCERT.DIRECTIVE_LEVEL, LOGCOM_PMCERT.ENTRY_TYPE, LOGCOM_PMCERT.INTERVAL_LENGTH,
		LOGLCO.MPS_FAC_BASE_IDENT AS LOGLCO_MPS_FAC_BASE_IDENT, LOGLCO.MPS_FAC_SUFX_IDENT AS LOGLCO_MPS_FAC_SUFX_IDENT, 
		LOGLCO.COMMENT_SEQUENCE, LOGLCO.COMMENT_DATE_TIME, LOGLCO.COMMENT_TEXT
		
		FROM ((mmssql.dbo.LOGHDR LOGHDR INNER JOIN
		mmssql.dbo.LOGCOM_PMCERT LOGCOM_PMCERT ON LOGHDR.MPS_FAC_BASE_IDENT = LOGCOM_PMCERT.MPS_FAC_BASE_IDENT AND 
		LOGHDR.MPS_FAC_SUFX_IDENT = LOGCOM_PMCERT.MPS_FAC_SUFX_IDENT AND LOGHDR.ENTRY_YEAR = LOGCOM_PMCERT.ENTRY_YEAR AND 
		LOGHDR.ENTRY_SEQUENCE = LOGCOM_PMCERT.ENTRY_SEQUENCE AND 
		LOGHDR.ENTRY_NUMBER = LOGCOM_PMCERT.ENTRY_NUMBER)
		
		LEFT OUTER JOIN mmssql.dbo.LOGLCO LOGLCO ON 
		LOGCOM_PMCERT.MPS_FAC_BASE_IDENT = LOGLCO.MPS_FAC_BASE_IDENT AND 
		LOGCOM_PMCERT.MPS_FAC_SUFX_IDENT = LOGLCO.MPS_FAC_SUFX_IDENT AND LOGCOM_PMCERT.ENTRY_YEAR = LOGLCO.ENTRY_YEAR AND 
		LOGCOM_PMCERT.ENTRY_NUMBER = LOGLCO.ENTRY_NUMBER AND LOGCOM_PMCERT.ENTRY_SEQUENCE = LOGLCO.ENTRY_SEQUENCE)
		
		WHERE 
		-- fixed selection criteria
		
		(LOGCOM_PMCERT.DIRECTIVE_LEVEL = 'N' OR LOGCOM_PMCERT.DIRECTIVE_LEVEL = '')
		AND 
		(LOGCOM_PMCERT.ENTRY_TYPE = 'LPM')
		AND
		(LOGHDR.VOID_STATUS <> 'V' OR LOGHDR.VOID_STATUS <> '')) RESULTDATA
		ON INTERVALGROUPS.INTERVALGROUP = RESULTDATA.INTERVALGROUP
ORDER BY
FAC_REGION ASC,
FAC_SECTOR ASC,
FAC_SUBSECTOR ASC

END
GO
Go to Top of Page

gems
Starting Member

19 Posts
Posted - 2005-11-04 : 16:37:22
Thanks a ton. I will try it out and get back to you.
Go to Top of Page

gems
Starting Member

19 Posts
Posted - 2005-11-04 : 16:43:40
One more thing, where do I create my Interval formula in the stored procedure?
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2005-11-04 : 17:18:02
It's already in the code I posted:
CASE WHEN LOGCOM_PMCERT.INTERVAL_LENGTH IN('DD','DA','SW','SW4') THEN 'LESS THAN WEEKLY INTERVALS' ELSE 'FOR WEEKLY OR GREATER INTERVALS' END AS INTERVAL_GROUP,

...right after the SELECT.
Go to Top of Page

gems
Starting Member

19 Posts
Posted - 2005-11-07 : 09:36:44
Hi Blindman, I added the code but it keeps giving me this error -

Error 107: The column prefix 'LOGCOM_PMCERT' does not match with a table name or alias name used in the query.
The column prefix 'LOGCOM_PMCERT' does not match with a table name or alias name used in the query.
The column prefix 'LOGCOM_PMCERT' does not match with a table name or alias name used in the query.

Table name is correct. Don't know why it is giving me this error.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site