Max Values In A Select Statement

Getting Different Values From Select Statement
I am running a select statement against a function that is giving me different values depending on how it is called. When I run it through sql server management studio, I get the proper results, the default value column has the parameters default value. When I call it through my web app, I get this in the default value column:
*** ERROR OCCURRED level 2 (this is not a default value) ***

Why would the same sql statement get different results?

Here is the call:
select * from dbo.f_GetSProcParameters('webservices_BENEFICIAL_USES_DM_SELECT')

Here are the two functions:
---------------------------------------------------
USE [si_training_db]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetSProcParameters] Script Date: 06/13/2008 09:29:21 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[f_GetSProcParameters](@StoredProcedureName VARCHAR(128) )

Returns @sProcParameters Table(ParmName VarChar(128),
DefaultValue VarChar(128),
HasDefault Bit,
IsInput Bit)



/*
* DESCRIPTION : This function returns a table listing all the parameters of a stored
*procedure and the default values of those parameters.
*
* RETURNS : table
*
*
* ORIG AUTHOR : Josh Kinder
* DATE WRITTEN : 3/14/2006
*
* REVISIONS LOG
*
*ID/Date PC# Description
*------- --- ----------------------------------------------------------
*/


As

Begin

Declare
@Count SmallInt,
@Index SmallInt,
@CurParm VarChar(128),
@DefaultVal VarChar(128),
@IsInput BIT


/*----------------------------------------------------------------------------------------------------------------------------------*/
--EDIT CHECK -sProc only
/*----------------------------------------------------------------------------------------------------------------------------------*/

-- Check that the proc name is valid
If OBJECT_ID(@StoredProcedureName, 'P') Is Null
Begin
Goto ScriptErr
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/




Declare @ParmTable Table (Id SmallInt Identity Primary Key Clustered, ParmName VarChar(128))

Insert Into @ParmTable
Select
a.Name
From
SysColumns a
Inner Join SysObjects b On b.Id = a.Id
Where
b.Name = @StoredProcedureName

Select
@Count = Count(Id),
@Index = 1
From
@ParmTable


While (@Index<=@Count)
Begin

Select @CurParm = ParmName From @ParmTable Where Id = @Index

Set @DefaultVal = dbo.f_GetsProcParamDefaultValue(@StoredProcedureName,
@CurParm)

SELECT@IsInput = CASE WHEN params.is_output = 1 THEN 0 ELSE 1 END
FROMsys.procedures AS procs
INNER JOINsys.all_parameters AS params ON params.object_id = procs.object_id
LEFT JOINsys.types AS types ON types.system_type_id = params.system_type_id
AND types.user_type_id = params.user_type_id
WHEREprocs.is_ms_shipped = 0
AND params.name = @CurParm
AND procs.name = @StoredProcedureName

Insert Into @sProcParameters
(
ParmName,
DefaultValue,
HasDefault,
IsInput
)
Values
(
@CurParm,
@DefaultVal,
Case When @DefaultVal = 'NoDefaultExists' Then 0 Else 1 End,
@IsInput
)

Set @Index = @Index + 1
End

ScriptErr:
Return

End

---------------------------------------------------
USE [si_training_db]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetsProcParamDefaultValue] Script Date: 06/13/2008 09:30:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[f_GetsProcParamDefaultValue]
(
@StoredProcedureName VarChar(128),
@ParamName VarChar(128)
)

ReturnsVarChar(128)

/*
* DESCRIPTION : This function returns a table listing all the parameters of a stored
*procedure and the default values of those parameters.
*
The following copyright info is for the parsing algorithm to get the default value.
I obtained the code from SQL Farms Solutions and their only stipulation for use is
that the copyright info remain with the code. Although I customized it for us, it is
still their algorithm.

==================================================================================
Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
This code may be used at no charge as long as this copyright notice is not removed.
==================================================================================

* RETURNS : table
*
*
* ORIG AUTHOR : Josh Kinder
* DATE WRITTEN : 3/14/2006
*
* REVISIONS LOG
*
*ID/Date PC# Description
*------- --- ----------------------------------------------------------
*/

As

Begin

Declare
@minRow Int,
@maxRow Int,
@tmpInt Int,
@tmpText VarChar(4000),
@tmpCharPos1 Int,
@tmpCharPos2 Int,
@tmpCharPos3 Int,
@ParameterDefault VarChar(128),
@sProcTextVarChar(4000),
@DelimiterChar(1),
@Count SmallInt,
@Index SmallInt,
@CurTextVarChar(4000)

Select
@minRow = Null,
@maxRow = Null,
@Delimiter = char(13)

Declare @ProcText Table (Id Int Identity(1, 1) Primary Key Clustered,
ProcText VarChar(4000))

Insert Into @ProcText
Select
a.Text
From
SysComments a
Inner Join SysObjects b On b.Id = a.Id
Where
b.Name = @StoredProcedureName


Select
@Count = Count(Id),
@Index = 1
From
@ProcText


While (@Index<=@Count)
Begin

Select @CurText = Coalesce(@CurText,'') + ProcText From @ProcText Where Id = @Index

Set @Index = @Index + 1

End


/*----------------------------------------------------------------------------------------------------------------------------------*/
--EDIT CHECKS
/*----------------------------------------------------------------------------------------------------------------------------------*/

-- Check that the parameter name is valid for the proc
If Not Exists(
Select
1
From
INFORMATION_SCHEMA.PARAMETERS
Where
SPECIFIC_NAME = @StoredProcedureName
And PARAMETER_NAME = @ParamName
)
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 1 (this is not a default value) ***'
Goto ScriptErr
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/




/*----------------------------------------------------------------------------------------------------------------------------------*/
--Get sProc into a workable temporary table
/*----------------------------------------------------------------------------------------------------------------------------------*/

Declare @ProcContent Table (Idx Int Identity(1, 1) Primary Key Clustered,
ProcText VarChar(4000))

Insert Into @ProcContent
Select Value From dbo.f_Split(@CurText, @Delimiter, 1, 0)

-- Make sure that some rows were returned successfully
If @@ROWCOUNT = 0
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 2 (this is not a default value) ***'
Goto ScriptErr
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/





/*----------------------------------------------------------------------------------------------------------------------------------*/
--Get location of parm and get ready to parse
/*----------------------------------------------------------------------------------------------------------------------------------*/

-- Get the first line where the parameter is referenced in the proc code.
-- (the LIKE here is a little complex since it is possible that multiple parameters
-- will start with the same string. Most cases of interest are covered by the
-- conditions listed below).
Select
@minRow = Min(Idx)
From
@ProcContent
Where
ProcText Like '%' + @ParamName + ' %'
Or ProcText Like '%' + @ParamName + Char(9) + '%'
Or ProcText Like '%' + @ParamName + Char(10) + '%'
Or ProcText Like '%' + @ParamName + Char(13) + '%'
Or ProcText Like '%' + @ParamName + '=%'
Or ProcText Like '%' + @ParamName + '%=%'
Or ProcText Like '%' + @ParamName + ',%'

-- Check that the parameter is referenced in the code
If @minRow Is Null
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 3 (this is not a default value) ***'
Goto ScriptErr
End



-- Get the proc line where the word 'AS' is declared. 'AS' is required
-- upon proc creation to complete the variable declaration.
-- Note: This cover most cases of interest. There could be scenarios where
-- additional condition should be applied.
Select
@maxRow = Min(Idx)
From
@ProcContent
Where
ProcText Like '% AS'
Or ProcText Like '% AS '
Or ProcText Like '% AS' + Char(9)
Or ProcText Like '% AS' + Char(10)
Or ProcText Like '% AS' + Char(13)
Or ProcText Like 'AS %'
Or Upper(RTrim(LTrim(ProcText))) = Char(10) + 'AS'
Or Upper(RTrim(LTrim(ProcText))) = 'AS'
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) + Char(10)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) + Char(13)

-- Check that the 'AS' string was found successfully
If @maxRow Is Null
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 4 (this is not a default value) ***'
Goto ScriptErr
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/






/*----------------------------------------------------------------------------------------------------------------------------------*/
--Parse and get the default value
/*----------------------------------------------------------------------------------------------------------------------------------*/

-- Get the first proc line of code where the parameter is referenced, for string processing,
-- and append to it all proc rows until the 'AS' string
Select
@tmpText = LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' '
From
@ProcContent
Where
Idx = @minRow




While @minRow < @maxRow
Begin
Set @minRow = @minRow + 1

Select
@tmpText = @tmpText + ' ' + LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' '
From
@ProcContent
Where
Idx = @minRow
End



-- Find the position of the parameter name. Delete all text before that position.
Set @tmpInt = Null
Set @tmpInt = PatIndex('%' + @ParamName + '%', @tmpText) - 1
Set @tmpText = Right(@tmpText, Len(@tmpText) - @tmpInt)



-- At this point we are nearly done:
-- We check whether the character '=' comes before the 'AS' or ',' string
-- If not- the parameter has no default value.
-- If so, we continue to find the value of the default parameter

Set @tmpCharPos1 = PatIndex('%=%', @tmpText)
Set @tmpCharPos2 = PatIndex('%,%', @tmpText)
Set @tmpCharPos3 = PatIndex('% AS %', @tmpText)



If @tmpCharPos1 <= 0
Or (@tmpCharPos1 > @tmpCharPos2 And @tmpCharPos2 > 0)
Or (@tmpCharPos1 > @tmpCharPos3 AND @tmpCharPos3 > 0)
Begin
-- The column does not have a default
Set @ParameterDefault = 'NoDefaultExists'
End
Else
Begin
-- Column has a default and it is left to find it.
-- First chop the string until the '=' character
Set @tmpInt = NULL
Set @tmpInt = PatIndex('%=%', @tmpText) - 1
Set @tmpText = LTrim(Right(@tmpText, Len(@tmpText) - @tmpInt))

-- Now, we p*** the remaining string until we get a ',' or a ' ' character
Set @tmpCharPos1 = NULL
Set @tmpCharPos2 = NULL

Set @tmpCharPos1 = PatIndex('%,%', @tmpText)
Set @tmpCharPos2 = PatIndex('% %', @tmpText)

Set @tmpInt = NULL


If @tmpCharPos2 > @tmpCharPos1 AND @tmpCharPos1 > 0
Set @tmpInt = @tmpCharPos1 - 1
Else
Set @tmpInt = @tmpCharPos2 - 1


If @tmpInt <= 0
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 5 (this is not a default value) ***'
Goto ScriptErr
End


Set @ParameterDefault = SubString(@tmpText, 1, @tmpInt)



-- If the parameter default is a string, then we will have an '' at each side of it.
-- These last lines of code will get rid of the ''.
If Len(@ParameterDefault) >= 1
If Right(@ParameterDefault, 1) = ''''
Set @ParameterDefault = Left(@ParameterDefault, Len(@ParameterDefault) - 1)

If Len(@ParameterDefault) >= 1
If Left(@ParameterDefault, 1) = ''''
Set @ParameterDefault = Right(@ParameterDefault, Len(@ParameterDefault) - 1)
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/






Goto ScriptExit

ScriptErr:

ScriptExit:

Return @ParameterDefault

End

View Replies !

---

Select Statement When Values Are Not Null
Hi.
I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format :
"number | name", but if there is a null value in the number it will display only the name and vice versa.
How can I do it ?
Any help is appreciated.

View Replies !

---

Use Listbox Values To Do Select Statement
Hi,
I have SQL database 2000 which has one table Sheet1, I retrieved the columns in the ListBox, then chosed some of them and moved it to ListBox2.
The past scenario worked great, and I checked the moved values, it was succesfully moved, but when I tried to copy the values in ArrayList to do a select statement it didn't worked at all.
public string str;protected void Button3_Click(object sender, EventArgs e)
{ArrayList itemsSelected = new ArrayList(); string sep = ",";
//string str;for (int i = 0; i < ListBox2.Items.Count; i++)
{if (ListBox2.Items[i].Selected)
{
itemsSelected.Add(ListBox2.Items[i].Value);
}
int itemsSelCount = itemsSelected.Count; // integer variable which holds the count of the selected items.
str = ListBox2.Items[i].Value + sep;
Response.Write(str);
}
 SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=stat_rpt;Integrated Security=True;Pooling=False");
String SQL1 = "SELECT " + str + " from Sheet1"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon);
SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataTable Dt = new DataTable();
Adptr.Fill(Dt);
//return Dt;
GridView1.DataBind();
SqlCon.Close();
}
I did some changes and the new error message is
Incorrect syntax near the keyword 'from'.
Thank you

View Replies !

---

Return Select Statement Or Values Using SqlDataSource?
Hello all,
 I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be?
 I have a SqlDataSource"
 <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)"
UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Action" Type="Byte" DefaultValue="2" />
</UpdateParameters>
</asp:SqlDataSource>
 And my SP:/* 09-19-07 Used to update affiliate profile */

CREATE PROCEDURE spUpsertAffiliateProfile
@Action tinyint,
@AffiliateId int,
@MemberId int = -1,
@CategoryId int,
@WebAddress varchar(50),
@Email varchar(50),
@Comments varchar(1500)
AS

SET NOCOUNT ON

-- Find errors first, check is not needed if deleting
IF @Action <> 3
IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%')
BEGIN
SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END
IF @Action = 1
IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId)
BEGIN
SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END


IF @Action = 1 AND @AffiliateId = 0-- insert
BEGIN
INSERT INTO tAffiliateMaster
(member_id, category_id, profile_web_address, profile_email_1, comments)
VALUES
(@MemberId, @CategoryId, @WebAddress, @Email, @Comments)

SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id
RETURN
END

ELSE IF @Action = 2 AND @AffiliateId > 0-- update
BEGIN
UPDATE
tAffiliateMaster

SET
category_id= @CategoryId,
profile_web_address=@WebAddress,
profile_email_1=@Email,
comments=@Comments

WHERE
affiliate_id = @AffiliateId AND member_id = @MemberId

SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id
RETURN
END

ELSE IF @Action = 3 AND @AffiliateId > 0-- delete
BEGIN
DELETE
tAffiliateMaster

WHERE
affiliate_id = @AffiliateId

SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id
RETURN
END
GO

 My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event?
 Thanks!
 

View Replies !

---

Adding Column Values Together In SQL SELECT Statement
I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin.  My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result.  My DB is SQL 2005 Express SP2.  My select statement is below:  SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescription AS CPU,
dbo.MFG_DATA_Parts_CPU.PartCost AS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescription AS Motherboard,
dbo.MFG_DATA_Parts_Motherboard.PartCost AS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescription AS RAM,
dbo.MFG_DATA_Parts_RAM.PartCost AS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescription AS HDD,
dbo.MFG_DATA_Parts_HDD.PartCost AS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescription AS OpticalDrive,
dbo.MFG_DATA_Parts_OpticalDrive.PartCost AS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescription AS Video,
dbo.MFG_DATA_Parts_Video.PartCost AS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescription AS OS, dbo.MFG_DATA_Parts_OS.PartCost AS OSCost,
dbo.MFG_DATA_Parts_Modem.PartDescription AS Modem, dbo.MFG_DATA_Parts_Modem.PartCost AS ModemCost,
dbo.MFG_DATA_Parts_FloppyDrive.PartDescription AS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCost AS FloppyDriveCost,
dbo.MFG_DATA_Parts_CardReader.PartDescription AS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCost AS CardReaderCost,
dbo.MFG_DATA_Parts_PowerSupply.PartDescription AS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCost AS PowerSupplyCost,
dbo.MFG_DATA_Parts_CaseType.PartDescription AS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCost AS CaseTypeCost,
dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride,
(dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost
+ dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost
+ dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost)
* ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100) AS SalePrice
FROM dbo.MFG_DATA_Machines LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CaseType ON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Motherboard ON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Video ON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_RAM ON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_PowerSupply ON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_OS ON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_OpticalDrive ON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Modem ON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_HDD ON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_FloppyDrive ON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CPU ON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CardReader ON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID  

View Replies !

---

Putting Commas Between Select Statement Values
Hello,

This may be a strange request, but I am going to ask about it anyways.

Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this:

NUMBERS
1
2
3
4

How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get:

1,2,3,4

Instead of:

1
2
3
4

Any ideas?

Thanks

View Replies !

---

Select Statement For Comma Separated Values
hi,
my sample SQL Server DB Tables are like,
SID Skill
--- -------
1 JAVA
2 ORACLE
3 C
4 C++

PID Skillset
--- ---------
1 1,2,3
2 2,4
3 1,2,3,4
4 3
I need the Query to display Person skills as follows...
PID Skillset
--- --------------
1 Java,Oracle,C
2 Oracle,C++
3 Java,Oracle,C,C++
4 C

and another query for Search..
if i give the search string as Java,C or i will pass the SID 1,3. i need to diplay the person records which contains the SID.

output will be...
PID Skillset
--- --------------
1 Java,Oracle,C
3 Java,Oracle,C,C++
4 C

or

PID Skillset
--- ---------
1 1,2,3
3 1,2,3,4
4 3
Plz help meee..
Thanking you in advance for your help.

View Replies !

---

Concatenating Column Values In SELECT Statement
I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression:

SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent
FROM Agents;

I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing.

Any help greatly appreciated. Please cc me privately so I receive your assistance at once!

TIA,
Arthur

View Replies !

---

'combining' Values From A Select Statement Into A String
Hi,

I got a really simple question here. Say I have a table with

ID, Name
1,  A1
2,  A2
3,  A3
....
10, A10

Now I want to combine the names into another table grouped by their ID (say 1-5, 6-10), so this new table has two names instead of 10:
A1 A2 A3 A4 A5
A6 A7 A8 A9 A10

Is there a function that allows me to 'combine' the names from a select statement?

Thanks in advance,
Steven

View Replies !

---

Zero Count Values Not Appearing In SELECT Statement
Hi all

I have the following tables:
 



