IsNull For Decimal Acting Up

T-SQL - Check For Decimal?? Like ISNULL, ISDATE, ISNUMERIC, Etc.
There is a MSSQL function that check the value. Like ISNULL(), ISDATE() and ISNUMERIC(). I don't see a function that check for decimal. If there isn't any then is there an user-defined function for it? I need to be able to validate the string value for decimal before it get assigned to a decimal datatype or T-SQL will run into an error.

I'm using MS-SQL 2000...

Thanks...

View Replies !   View Related

---

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
 
Example: AutomobileTypeId (PK, decimal(10,0), not null)

View Replies !   View Related

---

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal
Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.


Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric



Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View Replies !   View Related

---

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem
Hello!



I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View Replies !   View Related

---

DTS Package Acting Weird
I have built a DTS package in SQL Server 2000 to Delete all rows from a table and then import from another table. I have scheduled it run twice a day and everytime it runs, it does not delete anything but imports all the records from another table. So when its done, I have twice the number of records in my final table. Then I go back to the server and execute the package manually and it works perfect. It deletes all and then imports new. I have enabled package logging and it shows both the steps executed perfectly (even when it executes on scheduled time).
What can be the problem?
Thanks,
Bullpit

View Replies !   View Related

---

MSSQL Acting Strange
I have MSSQL2k SP3a on WIN2k SP4.moved a Date/log files to this server about a week ago from a SQL7serverand attached it to this new Sql2k server.everything works fine for about 24hrs and then it starts timing out !!all I have to so is restart the MSSQL service and works fine againtill the next day !there is only this one database on this server and nothing else isrunning on it.have anyone seen this before ? how could this be fixed !!thanksDon

View Replies !   View Related

---

Sql Server Acting Strange
MY DATABASE FOR SHOPPING CART IS ACTING STRANGE IT HAS AUTOMATICALLY DESTOYED ALL PRIMARY KEYS. AND HAS CREATED 4 DUPLICATE RECORDS FOR EACH RECORD SO AT THIS STAGE I HAVE AROUNG 15000 RECORDS IN EACH TABLE IT MEANS 15 TIMES 4 60000 RECORDS. CAN ANYONE UPDATEME WHY THIS HAS HAPPENED AND WHAT CAN BE DONE TO DELETE EXISTING DUPLICATES. I NEED FAST REPLY IN THIS MATTER AS THIS IS URGENT. IF POSSIBLE KINDLY WRITE QUERIES TRIGGERS FOR FUTURE PROBLEMS AS WELL AS THESE DAYS I AM SICK SO MY BRAIN AINT WORKING IN THIS MATTER. URGENT URGENT URGENT THANKS ALOT I HAVE PREVIOUSLY POSTED AND HAVE GOT A GOOD RESPONSE. AND HOPE A GOOD ONE AGAIN. ONE MORE THING IS THAT I HAVE A BACKUP HERE AT MY OFFICE SYSTEM IT ALSO HAS DONE THE SAMETHING. I MADE A NEW DATABASE AND TRYED TO IMPORT DATA WITH DISTINCT KEYWORD BUUT IT IS NOT COPYING IT AS WELL. URGENT RESPONSE REQUIRED IF ANYONE CAN DO.

View Replies !   View Related

---

Database Acting Strange
MY DATABASE FOR SHOPPING CART IS ACTING STRANGE IT HAS AUTOMATICALLY DESTOYED ALL PRIMARY KEYS. AND HAS CREATED 4 DUPLICATE RECORDS FOR EACH RECORD SO AT THIS STAGE I HAVE AROUNG 15000 RECORDS IN EACH TABLE IT MEANS 15 TIMES 4 60000 RECORDS. CAN ANYONE UPDATEME WHY THIS HAS HAPPENED AND WHAT CAN BE DONE TO DELETE EXISTING DUPLICATES. I NEED FAST REPLY IN THIS MATTER AS THIS IS URGENT. IF POSSIBLE KINDLY WRITE QUERIES TRIGGERS FOR FUTURE PROBLEMS AS WELL AS THESE DAYS I AM SICK SO MY BRAIN AINT WORKING IN THIS MATTER. URGENT URGENT URGENT THANKS ALOT I HAVE PREVIOUSLY POSTED AND HAVE GOT A GOOD RESPONSE. AND HOPE A GOOD ONE AGAIN. ONE MORE THING IS THAT I HAVE A BACKUP HERE AT MY OFFICE SYSTEM IT ALSO HAS DONE THE SAMETHING. I MADE A NEW DATABASE AND TRYED TO IMPORT DATA WITH DISTINCT KEYWORD BUUT IT IS NOT COPYING IT AS WELL. URGENT RESPONSE REQUIRED IF ANYONE CAN DO.

