Hello,
I have a business rule that says if PROD = ''XCDD' and ProductRollUp = 'XCSH' Then BillingType = 'Express Cash' or 'Payment Services'.
Is there a way to represent that rule with a CASE statement?
Thank you for your help!
cdun2
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.
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Thanks,
Jason
How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)
Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.
The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').
What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0
The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.
When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.
So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.
Does anyone know how I can make this work?
Example:
Select StoreId from tblStores
S1
S2
S3
S4
I want to display a more friendly name. I have been using a case statement:
select case
WHEN storeId = 'S1' THEN 'NY Store'
WHEN storeId = 'S2' THEN 'CA Store'
...
end AS StoreName
If I am doing a simple 1:1 mapping like that, is there a better way than using a case statement (without creating/modifying tables)?
How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked" ?
Thank you.
CREATE VIEW dbo.vwu_ReportViewASSELECT EmployeeID , LastName , FirstName , LocationCode , UserID , Today , Login , AMBreakOut , AMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS AMBreak , LunchOut , LunchIn , CASE WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66 THEN '0' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81 THEN '15' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96 THEN '30' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111 THEN '45' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126 THEN '60' ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon ,(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked
Hi there,
The following is my table whereby i have joined projects table with issue table (this is 1 to many relationship).
I have the following query:
SELECT
odf.mbb_sector sectorid,
SUM(case when odf.mbb_projecttype = 'lkp_val_appl' then 1 else 0 end) total_appl,
SUM(case when odf.mbb_projecttype = 'lkp_val_infrastructure' then 1 else 0 end) total_infra,
SUM(case when odf.mbb_projecttype = 'lkp_val_eval' then 1 else 0 end) total_eval,
SUM(case when odf.mbb_projecttype = 'lkp_val_subproject' then 1 else 0 end) total_subprj,
SUM(case when odf.mbb_projecttype = 'lkp_val_nonit' then 1 else 0 end) total_nonit,
SUM(case when odf.mbb_projecttype = 'lkp_val_adhocrptdataextract' or
odf.mbb_projecttype = 'lkp_val_productionproblem' or
odf.mbb_projecttype = 'lkp_val_maintwoprogchange' then 1 else 0 end) total_others,
COUNT(distinct prj.prid) total_prj
FROM
PRJ_PROJECTS AS PRJ,
SRM_PROJECTS AS SRM,
ODF_CA_PROJECT AS ODF
LEFT JOIN RIM_RISKS_AND_ISSUES AS RRI ON RRI.pk_id = odf.id
WHERE
prj.prid = srm.id
AND srm.id = odf.id
AND srm.is_active =1
AND odf.mbb_projecttype not in ('lkp_val_budget','lkp_val_itpc')
AND odf.mbb_funcunit = 'lkp_val_operation'
GROUP BY
odf.mbb_sector
which returns me the following result :
.
The problem is at the lkp_val_infosystem where it returns 3 instead of 1 in the total_infra column. How do I correct my case stmt to return the correct no of projects breakdown by different project type? Currently, only the total_prj which returns correct data.
Thanks
I'm new to SQL Server and would like to add a calculated column to this query from the report writer in our ERP system based on the NextFreq case statement result.
Basically, I want to create a column called service with result as follows:
If IV.meter > NextFreq then the result should be 'OVERDUE'
If (NextFreq - IV.meter) <50 then the result should be 'DUE SOON'
Otherwise the result should be 'NOT DUE'
This is the code from the current report writer query:
Select IV.item, IV.meter, isnull(wt.name,0)as name, case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int) > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq,
case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100
[Code] ....
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
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!
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
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
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
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies View RelatedHello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies View RelatedI am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies View RelatedI am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 Replies View RelatedI'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.
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
I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies View RelatedHi 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
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
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
Hi All
Have you got the same result as I have?
select convert(varchar(15), 0x4861707079204368726973746D6173)
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);
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 RelatedHello,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 RelatedDear 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
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.