I'm trying to understand why I can enter a query such as:
select 5,"random"
from customers;
and get two columns with 5 and "random" in every respective column field.Why don't I receive a syntax error ?
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies View RelatedOK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?
View 3 Replies View RelatedHi, I have something similar to the following: CREATE PROCEDURE dbo.MySproc @columnVal int = nullASBEGIN SELECT * FROM MyTable WHERE MyTable.column = @columnVal END If columnVal is not passed into the stored proc i want it to just select everything from 'MyTable' (without the WHERE clause)....how can I do this in as few lines of code possible?thanks
View 3 Replies View RelatedSo I have this table called "listings"... there are 100 unique listings with an integer ID for each.
I have another table called "ratings"... in there are multiple entries that have a listing_id field and a rating field. The rating field is a value from 0-10.
I want to select ALL "listings" from the listings table... and then sort based on the average number that the multiple rating fields in the ratings table has for that listing.
I CAN NOT figure it out!! Any help would be greatly appreciated. Please respond if I have not explained this properly. Thanks in advance.
I am using this below query to sum and select maximum values from table. I have converted the cost column here and how can I possibly sum the cost column?
select ID, MAX(Dates) Dates,'$ ' + replace(convert(varchar(100),
convert(money, Cost), 1), '.00', '') Cost, MAX(Funded) Funded from Application group by ID, Dates, Cost, Funded
as i stated in the subject i want to gather the biggest five records from the table with sql, how can i do that?
View 5 Replies View RelatedCREATE TABLE product
( product_id integer
, translated_name NVARCHAR2(50) );
insert into product values(1,N'kenenisa')
insert into product values(2,N'Ethiopia')
Note that i have used N becuase it is unicode data
then
select * from product; this works fine.
select * from product where product_id=1; this also works fine,
but
select * from product where translated_name=N'kenenisa'; this doesnot work correctly, so how can i fix this problem ?
I need to access a table and return 3 values from it in the samerecordset - ie one iteration of the recordset will have 3 values fromthe same database, I have looked at sub queries but they dont seem tobe able to do what i want.I would be grateful for any guidanceS
View 1 Replies View RelatedI have written sql query
select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +
[Code].....
I am getting different amount 984000.0000 and quantity 9.
Hi,
I've got a table with trialsubscriptions. When someone orders a trialsubscription he has to fill in a form. With the values in the form I like to fill a database with some extra fields from the trialsubscription table. I get those extra fields with a datareader. But when I insert I can't use the same datareader (its an insert), I can't make two datareaders because I have to close the first one etc.
Does someone had the same problem and has some example code (or make some :-)) Some keywords are also possible!
Thanks!
Roel
DECLARE @Query varchar(8000)
Create Table
#tempCountRichard (Status varchar(50), Number Varchar(1000)
Set @Query = 'Insert Into #tempCountRichard
Select Count(Status),
[code]...
With the following SQL. When I select from the temp table I return the right count but my second column doesn't return anything.
Count Status
1010 2000
1111 2222
When I run the query that is being inserted into the the temp table I return the correct results
Count Status
1010 Pass
2000 Pass with Obs
1111 Fail
2222 None
how to select the data from the temp table exactly the same way it was inserted. As I need to select the exact same data from the insert.
Is It possible to have a stored procedure that returns 2 values?
and call it from a C# webforms application.
Thanks.
I have a table 'wRelated' with the following columns
[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.
[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'
I have tried Unions and temporary table inserts.
I am hoping someone could help me understand why this is happening and perhaps a solution.
I am using ASP.NET 2.0 with a SQL 2005 database.
In code behind, I am performing a query using a parameter as below:
sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)"
objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text))
The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.
The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:
sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)
I also get NO records retuned using the same criteria.
What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?
Thanks!
I have following select-statement:
select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2
My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "erroneous" values are not inserted or rather are shown as null.
Anyone seen wrong values returned from a CLR update trigger when using the columncount property?
I have a 6-column table that I was experimenting on, and the isupdatedcolumn property was not returning true on the one column I was updating (I verfied the trigger was firing). So, I returned the columncount property in a pipe.send, and was surprised to see the value of 11. When I looped through each column's isupdatedcolumn property, the only column that returned true was column 9.
Then I remembered this table used to have more columns, so I conducted the following experiment:
Added a new column to the table (bringing the total number of columns to 7).
Executed an update the caused the trigger to fire; columncount sent to the pipe returned 12.
Deleted the column.
Executed another update that caused the trigger to fire; columncount returned 12.
Added another column to the table.
Executed an update the caused the trigger to fire; columncount sent to the pipe returned 13.
Deleted the column.
Executed another update that caused the trigger to fire; columncount returned 13.
See the pattern? I'm fairly perplexed. Anyone seen this or something similar? This table is in a database that was migrated from SQL 2000 to 2005 and is 9.0 compatibility mode.
Thanks,
STDEV() gives incorrect values with reasonable input.
I have a table filled with GPS readings. I've got a column LATITUDE (FLOAT) with about 20,000 records between 35.6369018 and 35.639890. (Same value to the first 5 digits of precision---what can i say, it's a good gps.)
Here's what happens when I ask SQL Server ("9.00.1399.06 (IntelX86)") to compute the standard deviation of the latitude:
// Transact-SQL StdDev function:
SELECT STDEV(LATITUDE) FROM GPSHISTORY
WHERE STATTIME BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
0
// Zero. ZERO??!?!!
//Let's re-implement Std Dev from the definition using other aggregate functions:
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE((LATITUDE - @AVERAGE)))/COUNT(LATITUDE)) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005392E-06
// That's better. Maybe STDEV is using fixed point arithmetic?!?
SELECT STDEV(10 * LATITUDE)/10 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
4.77267753808509E-06
SELECT STDEV(100 * LATITUDE)/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
1.66904329068838E-05
SELECT STDEV(1000 * LATITUDE)/1000 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
8.11904280806654E-06
// The standard deviation should, of course, be linear, e.g.
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE(100*(LATITUDE - @AVERAGE)))/COUNT(LATITUDE))/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005389E-06
// Std Dev is a numerically stable computation, although it does require traversing the dataset twice.
//
// This calculation is not being done correctly.
//
// Incidently, SQRT(VAR(Latitude....)) gives 4.80354E-4, which is also way off.
I will redefine STDEV to use a stored procedure similar to the above, but the algorithm used to compute VAR, STDEV etc should be reviewed and fixed.
-Rob Calhoun
I have a query which is returning a different result set when it is run against identical tables in 2 different environments.
The query is like:
Select
F.LicenseeID, IsSpecialLicensee
from FactTable F
left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID
The Create Statement for the view is like
Create View [dbo].[View_SpecialLicensee]
as
Select LicenseeID, LicenseeName, IsSpecialLicensee = 1
from DimensionLicensee
where LicenseeName like '%ibm%'
or LicenseeName like '%cisco%'
or LicenseeName like '%hp%'
In my test environment, I get the query result I expected:
LicenseeID, IsSpecialLicensee
1 , 1 - (where LicenseeName = 'IBM')
2, null - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, null - (where LicenseeName = 'Microsoft')
5, null - (where LicenseeName = 'Oracle')
6, null - (where LicenseeName = 'Apple')
In my production environment, I get the following query result:
1 , 1 - (where LicenseeName = 'IBM')
2, 1 - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, 1 - (where LicenseeName = 'Microsoft')
5, 1 - (where LicenseeName = 'Oracle')
6, 1 - (where LicenseeName = 'Apple')
Ideas as to what changed gratefully received.
FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.
HiI have a query that is performing very strangely.I f I put a top statement in it returns rows,soSelect top 10 * from .......returns 10 rowsbut without it then no data is returnedSelect * from ..........returns 0 rows.
View 1 Replies View RelatedGoodmorning,Could I have a SELECT statement that normally returns two rows,but that instead returns one row appending to the first row the secondone of the result ?For exampleQuery: "SELECT username from tab1 where year in (2001,2002)"Result:1° - "'John'"2° - "'Adam'"Instead I need:Result:"'John','Adam'"?I have Win2000 Pro , SqlServer2000.Thank You--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
View 3 Replies View RelatedFrom what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077);
select checksum_agg( ID ) from @t;
-----------
-7077
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-8112);
select checksum_agg( ID ) from @t;
-----------
1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int );
insert into @t ( ID ) values (-7077), (-7077);
select checksum_agg( ID ) from @t;
-----------
0
It's not specific to -7077, for example:
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
-997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777);
select checksum_agg( ID ) from @t;
-----------
0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int );
insert into @t ( ID ) values (-997777), (8112), (-997777), (8112);
select checksum_agg( ID ) from @t;
-----------
0
hi,
i have the following query to sum the total due balance for a customer:
select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?
thanks
I want to write a statement something like this
SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)
But i want the search to ignore the first 2 digits so that it will return e.g
10/11/1234, 09/11/1234 so that it's only matching the last part
Any Help Would be greatly appreciated Thanks
I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created
CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)
The following stmt won't work...
INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C
... as the number of supplied values does not match table definition.
Hi,
I have DB monitoring jobs which use Sysperfinfo to monitor some of the counters. On One SQL 2K Server since few days the select on sysperfinfo returns 0.
Do you think I need to start any service from the OS side to enable this?
Your input is highly apppreciated.
Thanks,:confused:
I am using the following conditional select statement but it returns no results.
Declare @DepartmentName as varchar
Set @DepartmentName = null
Declare @status as bigint
Set @status = 4
IF (@DepartmentName = null) BEGIN
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM AdminView
WHERE (Status = @status)
ORDER BY CallLoggedDT
END
ELSE IF (@DepartmentName <> null) Begin
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM dbo.AdminView
WHERE (Status = @status) AND
(DepartmentName = @DepartmentName)
ORDER BY CallLoggedDT
end
when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???
Chris Morton
I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?
View 4 Replies View RelatedHi,
The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"
I get the result as:
Name Address
---- -------
No rows affected.
(1 row(s) returned)
But not the actual row is displayed whereas I would expect to see something like:
Name Address
---- -------
John Doe
No rows affected.
(1 row(s) returned)
I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.
Why no results in first case?
Thanks,
Bahadir
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
View 2 Replies View RelatedDoes anyone know a select statement that would return the column namesand keys and indexes of a table?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View RelatedI have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid