Supress Result 0
Jul 20, 2005
I have the following code to SUM based on one field but want to be able
to not show rows which result is 0.
========================
SELECT Headers.employee_id AS Employee#,
SUM(CASE Headers.scan_type WHEN 'I' THEN 1 ELSE 0 END) AS SIP,
SUM(CASE Headers.scan_type WHEN 'F' THEN 1 ELSE 0 END) AS PUP,
SUM(CASE Headers.scan_type WHEN 'Q' THEN 1 ELSE 0 END) AS CMT,
SUM(CASE Headers.scan_type WHEN 'M' THEN 1 ELSE 0 END) AS CONS,
SUM(CASE Headers.scan_type WHEN '' THEN 1 ELSE 0 END) AS HIP,
SUM(CASE Headers.scan_type WHEN 'U' THEN 1 ELSE 0 END) AS HOP,
SUM(CASE Headers.scan_type WHEN '^' THEN 1 ELSE 0 END) AS INSTA,
SUM(CASE Headers.scan_type WHEN 'L' THEN 1 ELSE 0 END) AS ONRD,
SUM(CASE Headers.scan_type WHEN 'B' THEN 1 ELSE 0 END) AS POD,
SUM(CASE Headers.scan_type WHEN 'G' THEN 1 ELSE 0 END) AS PUX,
SUM(CASE Headers.scan_type WHEN '#' THEN 1 ELSE 0 END) AS ROP,
SUM(CASE Headers.scan_type WHEN 'J' THEN 1 ELSE 0 END) AS SOP,
SUM(CASE Headers.scan_type WHEN 'N' THEN 1 ELSE 0 END) AS STAT,
SUM(CASE Headers.scan_type WHEN 'R' THEN 1 ELSE 0 END) AS UNCON,
SUM(CASE Headers.scan_type WHEN '<' THEN 1 ELSE 0 END) AS
USPS_PSP,
SUM(CASE Headers.scan_type WHEN '"' THEN 1 ELSE 0 END) AS RIP,
COUNT (Scans.header_index) AS EmpTot
FROM Headers INNER JOIN
Scans ON Headers.header_index = Scans.header_index
WHERE (Scans.datetime BETWEEN '06/18/2003 00:00:00' AND
'06/19/2003 23:59:00')
GROUP BY Headers.employee_id
ORDER BY Headers.employee_id
===============================
Any ideas?
Thanks,
Manuel
--
Posted via http://dbforums.com
View 9 Replies
ADVERTISEMENT
Oct 4, 2007
Hi !
I am working on Converting existing Crystal reports to Reporting Services. Now I have a line in Crystal reports which has a formula for supression. I dont know whats the equivalent of supress in SSRS. I have tried Visibility as hidden but then it shows empty line but not supressed line.
View 2 Replies
View Related
Nov 11, 2003
I'm executing this dynamic query in the middle of stored proc to get the @hrs2 value:
select @sql=('select @hrs2 = sum('+quotename(@day2)+') from #pso_view where id = ')
select @sql= @sql+cast(@num as varchar(5))
EXEC sp_executesql @sql,N'@hrs2 int OUTPUT',@hrs2 OutPut
SELECT @hrs2
That works, but then it returns a row result with every exec and that messes up my desired row return from the contaner sp for my web control databind
Any tips on how to get the varible populated via the dynamic sql w/o rows return?
THANKS!!!!!!!!
View 2 Replies
View Related
Jan 31, 2007
How do I get a Report Table to supress blank rows from displaying ?
In Crystal Reports this option is available under Format Detail Section, supress blank section chekckbox.
View 9 Replies
View Related
Feb 12, 2007
Cannot find this anywhere while creating a report. Is this an option?
thanks.
View 2 Replies
View Related
Oct 5, 2001
Hi
Can we supress the System error messages ?
if yes how ?
for ex. In a table I am adding a row which is voilating primary key constarint ..I do not want system message for that ..
Server: Msg 2627, Level 14, State 1, Line 0
Violation of PRIMARY KEY constraint 'PK_b'. Cannot insert duplicate key in object 'b'.
The statement has been terminated.
I can write a user defined message for this purpose but I am not able to supress system message .
The procedure I am using is
CREATE PROCEDURE add_b
@num int,@empno int, @name varchar(6)
AS
INSERT INTO B (num, empno, ds ) values (@num,@empno,@name)
-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
RETURN(0)
END
GO
Sujit
View 2 Replies
View Related
Apr 23, 1999
I have a stored procedure where in a cursor I create a dynamic select
statement. If I have a non-zero result set (check @@rowcount) I output a message. The problem is I do not know how to supress the output of the
execute of the dynamically created select statement. All I want going to
output is the message. I know ISQL has this function, and so does EM. How do I do it totally within a stored procedure.
View 2 Replies
View Related
Jul 20, 2005
HiI have a dynamically constructed sql query that I want to execute, e.g.exec('select * from ' + @tablename)(1) Can I suppress the output somehow if this returns no values?(2) Can I use the result of this query in another query somehow? e.g.select(3) Can I control the size of the columns in the output somehowThanksF
View 3 Replies
View Related
Dec 26, 2006
I have several columns with expressions that return Nothing under certain conditions:
Example:=(IIF( Fields!solved.Value=False,IIF( Fields!TAT3.Value >120, IIF(Fields!TAT3.Value <144,Fields!Ticket.Value,Nothing),Nothing),Nothing))
As a result, I get tons of white/empty space, because 200 values were evaluated as Nothing and only 5 values/records were actual numbers.
How can I supress this Nothing value ?
View 4 Replies
View Related
Aug 24, 2007
Hello -
Does anybody know of a way to force SSRS to render a report with no page breaks at all? And better yet, to render as such but export using breaks (I know, thats asking a lot but the first part seems to be a reasonable request)
I have 1 report that renders a lot of data with no page breaks and cannot find a SINGLE property difference from the reports that do break...
Thanks in advace,
Gary
View 3 Replies
View Related
Feb 21, 2008
Is there any way to supress the popup window on the export option so that the first thing the user see's is the open/save dialog?
Alternatively
Is there a way to make the window close when the open/save dialog appears?
Thanks
Mike
View 7 Replies
View Related
Oct 16, 2007
Hi all!
Iīve having some issues with a package that Iīm currently building.
The package is pretty much finished and doing what itīs supposed to,
just doing some testing to ensure everything is in order.
The package is pretty basic:
Read a fixed with text file, do a lookup on destination database table,
conditional split to determine what to insert and what to update, and thatīs it!
At the source of my data flow I have the error output set to redirect all rows,
counting those rows, and finally writing them to a dynamically created file.
However, I just now realized that this file seems to be created ALWAYS,
regardless of rows actually being redirected or not. Everytime I run the package
with no rows being passed through the error output, I still end up with a
dynamically named, zero sized text file.
So my question: Is it possible to supress the creation of this file,
or do I have to add a file system component to my control flow,
deleting this file if my number of errorrows = 0 ?
Hope you can help!
Regards
Daniel
View 1 Replies
View Related
Jul 20, 2005
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies
View Related
Dec 26, 2007
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
View 5 Replies
View Related
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 Replies
View Related
Aug 2, 2007
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View 3 Replies
View Related
Apr 7, 2015
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday
select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],
[code]....
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
View 6 Replies
View Related
Mar 2, 2008
help strange result whan i do this
Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList
i try to get next unit value to next month
why i get this -1
on date
01/01/2008
1
-1
unit_date unit new_unit
01/02/2008
2
1
01/02/2008
1
4
01/01/2008
1
-1
01/02/2008
1
4
21/01/2008
1
-1
21/01/2008
1
-1
01/02/2008
1
4
TNX
View 3 Replies
View Related
Jan 28, 2008
Hi All, I need to For Each loop tied back to a SQL result set. This is what I have so far, I know its not much but I'm stuck. Thanks soooo much!TimDim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionStringconn = New SqlConnection(connectionstring)comm = New SqlCommand("Select Distinct([_Group_]) from _Smdba_._Groupdet_ Where [_InActive_:] = 0", conn)
Tryconn.Open()reader = comm.ExecuteReader()
For Each (FOR EACH SQL RECORD) 'I'm going to put some code in here.... reader.Close()Catch dberrorlabel.Text = "Error Loading Admin Panel"Finally conn.Close()End Try
View 3 Replies
View Related
Dec 2, 2003
hello everyone,
i have an sql statement which is like this
select f1,f2, ( sum(isNull(f3,0)) - (select sum(isNull(e1,0)) from otherTable where e2=Value) )
from Table where f3 = 4
it works great!
but there is a case it dont work it
when the subquery which is:
(select sum(isNull(e1,0)) from otherTable where e2=Value)
has no result it dont do the subtraction operation and the value is Null although the sum of F3 (which is subtracted from ) has a value
so is there any way i can give the sum value, the value of zero if there is no result in the record set.
and just for knowledge, i tried
sum(isNull(e1,0)
and
isNull(sum(e1),0)
the both cases and also it returns nothing
any one can help :)
Thank you
View 2 Replies
View Related
Mar 13, 2001
In 6.5 I ran a SELECT query with a specific WHERE clause. It gave me a result
set of 430 rows. But when I use the same conditions in WHERE clause in UPDATE,
it updated only 420.
What might be the reason?
Thanks,
Chan
View 1 Replies
View Related
Dec 22, 2006
Hi All
Have you got the same result as I have?
select convert(varchar(15), 0x4861707079204368726973746D6173)
View 2 Replies
View Related
Feb 22, 2006
HI EVERYBODY
YOU ALL REQUESTED TO HELP ME TO COMEOUT FROM PROBLEM : I HAVE A TABLE NAME PLUCK
IN WHICH DATA STORE: MONYEAR EMPCODE PDATE(ddmmyyy) JOBCODE HCD kg
022006 A01608P 01/02/2006 LA N 0
022006 A01608P 02/02/2006 LA N 0
022006 A01608P 03/02/2006 LA N 0
022006 A01608P 04/02/2006 LA N 0
022006 A01608P 05/02/2006 LH N 0
022006 A01693P 01/02/2006 PL N 21
022006 A01693P 02/02/2006 PL N 25
022006 A01693P 03/02/2006 PL N 15
022006 A01693P 04/02/2006 PL N 25
022006 A01693P 05/02/2006 LH N 0
BUT I WANT REPORT LIKE : 022006 A01608P LA N LA N LA N LA N LH N
022006 A01693P 21 N 25 N 15 N 25 N LH N
(IF JOBCODE = PL THAN INSTEAD OF JOBCODE KGPLUCK IS SHOWN..)
Pls guide how to write the SQL to achive the report...
Thanks
ASM
---sample data insert
USE NORTHWIND
CREATE TABLE PLUCK (MONYEAR CHAR(6), EMPCODE CHAR(7), PDATE SMALLDATETIME,JOBCODE CHAR(2), HCD CHAR(1), KGPLUCK INT)
INSERT INTO PLUCK VALUES('022006', 'A01608P','01/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','02/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','03/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','04/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','05/02/2006','LH','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01693P','01/02/2006','PL','N',21);
INSERT INTO PLUCK VALUES('022006', 'A01693P','02/02/2006','PL','N',25);
INSERT INTO PLUCK VALUES('022006', 'A01693P','03/02/2006','PL','N',15);
INSERT INTO PLUCK VALUES('022006', 'A01693P','04/02/2006','PL','N',25);
INSERT INTO PLUCK VALUES
('022006', 'A01693P','05/02/2006','LH','N',0);
View 2 Replies
View Related
Mar 12, 2008
Dear All,Can we do multiple result row as a single row while selection ..?Request you to provide the SQL if we can achieve this...For Eg,Table Name = Employeeid - EmployeeName - addresstype - address1 - address2 - City1 - AAA - permanent - 231 - First Street - XYA2 - AAA - Temporary - 343 - Second Street - XYAExpecting Result as a Single row as below,id - EmployeeName - Address1 - Address21 - AAA - 231/First Street/XYA - 343/Second Street/XYAThanks in advanceRgds,Ganapathi sundaram.G
View 1 Replies
View Related
Nov 28, 2006
Hello,Is there a way to merge the result of a query into one row?For example, let say I want the a list of name of people of 20 year soldMy result will look like that: NAME1 john2 Mike3 Craig I would like to have the result in one row: NAME1 John, Mike, CraigIs there a way to do that?Thanks
View 3 Replies
View Related
Dec 21, 2006
Dear Experts
Here is my code:
SqlConnectioncn = new SqlConnection(ConfigurationManager.ConnectionStrings["SimvipConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SP_SEARCH", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TypeOfSim", Request.QueryString["NID"]));
cmd.Parameters.Add(new SqlParameter("@CategoryID", Request.QueryString["CID"]));
cmd.Parameters.Add(new SqlParameter("@Price", Request.QueryString["L"]));
cmd.Parameters.Add(new SqlParameter("@Price2", Request.QueryString["S"]));
cmd.Parameters.Add(new SqlParameter("@Title", Request.QueryString["keyword"]));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter sqlAdapter1 = new SqlDataAdapter();
sqlAdapter1.SelectCommand = cmd;
DataSet ds = new DataSet();
sqlAdapter1.Fill(ds, "ds");
CollectionPager1.DataSource = ds.Tables["ds"].DefaultView;
CollectionPager1.BindToControl = DataList1;
DataList1.DataSource = CollectionPager1.DataSourcePaged;
}
My problem is when there is no result(no item in database), the DataList1 will display the text "some text will display here" instead of empty page.
In FormView I know the option <EmptyDataTemplate>. But in the DataList I don't know how to do this. I am a beginner.
Can anybody show me how....Thanks a lots
View 1 Replies
View Related
Jun 20, 2007
I have an account field which has datatype string. I want to only get those values which are in between 0-199.
I used
select sum(t.a_trans_amt) Credit from a_account a, a_transaction t
where a.a_account_num between '0' and '199'and
t.a_account_id=a.a_account_id
and t.a_debit_credit_ind='C'but this query also including those values which have starting 3 digit in between 0-199.
I don't know how to fix this problem . Can anybody help me on this issue.
Thanks in Advance.
View 3 Replies
View Related
Jan 17, 2008
Hi
I need help to build the below data to my requirement
i have table having the following data's
NoofLive
Action
Sold
Ratio
EffDt
10-49
43
0
0
1/1/2008
50-99
62
0
0
1/1/2008
100-199
73
0
0
1/1/2008
200-299
17
1
0.059
1/1/2008
300-499
25
0
0
1/1/2008
500-999
21
0
0
1/1/2008
1000+
45
0
0
1/1/2008
100-199
11
1
0.091
2/1/2008
1000+
1
0
0
2/1/2008
100-199
17
0
0
3/1/2008
500-999
2
0
0
3/1/2008
EffDt = (Jan-08,Feb-08,Mar-08)
I want creat result as follows from the above data's
Jan-08
Feb-08
Mar-08
Eligible Lives
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
10 - 49
43
0
0
0
0
0
0
0
0
50 - 99
62
0
0
0
0
0
0
0
0
100 - 199
73
0
0
11
0
0
0
0
0
200 - 299
17
1
0.059
0
0
0
17
0
0
300 - 499
25
0
0
0
0
0
0
0
0
500 - 999
21
0
0
0
0
0
0
0
0
1000+
45
0
0
1
0
0
2
0
0
Please anybody can help build a query to generate the above result.
Thanks in advance
sthanika
View 1 Replies
View Related
Mar 15, 2008
mail_delivery_master----------------------ml_id ml_from_mail_id ml_to_mail_id ml_user_id ml_subject ml_content ml_file_attached ml_no_of_file_attached ----------- ---------------------------------------- -------------- ------------ --------------------------------------------------------------------------------------------------------------------------------1 aa bb cc dd ee 2 joe@hotmail.com tt@gmail.com, JOHNYJP Forum answer somebody answer 0 1 3 joe@hotmail.com mailme@hotmail.com, JOHNYJP Forum answer somebody answer -1 0 4 joe@hotmail.com mailme@hotmail.com,janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 5 joe@hotmail.com janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 6 joe@hotmail.com dff@gg.com, JOHNYJP Forum answer somebody answer 0 0 7 joe@hotmail.com tt@gmail.com,janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 8 Durai@gamial.com yogesh@gamail.com durai test test 0 0 9 janani@ajsquare.net devi@ajsquare.net janu test hi 0 0 10 janani@ajsquare.net devi@ajsquare.net JANANI test Miss u -1 2 mail_attachementsml_id ml_file_name ml_file_format ml_file_size ml_file_select_from ----------- ---------------------------- -------------- ------------ ------------------------------------------ 1 chocolate .jpg 114528 F: empchocolate.pjg 2 Tiger Caspian Blue.jpg .jpg 1114407 F:imageTiger Caspian Blue.jpg 3 Autumn.jpg .jpg 66287 F:imageAutumn.jpg 4 Ascent.jpg .jpg 63244 F:imageAscent.jpg 5 Azul.jpg .jpg 61365 F:imageAzul.jpg 6 daisy.jpg .jpg 8197 F:imagedaisy.jpg 7 Stonehenge.jpg .jpg 59600 F:imageStonehenge.jpg i want the result :ml_id , ml_from_mail_id, ml_to_mail_id from mail_delivery_master ,,,, and sum(ml_file_size) for that mail from mail_attachements give me the code solution regards samuel chandradoss
View 2 Replies
View Related
May 18, 2008
Hi y'all, I have a problem in my query I think maybe you guys could help.I have this query:SELECT dbo.BOS_GL_Workplace.WpszState, Emp2.szName, Emp1.szName AS szEmpName, Emp1.szEmployeeId, dbo.BOS_PI_Division.szName AS Divisi, dbo.BOS_PI_Team.szDescription,dbo.BOS_SD_Route.szRouteId, dbo.BOS_SD_Route.szDescription, dbo.BOS_SD_Route.szScheduleId, dbo.BOS_SD_RouteItem.szCustId, dbo.BOS_SD_Route.szOpUserId,dbo.BOS_AR_Customer.szCustId ,dbo.BOS_AR_Customer.szName, dbo.BOS_AR_Customer.CustszAddress_1 AS SendAddres, dbo.BOS_AR_Customer.szTaxId, dbo.BOS_AR_Customer.CustszAddress_1 AS CollectAddress, dbo.BOS_AR_Customer.CustszCity, dbo.BOS_AR_Customer.CustszZipCode, dbo.BOS_AR_Customer.szStatus, dbo.BOS_AR_CustSales.szPaymentTermId, dbo.BOS_AR_CustSales.decCreditLimit,BOS_AR_Customer.dtmStart,BOS_AR_Customer.dtmLastUpdated FROM dbo.BOS_PI_Employee Emp1 LEFT OUTER JOIN BOS_PI_Employee Emp2 on Emp1.szSupervisorID = Emp2.szEmployeeID Left outer join dbo.BOS_GL_Workplace ON Emp1.szWorkplaceId = dbo.BOS_GL_Workplace.szWorkplaceId LEFT OUTER JOIN dbo.BOS_PI_Division ON Emp1.szDivisionId = dbo.BOS_PI_Division.szDivisionId LEFT OUTER JOIN dbo.BOS_PI_Team ON Emp1.szTeamId = dbo.BOS_PI_Team.szTeamId LEFT OUTER JOIN BOS_SD_Route on Emp1.szEmployeeId = BOS_SD_route.szOPuserId LEFT OUTER JOIN BOS_SD_RouteItem on BOS_SD_Route.szRouteId = BOS_SD_RouteItem.szRouteID LEFT OUTER JOIN BOS_AR_Customer on BOS_SD_RouteItem.szCustID = BOS_AR_Customer.szCUstID LEFT OUTER JOIN BOS_AR_CustSales on BOS_AR_Customer.szCustId = BOS_AR_CustSales.szCustID --order by BOS_PI_Employee_1.szNameWhen I query it's working fine the results is the way as I expected. But when I add where--and--clause like this :WHERE Emp2.szName >= 'JACK ANDREWS' AND Emp2.szName <='RICK COLLINS'it's only showing the header and no result at all, why ??? can you guys help me please, I appreciate any kinds of help. Thanks. FYI: I use SQL Server 2000 Best Regards.
View 3 Replies
View Related
Jul 10, 2004
Hi Ive just started with asp.net and this question is maby stupid but I cant find the error.
Problem: I am trying to make a loginpage with the username and password stored in a database. The page do not generates any error then runnin BUT it is not possible to log in. The sql-procedure always return -1 even if the username do exist.
--------------------------------------------
Function MyAuthentication(strUsername As String, strPassword As String) As Integer
Dim myConn As SQLConnection
Dim myCmd As SQLCommand
Dim myReturn As SQLParameter
Dim intResult As Integer
Dim conn As String
conn = ConfigurationSettings.AppSettings("Generala")
myConn = New SQLConnection(conn)
myCmd = New SQLCommand("MyAuthentication",myConn)
myCmd.CommandType = CommandType.StoredProcedure
myReturn = myCmd.Parameters.Add("RETURN_VALUE",SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
myCmd.Parameters.Add("@username",strUsername)
myCmd.Parameters.Add("@password",strPassword)
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters( "RETURN_VALUE" ).Value
myConn.Close()
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Visible = true
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Visible = true
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
Return intResult
End Function
-----------------------
SQL Procedure
----------------------
CREATE PROCEDURE MyAuthentication (@username As Char( 25 ), @password As Char( 25 )) As
DECLARE @actualPassword Char( 25 )
SELECT @actualPassword=Password FROM [User] WHERE user=@username
IF @actualPassword IS NOT NULL
IF @password = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
GO
-----------------------
Someone that can help me with and find the error?
View 4 Replies
View Related