Help With Select / Sum / Nulls

Nov 11, 2006

i have the following select statement, the balance due column allows nulls and I am trying to eliminate all rows where total or credit is null. Is the only way to run the two select statements again in the where clause?

select
c.CustomerID ConsultantID,
c.CustomerFullName CustomerFullName,
sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID) as Total,
sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID and TransactionTypeID = 'Credit Memo') as Credit

from
Customer c

order by ConsultantID

----------------

The following gives me what I want but I am sure there must be a better way, any pointers would be appreciated:)

select
c.CustomerID ConsultantID,
c.CustomerFullName CustomerFullName,
ISNULL((select sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID), 0) - as Total,
ISNULL((select sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID and TransactionTypeID = 'Credit Memo'), 0) as Credit

from
Customer c
where (ISNULL((select sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID), 0) <> 0
or
ISNULL((select sum(i.BalanceDue) from Invoice i where i.CustomerID = c.CustomerID and TransactionTypeID = 'Credit Memo'), 0) <> 0)

order by ConsultantID
GO

View 2 Replies


ADVERTISEMENT

SELECT Statement Returning Nulls Unexpectedtly

Jun 14, 2008

All- Please assist: In this SELECT statement:
 1 SELECT person_id, (last + ', ' + first + ' on ' + CASE f.address_1 WHEN NULL THEN 'none' ELSE f.address_1 END) as last_first_address
2 FROM person
3
4 LEFT JOIN family f
5 ON person.family_id = f.family_id

I'm finding that the compound column last_first_address returns <null> when f.address_1 is null, even though it seems like it should return the string "none" per my CASE statement. If f.address_1 is not NULL, then it the statement works as expected. Please advise as to how I can fix the statement so that "none" is returned as the final string in last_fist_address if f.address_1 is null.
Note: I tried removing the parens. No effect on result. 
Thanks!
-Kurt
 

View 2 Replies View Related

Column Allows Nulls I Want To Change No Nulls Allowed

May 16, 2006

When i do a select on my emplee table for rows with null idCompany i dont get any records

I then try to modify the table to not allow a null idCompany and i get this error message:

'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This sux

View 4 Replies View Related

Testing Permutations Of Nulls And Not Nulls

Feb 17, 2008

is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?

View 4 Replies View Related

Do Not Keep NULLS Using SSIS Bulk Insert Task - Insert Empty Strings Instead Of NULLS

May 15, 2008

I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.

The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.

The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.

So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).

I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.

Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?

From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.

View 2 Replies View Related

NULLs

Sep 16, 2004

Do NULLs take up space on the Database? I mean is room allocated for them?

View 9 Replies View Related

Nulls

Apr 5, 2004

Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?

Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.

View 12 Replies View Related

Nulls

Apr 12, 2008

I have a page with 100 numeric fields. SQL insert crashes lest there is a number in every field. Any way around this?

View 1 Replies View Related

Two Nulls

Feb 9, 2007

Hi

i have a table t1 and wish to have to fk's now i want to be able to set it up so that only one of them can be null example:

t1
id___________fk1________fk2

1____________6__________NULL
2____________NULL_______3
3____________7__________NULL
4____________NULL_______NULL <<<<<<< This should not be allowed

thanks
james

View 2 Replies View Related

Nulls

May 21, 2007

Hi i am new to SQL, and i have a data base thats got NULLS in it
is there a query i can run, which can take the NULL value out ?
anything will be greatly appreciated.

View 14 Replies View Related

NULLS And ''

Feb 6, 2007

Hi,I don't have any specific details as I do not really understand whythis is happening but we have a customer database that we have beenusing and queries on text fields have begun returning empty stringvalues instead of NULL.So for example:SELECT TAB1.DESCRIPTIONFROM TABLE1 TAB1WHERE TAB1.DESCRIPTION IS NOT NULLORDER BY 1may have returned 50 records in the past (purely an example). It isnow returning an additional record first that appears just to be ''.Now I am not too sure where to begin looking into this. ODBCconnections, collation settings? I just am not sure where to begin.The column will not have had '' inserted into it; therefore it shouldbe NULL.I have posted this mainly so I can see if anybody else has seen thissort of behaviour - I cannot see why this has suddenly happened.The collation setting on our server is different to that of thecustomer DB, but this shouldn't make a difference should it?If anybody has any ideas then I can post some more information.Thanks,Paul

