Hi,
In the following query the calculator gives value 3.3 but the query returns 3.0
I need to get the decimal part also. i.e I need to get 3.3 as answer from the query.
select cast(66/20 as decimal(6,2))
Need help.
Thanks
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
Hey...I've been cracking head about this one all day, and I'm sure it'san easy answer, but here goes:I have a column entitled Sequ which is defined as a Decimal withPrecision 10 and Scale 5. In an ASP.NET page, I'm performing acalculation which is inserted into the Sequ column. I'm doing aresponse.write, which enables me to see that the numbers are in factbeing calculated correctly complete with a string of numbers after thedecimal, but when I pull the numbers out to use on the page or accessthe table, Sequ lists all numbers as integers. I'm using a storedprocedure in which I'm declaring the @calc parameter (the calculatedvalue which is being inserted) as a Decimal -- I don't know what I'mdoing wrong. Help appreciated...thanks.Erik
i have a column name remarks and i want to retrieve data like by deleting some of the leading charaters and some last characters and show the middle one.
like remarks has CALLTRANSFER_OVER_SIP:XfrTime=86.05599975585938_en and i want to show only "XfrTime=86.05599975585938" this much
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site. I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part. While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as "Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
The table_cat returned a null value. I ended up writing
select * from linked_server..schema.table and it did work. However, it was really slow. 56 seconds vs. less than a second for the pass through openquery.
My guess is that the problem is that the table_cat was null and this is why it is so slow. Is this correct? How do I resolve this speed issue?
SELECT * FROM dbo.wce_contact WHERE (Mail1Date IS NOT NULL) AND (Mail2Date IS NULL) AND (Mail3Date IS NULL) AND (Mail4Date IS NULL) AND (Mail5Date IS NULL) AND (Mail6Date IS NULL) AND (Mail7Date IS NULL) AND (Mail8Date IS NULL) AND (Mail9Date IS NULL) AND (Mail10Date IS NULL) AND (Mail11Date IS NULL) AND (Mail12Date IS NULL) AND (Mail14Date IS NULL) AND (Mail15Date IS NULL) AND (Mail16Date IS NULL)
or(IDStatus LIKE '') or (NOT (Task LIKE '%x%')) or (ExpressEmail LIKE '%@%') or (WebSite NOT LIKE '') or (Unsubscribe = 0) or (Artwork LIKE '')
Basically the parts in red work best when they use the 'or' criteria.
My problem.
I run the query together black+red and get a different result than expected. Is there a way of saying 'do the black part first and its an imperative that the red part follows those rules on the black part of the query?' eg:
IDStatus must be LIKE '' when running the black part of the query and expressemail must be like '%@%' on the black part of the query but not on red part of the query. So it doesnt matter that the expressemail must have idstatus like '' whilst having an @
I have a huge problem as mentioned in my previous queries some of my applications is using Link Server Query as "select * from sm-matrix.matrix.dbo.stage_orders" this doesn't work it gives following error:-
ODBC: Msg 0, Level 18, State 1 SqlDumpExceptionHandler: Process 62 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. [OLE/DB provider returned message: Unspecified error]
I understand best method to use is "select * from openquery(sm-matrix,"select * from stage_orders")" but i can't do away with above mentioned query as lot of places in application it has been using.
This was working fine till i moved to Windows 2003 from Windows 2000.
I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this?? thanks
Hello all,I have an SQL query which retrieves a COUNT number from 2 different tables, and i want to do a division with botht he COUNT data retrieved. Trouble is I can't get it in the format that I want, my SQL query is as below :-SELECT ROUND( ((T1.Present/T2.Total ) * 100), 2) FROM(Select Count(Date) as Present from Attendance WHERE Month(Date)=12 AND Status=1) T1,(Select Count(Date) as Total from Attendance WHERE Month(Date)=12) T2The trouble here is that the result should be as below: T1.Present = 3T2.Total = 5 T1.Present/T2.Total = 3/5 = 0.6The final should be 60 after divided by 100But i am getting a zero as my result, even when I don't multiply the number by 100, the division result is still zero. I am guessing it is a conversion problem. Could anyone please offer me any advise on how to get the final result in the format I want?
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)
I'm converting some direct SQL queries into stored procedures. The original queries have conditional parts appended depending on certain parameters like this:
Sql = "First part" If certain condition then Sql &= "Second part" Endif
How could I do this in a stored procedure? So far I have made them like this:
IF @condition > 0 Fist part Second part ELSE First part
You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?
Hello, I've been trying for a couple of days now with the following problem in SQL Ive a table called tblRresults that has the following Date - just a regular date OfficeLocation - just a unique id such as a zip code SampleType - contains things like wall, floor, beam SampleResult - "Detected" or "NotDetected" When populated the table can look like 12/12/05 99505 Wall Detected 12/12/05 99505 Wall NotDetected 10/04/05 99211 Beam Detected 10/04/05 99211 Beam Detected 10/04/05 99111 Floor NotDetected 10/04/05 99111 Floor NotDetected What I want is (I guess) a crosstab query that produces the following Date Location SampleType CountDetected CountNotDetected 12/12/05 99505 Wall 1 1 10/04/05 99211 Beam 2 0 (or null would be ok) 10/04/05 99111 Floor 0 2 Any help gratefully accepted, this has been driving me mental! thanks
I have 2 tables (table1KKK, table2KKK), and want to run the same query on them by using parameter with the value "1" or "2". Is it possible to use that parameter as a part of the queried table name? Something like (only for demonstration €“ doesn€™t work): Select * From table +myParameter + kkk Thanks in advance!
I am working on a query that is quite complex. I need the query to return as part of the fields a field that will contain the total percentage of tickets in a version.The query is below
select cat.name as name,count(distinct bug.id) as numberOfBugs,cast(count(bug.id) * 1000.0 / sum(count(bug.id) * 10.0) over() as decimal(10,2))/100 AS qnt_pct, vers.version, dateadd(s,vers.date_order,'1/1/1970') as "Release_Date" from mantis_bug_table bug INNER JOIN mantis_category_table cat on cat.id = bug.category_id LEFT OUTER JOIN mantis_project_version_table vers on vers.project_id = vers.project_id and vers.version = bug.version
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
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?
Hi. I have a query that returns several averages and percentages. Is there some way to set up the query so the results only go to 2 decimal places? Here is a sample of the query I am using: Code:
$query = "SELECT COUNT(deal_id), SUM(vs), SUM(vs)/COUNT(deal_id)*100, AVG(fin) FROM sales GROUP BY salesperson";
It works great,except the results are several decimal places long, and I need it to be only 2.
Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:
i.e., SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv FROM tblNumericCovert
where testDiv needs to spit out results like this ###.##
I am totally lost, if anyone can help, I would appreciate it.
I am writing a stored procedure to prepare some reports.
The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).
When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?
alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status] @Salesmen nvarchar(50), @QuoteStatus nvarchar(50) AS BEGIN SET NOCOUNT ON;
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
I had problem when combining OpenRowSet and SP_EXECUTESQL, when i tried to run the following query, it complaints that RESID is not declared. any idea how should i put the query so i will pass @RESID as 1 of the parameter? BTW, i know that the SP_EXECUTESQL is able to run query which length up to 8000, but how about the parameter?
I have a linked server in SQL 2007 form SQL to DB2.(IBM)
If I write a distributed query from SQL to DB2 I get mt data returned. ie: select * from openquery(Movex_Extranet,'Select * From mvxadta.ooline')
If I write a Query from SQL to DB2 using the 4-part naming convention linkserver.catalog.schema.object then I also get my data returned.
ie: select * from MOVEX_EXTRANET.RCHASE5C.MVXADTA.OOLINE
My problem is with the Query Designer in SQL2007.
When I right click the first lot of code and select "Design query in editor" The Designer GUI opens up and displays my table in graphical form with all columns selected - Great.
But When I repeat this for the code that uses the 4 part naming The Designer opens up and displays the table but with no columns viewable or available for me to select ??
Anyone know what might be causing this to happen. Is it a bug in SQL 2007 ??
I have tried linking the server using 2 types of providers ( One from Microsoft & one from IBM) but each time I still cannot see or choose columns in the designer using the 4part naming convention.
The 2 providers I tried where. Microsoft OLE DB for ODBC Drivers. IBM DB2 UDB for Iseries IBMDA400 OLE DB Provider
Any help greatly appretiated as I would really love to use the designer using 4part naming convention.
Apparently if you read this the it should work ? http://msdn2.microsoft.com/en-us/library/aa225987(SQL.80).aspx#dvmscworkingwithtablesfromdifferentdatasources
i have one table completed tabletask_id task_due_datetime task_completed_on 1 03/21/2008 03/25/20082 03/12/2008 03/10/20083 03/10/2008 03/18/2008 i want the output:task_id on time late 1 0 1 2 0 0 3 0 1 how i get this output in sql query without the use of cursor...