MS SQL Server :: First Time SELECT CASE Statement User

CASE In Select Statement
Hi, i've got this stored procedure:
@LidFederatieNummer varchar(20),
@ClubID int,
@LidClubNummer varchar(50),
@PersoonNaam varchar(100),
@ClubStamnummer varchar(10),
@ClubNaam varchar(50),
@SeizoenID int,
@ShowAllJN bit,
@LidFederatieJN int,
@CategorieID int
AS
BEGIN

IF @CategorieID IS NOT NULL BEGIN
DECLARE @LicentieVerplicht int
SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
END

IF (@ShowAllJN = 0)
BEGIN
SET RowCount 100
END

CASE @LicentieVerplicht
WHEN '1' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 1
Order By Persoon
END

WHEN '2' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN)
ORDER BY Persoon
END

WHEN '3' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 0
Order By Persoon
END
According the value of my @LicentieVerplicht variable:
DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
My where clausule should change... how can i achieve this cause my case is not working because it is not inside the select statement

View Replies !

Case When In A Select Statement
Hi everyone,
I have the following problem withj this query:

SELECT DocNumber,
       Title,FirstName,LastName,Paper,
       ANNO,Location,Keywords,
       URL= case WHEN URL IS NULL THEN
'|<a
href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+
CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+
'&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' +
CONVERT(varchar,Firstname)+
'&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')>
Order through ILLiad</a>' ELSE URL END
FROM DocTable1 WHERE FREETEXT....

When URL is null, I don't get the string showing. It works properly only in the query analyzer.

Any ideas?

Christian

View Replies !

Select Within Case Statement?
Limitations: Needs to be single sql statement for exec, no udf/stored proc
Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals
if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty
if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg

What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice!

tblPerson: person_pk, affiliation_fk; home_city_fk, mail_goes_to
tblAffiliation: affiliation_pk, organization_fk
tblOrg: organization_pk, city_fk
tblCity: city_pk, county_fk
tblCounty: county_pk, s_fk

For resulting display only:
tblPersonArea: personArea_pk, area_fk, person_fk
tblArea: area_pk, areaDesc, division_fk
tblDivision: division_pk, divisionDesc

View Replies !

Case Statement Within A Select
Hey guys, i'm trying to use a CASE within a select but i'm not sure how to get started.

Here is my select so far, i'm inserting into a temp table that is used within a loop to gather data from my trigger:

Code:

insert into @temp_par
select CONVERT(varchar(20),d.PAT_ID),
ep.EXT_PAT_ID,
d.DRUG_DESC_ID,
d.ALLERGY_CD,
CONVERT(varchar(20),d.PAR_DT,120),
ISNULL(d.PAR_TXT, 'NULL'),
ISNULL(REPLACE(m.DRUG_NM,'"','^"'),'NULL'),
ISNULL(m.DOSAGE_FORM_CD,'NULL'),
ISNULL(m.STRGTH,'NULL'),
ISNULL(m.STRGTH_UM,'NULL')
from deleted d --PATIENT_PRIOR_ADVERSE_REACTIONS
join EXTERNAL_PATIENT ep on ep.PAT_ID = d.PAT_ID
join MEDNAME m on m.DRUG_DESC_ID = d.DRUG_DESC_ID

I would like to use a CASE for the d.ALLERGY_CD column. The data in this column is usually a 1,2,3,4,5 but i'd like to convert those to their real data, for example:

Code:

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

Any help?

View Replies !

Select Case Statement
INSERT INTO #rpt(Hic, DtImported, Source, PlanID, LastName, FirstName, MemID, CaseStatus)
        SELECT
s.Hic,
s.DtImported,
s.Source,
dbo.LastErolledPlan_C_D(s.Hic,s.IsPart_C) as PlanID,
m.LastName,
m.FirstName,
dbo.GetMemID_PartC_D(s.Hic,s.IsPart_C) as MemID,
s.CaseStatus = CASE WHEN CaseStatus ='1' or CaseStatus='0' THEN 'New'
                        CASE WHEN CaseStatus <>'1' or <> CaseStatus <>'0' Old

ElSE 'No Valid'
End

        from tbEsccSuspects s inner join tbPDMMembers m
        on s.Hic = m.HicNumber
        Where s.EUFStatus = 1 -- AND DtEUFSent is NULL

what I am doing wrong in the case statment that I am getting an error. ?????



View Replies !

Select Case Statement
Hi,

I'm wondering what syntax to use for a select case statement.

I have a list of questions. If a particular question is on the list, then it shouldn't show some other related questions.

For example, "have you been in the military?". If you haven't , then you wouldn't be asked all the military related questions.

So- I'm saying:
select case when question_id = 488 then don't return question_id 220, 962, 963

I'm trying something like this:

select q.client_id,
case when q.question_id = 488 then end q.question_id = 220 else q.question_id = 220 end q.question_id
from questions_yes_no q

How would I say 'dont show' question 220? End is not the way to go, obviously.

If you can see what I'm trying to do, can you point me to an article or bol search?

Thank you for any help.

View Replies !

Can Anyone Tell Me How To Use 'Case' Statement In Select Query In SQL ?
I need to pull one field from one table and one field from another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement???
is this correct ?? its showing me errors
Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ???
(case when 'DataSource =1' then 'ABC' then 'BCD' endcase)
Where .....
Pls correct me
Thanks

View Replies !

SQL Select Statement With Case SENSITIVE
Hi,I am wondering how do I write a query that will take case sensitive into consideration. For example, I have "ABCD", "abcD", ABcd", "AbCd" in a table. I want to retrieve "AbCd" only from the table. This is the sql statement that I have:sqlSelect = "SELECT * FROM [al_table] WHERE [alphabet]= @alpha"Dim selectCmd As New SqlCommand(sqlSelect, myConnection) selectCmd.Parameters.AddWithValue("@alpha", "AbCd") From what I have it will return all those value. But that is not what I want. Thanks

View Replies !

Using A CASE Statement Within A Select Query
Hi folks,

Hope you are all well.

I am using a CASE statement within a SELECT query to sum up values for different customers.

SELECT CR_CUST.Customer_Code,
'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END,
'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END,
'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END
FROM CR_CUST
INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code
INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code
WHERE (CR_PROD.Part_Class_Code = 1 OR
CR_PROD.Part_Class_Code = 7 OR
CR_PROD.Part_Class_Code = 8)
GROUP BY CR_CUST.Customer_Code,
CR_PROD.Part_Class_Code

The above query produces the following results...

Customer_Code General_01 General_07 General_08
------------- ---------------- ---------------- ----------------
02210 10074.30 .00 .00
02347 7606.49 .00 .00
02210 .00 12618.42 .00
02347 .00 13131.63 .00
02210 .00 .00 4505.44
02347 .00 .00 5018.03

My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so...

Customer_Code General_01 General_07 General_08
------------- ---------------- ---------------- ----------------
02210 10074.30 12618.42 4505.44
02347 7606.49 13131.63 5018.03

I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table.

SELECT Customer_Code,
SUM(General_01),
SUM(General_07),
SUM(General_08)
FROM #MyTempTable
GROUP BY Customer_Code

Thanks in advance,
Kev

View Replies !

Using Case In A Select Statement,urgent!
Hi All,

I need a hand in this script.
SELECT a.id ,value = CASE
when (x1-x0) = 0 then
((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0))
ELSE
((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0))
END
,value2 = CASE
when (x1-x0) = 0 then
((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0))
ELSE
((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0))
END
FROM table_name a
where value > 0
ORDER BY VALUE

The problem I'm having is in the where clause. Because the value is not a column name sql server is giving me an error. I need to evaluate the value returned. How can I get around this problem? Any sort of advise will help!
Thank you in advance!

View Replies !

Select Case Inside Sql Statement ?
Code:

function findingcinemaid(nameofthecinema)

findcinemaid = "select cinemasid from cinemas" &_
" where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'"
set cinemaidfound = objconndb.execute (findcinemaid)

end function

select case foreachcinema

case 0

cinemaname = "ONE UTAMA"
findingcinemaid(cinemaname)

case 1

cinemaname = "MINES"
findingcinemaid(cinemaname)

case 2

cinemaname = "SEREMBAN 2"
findingcinemaid(cinemaname)

case 3

cinemaname = "KINTA CITY"
findingcinemaid(cinemaname)

case 4

cinemaname = "BUKIT RAJA"
findingcinemaid(cinemaname)

case 5

cinemaname = "TEBRAU CITY"
findingcinemaid(cinemaname)

case 6

cinemaname = "SUNWAY PYRAMID"
findingcinemaid(cinemaname)

case 7

cinemaname = "SURIA KLCC"
findingcinemaid(cinemaname)

end select

any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it

View Replies !

T-SQL CASE Statement.. NOT In A SELECT Query - ??
How come SQL Server doesn't like the following?

CREATE PROCEDURE sp_myproc
(@myvar int)
AS
CASE @myvar
WHEN 1
    EXEC sp1
WHEN 2
    EXEC sp2
WHEN 3
    EXEC sp3
DEFAULT
    EXEC sp3
END

How can I code something like this legally in T-SQL?

Thanks
Jason

View Replies !

Case Does Not Work In My Select Statement
Hello everybody
I have problem with CASE statement. Here is select it

Code Block
select
mev.Id
,mev.MetaElementId
,mev.ElementValue
,mev.DocumentId
,me.ElementTypeId
,castedValue =
case
when me.ElementTypeId =3 then cast(mev.ElementValue as integer)
when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)
end
from dbo.tbMetaElementValue mev
inner join dbo.tbMetaElement me
on mev.MetaElementId = me.Id
where mev.MetaElementId =7

it returns

Id
MetaElementId
ElementValue
DocumentId
ElementTypeId
castedValue

49
7
2006
28
3
6/30/1905 0:00

53
7
2004
30
3
6/28/1905 0:00

61
7
2006
36
3
6/30/1905 0:00

67
7
2005
38
3
6/29/1905 0:00

70
7
2004
39
3
6/28/1905 0:00

105
7
2003
63
3
6/27/1905 0:00

166
7
2006
109
3
6/30/1905 0:00

195
7
2005
129
3
6/29/1905 0:00

220
7
2005
150
3
6/29/1905 0:00

223
7
2006
151
3
6/30/1905 0:00

As you can see it should return castedValue as integer but it cast to datetime which is wrong. If I commented line

Code Block
when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)

it casts everything normal, but as soon as it has more than one condition in CASE it will choose anything but not right casting

Looks like I am missing something really fundamental. Any help is apreciated !

Thanks

View Replies !

Case Sensitive Search In Sql Select Statement?
Hi ALL,
    How could you make the SQL Select statement query case sensitive, what i mean is i have this select statement below ...
Select * From Staff WHERE Staff.Staff_ID = 'KabirJ' AND Staff.Password = 'KaBir1!'
and a data row in the staff table that is the following information
Staff_ID      Password
Kabirj          kabir1!
Since the cases in database is different then in the string provided, i want sql to return me nothing, How do i go about and achieve that???????????
Thanks
Kabir

View Replies !

Need Help Converting A Select Query Into A Case Statement
I have the following query:

(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')

I need to convert this into a case statement.
I tried various ways but did not get the result that I was after

Thanks,
Laura

View Replies !

Case Statement Within A Select Where 2 Or More Instances Of The Record Exist.
Ok,I have a data warehouse that I am pulling records from using OracleSQL. I have a select statement that looks like the one below. Now whatI need to do is where the astrics are **** create a case statement orwhatever it is in Oracle to say that for this record if a 1/19/2005record exists then End_Date needs to be=1/19/2005 else getEnd_Date=12/31/9999. Keep in mind that a record could have both a1/19/2005 and 12/31/9999 instance of that account record. If 1/19exists that takes presedent if it doesnt then 12/31/9999. The problemis that the fields I pull from the table where the end_date is inquestion change based on which date I pull(12/31/9999 being the mostrecient which in some cases as you see I dont want.) so they are notidentical. This is tricky.Please let me know if you can help.SELECTCOLLECTOR_RESULTS.USER_ID,COLLECTOR_RESULTS.LETTER_CODE,COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,COLLECTOR_RESULTS.ACTIVITY_DATE,COLLECTOR_RESULTS.BEGIN_DATE,COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,COLLECTOR_RESULTS.PLACE_CALLED,COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,COLLECTOR_RESULTS.ORIG_FUNC_AREA,COLLECTOR_RESULTS.ORIG_STATE_NUMBER,COLLECTOR_RESULTS.CACS_FUNCTION_CODE,COLLECTOR_RESULTS.CACS_STATE_NUMBER,COLLECTOR_RESULTS.STATE_POSITION,COLLECTOR_RESULTS.TIME_OBTAINED,COLLECTOR_RESULTS.TIME_RELEASED,COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,FROMCOLLECTOR_RESULTS,COLLECT_ACCT_SYS_DATA,COLLECT_ACCOUNTWHERECOLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)ANDCOLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)AND COLLECTOR_RESULTS.ACTIVITY_DATE =to_date(''01/19/2005'',''mm/dd/yyyy'')AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************

View Replies !

Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:

SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate

I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.

Thanks!

View Replies !

Need Help With SQL Statement (combined SELECT And WHERE At Same Time)

I am building a file repository page. For data source I use SQLDataSource and Repeater to display the categories. I am trying to achieve the result of displaying the following data: List of all "Main Categories" and all "Sub Categories" under each main category name. Also, "Total Number of files" and "Category Description" under each "Main Category".
Sample:

Human Resources
All documents related to new hire process.
New Hire Request forms, New Hire forms, Termination Forms
Total Files: 25

Programs & Utilities
Windows utilities and other tools to keep you safe on the net.
Antivirus, Spyware Removal Tools, Other programs
Total Files: 37

My SQL2005 DB has 2 tables.

1) FileCategories. Fields(CategoryID, ParentCategoryID, CategoryName, CategoryDescription)
2) Files. (CategoryID, ParentCAtegoryID, FileTitle)
My "Main Categories" are the ones that has a NULL in ParentCategoryID field. Because they are the Parents.
My each "Sub Category" has it's own ID as well as parentCategoryID. This insures that each Sub Category corresponding to it's parent only.
Sample:

Human Resources. CategoryID=4, ParentCategoryID=NULL
New Hire Request forms. CategoryID=13, Parent CategoryID=4
New Hire Forms.                CategoryID=17, Parent CategoryID=4
Termination Forms.           CategoryID=22, Parent CategoryID=4

Programs & Utilities. CategoryID=6, ParentCategoryID=NULL
Antivirus.                                 CategoryID=8, Parent CategoryID=6
SpyWare Removal Tools.     CategoryID=26, Parent CategoryID=6
Other Programs.                    CategoryID=31, Parent CategoryID=6

When I am creating a sql Select statement first I need to choose All "CategoryIDs" where "Parent category" is NULL. Then All "CategoryIDs" where "Parent categories" are not NULL and corresponding to Main Categories.
Is it something accomplishable with SQLdataSource or I have to use ObjectDataSource or something else.
I'll be on the site all day today (10/29/07) and tomorrow (10/30/07) checking for replies. Thanks in advance and let me know if you have any questions.

View Replies !

Select Statement With Run Time Field Selection

I have this SELECT statement.

SELECT [issueID], [name] FROM [MyIssue]

What I wanted to do is in addition to the above statement, I want to add two run time fields like this:

99 [issueID],'All Issues' [name]

So let's say the above select statements generates this list:

Summer 2007 Issue
Winter 2007 Issue

The two addition fields will make the result list like this:

01   Summer 2007 Issue
02   Winter 2007 Issue
99   All Issues

How do I accomplish this? Any help is much appreciated.

View Replies !

Convert A Time Field In The Select Statement Of The Query
Hi,

I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.

Can anyone help me please?

Thx

View Replies !

Select Statement For User Pyramid
I have a user table for an organization that can best be described as a pyaramid. We are using the terms parent and child to define the relationship between users. Each user is a child under a specific parent; accept for the top person who is not a child under anyone. Each user can be a parent over many children.

User A has a parent - User B
User B has a parent - User C
User C has a parent - User D
User D has not parent

I want to do is create a select statement that would return all the parents for a particular user. For instance, if I ran the select statement for User A, it would return:

User A
User B
User C
User D

If I ran it for User C, it would return:
User C
User D

Ideas anyone on how I can accomplish this.

View Replies !

Using User Input As Part Of A SELECT Statement
I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks

View Replies !

Gridview Select Statement With User.identity.name In The Where Clause
Hello everyone,
I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches.
So far i have tried:
SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' "
with no success.
Any help is appreciated

View Replies !

How To Use A User Defined Stored Procedure/function In A Select Statement
How to use a User defined Stored Procedure/function In a Select statement.
eg. Select myproc(column1) from table1
How to register user defined functions( dlls) in SQL Server6.5/7 .
with Regards
K.Nageswara Rao

View Replies !

How To Make Select Statement With &"case Sensitive&"
Hi,

I am using SQL Server 2005 now.

I have to queries.that are

select 8 from testdb where name = 'TONY'

select 8 from testdb where name = 'tony'

the two queies get same results now. but 'TONY' and 'tony' are different if we need 'case sensitive'.

how to make the above queries get different result

My test data like following (testdb table)

name phone
TONY 12345
tony 67890

Thanks

Mark

View Replies !

Problem Using Result From CASE In Another CASE Statement
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

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 !

Case Statement Error In An Insert Statement
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View Replies !

How To Show Records Using Sql Case Statement Or If Else Statement
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows

as per data in MS Sql

my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --

2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no

3)Other_project
fields are -- other_proj_id,other_custname,po

for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id   custname contract_no field_no
    1                abc           234         66
    2             xyz             33            20

Other_project
============
other_proj_id     other_custname     po
      1        xxcx              111
      2        dsd                   222

material
=========
material_id         project_type     project_id          qty
    1                    AB Corporate 1              3
    2                     Other Project   2                7

i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project

sample query i write :--

select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
    select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op

but this query not work,also it gives errors

i want sql query to show data as follows

material_id      project_type       project_id      custname other_custname   qty
   1                   AB Corporate         1                        abc                        --                      3
   2                 Other Project            2                         --                         dsd                   7

so plz help me how can i write sql query for to show the output
plz send a sql query

View Replies !

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.
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.

View Replies !

How To Create Key Time Column And Key Column For A Case Table And A Nested Table For Time Series Algorithm?
Hi, all experts here,

Thanks for your kind attention.

I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View Replies !

Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm
Hi, all experts here,

Thank you very much for your kind attention.

I am confused on key column of case table and key time column of nested table by using Time Series algorithm.

In my case, the case table structure is as below:

Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),

While the nested table structure is as below:

Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)

So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?

Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?

Hope it is clear for your advices and help.

And I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View Replies !

Case Statement
Hi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.

View Replies !

Case Statement In Sql Help
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!

View Replies !

What Is The Best SQL Statement For This Case ?
Hi !!!i hope one of the sql specialists answer me about the best and most effeceint way to acheive what i am looking for Scenario:-------------i have a 3 tables related to each other Addresses, Groups and GroupAddressthe relation is for both addresses and groups is one to many in the GroupAddress.the behaviour in the application : user can add addresses to his address list and from the address list a user can add an address to many groups like if you have Group name "Freinds" and you add me in it and you have Football team group and you add me to it like that !!!not i have another function called "copy group"in the GroupAddress i have this data as example GroupID AddressID1 41 61 21 441 72 82 62 93 133 73 10and the group ID called "Freinds"i want to copy the group so i can have another group that has the same addresses by one click rather than collectiong them again one by one ...by the way the new copy will have a new group name ( as this is thebusiness logic so user can not have dupicate group name )so what is the best SQL statement that i need to copy the group ???i hope that clear enough!

View Replies !

Sql Case Statement Help
Hi all,
I was wondering if there is any way in an sql statement to check whether the data your trying to get out of the DB is of a particular type, ie. Int, char etc. I was thinking about a case statement such as
<code>
CASE WHEN (MyNum <> INT) then 0 end AS MyNum
</code>

This has to be included in the sql statement cause I need this field to get other data.
Any thoughts on how to achieve this would be greatly appreciated.

If Iâ€™m in the wrong thread section please advise of best one to get help in.

View Replies !