View 12 Replies View Related

Nulls

Mar 20, 2007

create table t1(c1 int, c2 varchar(10))insert t1 values(1,'Hello')insert t1 values(2,'')insert t1 values(3,NULL)select *from t1c1c21Hello23NULLselect *from t1where c2 = ' 'c1c22select *from t1where ltrim(rtrim(c2)) is nullc1c23NULLThe last query should have result as following. However sql server2000 does no list row c1 = 2.c1c223NULL

View 6 Replies View Related

Are Nulls Bad

Jul 20, 2005

HiI am probably going to regret asking this because I'm sure you are going totell me my design is bad 8-) ah well we all have to learn....anywayI often use Nulls as a marker to see if certain tasks have been completed.A typical example would be a column say invoice_valuewhen new work is entered and a new record is appended, I leave theinvoice_value column as NULL, so if I want a View for uninvoiced work Icheck for the invoice_value being equal to NULL, and alternatively if I wantinvoiced work, I check for not null. I did it this way to save putting inanother column that needed to be set to TRUE or FALSE. (I do similar thingselsewhere aswell)so far everything seems to work OK but reading some old stuff on Google Iget the feeling that NULLS should be left alone, have I done wrong?many thanksAndy

View 3 Replies View Related

NULLS

Nov 26, 2007

Hello all

I have a colunm - Datatype : nvarchar - Contains: Numeric, characters, strings, date and NULLS

I am getting a the value of each row of this colunm in a variable say @var1

Now I am trying to do some functionalities depending on this variable.

The requirement is if this variable is not null I need to do some stuff.

So I write simple query:

IF (@var1 IS NOT NULL)
BEGIN
......
END

when i am doing this I get an Error:


Error converting data type nvarchar to numeric.

realizing this I change the condition to
IF ( LEN (@Var1) > 1)

then
IF (LEN (@Var1) <> 0)

but no use. I get the same error.

I even use cast
I get the error

Conversion failed when converting the nvarchar value 'null' to data type int.


IF(CAST (@OldValue AS INT)IS NOT NULL)
OR

IF(LEN (CAST (@OldValue AS INT)) <> 0)


Can somebody help in guiding the best practice to do this?





View 8 Replies View Related

Column Does Not Allow Nulls

Nov 4, 2007

 Hi,This should be straight forward, but I've searched high and low on the net.I have a FORM which allows me to INSERT data, SQL Server 2005 backend. I populate the all the mandatory fields. But when I click on the Insert button, it won't let me save and says:Column 'PERSONAL_ID' does not allow nullsI'm
