sachiek
asked on
Small query -
Hi,
I got a query which is been wrote by someother team mate.
SELECT AssetEntityID,
MaintenanceRecordID,
PropertyEntityID,
ServiceType,
InternalRefNo,
LocationID,
LocationName,
FaultName=null,
ScheduledDate,
JobStart,
JobEnd,
EquipmentStart,
EquipmentStop,
ServiceResultID
FROM vwMaintenanceHistoryCorrec tiveWR
Now there are some 10 fields which should be added to this above select statment.
These fields are
@pServiceType nvarchar(32)
@pInternalRefNo nvarchar(32)
@pFaultName nvarchar(32)
@pCountryID int,
@pRegionID int,
@pSiteID int,
It may be containing values or null.
If those are null, then it should not be in where clause , if having values then it should be in class.
Actually this @pCountryID,@pRegionID,@pS iteID are one group. If siteID is null then it should look for region and country. If region is null, then it should look for country. If all the three are null then none should be taken.
If this procedure is geting complicated I can increase the points. :)
Sachi
I got a query which is been wrote by someother team mate.
SELECT AssetEntityID,
MaintenanceRecordID,
PropertyEntityID,
ServiceType,
InternalRefNo,
LocationID,
LocationName,
FaultName=null,
ScheduledDate,
JobStart,
JobEnd,
EquipmentStart,
EquipmentStop,
ServiceResultID
FROM vwMaintenanceHistoryCorrec
Now there are some 10 fields which should be added to this above select statment.
These fields are
@pServiceType nvarchar(32)
@pInternalRefNo nvarchar(32)
@pFaultName nvarchar(32)
@pCountryID int,
@pRegionID int,
@pSiteID int,
It may be containing values or null.
If those are null, then it should not be in where clause , if having values then it should be in class.
Actually this @pCountryID,@pRegionID,@pS
If this procedure is geting complicated I can increase the points. :)
Sachi
COALESCE
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.
Return Types
Returns the same value as expression.
Remarks
If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,...n) is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
Examples
In this example, the wages table is shown to include three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the nonnull value found in hourly_wage, salary, and commission.
SET NOCOUNT ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM wages
GO
Here is the result set:
Total Salary
------------
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000
(12 row(s) affected)
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.
Return Types
Returns the same value as expression.
Remarks
If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,...n)
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
Examples
In this example, the wages table is shown to include three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the nonnull value found in hourly_wage, salary, and commission.
SET NOCOUNT ON
GO
USE master
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'wages')
DROP TABLE wages
GO
CREATE TABLE wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
)
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
GO
SET NOCOUNT OFF
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM wages
GO
Here is the result set:
Total Salary
------------
20800.0000
41600.0000
62400.0000
83200.0000
10000.0000
20000.0000
30000.0000
40000.0000
45000.0000
50000.0000
120000.0000
56000.0000
(12 row(s) affected)
ASKER
I that example already.
But not sure how it is going to help me. Any possible code with my above query?
Sachi
But not sure how it is going to help me. Any possible code with my above query?
Sachi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ya. That is correct way. I figured it our yesterday. Anyway thanks. :)
Sachi
Sachi
which will solve ur issue