Case Statement
i am working on a sql statement that is kind of weird
i have a table but it only links to another table if that type of activity exists, for instance
i have an activity information in the CloseCall table if the call is closed, but if it is a followup activity it is in the followup table

i would like to display the close call notes from the closecall table if there is a close call activity in the activity table

T_Followup
T_activity<
T_CloseCall

here is the sql statement that i am trying to make work

SELECT t_Activity.ActivityDate, tlkup_ActivityType.ActivityTypeName, t_Activity.LocationID, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName,
tlkup_InterestLevel.InterestLevel, CASE activitytypeid WHEN 6 THEN
SELECT Closecallnotes
FROM t_closecall
WHERE t_Closecall.activityid = t_activity.activityid ELSE t_Activity.ActivityNotes END AS ActivityNotes
FROM t_Activity INNER JOIN
tlkup_ActivityType ON t_Activity.ActivityTypeID = tlkup_ActivityType.ActivityTypeID INNER JOIN
tlkup_Rep ON t_Activity.RepID = tlkup_Rep.RepID INNER JOIN
tlkup_InterestLevel ON t_Activity.InterestLevelID = tlkup_InterestLevel.InterestLevelID
WHERE (t_Activity.LocationID = 93)
ORDER BY t_Activity.ActivityDate DESC

is this possible this way if not how do i make it work, right now it is just telling me there is a syntax problem, which is why i thought i would post it here because i am not too sure on the syntax of a case command and how it can be used

View Replies !

Using LIKE In A CASE Statement
I have a unique situation. My data looks something like:Test 1Test 2Test 3Test 2Test 1Test 300Test 200Test 1Test 300Test 200I want to display all of the above like:Test 1Test 2Test 3Test 2MetalsTest 1Test 1Notice that I have everything displayed except for 'Test 200' and'Test 300'. This have been replaced with the word 'Metals' and I'monly displaying it one time, no matter how often Test 200 shows up, orTest 300.My code looks like the following. It works good, except if I have toadd 'Test 400' etc...I would have to hard code them and it will buildup real quick. I looked at GROUPs to see if that would help, but Idon't think it would because it has to be displayed with a differentname such as 'Metals' for all the tests...Test 100, 200, etc... Isthere a better way without having to add each number in it? I thinkthe best way is to use a LIKE statment where the WHEN is being usedbut I keep getting errors if I use the LIKE sytnax where the WHEN isbeing used.SELECT sampleFROM mysamplesWHERE (sample <'TEST 200') AND (sample <'TEST 300')UNION ALLSELECT DISTINCTSAMPLE = CASE Sample WHEN 'test 200' THEN 'Metals' WHEN 'Test 300'THEN 'Metals'ENDFROM MYSAMPLESWHERE (sample = 'TEST 200') OR(sample = 'TEST 300')Any help is appreciated...

View Replies !

SQL CASE Statement
Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y)

Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help?

SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage
FROM [Company/Year]
GROUP BY CCode, BS_TOTAL_ASSETS

Thanks

View Replies !

CASE Statement Help
Hello everyone again, now I want to take my SP a step further. As in using a CASE towards my 'taxable' field. It's value is 1 if no tax, and null if taxed. I have two seperate formulas I want to run depending on the 'taxable' value.

Below is what i'm starting to chew on...

CASE when tbl_products.ptaxable = '1' then ((1+(.02))*((tbl_products.pprice)*(tbl_material_us ed.mqty))) else 0 end

How would I integrate my other formula for when the 'taxable' value is null? After the "else 0" in my CASE?

CASE when 'taxable' = 1 THEN run_formula1 ELSE run_formula2 ???

These CASE statments will go in my WHERE clause correct?
Thanks!!!

View Replies !

AVG(CASE) Statement - Help Please
I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype.
I want to do a CASE statement to get all average scores for all calltypes.

Select Site, avg(totalscore) as [Avg Score]
FROM DB
WHERE calltype = 'A'
GROUP BY Site

Results

Site Avg Score (for A)
1 85
2 75.5
3 85.33

SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore
ELSE 0 END) AS [Avg Score For A]
FROM DB
GROUP BY Site

Results

Site Avg Score For A
1 i get 8.5
2 i get 37.75
3 i get 36.57
Why am I getting a difference?
Any help is greatly appreciated - thank you