using tableadapters, business logic layers etc. and pausing clearly
shows that the values from the form are being passed to the procedure
that I have created "AddNewRecord". And PERSONAL_ID is definitely not
NULL! It fails on Line 971: Me.Rows.Add(row)PERSONAL_ID
is a primary key which I generate. The pause also shows that for
PERSONAL_ID it says " {"Conversion from type 'DBNull' to type 'String'
is not valid."}Function AddNewRecord looks like this:<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function AddNewRecord(ByVal PERSONAL_ID As String, ByVal SURNAME
As String, ByVal CHRISTIAN_NAME As String, ByVal SEX As String, _
ByVal FAMILY_POSITION As String, ByVal FAMILY_ID As String, ByVal
ADDRESS_1 As String, ByVal ADDRESS_2 As String, _ ByVal ADDRESS_3
As String, ByVal ADDRESS_4 As String, ByVal ADDRESS_5 As String, ByVal
ADDRESS_6 As String, ByVal POSTCODE As String, ByVal COUNTRY As String,
ByVal ORG_ID As String) As Boolean ' create a new details row instance Dim details As New smDetails.smTbl_DetailsIDDataTable Dim detail As smDetails.smTbl_DetailsIDRow = details.NewsmTbl_DetailsIDRow details.AddsmTbl_DetailsIDRow(detail)...my formview code is this: <InsertItemTemplate> PERSONAL_ID: <asp:TextBox ID="PERSONAL_IDTextBox" runat="server" Text='<%# Bind("PERSONAL_ID") %>' AutoPostBack="True" /> <br /> SURNAME: <asp:TextBox ID="SURNAMETextBox" runat="server" Text='<%# Bind("SURNAME") %>' AutoPostBack="True" /> <br /> CHRISTIAN_NAME: <asp:TextBox ID="CHRISTIAN_NAMETextBox" runat="server" Text='<%# Bind("CHRISTIAN_NAME") %>' /> <br /> SEX: <asp:TextBox ID="SEXTextBox" runat="server" Text='<%# Bind("SEX") %>' /> <br /> FAMILY_POSITION: <asp:TextBox ID="FAMILY_POSITIONTextBox" runat="server" Text='<%# Bind("FAMILY_POSITION") %>' /> <br /> FAMILY_ID: <asp:TextBox ID="FAMILY_IDTextBox" runat="server" Text='<%# Bind("FAMILY_ID") %>' /> <br /> ADDRESS_1: <asp:TextBox ID="ADDRESS_1TextBox" runat="server" Text='<%# Bind("ADDRESS_1") %>' /> <br /> ADDRESS_2: <asp:TextBox ID="ADDRESS_2TextBox" runat="server" Text='<%# Bind("ADDRESS_2") %>' /> <br /> ADDRESS_3: <asp:TextBox ID="ADDRESS_3TextBox" runat="server" Text='<%# Bind("ADDRESS_3") %>' /> <br /> ADDRESS_4: <asp:TextBox ID="ADDRESS_4TextBox" runat="server" Text='<%# Bind("ADDRESS_4") %>' /> <br /> ADDRESS_5: <asp:TextBox ID="ADDRESS_5TextBox" runat="server" Text='<%# Bind("ADDRESS_5") %>' /> <br /> ADDRESS_6: <asp:TextBox ID="ADDRESS_6TextBox" runat="server" Text='<%# Bind("ADDRESS_6") %>' /> <br /> POSTCODE: <asp:TextBox ID="POSTCODETextBox" runat="server" Text='<%# Bind("POSTCODE") %>' /> <br /> COUNTRY: <asp:TextBox ID="COUNTRYTextBox" runat="server" Text='<%# Bind("COUNTRY") %>' /> <br /> ORG_ID: <asp:TextBox ID="ORG_IDTextBox" runat="server" Text='<%# Bind("ORG_ID") %>' /> <br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" /> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" /> </InsertItemTemplate> Any pointers in the right direction would be appreciated. Thanks in advanceTushar

View 3 Replies View Related

Need To Get NULLs Into The Action..

Mar 11, 2008

I have the following query:
 SELECT tblArticleCategory.ACategoryID, IsNull(ParentCategory, '') + IsNull( ' > ' + ACategoryName, '') AS Category from tblArticlecategory
RIGHT OUTER JOIN (SELECT ACategoryName as ParentCategory, ACategoryID from tblArticleCategory WHERE AParentID is null AND aActive=1) AS Parent
on parent.ACategoryID = tblArticleCategory.AParentID
WHERE tblArticleCategory.AActive=1ORDER BY Category
This produces an ordered list of the parent categories and subcategories from the table as long as there is a subcateogry directly below one of the parent categories. If there isn't one directly below the parent category, the parent category is dropped from the list. None of the parents with the NULLS back to back are in the list.

6 Arts & Entertainment > Dance2 Arts & Entertainment > Movies13 Computers > E-Learning4 Computers > Hardware14 Computers > Java16 Computers > Link Popularity17 Computers > Microsoft.net15 Computers > RSS5 Computers > Software8 Real Estate > Finance
How do I fix the query so that Automotive, Business, Cancer and Communications are included in the list?

View 9 Replies View Related

Allow Nulls Cannot Be Changed

Apr 9, 2003

I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.

'CONTACTS' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.

will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?

View 7 Replies View Related

How To Sort NULLs?

Jul 13, 2004

Hi All,

I have a table with 3 columns. Product, Location and Value. The data looks like this:

NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40

This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:


Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100

Thanks,

Shab

View 4 Replies View Related

Inserting Nulls In EM

Dec 21, 2005

Hi,

I've forgotten how to put a null value in a column of a table where a previous value existed before.

What I mean is if through EM, you look at the data (Open Table -> Return all Rows) and you see a value in a column that is nullable (for example, I want to change a column that has a 'ABC Company' to a NULL value).

How do you that - i've done it in the past ,but cannot remember

Thanks in advance

View 2 Replies View Related

Space That Nulls Take

Mar 10, 1999

We have compare two table with populated with the same data and with the same definition except, one table allows nulls
and the other table does not. All the fields are defined as integer. The table that allows nulls takes much more data space.
Why is that? I could not find an explaination in BOL.