View Replies !   View Related

---

Please Help! Stored Proc Acting Odd
I am so confused. I hope someone can help! I wrote a large stored procedure that is meant to be called from a VB application. The store procedure collects data from several tables and buildsa new table of summary data. It is called with 4 parameters (date ranges and names for the new summary tables to create).

I tested the SP in SQL 7 while developing it, and never had any problems. But when I tried it in 6.5, it complained that I had too many subqueries. So I rewrote the procedure with less nestings, and then when I tried it I didn't get an error but it crashed with a DB Disconnected error.

I rewrote it again and then it finally worked (but only from Enterprise Manager); when I called from a VB application (using ADO), I get an error "protocol error in TDS stream." And looking in the SQL error log I see a whole lot of errors, none of which I understand, but the first mesage is "EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump.

I know that without knowing the contents of the stored procedure, there's no way anybody can tell me exactly what's going wrong, but I'm sure hoping that some people can give me some clues or directions to look. I'm desperate and have been working on this one project for too long already!

Thanks for any help or insight you can offer.

View Replies !   View Related

---

RetainConnection Acting Random
Happy new year all SSIS Experts,
 
I wonder if anyone can explain what I am doing wrong here:
 
I need to transport data between several tables using dataflow components. I need to wrap this in transactions to either succeed or fail completly, I do want to use my own BEGIN TRAN and COMMIT TRAN statements, not the transaction support built into SSIS.
 
As written in several BLOGs, I should set the "RetainConnection" property to "TRUE" to make sure each dataflow runs on the same connection as the BEGIN and COMMIT statements... and that seems to be random.
 
Sometimes they do run on the same connection, but I also logged (using SQL Profiler) package executions where the BEGIN TRAN was issued to SPID different than the one used in first dataflow, another SPID again for the second dataflow and finally yet another for the COMMIT... in this case COMMIT fails with "needs an open transaction".
 
Any idea what is wrong there?
 
Cheers!Ralf

View Replies !   View Related

---

SQL Table Acting Strange
Hi,

 

I have a strange problem with a sql table.

One of my table, dbo.Customer, gives me different values of "row count" everytime I check the properties.

The dbo.Customer have around 12 000 rows but when I check the properties (or open the table) the value always differs. The table row count can have any value from ~9000 - 12000.

Its like the table are trying to load all rows but cant.

 

Anyone have any idea what the problem can be?  

View Replies !   View Related

---

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?
 

I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View Replies !   View Related

---

Problem With Vb Procedure - Acting With Database
Hi,I have a problem with the execution of a vb-procedure that should do 2 updates of databasetables.The procedure runs when the eventhandler registrates that I want make an update within the <asp:detailview..>-controll.But I get the errormessage:  End-Of-Command requestedThis is the procedure:Public Sub myDetails_Update(ByVal sender As Object, ByVal e As System.EventArgs) handle DetailsView1.Databound    Dim conn As New SQLConnection("Data Source=.SQLEXPRESS;AttachDbFile=C:Pferdeservice KarleApp_dataPferde.mdf;Integrated Security=True Connection Timeout=30;User Instance=True")    conn.open()    Dim cmd As New SqlCommand()    Dim cmd1 As New SqlCommand()    cmd.Connection = conn    cmd.CommandText = "UPDATE News_Kultur set Header = @Header, Ueberschrift_D = @Ueberschrift_D, Ueberschrift_E = @Ueberschrift_E, Text_D = @Text_D, News_Kultur.Text_E = @Text_E"WHERE (((News_Kultur.ID)=@ArtikelID))"    cmd.CommandType = CommandType.Text    cmd1.CommandText = "UPDATE News set Datum= @Datum WHERE ID=@NewsID"    cmd1.CommandType = CommandType.Text    cmd.Parameters.add("@Headers", SqlDbType.nvarchar, 50)    cmd.Parameters.add("@Ueberschrift_D", SqlDbType.nvarchar, 50)    cmd.Parameters.add("@Ueberschrift_E", SqlDbType.nvarchar, 50)    cmd.Parameters.add("@Text_D", SqlDbType.Text)    cmd.Parameters.add("@Text_E", SqlDbType.Text)    cmd.Parameters.add("@ArtikelID", SqlDbType.Int)    cmd1.Parameters.add("@Datum", SqlDbType.Datetime)    cmd1.Parameters.add("@NewsID", SqlDbType.Int)    cmd.Parameters.add("@Headers").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text    cmd.Parameters.add("@Ueberschrift_D").value = DirectCast(DetailsView1.FindControl("Ueberschrift_D"), TextBox).Text    cmd.Parameters.add("@Ueberschrift_E").value = DirectCast(DetailsView1.FindControl("Ueberschrift_E"), TextBox).Text    cmd.Parameters.add("@Text_D").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text    cmd.Parameters.add("@Text_E").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text    cmd.Parameters.add("@ArtikelID").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text    cmd.ExecuteScalar()    cmd1.Parameters.add("@Datum").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text    cmd1.Parameters.add("@NewsID").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text    cmd1.ExecuteScalar()    conn.close()End Sub Regards  