Code Snippet
CREATE TABLE #Lkp_Circle
(
ID INT ,
Abbreviation varchar(50)
)
GO
CREATE TABLE #Lkp_OtherCircles
(
Circle varchar(50)
)
GO
CREATE TABLE #Tbl_User
(
ID INT,
Name VARCHAR(50),
IsActive bit
)
GO
CREATE TABLE #Tbl_UserDetails
(
AssociateID INT,
CircleID INT
)
GO
INSERT INTO #Lkp_Circle VALUES (1,'C1')
INSERT INTO #Lkp_Circle VALUES (2,'C2')
INSERT INTO #Lkp_Circle VALUES (3,'C3')
INSERT INTO #Lkp_Circle VALUES (4,'C4')
INSERT INTO #Lkp_Circle VALUES (5,'C5')
INSERT INTO #Lkp_Circle VALUES (6,'C6')
INSERT INTO #Lkp_Circle VALUES (7,'C7')
GO
INSERT INTO #Lkp_OtherCircles VALUES ('C3')
INSERT INTO #Lkp_OtherCircles VALUES ('C4')
INSERT INTO #Lkp_OtherCircles VALUES ('C5')
INSERT INTO #Lkp_OtherCircles VALUES ('C6')
GO
INSERT INTO #Tbl_User VALUES ( 101,'U 1','True')
INSERT INTO #Tbl_User VALUES ( 102,'U 2','True')
INSERT INTO #Tbl_User VALUES ( 103,'U 3','True')
INSERT INTO #Tbl_User VALUES ( 104,'U 4','True')
INSERT INTO #Tbl_User VALUES ( 105,'U 5','True')
GO
INSERT INTO #Tbl_UserDetails VALUES(101,3)
INSERT INTO #Tbl_UserDetails VALUES(102,4)
INSERT INTO #Tbl_UserDetails VALUES(103,5)
INSERT INTO #Tbl_UserDetails VALUES(104,5)
INSERT INTO #Tbl_UserDetails VALUES(105,3)
GO
 
 
 
SELECT ISNULL(Circle,'Total') Circle, ISNULL(COUNT([HeadCount]),SUM(1)) AS [Total]
FROM
(
SELECT DISTINCT 'Circle' = CASE
WHEN #Lkp_Circle.Abbreviation IN (SELECT Circle FROM #Lkp_OtherCircles) THEN #Lkp_Circle.Abbreviation
WHEN #Lkp_Circle.Abbreviation NOT IN (SELECT Circle FROM #Lkp_OtherCircles) THEN 'Others'
ELSE 'Total' END,ISNULL(#Tbl_UserDetails.AssociateID,0) AS 'HeadCount'
FROM #Tbl_User INNER JOIN #Tbl_UserDetails ON #Tbl_User.ID = #Tbl_UserDetails.AssociateID INNER JOIN
#Lkp_Circle ON #Tbl_UserDetails.CircleID = #Lkp_Circle.ID
WHERE #Tbl_User.IsActive='True' AND #Tbl_User.ID>0 AND #Tbl_UserDetails.AssociateID>0
) AS PivotTable
GROUP BY Circle
WITH Cube
 
DROP TABLE #Tbl_User,#Tbl_UserDetails,#Lkp_Circle,#Lkp_OtherCircles
 
----EXPECTED RESULT
--Circle HeadCount
--C3           2
--C4           1
--C5           2
--C6           0
--Others     0
--Total       5
--
----ACTUAL RESULT
--Circle HeadCount
--C3          2
--C4          1
--C5          2
--Total       5
 
 

The criteria for Others is that those circles which are not part of #Lkp_OtherCircles i.e. C1,C2,C3 and C7 clubbed together. I have tried checking for the condition ISNULL when for that circle there is no user but the end result is same. Can someone tell me where I am going wrong and how to correct it?
 
 

View Replies !

---

Overloading Select Statement Return Values
Hey guys,

This is what I think and hope is a fairly straight forward SQL question

Essentially, I have a table which has the following columns that are relevent to my question:

PROJID
ACTIVITY_NAME
COMPLETION_DATE

Rows in this table are, for example:

PROJID    ACTIVITY_NAME    COMPLETION_DATE
1              Prepro                   10/12/2007 3:42:30
2              Prepro                   10/13/2007 9:16:27
2              QA                        10/13/2007 10:00:01
2              Delivery                  10/14/2007 09:31:12
etc.

So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that).

(Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID).  In other words, ultimately, I would like to return something like this:

PROJID    LAST_ACTIVITY
----------------------------------------------
1              Prepro
2              Delivery

where the activity order (in this case) is Prepro, QA, Delivery.  So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro

I really appreciate the help

Thanks,
Steve

View Replies !

---

Display Custom Values In Select Statement
Hi,
I have a table called emp, having 2 field name & sex

values are:
name sex
a 1
b 2
c 1

now i want to display the values in above table as like below...
name sex
a Male
b Female
c Male


How to do that...?

View Replies !

---

SQLDataSource - How To Retreive The SQL Select Statement With The Values Of The Parameters
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

View Replies !

---

Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox.  The gridview renders fine if I only select one value from the listbox.  I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int.  If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
 
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here?  Thanks!

View Replies !

---

Change NULL Values To Default In SELECT Statement
I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
 
 

View Replies !

---

Return NULL Values In SELECT Statement With INNER JOIN ?
If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows. 
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value.  For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank.  Is that possible?

<code>
    Sub BindSearchGrid()
        Dim searchUnitID As String
        Dim searchQuery As String
        searchUnitID = tbSearchUnitID.Text
        lblIDNum.Text = searchUnitID
        searchQuery = "SELECT * FROM Assets " & _
        "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
        "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
        "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
        "INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
        "INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"

        Dim myReader As SqlDataReader
        myReader = Data.queryDB(searchQuery)
        While myReader.Read
            If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
            If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
            If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
            If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
            If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
            If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
            If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
            If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
            If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
            If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
            If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
            If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
            If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
            If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
            If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
            If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
            If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
            If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
            If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
            If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
            If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
            If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
            If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
            If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
            If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
            If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")

        End While
    End Sub</code>

View Replies !

---

Select Boolean Values Merged In A Text Statement
Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -

View Replies !

---

Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works
Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430' 
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha

View Replies !

---

Add Column With Fixed Number Of Values (text) To The Select Statement
Hello,
 
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.

View Replies !

---

Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization....
 
I have a query that looks like this:

 
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)

 
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View Replies !

---

Select Values From One Table Based Upon Values In Another...
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?

View Replies !

---

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View Replies !

---

Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View Replies !

---

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' 
FROM myTables
WHERE Conditions are true
ORDER BY Field01
 
The results are just as I need:
 

Field01           Field02

-------------          ----------------------

192473           8461760

192474           22810


 
Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' 
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

 Field02

----------------------

22810
8461760
 
And what I need is (without showing any other field):

Field02

----------------------

8461760
22810

 
Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View Replies !

---

How To Write Select Statement Inside CASE Statement ?
Hello friends,
      I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
    CASE E.EmployeeType
        WHEN 1 THEN
            select * from Tbl1
        WHEN 2 THEN
            select * from Tbl2
        WHEN 3 THEN
            select * from Tbl3
    END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View Replies !

---

Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID

I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID.  I have this select statement that works fine, but I am having trouble converting it to a delete statement:

SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180

View Replies !

---

Select Statement Problem - Group By Maybe Nested Select?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt 

View Replies !

---

SQL Select Statement To Select The Last Ten Records Posted
SELECT Top 10    Name, Contact AS DCC, DateAdded AS DateTimeFROM         NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database.  As you know each day someone could add one or two records, how can I write it show the last 10 records entered.

View Replies !

---

NULL Values In A SELECT In Another SELECT
Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks

View Replies !

---

Using Select Statement Result In If Statement Please Help
Hello
How can i say this I would like my if statement to say:  if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated

View Replies !

---

If STATEMENT Within Select Statement Syntax
Hi,

I am a newbie to this site and hope someone can help....

I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:

if(TL_flag= '1', "yes") as [Trial Leave]

it is coming up with an error.... I can use Select case but I should not need to as this should work?

Any ideas?

View Replies !

---

Case Statement Get Values
i have a case statement

CASE
when stuff then value
end as email

there are several instances where i come up with 2 values

is there a way to get these values distinctly

not email = value1 + "," + value2

but actually get value1 and value2 so i can use them as output without parsing in the program?

View Replies !

---

Can I Use SELECT Statement To Select First 100 Record????
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?

Thanks,

Sam

View Replies !

---

Selected Listbox Values In SQL Statement
I have seen some information on this but have not understood fully the answers.  I am fairly new to this and have spent the afternoon trying to work on this and although I have leant some really cool things I am no nearer.  How do I get selected values from a LIstbox into a SQL statement using the IN command  
First I loop through a List box control to get the multi selected values: Dim li As ListItemFor Each li In lbPClass.Items  If li.Selected = True Then    strPC += li.Value & ", "  End IfNextLabel5.Text = strPC The result in the lable looks something like: 100, 101, 102 , I get rid of the trailing blank and commar with:Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) I have created a parameter called @PROPCLASS that will use the values from the selected values in the listbox in a SQL query using an IN statement: Cmd.Parameters.Add(New OleDbParameter("@PROPCLASS", Label6.Text)) strSQL = "SELECT * FROM web_transfer WHERE ( PROP_CLASS IN (@PROPCLASS)) AND (ACRES >= @lowSize)AND (ACRES < @HighSize)  AND (YEAR = @lowYear)AND (SALE_PRICE > @lowPrice) AND (SALE_PRICE < @highPrice) " 
It is really the first bit of the WHERE clause I am interested in.  The sql statement works if I either type in the actual values IN( 100, 101,102) or if I select only one value in the list box.  If I select two then the sql statement does not work. 
My question is: How do I get the values selected in the List box into my SQL statement   Thank you in advance, s

View Replies !

---

SQL Statement - Query A List Of Values
Hello  I have a newbie question.  If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen 

View Replies !

---

SQL Statement Count NULL Values
Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

---

Get Unique Values In A Group Statement
Hi,
Suppose a table [Inventory]:

Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50

I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:


Table Blue,Red,Yellow 60
Chair Blue,Red 90

Does anyone has an idea how this can be achieved?

Regards,
Manolis Perrakis

View Replies !

---

Troubles Returning Values With SQL Statement -
Hi, I just want to know the following.

Is it posible to write a SQL statement that searches all values in a table where the

e.g:

"Jobid = 1" and sort the result set according to the "CategoryID"?

then if you want to display the values, you can populate them according to the "CategoryID"?

 

and then also write the result set to a "text file", and catagorize everything  according to the "categoryID" in the following format???

 

Category Number: 8                   Description: Site

  Date             Labour                     Hours                   Rate                 Subtotal

10/05/2005   Artisan                      20                        95.00                1,900.00
10/06/2005   Labourer                   7                           45                       315.00

       
                                                                                     Category Total 2,215.00

 

And then after that the next "categoryid"

Thank You, I really hope you understand what I mean.

your help will be greatly appreaciated.

View Replies !

---

Sql 2005 Multiple Values In WHERE Statement Question
Hi,I want to know if it's possible to put multiple values to a where statement in sql 2005 without the AND.I explain, is it possible to do? :WHERE Category = 1000, 2000, 3000, 4000, 5000, ...Or we must do :WHERE Category = 1000 AND Category = 2000 AND Category = 3000 AND Category = 4000 AND Category = 5000 ...I would like to do : WHERE Category = 1000, 2000, 3000, 4000, 5000, ...but i don't know if it's possible...

View Replies !

---

Need CASE Statement(s) To Populate 5 Fields Based On Values Of 2
I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.

What I have so far works, to an extent.  If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):

 

1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

Y  N  __ MD XX Y  N  __ MD XX N  __ __ __ __ Y  N  __ DN XX

 

If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement).  if they DON'T have that type of coverage, A always = N and the remaining field are NULL.

 

After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions.  Below is an sample of code representing the 1x statements.  This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.

 

CASE HB.PLAN_TYPE

WHEN '10' THEN 'Y'

ELSE 'N' END AS 1A,

CASE HB.PLAN_TYPE

WHEN '10' THEN 'N'

ELSE ' ' END AS 1B,

' ' AS 1C,

CASE HB.PLAN_TYPE

WHEN '10' THEN HB.BENEFIT_PLAN

ELSE ' ' END AS 1D,

CASE HB.PLAN_TYPE

WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'

WHEN HB.COVRG_CD ='2' THEN 'YY'

WHEN HB.COVRG_CD ='3' THEN 'ZZ'

ELSE 'WW' END)

ELSE ' ' END AS 1E,

 

It works to an extent.  While the desires/required output it above, the output this produces (same scenario) is:

 

1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

Y  N  __ MD XX N  __ __ __ __ N  __ __ __ __ N  __ __ __ __

 

1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

N  __ __ __ __ Y  N  __ MD XX N  __ __ __ __  N  __ __ __ __

 

1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

N  __ __ __ __ N  __ __ __ __ N  __ __ __ __ Y  N  __ DN XX

 

While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type.  I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile.  Far too much work to produce a file which is why I'm trying to convert it to raw SQL. 
Any assistance in this matter would be greatly appreciated.

View Replies !

---

WHILE Statement To Loop Through A Table And Get The IDENT_CURRENT Values As It Inserts
 

Hi
 
I have a SSIS package that imports data into a staging table from an excel sheet (This works fine). From the staging tabler i want it to insert the values into my members table, take that unique indentityID that gets created and insert the other values into other tables for that member that was just created.
 
In the staging table, i have all the values for a single member. But the structure of the database needs all the values inserted into seperate tables. There is no conditions ID in my members table, so the member first has to be created and from there i need to use the newly created member's MemberID and insert the conditions into a seperate table using the MemberID
 
I have created some sample data that can be used. I think i have an idea of how to do it, but i'm not totally sure if it will work that way, i have however included it in the sample data.

 



Code Snippet
DECLARE @ImportedStagingData TABLE
(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50),
[Chronic Heart Failure] INT,
[Colon Cancer] INT
)
INSERT INTO @ImportedStagingData VALUES ('Carel', 'Greaves', 'CarelG@Email.com', 1,0)
INSERT INTO @ImportedStagingData VALUES ('Jamie', 'Jameson', 'JamieJ@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Sarah', 'Bolls', 'SarahB@Email.com', 0,1)
INSERT INTO @ImportedStagingData VALUES ('Bells', 'Scotch', 'BellsS@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Stroh', 'Rum', 'StrohR@Email.com', 0,0)
DECLARE @Conditions TABLE
(
ID INT IDENTITY(1,1),
Condition VARCHAR(50)
)
INSERT INTO @Conditions VALUES ('Chronic Heart Failure')
INSERT INTO @Conditions VALUES ('Colon Cancer')
DECLARE @Members TABLE
(
MemberID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50)
)
DECLARE @memConditions TABLE
(
MemberID INT,
ConditionID INT
)
SELECT * FROM @ImportedStagingData
SELECT * FROM @Conditions
SELECT * FROM @Members
SELECT * FROM @memConditions
/* --- This is the part that i am battling with ---
DECLARE @CurrentValue INT
DECLARE @numValues INT
SET @numValues = (SELECT COUNT(ID) FROM @ImportedStagingData)
WHILE @numValues <> 0
BEGIN
INSERT INTO @Members
SELECT Name, surname, email
FROM @ImportedStagingData
GO
SET @CurrentValue = (SELECT IDENT_CURRENT('@ImportedStagingData'))
INSERT INTO @memConditions (MemberID), (ConditionID)
VALUES (@CurrentValue, --ConditionValue from @ImportedStagingData, all the values that have a 1)

@numValues = @numValues - 1
END
END
*/
 
 




