A query that returns very few records

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

SQL Server Development (2000)

A query that returns very few records

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-10 : 07:07:28

Hi,

I have a SP that makes a select in Offers table according to some parameters selected by user in the application:


USE market3
GO
ALTER PROCEDURE find_offers

@Quality_num tinyint, @Caliber_min numeric(6,2), @Caliber_max numeric(6,2), @Image tinyint, @Rippening_num tinyint
As

    Select Offer_id, User_num, Offer_date, Offer_title, Company_name, City_name
    From Offers As offe 

WHERE EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0))    
     And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Caliber BETWEEN @Caliber_min And @Caliber_max And @Caliber_min) Or (@Caliber_min = 0 And @Caliber_max = 0))       
     And EXISTS (SELECT * FROM Offers_rippening As of_rip WHERE (of_rip.Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0)) And of_rip.Rippening_num = @Rippening_num) Or @Rippening_num = 0)     
     And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id = of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1) Or @Image = 0))  

GO

Somebody knows why I am getting so few records from Offers table? When user doesn’ t specify any parameter or only specify some of them, I want all the records from Offers table are returned. How can I achieve it? Is it possible is a question of null values that when are found through ‘Where’ clause the record isn’ t returned?

Thank you

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-10-11 : 07:57:48

Hi
Obviously, it's a bit hard without any example data or DDL, but it looks as though you need to provide some defaults for your parameters. When you say the user "doesn't specify any parameter or only specify some of them", I'm presuming you mean that NULLs are getting passed in, as failing to specify them would result in the procedure erroring.
Your WHERE clause also looks a bit convoluted. I've tried to replace it with joins, but this may lead to duplication depending upon your data:

ALTER PROCEDURE find_offers
	@Quality_num TINYINT = NULL, 
	@Caliber_min NUMERIC(6,2) = NULL, 
	@Caliber_max NUMERIC(6,2) = NULL, 
	@Image TINYINT, 
	@Rippening_num TINYINT = NULL
AS

	SELECT 
		Offer_id, 
		User_num, 
		Offer_date, 
		Offer_title, 
		Company_name, 
		City_name
	FROM 
		dbo.Offers As offe 
		JOIN dbo.Offers_quality AS of_qual
			ON of_qual.Offer_Num = offe.Offer_ID
			JOIN dbo.Offers_rippening AS of_rip
				ON of_rip.Offer_qual_num = of_qual.Offer_qual_id

	WHERE
		of_qual.Quality_num = COALESCE(@Quality_num, of_qual.Quality_num)
		AND of_qual.Caliber BETWEEN COALESCE(@Caliber_min, of_qual.Caliber) AND COALESCE(@Caliber_max, of_qual.Caliber)
		AND of_qual.Quality_num = COALESCE(@Quality_num, of_qual.Quality_num) 
		AND((of_qual.Image_name IS NOT NULL AND @Image = 1) OR @Image = 0)
		AND of_rip.Rippening_num = COALESCE(@Rippening_num, of_rip.Rippening_num)

Mark

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-11 : 13:37:33

I tried your code but it doesn' t work. Instead I wrote this:


USE market3
GO
ALTER PROCEDURE find_offers

@Quality_num tinyint, @Caliber_min numeric(6,2), @Caliber_max numeric(6,2), @Image tinyint, @Rippening_num tinyint
As

    Select Offer_id, User_num, Offer_date, Offer_title, Company_name, City_name
    From Offers As offe 


   WHERE (@Quality_num = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id= of_qual.Offer_num And (of_qual.Quality_num = @Quality_num)))    

     And (((@Quality_num = 0 And @Caliber_min <> 0 And @Caliber_max <> 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num) And (of_qual.Caliber BETWEEN @Caliber_min And @Caliber_max))) 
          Or ((@Quality_num = 0 And @Caliber_min <> 0 And @Caliber_max = 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num) And (of_qual.Caliber >= @Caliber_min)))
          Or ((@Quality_num = 0 And @Caliber_min = 0 And @Caliber_max <> 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num) And (of_qual.Caliber <= @Caliber_max)))
          Or (@Quality_num = 0 And @Caliber_min = 0 And @Caliber_max = 0)
          Or (@Quality_num <> 0 And @Caliber_min = 0 And @Caliber_max = 0)  
          Or ((@Quality_num <> 0 And @Caliber_min <> 0 And @Caliber_max <> 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num And of_qual.Quality_num = @Quality_num) And (of_qual.Caliber BETWEEN @Caliber_min And @Caliber_max))) 
          Or ((@Quality_num <> 0 And @Caliber_min <> 0 And @Caliber_max = 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num And of_qual.Quality_num = @Quality_num) And (of_qual.Caliber >= @Caliber_min)))   
          Or ((@Quality_num <> 0 And @Caliber_min = 0 And @Caliber_max <> 0) And EXISTS (SELECT * FROM Offers_quality As of_qual WHERE (offe.Offer_id= of_qual.Offer_num And of_qual.Quality_num = @Quality_num) And (of_qual.Caliber <= @Caliber_max))))  

     And ((@Quality_num = 0 And @Rippening_num = 0)
          Or ((@Quality_num = 0 And @Rippening_num <> 0) And EXISTS (SELECT * FROM Offers_Rippening As of_rip WHERE (of_rip.Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality As of_qual WHERE offe.Offer_id= of_qual.Offer_num) And of_rip.Rippening_num = @Rippening_num)))  
          Or (@Quality_num <> 0 And @Rippening_num = 0)
          Or ((@Quality_num <> 0 And @Rippening_num <> 0) And EXISTS (SELECT * FROM Offers_Rippening As of_rip WHERE (of_rip.Offer_qual_num In(SELECT Offer_qual_id FROM Offers_quality As of_qual WHERE offe.Offer_id= of_qual.Offer_num And of_qual.Quality_num = @Quality_num) And of_rip.Rippening_num = @Rippening_num))))  

     And (@Image = 0 Or EXISTS (SELECT * FROM Offers_quality As of_qual WHERE offe.Offer_id= of_qual.Offer_num And (of_qual.Quality_num = @Quality_num Or @Quality_num = 0) And ((of_qual.Image_name Is Not Null And @Image = 1))))