View Replies !   View Related

---

Any Web Client Acting As Enterprise Manager ?
Hi,

one of my clients' hosting company refuses to allow me to access the SQL Server database if I do not have a fix IP (which I do not). Therefore connections using Enterprise Manager or Visual Studio are refused.

The only way around is using a web application installed on the same server. This works ok with ASPNET Enterprise Manager (http://www.aspenterprisemanager.com). However, this tool does not seem to be under development anymore, and does not have many features.

Anyone could help me finding an other one ?

thanks for your help

Johann

View Replies !   View Related

---

Questions On Attributes Acting As Both Inputs And Predictive?
Hi, all experts here,

Thanks a lot for your kind attention.

I am having quesion about attributes acting as both inputs and predictive outputs in a mining models, I mean if we are going to predict the outputs, then I cant actually see the effects of themselves acting as inputs as well?

I dont really see through the points of it.

Could please any experts here shed me any light on it.

I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

 

View Replies !   View Related

---

Bulk Insert Acting Differently Than Openrowset
I am trying to import some data from csv files. When I try it using bulk insert I get a conversion error. When I use the exact same format file and data file with an openrowset it works fine. I would prefer to use the BULK insert as I can make some generic stored procedures to handle all my imports and not have to code the column names in the SQL. Any suggestions?
 
 

BULK Insert stuff

From 'c:projects estdatalist.txt'

with

(FORMATFILE='c:projects estdatamyformat.xml')

 
insert into stuff (ExternalId, Description, ScheduledDate, SentDate, Name)

select *

from OPENROWSET (BULK 'c:projects estdatalist.txt',

FORMATFILE='c:projects estdatamyformat.xml')

as t1

 
The destination table has more columns than the data file. The Field IDs represent the ordinal position of the columns in the destination table. Column 1 in the destination table is an int identity. The conversion failure is from trying to convert column 5 to int which makes me think bulk insert is ignoring the name attributes in the XML and just trying to insert the columns into the table in order without skipping.
 
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="12"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="200" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="24"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR=" " MAX_LENGTH="24"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="
" MAX_LENGTH="500" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" NAME="ExternalId" xsi:type="SQLINT"/>
  <COLUMN SOURCE="5" NAME="Description" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="ScheduledDate" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="8" NAME="SentDate" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="9" NAME="Name" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

View Replies !   View Related

---

Converting Decimal To String W/O Decimal Point
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View Replies !   View Related

---

Converting Decimal Point To Decimal Comma
Hi all,
 
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
 
e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00
 
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
 
Any help would be much appreciated
 
Thanks!
 
 

View Replies !   View Related

---

Decimal Limited To 4 Digits To Right Of Decimal?
 

 

I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task  to decimal(with scale: 15) and try to save it using OLE DB Destination.

 

It works fine for 4 digits after the decimal (like 1.1234), but always failes for more   than 4 digits (1.12345).

Is the decimal limited to scale 4  ???

 

Thank you for your help!

Anna

 

 

 

View Replies !   View Related

---

Packed Decimal To Decimal Conversion
Hi,

 


I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.

File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.

Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030      Å“&
        20060715        0001010100010101


 

Please help!

 

Thanks

Mirudhu

View Replies !   View Related

---

A User Group Account Acting Like A Content Manager And Admin On The Report Manager????
 

A user was created with a limited privilege under the USERS group. Once this user loged in the Report Manager he is acting like an Admin and Content Manager, though he is not given even a browser role.
 
What do u think that this guy is acting like a Super User evenif he is restricted to a browser role on the Report Manager ????????????
 
I did all my best, but no luck so far

View Replies !   View Related

---

Where To Put ISNULL
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 Replies !   View Related

---

IsNull
I can get the following statement to work in SQL Server, but cannot get it to work on Access, please help

select isnull(stdev(myvalue2),0) as mystdev from tempstdev where myvalue2>0 and sessionvalue = '25768'

View Replies !   View Related

---

Isnull
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 Replies !   View Related

---

Isnull()
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 Replies !   View Related

---

Using Isnull
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 Replies !   View Related

---

ISNULL With LIKE?
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 Replies !   View Related

---

Using ISNULL In WHERE Clauses
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 Replies !   View Related

---

My ISNULL Confusion
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 Replies !   View Related

---

ISNull Question
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 Replies !   View Related

---

Default Value: ISNULL()
Hi!I'm wondering whether it's possible to set up the MS SQL functionISNULL() as a default value to avoid NULL entries when importing datainto a table?!For example, I want the column1, to have a 0 (zero) as default value,when entering/importing data: isnull("column1",0)I remember that it is possible to set up with a date function likenow(), having for each record the current time as default value. Isthat also with isnull() somehow possible?Thx a lot!Peter

View Replies !   View Related

---

IsNull Ordering
Hi-I've got some issues with the ordering of search results. I've got 4fields, (a, b, c, d). Most often there is data in field a. Sometimesthere isn't and so then I'd like to look at field c.I can accomplish most of what I want with this:selecta as location1, ISNULL(SiteCity, SiteCityOther) AS location1,ISNULL(SiteCityOther, SiteCounty) AS location1, ISNULL(SiteCounty,SiteCountyOther)AS location1,

View Replies !   View Related

---

ISNULL In SSIS
 

Hi All,
 
I want to accomplish something like this , I want to sum two of my columns and pass them as input into a third column, looks pretty simple but I think the way ISNULL is handled in SSIS is different from the way it is handled in TSQL.
 
Basically I wanna write an expression for the following TSQL
 

(ISNULL(Column1 , 0) + ISNULL(Column2 ,0)) as Column3
 
Thanks

View Replies !   View Related

---

IsNull Question
I have a some currency fields that are sometimes null and I want to display them as '--' or something similar when they are such.  I can do this with IIF statements, but I have a lot of them and would like a more manageable solution.  I thought of the IsNull SQL Command, but the problem is that it won't let me insert char values into a currency field.  Any other solutions?  Thanks.

View Replies !   View Related

---

Isnull Problem
 

hello,
I have a stored procedure that inserts or updates data depending of the value of one select that uses isnull finction. Here is the code:

 
Declare @id1 int
Select @id1= isnull(id,0) from tablename where name=@name

if @id=0
begin
insert.....
end
else
begin
update
end
 the problem is that even i use isnull function the select statement does not return 0 in the @id  when it does not find the record. ID column is of type Bigint and it is autoincremented identity

View Replies !   View Related

---

When Is ISNULL Not An Allowed To Be Used
Hi everyone,
I was browsing and came across this code with this result set
CREATE TABLE dbo.SalesByQuarter

(

Y INT,

Q INT,

sales INT,

PRIMARY KEY (Y,Q)

)

GO



INSERT dbo.SalesByQuarter(Y,Q,Sales)

SELECT 2003, 2, 479000

UNION SELECT 2003, 3, 321000

UNION SELECT 2003, 4, 324000

UNION SELECT 2004, 1, 612000

UNION SELECT 2004, 2, 524000

UNION SELECT 2004, 3, 342000

UNION SELECT 2004, 4, 357000

UNION SELECT 2005, 1, 734000

GO



SELECT Y,

[1] AS Q1,

[2] AS Q2,

[3] AS Q3,

[4] AS Q4

FROM

(SELECT Y, Q, Sales

FROM SalesByQuarter) s

PIVOT

(

SUM(Sales )

FOR Q IN ([1],[2],[3],[4])

) p

ORDER BY

GO



DROP TABLE dbo.SalesByQuarter

GO

Y Q1 Q2 Q3 Q4
2003 NULL 479000 321000 324000
2004 612000 524000 342000 357000
2005 734000 NULL NULL NULL
 
I tried to modify it to remove the nulls by changing this line of code

SUM( ISNULL(Sales,0))

 
I got this error
 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'ISNULL'.
 

so i tried it like this

 
and got this error

Msg 195, Level 15, State 1, Line 12

'ISNULL' is not a recognized aggregate function.

 
My question is why can't i use isnull to change NULL TO 0
 
Thanx
Slimshim

View Replies !   View Related

---

ISNULL() With Performance
 



isnull(startweight)  = case startWeight when NULL then 0 else startWeight end  ?
 
I have read some where that using function on column while selecting a bunch of data makes the query slower because the function needs to work on each row of the table..
 
 
SO I was just thinking like
 
is the value of  above 2 would be same perfrmance wise and value wise ?
 
Which one will execute faster?
 
 

View Replies !   View Related

---

Help Using ISNULL Function
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 Replies !   View Related

---

IsNull Question
Hi All,

Quick question...

why doesn't this work....
select IsNULL(select null),'TallOne')