All help will be greatly appreciated.
 
Kind Regards
Carel Greaves

View Replies !

---

Capture Null/blank Values In An Update Statement
I have built a SSIS package that bascially updates two columns in table A...the update statement reads;

update Table A
set Colum 1 = ?,('?' is a variable)
Column 2 = ?

In my SSIS package, I would like to be notified/capture, if one or both variables are null....How do I do that ?..error log ?

The package runs fine both ways (if variables are null or not)

Thank you

View Replies !

---

Select Some Values From A Row And Insert On Another Row
I have a table wih multiple records and some has common values.
For example, new child row need to inherit some of parent's data.
Here is how I want to do:
Select [parent data1], [parent data2] from [TABEL] where id = parent
Insert INTO [TABLE]  ([child id], [child data1], [child data2] ) VALUES [child  id] [parent id] [parent id]...........
I was thinking about using Selected and Inserting events but not sure how.
Please help?
 Thanks,

View Replies !

---

Select 'many Values' From One Column
I'm building a textbox with search functionality towards SQL 2000.
The textbox is to include various search phrases, like "phrase1 phrase2 phrase3". Sort of like search engines where all words are considered. Do keep in mind that it's SQL2000 and not 2005. I've got all the strings I want to perform the search against in one column but various values in this column.
The only thing I've come up with is to create "WHERE columnName LIKE @phrase1 OR columnName LIKE @phrase2" where I inject % as apropiate and chop up the string, but I'm hoping this is not the solution because it doesn't seem really clean (with 10 search words) and today I'm not having a 'bad-hack' day so I want it better.
Whats is the best way of doing this?
Cheers!
 /Eskil

View Replies !

---

How To Select From A List Of Values
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example:
selectcmd = "Select * from mytable where myfile =" ???????
cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
 
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values:
A
B
C
D
E
 
I would like to build a query like that:
selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
 
How can I do that?
Thanks.jsn
 

View Replies !

---

Query To ADD/SELECT Values From An SQL
Hello.

I need a query that will RETRIEVE a value from a database if it is present, but if the data isn't present, then the data will be INSERTed into the table.

Either way, I need the row returned at the end of the query.

I can do SELECT queries, but I don't have a clue as to how to proceed with branching statements.

For example:

User runs a query for "Canada".
Canada exists in the database, so the database returns Canada along with its ID.

Next user runs a query for "Chile".
Chile isn't in the database so a record is created and the ID (an IDENTITY field) is returned.

Does anyone know how I may accomplish this?

View Replies !

---

Select Minimum Between Two Values
Hello,I need to select the minimum between the result of a function and anumber, but i can't find a smart way. By now I'm doing like thefollowing, but I think is very expensive because it evaluates thefunction twice:select case when (myfunction())<100 then (myfunction()) else 100 endAny idea is appreciated.Thank youRegards--elimina carraro per rispondere via email

View Replies !

---

Select Row Values To Be Column Name
I am new to sql query and have a question.I would like to display row column values as column names.questions table consisted ofquestion_id, group_id, questionresponse table consisted ofresponse_id, group_id, question_id, answerI would like to display the answer responses under the columns ofquestions : such asresponse_id, question(1), question(2), question(3)...It will be greatly appreciated if someone help me to get this down.Jason

View Replies !

---

BCP Help -Null Values In Select
I am using a bcp command to cretae a text file.

set @bcpcommand = 'bcp "select med + replicate('' '',10-datalength(med)),ml+ replicate('' '',20-datalength(ml)),iname + replicate('' '',10-datalength(iname)) from copy_tbl" queryout "'+ @filename + '" -U -P -c'

exec master..xp_cmdshell @bcpCommand

Some of the values in the select statement are Null values and are getting skipped in the text file .My output looks like
At A02 At1E
AtE A03 At2E
c100 c1230
I want them to allign but the third row has a null in the middle so it skips it and put the third value in the seconds place

View Replies !

---

Select Same Values From A Column.
bm

View Replies !

---