And works!

, and seems to work fast.. What do you think?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-10-12 : 05:11:28

Hi cesark,
No offence, but that code is not fun to read!!

As I said, I couldn't really offer any guarantees when I don't have any DDL or example data. When you say it "doesn't work", do you mean it errored, or it didn't return the correct result?
I'm happy that your code returns the correct result quickly, but I can't help but think there is probably a more efficient and more easily maintainable solution.

Mark

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-12 : 07:50:13

You should OUTER JOIN rather than using an EXISTS predicate.

Jay White

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-13 : 03:22:54

Mark!, here you have the DDL you needed. I posted the three tables involved in English (translated from Catalan language, which the tables are, to English), if you don’ t understand something or it’ s confusing I will post the original tables in Catalan language.


CREATE TABLE [Offers] (
	[Offer_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Reference] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[User_num] [bigint] NULL ,
	[Offer_date] [datetime] NULL ,
	[Product_num] [smallint] NULL ,
	[Other_families] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Other_products] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Variety] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Trade_name] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Offer_title] [varchar] (100) COLLATE Modern_Spanish_CI_AS NULL ,
	[place_city_num] [int] NULL ,
	[Harvest date] [smalldatetime] NULL ,
	[origin_state_num] [smallint] NULL ,
	[origin_city_num] [int] NULL ,
	[Kind_of_production] [tinyint] NULL ,
	[DOP_IGP] [smallint] NULL ,
	[Private_certificate] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL ,
	[Certificate_aut] [smallint] NULL ,
	[Other_certificates] [varchar] (150) COLLATE Modern_Spanish_CI_AS NULL ,
	[Other_data] [varchar] (1000) COLLATE Modern_Spanish_CI_AS NULL ,
	[Currency_num] [smallint] NULL ,
	[Complete_offer] [bit] NULL ,
	CONSTRAINT [PK_Products_Offers] PRIMARY KEY  CLUSTERED 
	(
		[Offer_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Offers_Certificates_quality] FOREIGN KEY 
	(
		[Certificate_aut]
	) REFERENCES [Certificates_quality] (
		[Certificate_quality_id]
	),
	CONSTRAINT [FK_Offers_DOP_IGP_product] FOREIGN KEY 
	(
		[DOP_IGP]
	) REFERENCES [DOP_IGP_product] (
		[DOP_IGP_product_id]
	),
	CONSTRAINT [FK_Offers_Currencies] FOREIGN KEY 
	(
		[Currency_num]
	) REFERENCES [Currencies] (
		[Currency_id]
	),
	CONSTRAINT [FK_Offers_Cities] FOREIGN KEY 
	(
		[place_city_num]
	) REFERENCES [Cities] (
		[City_id]
	),
	CONSTRAINT [FK_Offers_Cities1] FOREIGN KEY 
	(
		[origin_city_num]
	) REFERENCES [Cities] (
		[City_id]
	),
	CONSTRAINT [FK_Offers_Products_names] FOREIGN KEY 
	(
		[Product_num]
	) REFERENCES [Products_names] (
		[Product_Id]
	),
	CONSTRAINT [FK_Offers_States] FOREIGN KEY 
	(
		[origin_state_num]
	) REFERENCES [States] (
		[State_Id]
	),
	CONSTRAINT [FK_Offers_Kind_of_production] FOREIGN KEY 
	(
		[Kind_of_production]
	) REFERENCES [Kind_of_production] (
		[Kind_production_id]
	),
	CONSTRAINT [FK_Offers_Users] FOREIGN KEY 
	(
		[User_num]
	) REFERENCES [Users] (
		[User_id]
	)
) ON [PRIMARY]
GO




CREATE TABLE [Offers_quality] (
	[Offer_qual_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Offer_num] [bigint] NULL ,
	[Quality_num] [tinyint] NULL ,
	[Caliber] [numeric](6, 2) NULL ,
	[Measure_c] [smallint] NULL ,
	[Kind_caliber] [smallint] NULL ,
	[Image_name] [varchar] (256) COLLATE Modern_Spanish_CI_AS NULL ,
	[Folder_path_num] [int] NULL ,
	[Transport_cost] [smallint] NULL ,
	CONSTRAINT [PK_Offers_quality] PRIMARY KEY  CLUSTERED 
	(
		[Offer_qual_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Offers_quality_Kind_calibers] FOREIGN KEY 
	(
		[Kind_caliber]
	) REFERENCES [Kind_calibers] (
		[Measure_caliber_id]
	),
	CONSTRAINT [FK_Offers_quality_Transport_cost] FOREIGN KEY 
	(
		[Transport_cost]
	) REFERENCES [Transport_cost] (
		[Transport_cost_id]
	),
	CONSTRAINT [FK_Offers_quality_Offers] FOREIGN KEY 
	(
		[Offer_num]
	) REFERENCES [Offers] (
		[Offer_id]
	),
	CONSTRAINT [FK_Offers_quality_Kind_quality] FOREIGN KEY 
	(
		[Quality_num]
	) REFERENCES [Kind_quality] (
		[Quality_id]
	),
	CONSTRAINT [FK_Offers_quality_Folders_path] FOREIGN KEY 
	(
		[Folder_path_num]
	) REFERENCES [Folders_path] (
		[Folder_path_id]
	),
	CONSTRAINT [FK_Offers_quality_Measurement_units] FOREIGN KEY 
	(
		[Measure_caliber]
	) REFERENCES [Measurement_units] (
		[Unit_Measure_id]
	)
) ON [PRIMARY]
GO




CREATE TABLE [Offers_rippening] (
	[Offer_rip_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Offer_qual_num] [bigint] NULL ,
	[Rippening_num] [tinyint] NULL ,
	[Quantity_available] [numeric](8, 2) NULL ,
	[Measure_quant] [smallint] NULL ,
	[Package_type] [smallint] NULL ,
	[Quant_package] [numeric](8, 2) NULL ,
	[Measure_quantPack] [smallint] NULL ,
	[Price] [numeric](8, 2) NULL ,
	[Measure_Price] [smallint] NULL ,
	CONSTRAINT [PK_Offers_rippening] PRIMARY KEY  CLUSTERED 
	(
		[Offer_rip_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Offers_rippening_Packages_type] FOREIGN KEY 
	(
		[Package_type]
	) REFERENCES [Packages_type] (
		[Package_type_id]
	),
	CONSTRAINT [FK_Offers_rippening_Rippening_type] FOREIGN KEY 
	(
		[Rippening_num]
	) REFERENCES [Rippening_type] (
		[Rippening_id]
	),
	CONSTRAINT [FK_Offers_rippening_Offers_quality] FOREIGN KEY 
	(
		[Offer_qual_num]
	) REFERENCES [Offers_quality] (
		[Offer_qual_id]
	),
	CONSTRAINT [FK_Offers_rippening_Units_Measure] FOREIGN KEY 
	(
		[Measure_quant]
	) REFERENCES [Units_Measure] (
		[Unit_Measure_id]
	),
	CONSTRAINT [FK_Offers_rippening_Units_Measure1] FOREIGN KEY 
	(
		[Measure_quantPack]
	) REFERENCES [Units_Measure] (
		[Unit_Measure_id]
	),
	CONSTRAINT [FK_Offers_rippening_Units_Measure2] FOREIGN KEY 
	(
		[Measure_Price]
	) REFERENCES [Units_Measure] (
		[Unit_Measure_id]
	)
) ON [PRIMARY]
GO

]

quote:

When you say it "doesn't work", do you mean it errored, or it didn't return the correct result?

I meant that none result was returned in any case.

Could you help me now in the way I have to write this SP so that, as you said, is more efficient and more easily maintainable please?

Thank you,
Cesar

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-10-14 : 05:35:33

Hi Cesar
Could you post some DML to insert sample data. I.e. INSERT INTO dbo.MyTable (Field1, Field2, ...) VALUES('Some value', 'Some other value') Also, not really a problem, but a lot of the FK constraints refer to tables not included in the DDL you posted.

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-14 : 06:04:14

mwjdavidson, thank you for your aid but finally I am changing my code to Dynamic SQL. In this scenario I think it' s the best option, with Static SQL it' s too difficult to cover all combinations of parameter values, can be very slow in performance terms, and the resultant code can be very difficult to maintain.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-10-17 : 07:12:32

Hi Cesar,
Again, I wouldn't advise this course of action, and it doesn't appear to be necessary. However, it's your call!

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-18 : 11:40:52

quote:
Hi Cesar
Could you post some DML to insert sample data. I.e. INSERT INTO dbo.MyTable (Field1, Field2, ...) VALUES('Some value', 'Some other value') Also, not really a problem, but a lot of the FK constraints refer to tables not included in the DDL you posted.

If you really think that my query can be implemented with Static Sql and can be reasonably maintainable and with acceptable speed performance (for tables with many records), then I will be very happy.

Here you have what you asked for to accomplish that task:


USE market
GO
ALTER PROC insert_QA       -- QA means high quality = 1
@Offer_id bigint,
@Caliber_QA numeric(6,2) = null, @Measure_c_QA smallint = null, 
@Kind_caliber_QA smallint = null, @Image_name_QA varchar(256) = null, 
@Transport_cost_QA smallint = null, @OfId_Image_name_QA varchar(256) = null output,
@Rippening_num_QA smallint = null, @Quantity_available_QA numeric(8,2) = null,
@Measure_quant_QA smallint = null, @Package_type_QA smallint = null, 
@Quant_package_QA numeric(8,2) = null, @Measure_quantPack_QA smallint = null, 
@Price_QA numeric(8,2) = null, @Measure_Price_QA smallint = null
AS

Declare @Offer_num As varchar(256)
SET @Offer_num = @Offer_id
 
SET @OfId_Image_name_QA =
CASE @Image_name_QA WHEN null THEN null ELSE 'QA_' + @Offer_num + '_' + @Image_name_QA END


SET NOCOUNT ON
  INSERT INTO Offers_quality (Offer_num, Quality_num, Caliber, Measure_c,
    Kind_caliber, Image_name, Folder_path_num, Transport_cost)
  VALUES (@Offer_id, 1, @Caliber_QA, @Measure_c_QA, Kind_caliber_QA, @OfId_Image_name_QA, 
    5, @Transport_cost_QA)
  Declare @Offer_q_id bigint 
  SET @Offer_q_id = SCOPE_IDENTITY()
SET NOCOUNT OFF


SET NOCOUNT ON
  INSERT INTO Offers_rippening (Offer_qual_num, Rippening_num, Quantity_available, Measure_quant,
   Package_type, Quant_package, Measure_quantPack, Price, Measure_Price)
  VALUES (@Offer_q_id, @ Rippening_num_QA, @Quantity_available_QA, @Measure_quant_QA, @Package_type_QA,
   @Quant_package_QA, @Measure_quantPack_QA, @Price_QA, @ Measure_Price_QA)
SET NOCOUNT OFF
GO

Thank you,
Cesar

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-10-19 : 10:19:37

Hi Cesar
Thanks for that. However, what I would need is some actual values.
I.e.

  INSERT INTO Offers_quality (Offer_num, Quality_num, Caliber, Measure_c, Kind_caliber, Image_name, Folder_path_num, Transport_cost)
  VALUES (1, 1, 1, 1, 1, 'name1', 1, 1)
INSERT INTO Offers_quality (Offer_num, Quality_num, Caliber, Measure_c, Kind_caliber, Image_name, Folder_path_num, Transport_cost)
  VALUES (2, 2, 2, 2, 2, 'name2', 2, 2)
etc.
(obviously, these are just example values and so the datatypes aren't correct).

and the expected result of the stored procedure.

Mark

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-19 : 11:09:22

I don't understand why you need now what you said..

 
INSERT INTO Offers_quality (Offer_num, Quality_num, Caliber, Measure_c, Kind_caliber, Image_name, Folder_path_num, Transport_cost)
  VALUES (420, 1, 25.59, 5, 1, ‘productB_Image25’, 5, 4)

INSERT INTO Offers_rippening (Offer_qual_num, Rippening_num, Quantity_available, Measure_quant,
   Package_type, Quant_package, Measure_quantPack, Price, Measure_Price)
  VALUES (620, 2, 350, 2, 5, 25.85, 2, 1.24, 2)

And I expect the SP insert all the data in every column normally.. I think you have all the necessary information.