But this does...
select IsNull((Select null),0)

How does it know what datatype I'm trying to get?

View Replies !   View Related

---

Getting An Error Around The Isnull
I have a null in my column IV2.Inventory. So im trying the case statement at the bottom and keeping getting errors. Where am I going Wrong?

Select StoreGroupID, Sum(SnapShotQuantity * SnapShotPrice) as Inventory,
Convert(DateTime, Convert(Char, RecordDate, 101)) as [Date]
Into #ttIV
From Delsol.dbo.ItemSnapshotStore

Where DateDiff(mm,Convert(DateTime, Convert(Char, GetDate(), 101)),Convert(DateTime, Convert(Char, RecordDate, 101))) >= -24
and Day(RecordDate) = 1
and StoreGroupID = 1

Group By Convert(DateTime, Convert(Char, RecordDate, 101)), StoreGroupID
Order By Convert(DateTime, Convert(Char, RecordDate, 101)) desc

Select *, IV1.Inventory-IV2.Inventory/IV2.Inventory as Trend,
Case When IV2.Inventory (isnull(0,IV1.Inventory-IV2.Inventory/IV2.Inventory))
From #ttIV IV1
Left Join #ttIV IV2 on IV1.[Date] = DateAdd(mm,1,IV2.[Date])
--Drop Table #ttIV

View Replies !   View Related

---

ISNULL Function
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 Replies !   View Related

---

Isnull Function
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 Replies !   View Related

---

ISNull Troubles
I'm having a problem creating a isnull statement.
I want to do two things I want to list all of the accounts that have a null value in a numeric field. And I want to update those accounts to 0.00.
I tried:
select Account_Num, isnull(TotalDDMFEE, 0)
FROM Addr_20080402
But it returned all records

For the update I tried:
update Addr_20080402
CASE
when TotalDDMFEE = ' ' then TotalDDMFEE = 0.00
AND
update Addr_20080402
CASE
when TotalDDMFEE = isnull(TotalDDMFEE, 0) then TotalDDMFEE = 0.00

Any ideas how I should have written these two queries?
Thanx,
Trudye

View Replies !   View Related

---

Isnull Not Working In Sql Server
select isnull(comments,'-') as comments from drawing_version where drawing_code='ACC    -Arch -IS-2008-54642' the comment value value is empty i want to display as "-" 

View Replies !   View Related

---

I Need Help Understadning--&> Isnull(max(PermissionValue),0)
I am trying to understand the block of sql code below. What I do not understand the second line, when they have isnull(max(PermissionValue),0)Can someone help me to understand what the purpose of this might be?
declare     @ThePermissionValue as intSELECT  @ThePermissionValue = isnull(max(PermissionValue),0)FROM Permission,WHERE Permission.EncryptedId = @TheId
 
Bill

View Replies !   View Related

---

Function Similar To ISNULL()
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 Replies !   View Related

---