How do i retrieve 0 for null value in the following query
select customerid, count(*) from calls_new_today c, customerdnis cd where c.cpn=cd.dnis and cd.enddate is null
group by customerid
order by customerid
what i mean is when count(*) = 0 no value is returned i want a Zero in the output how do i get it?
Is there a chance to retreive let's say rows positioned from 30-70 if a query produced result that contains 100 rows?
View 3 Replies View RelatedIs there a way to create a query that will return all user tables inside a sql db
Thanx
I'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table.
Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form.
TIA
Steve
hey guys, i getting a minor problem here. Hope u guys may help me =) I would like to count all the records from a table ' member ' and store the value inside a variable ' iCnt ' i wrote like tis:cmd.commandtext = " select count(*) as total from member" dim iCnt = cmd.ExecuteScalar Hmm.. I am wondering whether my code is correct, it seem like cant work =(Any help is appreciated, thanks u guys.
View 1 Replies View Relatedif a db field "mynum" of type int contains for example the number 543.Is there a way I can update this field without first selecting the value?so for example set the the current value to +1
Hi,Is it possible to retrieve a single table from a backup in SQL 7.0?
TIA
Jay
Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???
Thanks :)
Hi: Guys
Given the table below I want a select query that returns the AccountRepID with the largest single sale for each RegionID. In the event of a tie choose any single top AccountRepID to return.
CREATE TABLE [Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
RegionID] [int],
[AccountRepID] [int],
[SalesAmount] [money]
)
If the data were
salesid,regionid,accountrepid,salesamount
1,101,31,$50
2,101,32,$25
3,102,31,$25
4,102,32,$25
5,102,31,$15
The query should return
regionid,accountrepid
101,31
102,31 or 102,32
Is there another way to get the data other than the following query:
select regionID,accountrepid FROM Sales
where salesamount in
(select max(salesamount) FROM Sales group by regionid)
Thanks
i want to insert a row from t1 to t2 if any of the row is updated in the t1. say if Nth row is updated then the values of the row should be inserted into t2. inside the trigger i need to retrieve the row which is updated, how to do it? any specific global variable like @@identity is there for updation?
View 6 Replies View RelatedI would like to know which products are my best sells by sellers, but iwould like to retreive this info by product id, seller id and the totalamount of sells for this product.My Sells table is :Seller_idProduct_idTotaldate_s1 2 1020/05/042 4 1512/05/043 5 2206/06/041 5 1807/06/044 8 1213/05/047 2 1119/05/043 4 1421/05/042 4 1418/05/041 5 1817/06/042 5 5008/05/04etc....I know how to retreive the total sells by product id and seller idSELECT Seller_id, Product_id, SUM(Total) AS totalFROM SellsWHERE date_s > '01/05/04'GROUP BY Seller_id,Product_id order by Seller_idSeller_idProduct_idTotal1 5 361 2 102 5 502 4 293 5 223 4 14I would like retreive only the max of total, and the Seller id andproduct id, like this :Seller_idProduct_idTotal1 5 362 5 503 5 22How can i do without using a temp table ?Thanks for your help.
View 3 Replies View Related
Hello Everyody,
Some background: I have a SQL server 2000 server that I just restored from backup after complete OS reinstallation. I have retored all databases (except the master) and I still have the mdf and ldf file from before reinstallation (master.mdf as well).
Now my problem is that I have 1 application that use a specific SQL user with a password that the application choose itself and of course this application doesn't work anymore because I didn't restore the sql users (I use windows authentification as much as possible). I cannot do anything to the application to use another password but I know the login used.
My questions is "How to I recover the password?". Is there a way to log the password that this application send to sql or from the old master.mdf file?
Any help would be greatly appreciated.
Bruno.
hi,
can anyone tell me how to retreive a row from a table using rowid (For example i want to retreive fifth row from a table)
Hi guys, I'm creating a webSite and i'd like to save images to my db. I've been using fileUpload to get the image i want to store, but i can't get it right! After "mastering" the storage of my images , i'd like to be able to retrieve them. I allready have SPs created for both storage & retrieval of images!Can anyone help me please? Thanks,superJB
View 3 Replies View RelatedI have developed a SSIS package on my local box and everything worked fine until I moved it to the production server. I have a Send Mail Task that is triggered by any of the other processes when they fail. It sends an email to the Admin with the current log file. My issue is that when it attempts to grab the log file (as an attachment) it fails with the following error message:
OnError,BILBO,UMIALIKJamesT,Email George,{a73bfc5c-d68e-426a-98ad-55e701670470},{677B8238-8697-454F-8F8A-DD6378E93455},4/1/2008 10:25:28 AM,4/1/2008 10:25:28 AM,-1073573429,0x,Either the file "D:SQL200590DTSPackagesPPROutputoutput.txt" does not exist or you do not have permissions to access the file.
It certainly exists and I've open all permissions up on the folder. What I find even more interesting is that the Package is just a single directory up from the Output file. The Package resides at D:SQL200590DTSPackagesPPR and is run via a batch file. It seems to me that it should have no issue grabbing this file and sending it, and when I don't attach the file it sends just fine.
Thank you in advance. If you need anymore information just let me know and I'll dig it up.
Peace,
James
I want to allow administrators of the program to change the permissions of the database roles. Is there a way to retreive the the specific permissions granted an denyed to a user and a database role. For instance, a store procedure that you call, pass it the database role and you get whether SELECT is allowed on table1, or if UPDATE is denied on table2, etc.
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
Hi all, I've been struggling with this one for a while, but am still doing something wrong: I have three tables which need to be updated. I have a stored proc which accomplishes the first writes the data in for the first 2 tables. The last table is a one to many, so needs a seperate stored proc which will be called multiple times depending on the number of items in the order. My only question I am trying to get to here is: How do I get the first stored proc to return me the primary key value from the 1st insert (NOT the second)? I've tried a few different methods: the current one shown below returns me "2", as in the number of inserts performed. Dim InsertCmd As New SqlCommand("WriteOrder", oSQLConn)
InsertCmd.CommandType = CommandType.StoredProcedure
InsertCmd.Parameters.AddWithValue("@CartTotal", Session("CartTotal"))
InsertCmd.Parameters.AddWithValue("@CARDFNAME", BillingInfo("CARDFNAME"))
InsertCmd.Parameters.AddWithValue("@CARDLNAME", BillingInfo("CARDLNAME"))
...
InsertCmd.Parameters.AddWithValue("@CONTACTEMAIL", BillingInfo("CONTACTEMAIL"))
InsertCmd.Parameters.AddWithValue("@COMPANYMATCH", 0)
InsertCmd.Parameters.AddWithValue("@RECNUM", 0)
Response.Write("---" & InsertCmd.ExecuteNonQuery().ToString() & "---")
---------------------STORED PROC---------------------
ALTER PROCEDURE [dbo].[WriteOrder]
@CartTotal float,
@CARDFNAME varchar(30),
...
@CONTACTEMAIL varchar(100),
@COMPANYMATCH bit,
@RecNum int = 0 OUTPUT
AS
INSERT INTO [dbo].[ORDER]
([OrderDate], [OrderTotal], [CARDFIRSTNAME], [CARDLASTNAME], [BILLINGADDR1], [BILLINGADDR2], [BILLINGADDR3], [BILLINGCITY], [BILLINGSTATE], [BILLINGPOSTALCODE], [BILLINGCOUNTRY], [BILLINGPHONE], [BILLINGEMAIL], [CCTYPE], [ACCOUNTNUMBER], [CARDEXPIREMONTH], [CARDEXPIREYEAR], [CVV2], [TransactionID], [TransDateStamp], [ProcessorAuthCode])
VALUES
(GetDate() ,@CartTotal , @CARDFNAME, @CARDLNAME, @BILLINGADDRESS1, @BILLINGADDRESS2, @BILLINGADDRESS3, @BILLINGCITY, @BILLINGSTATE, @BILLINGPOSTALCODE, @BILLINGCOUNTRY, @BILLINGPHONE, @BILLINGEMAIL, @CCTYPE, @ACCOUNTNUMBER, @EXPIRATIONMONTH, @EXPIRATIONYEAR, @CVV2,@TransactionID, @TransDateStamp,@ProcessorAuthCode)
SET @RecNum = @@IDENTITY
INSERT INTO [dbo].[CONTACT]
([ORDERID], [CONTACTFNAME], [CONTACTLNAME], [CONTACTADDRESS1], [CONTACTADDRESS2], [CONTACTADDRESS3],
[CONTACTCITY], [CONTACTSTATE], [CONTACTPOSTALCODE], [CONTACTCOUNTRY], [CONTACTPHONE], [CONTACTEMAIL], [COMPANYMATCH])
VALUES
(@@IDENTITY , @CONTACTFNAME, @CONTACTLNAME, @CONTACTADDRESS1, @CONTACTADDRESS2, @CONTACTADDRESS3, @CONTACTCITY, @CONTACTSTATE, @CONTACTPOSTALCODE, @CONTACTCOUNTRY, @CONTACTPHONE, @CONTACTEMAIL, @COMPANYMATCH)
RETURN @RecNum
GO
I've also tried returning parameters like this, with no luck: InsertCmd.Parameters(32).SqlDbType = SqlDbType.Int InsertCmd.Parameters(32).Direction = ParameterDirection.ReturnValue Response.Write("---" & InsertCmd.Parameters(32).Value() & "---")Any help is greatly appreciated!
I am using ASP.NET 3.5 with SQLDataSource, when a record is inserted is there a way to retreive the newly created GUID value in a field?
I am currently using SELECT @NewID = SCOPE_IDENTITY() to retreive the newly created record unique ID field, but I need the secondary field GUID field's value instead in this case.
Thank you.
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
Hi Friends,
I am Using SQL SERVER 2005
I need to retreive data from Excel file to SQLSERVER table..
Thanks in advance
Regards
Rajkumar.M
Hi friends,
I need to retreive data from Excel into SQLSERVER database table
give me reply asap.
Thanks in advance...
I use the following query to shred an xml and insert it into a table, but I want to have the whole html structure into the column to be able to present the formatted text in Cognos 8 BI.
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
INSERT INTO TMP_ATGFORSLAG (AtgForslagDesc)
SELECT
AtgForslagRad.value('(/my:AtgForslagRad/my:AtgForslagDesc)[1]', 'varchar(2000)') AS AtgForslagDesc
FROM dbo.DeklAtgForslagXml
The structure of the source in the xmlfile I want to import is:
<my:AtgForslagDesc>
<html xmlns="http://www.w3.org/1999/xhtml">
<ol>
<li>Text1...</li>
<li>Text2...</li>
<li>Text3...</li>
</ol>
</html>
</my:AtgForslagDesc>
How do I shred not only the text but the whole starting <html> to finishing </html> and insert it into a table I would be very happy.
I've looked through msdn and found an example, but it doesn't work. The problem is that when i use such construction
ResultSet rs1 = stmt.executeQuery("SELECT TOP 1 * FROM sys.check_constraints");
rs1.next();
Reader reader = rs1.getCharacterStream(17);
reader is set to null,but it mustn't be null cause i see some data through management studio in 17th column of sys.check_constraints table.
hi
here is sql of query when any of sub query return zero result the result become null though other may have some results
litke
(select * from t1)-(select * from t)
in this case if either of table return zero rows the result will be null
plz help
thnx
faisal iqbal
sELECT bet.Party_id, (((select sum(amount*rate) from bet where team=1 and kl='l' and matchid = 18 and Party_id=1 )*1)-((select sum(amount*rate) from bet where team=1 and kl='k' and matchid = 18 and Party_id=1 )*1)) AS Expr1, (select sum(amount*rate) from bet where team=2 and matchid = 18 and bet.Party_id=1) AS Expr2, (select sum(amount*rate) from bet where team=3 and matchid = 18 and bet.Party_id=1) AS Expr3
FROM bet
GROUP BY bet.Party_id;
Hi there, I'm kind of new with SQL and I need some help with a query I am Creating to pull records from date field whose defaul value is NULL. The query i have works in SQL but does not work in Crystal Reports any clue how?
View 1 Replies View RelatedHiI have a table called Orders with columns OrderId, ItemReceiptDate and itemnumber.Sample Data is given below:OrderID | ItemReceiptDate | ItemNumber-------------------------------------------1 | '2004-01-01' | 31 | '2004-01-28' | 41 | NULL | 52 | '2002-11-19' | 12 | '2003-01-28' | 4What I would like to SELECT is:OrderID | ItemReceiptDate | ItemNumber-------------------------------------------1 | NULL | 52 | '2003-01-28' | 4i.e. if ItemReceiptDate is null for a particular order id ==> return that recordelse return the record with maximum ItemReceiptDate.Please help.Thanks
View 8 Replies View Related
In my database, in a few tables there are NULL values in some of the columns. This is okay, but I need to know how to query for nulls. For example I tried the following query but it did not work:
select * from Employee where DateOfBirth =NULL
This did not work so I also tried the following:
select * from Employee where DateOfBirth ='NULL'
Neither of these worked. Can someone help me out?
how can i write a SQL query that will pull all records that are equal to NULL??
View 1 Replies View RelatedHow does the phrase "Is Not Null" in the where clause effect theeffectiveness of a query?If it is a determent to the effectiveness of the query, how do youwork around it?Thanks,
View 3 Replies View RelatedI'd like insert null value from a query but i can do it,if my query is :UPDATE table:name UPDATE euro='' WHERE id='5';i have a sybase message warning like this :Error converting data type varchar to numeric.Why ????
View 4 Replies View RelatedI am trying to have a query with the option for items to be null. (So users don't need to fill in the other fields if they choose not too) SELECT Tickets.TicketID, Tickets.UserID, Tickets.SystemID, Tickets.Title, Tickets.Description, Tickets.Software, Tickets.Date, Systems.OS, OS.OS AS OstitleFROM Tickets INNER JOIN Systems ON Tickets.SystemID = Systems.SystemID INNER JOIN OS ON Systems.OS = OS.osIDWHERE (Tickets.Title LIKE '%' + @title + '%') AND (Tickets.Software LIKE '%' + @software + '%') AND (Tickets.Description LIKE '%' + @descrip + '%') AND (Systems.OS = @osid) OR (@osid IS NULL)This works when i give the LIKE values % as a parameter. So they can choose to search by title + software but not description or description and nothing else etc etc etc. The problem is, the osid. If I give it a value it works but if i try to do null, *. or % it always displays every item in the databse ignoring any of the previous like statements. Anyone have an idea?
View 3 Replies View RelatedHello.
I have (2) related questions.
#1: I am using a paramterized query, but am unable to make it work if one of the values happens to be null.
if (Request.Form["txtLink1"] != "")
{
mySqlCmd.Parameters.Add(new SqlParameter("@link1",
SqlDbType.VarChar));
mySqlCmd.Parameters["@link1"].Value =
Request.Form["txtLink1"];
}
else
{
mySqlCmd.Parameters.Add(new SqlParameter("@link1",
SqlDbType.VarChar));
mySqlCmd.Parameters["@link1"].Value = null;
}
If txtLink1 happens to be empty, I want @link1 to enter null. The
column in the Sql Server allows for nulls, but I get an error message
that says no value was supplied. In short, how do I supply a null value
using a parameterized query?
#2: For debugging purposes, how can I view what my SQL string looks
like (with all the values entered) before it gets submitted to the
database? When I view the string, it still contains the placeholder
values (@link1) instead of the actual values.
Thanks in advance!
-Brenden