View Replies !

Case Statement
Have a procedure called Collections_Cats. It runs fine but l want to modify it but l'm finding it difficult
to get the desired output. The case statement gives an example of what l'm trying to achieve. l would like to build it into my procedure.

l have a table called

paysheet

customer_no loan_no payday payfrequency etc
0000000000 000000 22 3
111111111122222212 4
3333333333 333333 21 2
5555555555 555555 6 1

payfrequency 1= daily
2= weekly
3= fortnightly
4= monthly

l would like to get all the records that satisfy my select criteria. Which is select all records and fields as
specified in my procedure that have a payday thats today only?

How can l build my case statement into the procedure?Is ther a better way of doing it?

l also want to do a count of all the records that have been processed by the procedure ?

Declare @Today int
Set @Today = (SELECT Day(GETDATE()))
SELECT
CASE
WHEN Pay_frequency = 4 And payday =@Today THEN @Today
WHEN Pay_frequency = 3 And payday =@Today THEN @Today
WHEN Pay_frequency = 2 And payday =@Today THEN @Today
WHEN Pay_frequency = 1 And payday =@Today THEN @Today
ELSE 0
END AS "Pay_day",Customer_No,Pay_Frequency
FROM Pay_Sheet
Order by Pay_frequency Desc

************************************************** *************

************************************************** *************

CREATE Procedure Collections_Cats
AS
BEGIN

Declare @today int
Set @today = (SELECT Day(GETDATE()))

DECLARE Collections_Cats_Cursor
CURSOR
FOR
SELECT n.loan_No AS Loan_No,
n.customer_No AS Customer_No,
c.first_name AS First_name,
c.second_name AS Second_name,
c.surname AS Surname,
c.initials AS Initials,
b.Bank_name AS Bank_name,
br.branch_code AS Branch_code,
d.bank_acc_type AS Bank_acc_type,
pay_sheet.pay_frequency AS Pay_Frequency,
n.monthly_Payment AS monthly_Payment,
pay_sheet.payday AS payday
FROM Transaction_Record tr

INNER JOIN
Loan n ON tr.loan_No = n.loan_No
INNER JOIN
Customer c ON n.customer_No = c.customer_no
INNER JOIN
Bank_detail d ON c.customer_no = d.customer_no
INNER JOIN
Branch br ON d.Branch = br.Branch
INNER JOIN
Bank b ON br.Bank = b.Bank
INNER JOIN
pay_sheet ON c.customer_no = pay_sheet.customer_no
WHERE Pay_sheet.Payday = @today

OPEN Collections_Cats_Cursor

-- Perform the first fetch.
FETCH NEXT FROM Collections_Cats_Cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Collections_Cats_Cursor
END

CLOSE Collections_Cats_Cursor
DEALLOCATE Collections_Cats_Cursor
END;
GO

View Replies !

Case Statement
How do l use the case statement to cater for these updates.

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = ('AB')
WHERE AB_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + ' | SB'
WHERE SB_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'SB'
WHERE SB_CLIENT = 1 And entity is null
COMMIT
go

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + (' | CI')
WHERE CI_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'CI'
WHERE CI_CLIENT = 1 And entity is null
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + (' | GEMS')
WHERE GEMS_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'GEMS'
WHERE GEMS_CLIENT = 1 And entity is null
COMMIT
GO

View Replies !

IF Else Within A Case Statement
In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row.
I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated.
Please help
Thanks

select
isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname
, case ProductID
WHEN 22 then count(S.Product)
Else 0
END AS Builders
, case ProductID
WHEN 23 then count(S.Product)
Else 0
END AS Associates
, case ProductID
WHEN 24 then count(S.Product)
Else 0
END AS Affiliates
FROM vwpersons p with (nolock)
join vwSubscriptions S with (nolock)
on S.RecipientID = P.ID
where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
group by P.Lastname, S.ProductID
having count(P.LastName)>=1
order by 1

Result set :

Firstname Builders AssociatesAffiliates

Abarca 010
Abascal200
Abelar 100
Abeyta100
Abeyta010
Abreu 100
Abreu 010
Acevedo100
Acevedo050

View Replies !