View 1 Replies View Related

Dealing With Nulls

Mar 15, 2002

I have the following query in a stored procedure. If there are no rows in the history file, it returns a null. If there some setting or function that would have it return a zero if no rows are found? I use the variable to do arithmetic later on and a null messes everything up.

select@MarketTotal = sum(isnull(MarketValue,0))
fromhistory
whereEmpUID = @EmpUID and
Shares > 0

Thanks,
Ken Nicholson

View 2 Replies View Related

Inserting Nulls Into The Db

Mar 27, 2002

I'm using ADO.NET and have noticed that it is somewhat more difficult to send/receive nulls to/from the database. What are the benefits (if any exist) with storing a null value in the db as opposed to a default value?

In particular, do you save any space by storing a null opposed to a zero or 01/01/1900? Are the nulls faster for searching/comparing when querying the db?
Basically I'm just wondering if storing nulls is really the best practice and why, so that I know whether it's worth the effort in .NET to transform my variables into a null-happy form.

Any facts/opinions are appreciated,
Inge

View 1 Replies View Related

Default Value And NULLs

Oct 18, 2001

Documentation states that you can set the Default Value for a column which is to be used whenever a row with a null value is inserted into the table. However, it appears that the column ends up as NULL regardless of this setting when inserted field has a value of NULL.

We have a stored procedure that accepts parameters for each column on the table and initializes the incoming parameter to NULL. It then inserts the row into the table. Table definition allows NULLs in the columns, but has a Default Value of ''.

Are we missing a setting somewhere?

View 1 Replies View Related

How To Sort NULLS

Jul 13, 2004

Hi All,

I have a table with 3 columns. Product, Location and Value. The data looks like this:

NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40

This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:


Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100

Thanks,

Shab

View 2 Replies View Related

Sorting With NULLs

Dec 29, 2004

Hi,

I'm not sure why this is happening, but I'm trying to sort/order on this special field/column that I have created to allow precise ordering. However, for all of the values that are returned, if there are any NULLS, they are returned first/ahead of the values of what I want to be returned first. This field/column I created is called 'POW' (stands for Priority on Web). The values are just integers, 1-whatever, and we try to make sure that they're all unique. So for example, if we have a 1 in that field, then that row should be returned first, and if another row has 2, then it should be next. However, this is not working exactly - all of the NULL values are returned FIRST, then the proper values in the correct order. I want the NULLS to be last, because it may take some time for someone to go into each record, and assign it a unique value. My current SQL statement reads:

mySQL = "SELECT SignTypes.Id, SignTypes.SignType, SignTypes.SignDescription, SignTypes.FinalSignWidth, SignTypes.FinalSignHeight FROM SignTypes JOIN PropLocSigns ON SignTypes.Id = PropLocSigns.SignTypeID WHERE PropLocSigns.PropLocID = " & Session("PropID") & " AND (RestrictOnWeb = 0 OR RestrictOnWeb IS NULL) ORDER BY SignTypes.POW; SELECT @@ROWCOUNT"

So if someone knows of a way to put NULLS last, and still have my proper ordering, that would be great! I would think that there's some simple way of doing this, but not being an expert, I don't know. Thanks for the help!

View 3 Replies View Related

Are Nulls Slow?

Dec 12, 2005

I have several tables anywhere from 50,000 to 100,000 rows with about 100 columns in each that allow nulls. How bad will this affect performance (if any)? If it's significant, how can I replace nulls with zeros without doing it column by column? (100 columns will take too much time) thanks

View 1 Replies View Related

Why Not Showing The Nulls?

Jan 19, 2007

Hi,

I have two tbls joined via a varchar field.. which in tbl I have to use a CASE statement to format it correctly for the JOIN..

All works well.. however, if I add a WHERE clause to the statement, with the WHERE clause affecting only one of the JOINED tbls.. All the NULLS are not shown.

I should get results like:

Postcode Count

tf4 0
tf5 23
tf6 15
tf7 0
etc

Whereas, when I add the where clause, none of the "zeros" are shown, so I get:

tf5 23
tf6 15

Why is this?



Here's the SQL:



SELECT ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2)
WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0)
AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
dbo.UkPostcodes.Master_Account
FROM dbo.UkPostcodes LEFT OUTER JOIN
dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END
WHERE (dbo.Customers_Trade.TradeCustomerID IN
(SELECT DISTINCT TradeCustomerID
FROM dbo.Orders_Trade
WHERE (DATEDIFF(d, order_date, GETDATE()) < 60))) AND (dbo.Customers_Trade.is_activated <> 'No') OR
(dbo.Customers_Trade.BillingAccountID <> '') AND (dbo.Customers_Trade.BillingAccountID IN
(SELECT DISTINCT TradeCustomerID
FROM dbo.Orders_Trade
WHERE (DATEDIFF(d, order_date, GETDATE()) < 60) AND (is_activated <> 'No')))
GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account





does not show the nulls:



where as:



SELECT ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2)
WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0)
AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
dbo.UkPostcodes.Master_Account
FROM dbo.UkPostcodes LEFT OUTER JOIN
dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''))
WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3)
ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END
GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country,
dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account

View 3 Replies View Related

Those Darn Nulls

Jul 25, 2007

SELECT title_id,advance,
CASE advance
WHEN advance ISNULL THEN '0'
-- i tried is null also and keep getting error
ELSE advance
END as 'How much to I advance'
FROM titles

Gives me this error:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ISNULL'.

WHen i use just null i get null in my result set where i want 0

View 10 Replies View Related

Nulls And Lags In MDX

Mar 23, 2008

Friends ,



I have been struggling to find a solution for nulls and lags , please help with a solution



i need to get a average of 3 months , for which i have used lag function , but apparently, the values in 1st two months is null for which its not giving me the correct results.



While i am using this code , i get the following results, which are not correct



AVG(

[Tbl Date Key].[Hierarchy].CurrentMember.lag(2):[Tbl Date Key].[Hierarchy].CurrentMember,

[Measures].[Q1_Avg])



i get the following result which is not acceptable



Level 04 Level 05

Region CE
District CE1

Calendar Year Calendar Month Ke Date Q1_Avg try
2007 1 .00 0.00
2 .00 0.00
3 950.00 316.67
4 955.61 635.20
5 949.82 951.81
6 939.81 948.41
7 945.25 944.96
8 945.04 943.37
9 945.51 945.27
10 955.89 948.82
11 947.24 949.55
12 946.61 949.92
Total 947.47 947.47
Grand Total 947.47 947.47





The Actual Result should be





Calendar Year Calendar Month Ke Date Q1_Avg try
2007 1 .00 0.00
2 .00 0.00
3 950.00 950.00
4 955.61 952.81
5 949.82 951.81
6 939.81 948.41
7 945.25 944.96
8 945.04 943.37
9 945.51 945.27
10 955.89 948.81
11 947.24 949.55
12 946.61 949.91
Total 947.47 947.47
Grand Total 947.47 947.47




Please advise with can be done on this code

View 1 Replies View Related

NULLs And Empties

Apr 8, 2008

I've got a nice task of troubleshooting a report in word, data is taken from the database this way:ISNULL(bCountry + ', ', ' ') + ISNULL(bAddr_Region + ', ', ' ') + ISNULL(bAddr_okrug + ', ', ' ') + ISNULL(bAddr_City , ' ')when 'bAddr_Region' and 'bAddr_okrug' are blank, result looks like this:Country, , , Citythat's because the program writes empty values to the table, not NULLs.. :mad:how do i check whether value is empty in the statement above? :S

View 2 Replies View Related

Why Are Nulls Not Being Returned?

Apr 29, 2008

Select * from table where field <> 'This'


Why does that not return nulls? 'This' does not equal null. Why does sql server work this way?

View 2 Replies View Related

Sum Of Values Between Nulls

Jun 22, 2008

Hi Guys,

I have a resulset in this manner.

id----- amt
----------- -----------
1----- NULL
2----- 20
3----- 20
4----- 20
8----- NULL
9----- 30
10----- 120
11----- 110
21----- NULL
22----- 110

Furthermore I would like to sum the minimum amt & maximum amt between two Null values.So ideally the output should be

minid maxid amt
2----- 4----- 40
9----- 11----- 140
21---- NULL----- 110

Thanks for any help.

View 1 Replies View Related

Getting Nulls When Pivoting

Aug 29, 2013

I'm getting nulls on execution of the below query. I have tried both isnull and coalesce, but to no avail.

select *
from
(
select
a.BusinessUnitCode,
a.AdmitCCYYMM As Date, a.[Count of Admits]*1.0 / m.MemberCount * 12000 AdmitsPer1000

[Code] .....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved