Working With NULLS
Jul 27, 2006
I have a field that may be null which is valid, and I am finding
something I didnt expect when working with nulls.
SELECT NULL/4.0 will return NULL (which I expect), however, when I test
it with a case it does not:
SELECT NULL/4.0 AS 'TEST1', TEST2 = CASE NULL/4.0 WHEN NULL THEN
'HURAY' ELSE 'OH DARN' END
I can work around by testing for NULL first else CASE ..., but I'd like
to understand why the CASE does not test for null.
TIA
rob
SQL 2005 Enterprise x64, SP1
View 3 Replies
ADVERTISEMENT
Dec 4, 2006
I have an instead of trigger for update on particular table which adds3 or so columns and puts the total in a 4th No matter what the input is(as long as there is one NULL) the total is always NULL. More thanlikely is that only one of the fields has a value in it and the restnull but i don't want to do the MAX in case that is not true.a quick visual aid from input and desired outputInput DatacolA colB ColC tot5 NULL NULL NULLCurrent OutputcolA colB ColC tot5 NULL NULL NULLDesired OutputcolA colB ColC tot5 NULL NULL 5How do i Manipulate the TSQL command to work correctly?Update tbl_lossChecksSETclc_totalFees = Inserted.mny_LegalFees + Inserted.mny_AdjusterFees +Inserted.mny_Expense ,
View 4 Replies
View Related
Jul 31, 2006
I'm trying to use a DTS package to import data from an excel file. A few tables keep throwing errors about not being able to insert null values.
Is there any way to just skip a row when a column is null? I know there are certain columns that will never be null, so if they are null, I know that I just have a messed up row in Excel.
I could probably do it by implying an SQL query and having it ignore null rows, but I can't find anything useful on SQL syntax where Excel files are concerned.
View 5 Replies
View Related
Jul 20, 2005
Hi folks,I'm doing calculations based on data in a table, but the data has somezeros in the field I'm dividing by. I'm trying to write a script toreplace any field with 0 or null with 1, but it's not working. HEre'swhat I've got:Update A Set A.deptcode = A.deptcode,A.type = A.Type,A.Volume = (case A.VolumeWhen Null Then 1When 0 then 1Else A.VolumeEnd)From Data_Unsorted A Join Data_Unsorted B OnA.deptcode = B.deptcode and A.type = B.TypeMy table is data_unsorted and deptcode and type are my primary keysVolume is the item I want to put 1 if null or zero, and I'd thing theabove statement would work, but it doesn't. This table has 383 rows,and it says it updates 383 rows, but when I run the following query totest:select a.deptcode, a.type, a.volumefrom data_unsorted awhere a.AveMonthVolume = 0 or a.AveMonthVOlume is nullIt didn't work... still TONS of nulls and zero's. Is there a trick tothis???Thanks,Alex.
View 2 Replies
View Related
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
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
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
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Sep 16, 2004
Do NULLs take up space on the Database? I mean is room allocated for them?
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
