Analysis :: ISNULL Function Is MDX Script
Oct 12, 2015
I have an MDX query
WITH MEMBER [Measures].[Parm1] AS
[Member].[Member Deceased Year].currentmember.member_caption MEMBER [Measures].[ResultValue]
AS ([Month].[Calendar].[Month].&[20150531], [Measures].[Member Count])
SELECT ({[Measures].[Parm1],[Measures].[ResultValue]}) ON COLUMNS,
ORDER([Member].[Member Deceased Year].[Member Deceased Year], [Measures].[Parm1], DESC) ON ROWS
FROM [TXERSDW]
Now from the Above query I am resulting with NULL as Parm1 values, I need to replace with a value of 12-31-9999 in the Parm1 if there are NULLS.
View 6 Replies
ADVERTISEMENT
Apr 18, 2008
Hey, I'm taking an intro SQL Server class, and I have a pretty simple homework assignment. We were provided with a DB and asked to write several SELECT statements. However, I'm stuck up one of the questions. Here is the question: 12.Create a SELECT statement that displays all employees and their Qualifications. Display that individuals with no Qualifications as having ‘NoQual’. Hint: Use a function to determine this ‘empty’ field using ISNULL.
Here is what I have:
SELECT
FNAME + ' ' + LNAME AS 'Employee Name', ISNULL(QUALID, 'NoQual') AS 'Qualifications'
FROM
EMPLOYEE, QUALIFICATION
WHERE
EMPLOYEE.QUALID = QUALIFICATION.QUALID;
However, I do not get any results that have a NULL value in the QUALID column. Here is the code for the DB:
CREATE TABLE emplevel
(LevelNoint,
LowSalaryint,
HighSalaryint,
CONSTRAINT emplevel_levelno_pk PRIMARY KEY (LevelNo));
GO
CREATE TABLE position
(PositionIdint,
PosDescVARCHAR (10),
CONSTRAINT position_positionid_pk PRIMARY KEY (PositionId));
GO
CREATE TABLE qualification
(QualIdint,
QualDescVARCHAR (11),
CONSTRAINT qualification_qualid_pk PRIMARY KEY (QualId)
);
GO
CREATE TABLE dept
(DeptIdint,
DeptNameVARCHAR (12) ,
LocationVARCHAR (15),
EmployeeIdint,
CONSTRAINT dept_deptid_pk PRIMARY KEY (DeptId)
);
GO
CREATE TABLE employee
(EmployeeId int,
LnameVARCHAR (15) CONSTRAINT employee_lname_nn NOT NULL,
Fname VARCHAR (15) CONSTRAINT employee_fname_nn NOT NULL,
PositionId int,
Supervisorint,
HireDate DATETIME,
Salaryint,
Commissionint,
DeptIdint,
QualIdint,
CONSTRAINT employee_employeeid_pk
PRIMARY KEY (EmployeeId)
);
GO
CREATE TABLE dependent
(EmployeeId int,
DependentIdint,
DepDOBDATETIME,
RelationVARCHAR (8),
CONSTRAINT dependent_empiddepid_pk PRIMARY KEY (EmployeeId, DependentId)
);
GO
INSERT INTO position VALUES (1, 'President');
INSERT INTO position VALUES (2, 'Manager');
INSERT INTO position VALUES (3, 'Programmer');
INSERT INTO position VALUES (4, 'Accountant');
INSERT INTO position VALUES (5, 'Salesman');
INSERT INTO emplevel VALUES (1, 1, 25000);
INSERT INTO emplevel VALUES (2, 25001, 50000);
INSERT INTO emplevel VALUES (3, 50001, 100000);
INSERT INTO emplevel VALUES (4, 100001, 500000);
INSERT INTO qualification VALUES (1, 'Doctorate');
INSERT INTO qualification VALUES (2, 'Masters');
INSERT INTO qualification VALUES (3, 'Bachelors');
INSERT INTO qualification VALUES (4, 'Associates');
INSERT INTO qualification VALUES (5, 'High School');
INSERT INTO dept VALUES (10, 'Finance', 'Charlotte', 123);
INSERT INTO dept VALUES (20, 'InfoSys', 'New York', 543);
INSERT INTO dept VALUES (30, 'Sales', 'Woodbridge', 135);
INSERT INTO dept VALUES (40, 'Marketing', 'Los Angeles', 246);
INSERT INTO employee VALUES (111, 'Smith', 'John', 1, NULL,'04/15/1960', 265000, 35000, 10, 1);
INSERT INTO employee VALUES (246, 'Houston', 'Larry', 2, 111,'05/19/1967', 150000, 10000, 40, 2);
INSERT INTO employee VALUES (123, 'Roberts', 'Sandi', 2, 111,'12/02/1991',75000, NULL, 10, 2);
INSERT INTO employee VALUES (433, 'McCall', 'Alex', 3, 543,'05/10/1997',66500, NULL, 20, 4);
INSERT INTO employee VALUES (543, 'Dev', 'Derek', 2, 111,'03/15/1995',80000, 20000, 20, 1);
INSERT INTO employee VALUES (200, 'Shaw', 'Jinku', 5, 135,'01/03/00',24500, 3000, 30, NULL);
INSERT INTO employee VALUES (135, 'Garner', 'Stanley', 2, 111,'02/29/1996',45000, 5000, 30, 5);
INSERT INTO employee VALUES (222, 'Chen', 'Sunny', 4, 123,'08/15/1999',35000, NULL, 10, 3);
INSERT INTO dependent VALUES (543, 1,'09/28/1958','Spouse');
INSERT INTO dependent VALUES (543, 2,'10/14/1988','Son');
INSERT INTO dependent VALUES (200, 1,'06/10/1976','Spouse');
INSERT INTO dependent VALUES (222, 1,'02/04/1975','Spouse');
INSERT INTO dependent VALUES (222, 2,'08/23/1997','Son');
INSERT INTO dependent VALUES (222, 3,'07/10/1999','Daughter');
INSERT INTO dependent VALUES (111, 1,'12/12/1945','Spouse');
ALTER TABLE dept
ADD CONSTRAINT dept_employeeid_fk FOREIGN KEY(EmployeeId)
REFERENCES employee(EmployeeId);
GO
--ALTER TABLE employee
--ADD CONSTRAINT employee_supervisor_fk FOREIGN KEY(Supervisor)
--REFERENCES employee(EmployeeId);
ALTER TABLE employee ADD CONSTRAINT employee_positionid_fk FOREIGN KEY (PositionId)
REFERENCES position (PositionId);
GO
ALTER TABLE employee ADD CONSTRAINT employee_deptid_fk FOREIGN KEY (DeptId)
REFERENCES dept (DeptId);
GO
ALTER TABLE employee ADD CONSTRAINT employee_qualid_fk FOREIGN KEY (QualId)
REFERENCES qualification (QualId);
GO
ALTER TABLE dependent ADD CONSTRAINT dependent_employeeid_fk FOREIGN KEY (EmployeeId)
REFERENCES employee (EmployeeId);
GO
View 3 Replies
View Related
Dec 29, 2006
okay, using isnull function we could replace null value..
but i want to do opposite, i want to replace if it's NOT null..
i tried notisnull also cannot..
Note : this is for select statement
SELECT isnull(d.ClientID,'-') FROM blabla
How to replace something if it's not null
SELECT isNOTnull(d.ClientID, '-')
View 1 Replies
View Related
Jun 18, 2007
SELECT JS_ID = ISNULL(ID.JS_ID,'-')
FROM
dbo.FM_INVOICE I,
dbo.FM_INVOICE_DETAILS ID
WHERE
I.INVOICE_ID = ID.INVOICE_ID
AND WO_ID = '-'--ISNULL(@GetLatest, '-')
is there any mistake i made? because it still return NULL when no data is found. how do i make it return '-' if a null is found
View 3 Replies
View Related
Oct 6, 2005
I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.
View 2 Replies
View Related
Aug 27, 2007
What is wrong with this code. I can not get ISNULL function to work
IF ISNULL([JRL_Req].[MDN])
UPDATE JRL_Req
SET [JRL_Req].[Product Code] = [OrderItem].[ProductCode]
FROM JRL_Req
INNER JOIN [order]
ON [JRL_Req].[Order ID]= [order].[OrderId]
INNER JOIN [OrderItem]
ON [order].[OrderId] = [OrderItem].[OrderId]
ELSE
UPDATE JRL_Req
SET [JRL_Req].[Product Code] = [OrderItem].[ProductCode]
FROM JRL_Req
INNER JOIN [order]
ON [JRL_Req].[Order ID]= [order].[OrderId]
INNER JOIN [OrderItem]
ON [order].[OrderId] = [OrderItem].[OrderId] AND [OrderItem].[MDN] = [JRL_Req].[MDN]
END IF
View 3 Replies
View Related
Mar 20, 2008
Hi,
I created a new column in Derived column editor wich depends on two columns that I'm evaluation.
This is my function for the new column:
(COLUMN1 == "Hello") || ISNULL(COLUMN1) ? COLUMN2 : COLUMN3
The problem is that when a NULL data comes for COLUMN1, it doesn't insert the value of COLUMN2 as the formula estipulates. It looks like is not understanding de ISNULL function.
Any suggestion?
Thanks in advance.
View 4 Replies
View Related
May 21, 2004
i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks.
Thank you,
Thomas
insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms)
SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#,
dbo.tblCustomersIOA.Active,
iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) ,
dbo.tblCustomersIOA.CreditLimit,
FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN
dbo.tblCustomersIOA LEFT OUTER JOIN
WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);
View 1 Replies
View Related
Jul 20, 2005
Dear GroupJust wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL?SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')Thanks very much for your expertise and efforts!Best Regards,Martin
View 6 Replies
View Related
Jul 31, 2007
Hello all,
I have a problem that some of the data I reciave from the DB is null.
I would like to replace it with '0'.
I used the function IsNull on the SQL statment.
The problem is that I recaived a false/true values..
Is there an option to get the real data?
this is the SQL:
SELECT OrderDetails.itemID, OrderDetails.itemName, OrderDetails.quantityToPick, OrderDetails.quantityPicked, OrderDetails.colorDescription,
OrderDetails.colorDetails, OrderDetails.bases, OrderDetails.diameter, OrderDetails.axis, OrderDetails.referenceNum, OrderDetails.remarks,
Isnull(Stock.stockQuantity, 0) AS stockQuantity, OrderDetails.quantityOrdered, OrderDetails.status
FROM OrderDetails LEFT OUTER JOIN
Stock ON OrderDetails.itemID = Stock.itemID
WHERE (OrderDetails.storeID = @storeIDTemp)
Any ideas?
View 1 Replies
View Related
Aug 10, 2015
I'm attempting to use the isnull function to convert null values in a column to a blank. It works on the alpha field but not the numeric and I was wondering what can be done for numeric field conversion.
View 6 Replies
View Related
Aug 3, 2010
I am looking a way to use a tail funcion to achieve something like this
If you see the picture i want obtain in one row the total amount of the month over a selected range. If I select months between 1 and 12, i want the values only for the month 12, if a select months between 1 and 10 I want only de value for October and so on.
I tried this simple mdx query:
Select
Tail([Id Fecha Orden Pago].[Mes].allmembers,1) on
Rows
,
{[Measures].[Total Gastos Orden Pago]} on
Columns
From
[CUB_DGM_TestEnamiDW] WHERE
[DIM Programa Fomento].[Nombre Programa Fomento].&[RECONOCIMIENTO DE RESERVAS]
View 7 Replies
View Related
Jul 22, 2015
I am here with a doubt in MDX. I want to calculate the Binomial Distribution for one of the Measure in my cube. The requirement is to create a new calculated measure in the cube which calculates the binomial distribution for an inbuilt measure.I am using SSAS 2012 Standard Edition.
View 6 Replies
View Related
May 18, 2015
I need other function act like subset function using mdxquery.
In my mdxqueries,contain subset,order functions are available.
subset and order is getting poor query performance.
View 8 Replies
View Related
May 27, 2015
My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)
View 3 Replies
View Related
Aug 23, 2015
I’m trying to build a calculated member (see below script) using “Except” function but I get an error result:
#Error The function expects a string or numeric expression for the argument. A tuple set expression was used.
My idea is to take a measure and exclude 2 members from the dimension.
I tried using “Aggregate” but got the error:
#Error Query (3, 1) Aggregate functions cannot be used on calculated members in the Measures dimension.
Please note that my measure is ACD that is already calculated average in olap and I can’t use AVG function instead of Aggregate. What can I do?
With
member [Measures].[AppOrig All Roaming]
as
(
Except(
[Source IP Location].[Country Name].[All].children,{
[Source IP Location].[Country Name].&[Colombia]
[Code] ....
View 6 Replies
View Related
May 14, 2015
I am using following Query to get data from Cube for some dates and Quarters at single time, but I am getting an error "Two sets Specified in the UNION function have different dimensional".
WITH
MEMBER [Measures].[Data Value1]
AS [Measures].[Average Value]
SELECT
{UNION(
([DimPeriodType].[Period Type Key].&[1],[DimDate].[Date Key].&[20150428],[DimEnergyCalculated Ratio].[Calc Ratio Key].&[1],[Measures].[Data
[code]....
View 3 Replies
View Related
Mar 29, 2007
There's a change in SP2 that I'm very happy about, but it took me by surprise since I haven't seen it documented or discussed:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/1/Default.aspx
Can somebody on the SSRS product team double check me that I've written that up right? That seems like a pretty important change and I can't see it mentioned in BOL anywhere.
View 9 Replies
View Related
Jul 8, 2015
I'm working with the statistical functions Stdev and Median with calculated members. The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself). Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value]) returns answers in the thousands when it should be more like 2.5
When used with a query, there would only be a single date member and a specific parameter member. The total number of fact records is between 200 and 500 with values that range between 0 and 150. This is the version that gives me answers that resemble the total sum of the [Measures].[Value].
If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])
View 4 Replies
View Related
May 22, 2015
I am having trouble limiting a sum function to only evaluate the data in the slicer. I need a sum of data calculated per employee and when there is a slicer of one employee my measure still evaluates all employee. For example:
WITH
MEMBER [Measures].[TotalEmpHours] AS
SUM([Employee].[Employee].members, [Measures].[Actual Ovt Hours])
select
{
[Measures].[TotalEmpHours],
[Code] ....
The above reports the overtime for one employee in [Measures].[Actual Ovt Hours], I expected [Measures].
[TotalEmpHours] to be the same as there is only one employee in the slicer select. The real measure is the minimum of two values for each employee.
This is a simplified example but I think it should work.Is there another construct to iterate the employee set? I have no control over the query as it is from a pivot grid. If the filter (employee) is leftmost in the row area, then it works because the employee ends up in the row part of the query.
View 7 Replies
View Related
Apr 20, 2015
I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :
SELECT
non empty
[Measures].[TRANSACTIONS Count] on 0,
non empty ([TRANSACTIONS].[Days].[Days],
[TRANSACTIONS].[Transaction Month].[Transaction Month]) on 1 from [cube]
I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :
WITH MEMBER
[Measures].[Cumm Account Count]
AS
(
AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count])
)
SELECT
non empty [Measures].[Cumm Account Count] on 0,
non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from [cube];
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function accepts members only...
View 4 Replies
View Related
Aug 28, 2015
I am new one in MDX. Our PeriodsToDate function does not return any value. We have set type property of our Date Dimension as time.Actually CURRENT MEMBER does not return a valid value. So our PeriodsToDate function fail.
With MEMBER [Measures].[YTD Actual]
AS
Aggregate
(
PeriodsToDate
(
[DimDate].[CalendarHierarchyDateLevel].[Calendar Year]
,[DimDate].[CalendarHierarchyDateLevel].CURRENTMEMBER
)
[code]....
View 2 Replies
View Related
May 25, 2015
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
View 3 Replies
View Related
Apr 28, 2006
Hi, all here,
I am just wondering about the Discretization function available on analysis service server (which can not actually discretize data into user-defined expressions) . Isnt it redundant ? I mean since users can discretize their data more meaningfully based on their own expressions in database engine of SQL Server 2005.
Thanks a lot.
View 1 Replies
View Related
Jun 7, 2007
I am relatively new to SQL-Server. I was working in a company that used SS2005. Now I am with a company and client where SS2K is my only option. I know SS2005 had Analysis Services which allowed building hypercubes but I never got the chance to learn about it or use it. I am not that familiar with what SS2K has.
I have no access to any OLAP tools. I have MS-Access and MS-Excel. My SQL-Server database has to crunch a table with thousands of rows.
Having worked with MS-Access and MS-Excel for a long time, I know how powerful creating a pivot table is. I heard that SQL2005 had a T-SQL PIVOT function but never had the opportunity to use it.
Know I have the need to do this in SQL2000.
I have a lot of data so the method shown in BOL for cross-tab won't help me. The client changes the "column" and "header" categories on a regular basis, so I need something that will allow me to NOT hard code them but choose them dynamically like one would do in a hypercube.
I have more than 5 categories for "row headers". I have currency for the data to aggregate. I have "pay periods" (26 of them) for my "column" headers, but not all 26 will be present until Dec 31, so that part has to be dynamic.
Question:
Is there any feature in the standard S2K that allows one to create anything like a pivot table without massive coding (T-SQL function or technique or something like a hypercube ability)?
Can you point me to something on the web that would explain how to get started?
Many thanks ahead of time from a newbie!
View 1 Replies
View Related
Aug 22, 2000
I am trying to delete a row of data that is all nulls. Help in BOL was a little help but it is not working.
View 2 Replies
View Related
Apr 30, 2008
I create a stored procedure like such:
"create procedure tmptable_query (@lname varchar(20)) as select * from #temp_table where lname like ISNULL(@lname+'%',lname)"
Unfortunately, it only returns exact matches, as if it were written as "where lname = ISNULL(@lname, lname)"
However, if I query from a static table the code works correctly.Any ideas on what could be wrong?
Thank you.
View 3 Replies
View Related
Feb 28, 2008
for the query i created i need zeros where ever the filed is blank. i have used count(acc) for selecting the count . can any one help me out with sample query. Thanks in advance
View 5 Replies
View Related
Jul 20, 2005
I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTOinto the import table I want to create a delta from it (i.e. leave onlythe changed items). I have a view (simplified)SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_RevFROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONoNow to get all rows where old_rev = new_rev I also want all rows whereboth are null, is the best method to put ISNULL() in the view or toselect from the view using ISNULL in the criteria, e.g.select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)or in the viewCREATE VIEW view1 asSELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev ASOld_Rev,0)FROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;select * from view1 where Old_Rev=new_rev;
View 2 Replies
View Related
Apr 24, 2006
does sql server mobile 2005 support the isnull function? I'm getting an error when I try to use it and I don't know if it is becuase of using the isnull function or not, but when I run the same query on Sql Server 2005 it works fine.
View 5 Replies
View Related
Apr 29, 2008
I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else?
View 4 Replies
View Related
May 1, 2008
I've got this query and it use to work or at least I thought it did. Could someone please help me with it.
Thank you
SELECT CID, CompletionDate, MarkedExport, CustomerName, EditUser, RouteID, WorkOrder FROM RouteCustomer WHERE (CompletionDate IS NOT NULL) AND (ExportDate IS NULL) AND (RouteID LIKE '%' + ISNULL(RouteID,@RouteID) + '%') AND (EditUser IS NULL OR EditUser = '' OR EditUser = @EmployeeID) AND (MONTH(CompletionDate) = ISNULL(MONTH(CompletionDate),@Month))
The problem comes with in the WHERE clause. What I wanted it to do is if the user did want to use a RouteID critera then the user would speified one else it wouldn't, and it was my belief that the ISNULL feature in SQL was the answer for that. same for the Month. I believe the EditUser is fine the way it is written.
thanks to anyone that can help me with this.
Rex
View 9 Replies
View Related
Jun 7, 2005
I have to display string "not assigined" when a datefield is null in a table.
I am using like ISNULL(datefiled, "not assigned"), but I am getting following error
Syntax error converting character string to smalldatetime data type.
Is there any way, I can acheive desired result.
Please help
View 1 Replies
View Related
