The query isn't working properly!

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 2005 Forums

Transact-SQL (2005)

The query isn't working properly!

Author

Topic

ArviL
Starting Member

25 Posts

Posted - 2010-10-12 : 03:25:03

USE [MyDB]

DECLARE @DeviceID varchar(15)
DECLARE @ContractID varchar(25)
DECLARE @PayDate datetime

SELECT @DeviceID=MIN(DeviceID)
FROM dbo.InputLeasePayments
WHERE
COALESCE(DeliveryCost, MaintenanceCost, LeasePayment, FixedRent,0)>0

PRINT CASE WHEN @DeviceID Is Null Then 'No Device!' Else 'Device ' + CAST(@DeviceID AS varchar(25)) END
-- A non-zero value for DeviceID is returned.

SELECT @ContractID=MIN(ContractID)
FROM dbo.InputLeasePayments
WHERE
COALESCE(DeliveryCost, MaintenanceCost, LeasePayment, FixedRent,0)>0
AND DeviceID = @DeviceID

PRINT CASE WHEN @ContractID Is Null Then 'Contract is missing!' Else 'Contract ' + CAST(@ContractID AS varchar(15)) END
-- A non-zero value for ContractID is returned.

SELECT *
FROM dbo.InputLeasePayments
WHERE
COALESCE(DeliveryCost, MaintenanceCost, LeasePayment, FixedRent,0)>0
AND DeviceID = @DeviceID
-- The query returns a single row, where both ContractID and PayDate have non-zero values and at least one of the values DeliveryCost, MaintenanceCost, LeasePayment or FixedRent is >0.

SELECT *
FROM dbo.InputLeasePayments
WHERE
COALESCE(DeliveryCost, MaintenanceCost, LeasePayment, FixedRent,0)>0
AND ContractID = @ContractID
-- The query returns several rows, where one has ContractID = @ContractID AND DeviceID = @DeviceID. PayDate for this row has non-zero value and at least one of the values DeliveryCost, MaintenanceCost, LeasePayment or FixedRent is >0.

SELECT @PayDate = MIN(PayDate)
FROM dbo.InputLeasePayments
WHERE
COALESCE(LeasePayment, FixedRent, MaintenanceCost, DeliveryCost,0)>0
AND DeviceID = @DeviceID
AND ContractID = @ContractID

PRINT CASE WHEN @PayDate Is Null Then 'Paydate is missing!' Else 'Paydate ' + CAST(@PayDate AS varchar(15)) END
-- A message 'Paydate is missing!' is returned!

SELECT *
FROM dbo.InputLeasePayments
WHERE
COALESCE(LeasePayment, FixedRent, MaintenanceCost, DeliveryCost,0)>0
AND DeviceID = @DeviceID
AND ContractID=@ContractID
-- No rows are returned!

-----------------------
How can I get the paydate (which really exists in table!)?

Thanks in advance!
Arvi Laanemets

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 04:02:51

What does this query return ?


SELECT COALESCE(LeasePayment, FixedRent, MaintenanceCost, DeliveryCost,0)
FROM dbo.InputLeasePayments
WHERE
DeviceID = @DeviceID
AND ContractID=@ContractID
-- No rows are returned!

PBUH

ArviL
Starting Member

25 Posts

Posted - 2010-10-12 : 04:57:05

quote:

Originally posted by Sachin.Nand

What does this query return ?


SELECT COALESCE(LeasePayment, FixedRent, MaintenanceCost, DeliveryCost,0)
FROM dbo.InputLeasePayments
WHERE
DeviceID = @DeviceID
AND ContractID=@ContractID
-- No rows are returned!

PBUH

It returns several rows with COALESCE(...)=0 for all of them!

So somehow for one of queries (the one which has 3 conditions) COALESCE() stops working?

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 05:20:56

quote:
WHERE COALESCE(LeasePayment, FixedRent, MaintenanceCost, DeliveryCost,0)>0

If the query I posted is returning 0 then the above condition will never get satisfied & hence no rows will be returned.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 05:23:17

You will have to add '=' in the filter clause .

quote:
WHERE COALESCE(LeasePayment, FixedRent, MaintenanceCost, DeliveryCost,0)=>0

PBUH

ArviL
Starting Member

25 Posts

Posted - 2010-10-12 : 05:30:42

I replaced the condition with COALESCE() as
(LeasePayment>0 OR FixedRent>0 OR MaintenanceCost>0 OR DeliveryCost>0)
, and the results were completly different. My error completly! I assumed that empty fields have value Null as in other my tables, withou checking it out, but data in this one were imported from Access, and sometimes they have 0's for empty values instead.

Arvi Laanemets

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 05:32:13

So is it working now?

PBUH

ArviL
Starting Member

25 Posts

Posted - 2010-10-12 : 06:02:29

quote:
Originally posted by Sachin.Nand

So is it working now?

PBUH

Yes.

I lost a day and half on such a stupid mistake

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-12 : 06:08:07

I don't know. Think of it as a half a day's lesson in "the importance of knowing what your data is like" instead. Then it's not half a day wasted

Seriously though -- have you considered implementing a NOT NULL constraint on the columns and changing the way that the data is imported. If the column can't contain a null and there is no logical reason to have one then.......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 07:58:52

quote:
Originally posted by Transact Charlie

I don't know. Think of it as a half a day's lesson in "the importance of knowing what your data is like" instead. Then it's not half a day wasted

Seriously though -- have you considered implementing a NOT NULL constraint on the columns and changing the way that the data is imported. If the column can't contain a null and there is no logical reason to have one then.......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TC the OP lost day and half & not half a day.

PBUH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-12 : 12:32:56

>> I assumed that empty fields [sic: columns are not fields] have value NULL as in other my tables, without checking it out, but data in this one were imported from Access, and sometimes they have 0's for empty values instead. <<

There is an empty string in SQL, but not an empty value. Since you data looks like money amounts, shouldn't they be declared like this?

CREATE TABLE LeasePayments
(..
maintenance_cost DECIMAL (12,5) DEFAULT 0.00000 NOT NULL,
..);

Five decimals to keep the Euro and GAAP people happy and a default to zero.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources