I have a query and I need to be able to take negative numbers out of it, example -16495.00. I am not sure how to add it to my where clause. I was going to put clm_tchg <> ‘-‘ I know that isn’t right, but I don’t know how to write it. Please help, Thanks.
View 3 RepliesI have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
my example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
How does one pass into a Stored Procedure an array of numbers to be used in an IN clause? If I pass "1,2" in a VARCHAR, the stored procedure sees only the first number (1 in this case).
I'm using VB and ADO.NET, but I don't know how to set up the stored procedure for an array. Is there a parsing function to do this?
CREATE PROCEDURE TestInClause( @TeamList VARCHAR)ASSELECT Name FROM Teams WHERE TeamID IN (@TeamList); /* sees only 1st number */GO
I have a question in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies View Related
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
View 3 Replies View RelatedI am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.
When on SQL Server 2000 the following statement ran without issue:
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
GROUP BY TrackID
HAVING MAX(LegNum) = 1 AND
TrackID + 'x1' IN
(
SELECT
dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
WHERE TrackID + 'x1' IN
(
SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
GROUP BY TrackID
HAVING MAX(LegNum) = 1
)
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
Hi,
What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?
eg :
SELECT SUM(col1) from test HAVING col2 < 5
SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5
I want the equivalent query in MSSQLServer for the above Oracle query.
Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.
Thanks,
Gopi.
How Can I use Top Clause with GROUP BY clause?
Here is my simple problem.
I have two tables
Categories
Products
I want to know Top 5 Products in CategoryID 1,2,3,4,5
Resultset should contain 25 Rows ( 5 top products from each category )
I hope someone will help me soon.
Its urngent
thanks in advance
regards
Waqas
hi..
i have basic question like
what is differance between conditions put in ON clause and in WHERE clause in JOINS????
see conditions that shown in brown color
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)
another thing...
if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????
Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Thanks
I need to figure out how to pull records if the id is even or odd.
ie
ID Name
1 bill
2 john
3 joe
4 jane
for example i need a idea of a select statement that will pull the even records 2 and 4.
is this possible?
Hello,
I have the following:
insert into dbo.Categories (CategoryID, [Name])
select
newid(),
'Category ' + right('000' + convert(varchar(3), n + 1), 3)
from @Numbers
Numbers is a table with 1000 numbers.
How can I create only 10 records in Categories?
I need to restrict the number of @Numbers used.
Thanks,
Miguel
In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.
SELECT dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total,
dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid,
dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding,
ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage,
dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission,
ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order
FROM dbo.t_Agents LEFT OUTER JOIN
dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN
dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN
dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID
Any ideas how i do this?
Cheers
Anthony Swift
I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.
Does this sound like something that can be done within SSRS?
I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.
Hi,
Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?
Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.
Cheers,
koekie
I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this? Thanks for any help you provide.
View 2 Replies View Relatedhi,
I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.
The number will have 8 digits.
first 2 digits correspond to year. ex:04 or 03
the next 6 digits start with 000001 and it should get added for each new entry of data.
i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.
Any help would be highly appreciated.
thanks,
-sriram
How to generate serial numbers ? I had already tired ident. i am getting the error. Can any people who is willing to write a syntax for me.
In sybase if we use Number * function. It will automatically generates the serial numbers from 1 to n. similarly i need the same function in SQL server 7.0 so that my problem will be solved...
I am converting sybase stored procedure into sql server stored procedure that is why i am asking about that. i am struggling hard to find an answer...
please help me in this...
urs
vj
Looking for a way to round numbers to a specified number of significant digits. The ROUND function rounds to a specific decimal place but does not take into account the level of significance of the remaining numbers. (i.e.
ROUND(7.12435,2)=7.12000) The type of function I need would round the number in the following manner: SigFigRound(7.12435,3)=7.12 or
SigFigRound(7.12345,1)=7.
Any solutions?
Hello,
I am trying to run a query
select logid, count(logid) from temp2
group by logid
order by logid
compute sum(count(logid))
when I get the result the numbers are being truncated
eg instead of 10471066 it shows 104710 so last two digits
get truncated. Any ideas or hints appreciated.
Thanks
HP
Hi All,
After working out the basics and getting a report to give me back rows based upon a dropdown list, i know want to ask how do i show the sum of values for a customer.
So the out put would look something like this
Customer Total
0001 12234.56
0002 232.98
0003 456.78
I have two tables "customer" and "trades" i all ready have them linked and giving me all the individual items, oh and i have got it doing BETWEEN to dates, but i would really like to know how to show just the TOTAL trades for each cutomer on each line not every single trade for the customer.
Many thanks for any help with this
Dave C :confused:
I have the following table:
CREATE TABLE ITEMS ([ITEMID] int, [itRULE] varchar(1))
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (11, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (12, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (21, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (22, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (31, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (32, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (41, 0)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (42, 0)
-- Those works and gives me 11,12,21,22
SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3)
SELECT ITEMID FROM ITEMS WHERE itRULE IN ('2','3')
-- This doesn't works
declare @Rule varchar(10)
set @Rule='2,3'
SELECT ITEMID FROM ITEMS WHERE itRULE IN (@Rule)
Any idea?
I don't mind to change the data type if it works.
I am trying to get random numbers to have a unique value for different processes, then I can identify each process. What happens is that I use rand() function without seed, so I got my random numbers, but after shutting down SQLServer and try to get again another random number after booting up, the same series of random numbers is given again and again. So if anyone knows how I can get unique values,even though reseting the server, and using random function or any other method which automatically provides unique values,I'll really appreciate it if you let me know it.
This is the function: select rand()
Alberto.
I have two identical databases. I need to delete the even customer numbers from the first database and the odd numbers from the other database. I know I can do this writing a delete statement for each table. However there are a total of 54 tables.
Does anyone know a faster way to write the script without writing 54 scripts?
I would appreciate your help.
As always you guys are the greatest.
Dianne