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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
'Case' Statement Inside 'Where' Clause
Hi I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following? DECLARE @searchCriteria Int SET @searchCriteria = 2 SELECT column1, column2 FROM TABLE WHERE CASE @searchCriteria WHEN 1 THEN (column3 = 1000100) WHEN 2 THEN (column3 = 1000101) END CASE ...cheers
View Replies !
Using A While Inside A Select Statement
Hi All, Can we use the while loop inside a select statement? Meaning, something like this: Code Block SELECT DATE, WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23 (SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) ) FROM SC_DATEDIMENSION_TABLE What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right. Hope the question is clear. Thanks a lot. Mannu.
View Replies !
Select Statement Inside UDf
iam trying to rerieve a certain value from one table and i want to use that vaue inside a UDF iam usinf a table valued function as i have to retireve no of values Can i do something like this to retrieve the value SET @Value=Select Value from Table WHERE xyz='some no.' as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.
View Replies !
Counter Inside Select Statement?
Hi, can you add a counter inside a select statement to get a unique id line of the rows? In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order. This is the normal way: SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements This is what i want: DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1) Anyone know if this can be done?
View Replies !
Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query. So! Here's the statement that works: ====================== DECLARE @fixeddate datetime DECLARE @CVG_ELECT char(1) DECLARE @Effdt datetime DECLARE EFFDTS CURSOR FOR SELECT Z.EFFDT, COVERAGE_ELECT FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = '1000' AND Z.EFFDT <= GETDATE() AND Z.PLAN_TYPE = '20' ORDER BY Z.EFFDT DESC OPEN EFFDTS FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT WHILE @@FETCH_STATUS = 0 BEGIN if @CVG_ELECT <> 'E' break ELSE SET @fixeddate = @Effdt FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT END CLOSE EFFDTS DEALLOCATE EFFDTS PRINT @fixeddate ====================== If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea). Is it possible to encapsulate this inside a select statement?
View Replies !
Trying To Write A Select Statement.
I want to write a select statement that will show only distinct Student IDs where every record in the table with that student id has a grade above 80? Lets say for example the table looks like this: Student_ID Grade 11276 93 56879 81 11276 76 44327 92 11276 84 56879 97 56879 82 The select statement should only show students, 56879 & 44327 because student 11276 made a 76 in one class.Anyone know how to word this select statement?Thanks for any help!
View Replies !
How To Write The Select Statement?
I create a table that is named T1(C1,C2) and insert some data,as following: C1 C2 1 1 4 NULL NULL 5 7 NULL NULL 9 12 12 14 NULL NULL 16 I want to write a select statement to obtain the following result: C1 C2 1 1 4 5 7 9 12 12 14 16 how to write the select statement? 　
View Replies !
Is It Possible To Write If/Then Else Within SQL Select Statement For Vb.net?
I'm running a select statement from within my VB.net code. However, if the software package name that I chose from a drop down list in my form is not available in the search (in the TableColumn called Software.Package_Name), I would like to be able to write the message "Record Not Found" to the Package_Name "ListBox" column. Here's the select statement: "SELECT n.Workstation_Name , s.install_status , s.install_date , s.name , s.version , s.build , s.Package_Name , n.city , n.state FROM software s , network n where ( s.mac=n.mac AND n.Workstation_Name='B001617CA709E' ) AND ( s.name='Adobe Reader' AND s.Install_status='1') ORDER BY n.Workstation_Name ASC" My ListBox control is named lstQueryResults and uses a Structure that is one to one with each row of the lstQueryResults control. i.e. Public Class LIST_CTRL_DATA Public WksName As String Public InstallStatus As String Public InstallDate As String Public PackageName As String Public AppName As String Public Version As String Public Build As String Public City As String Public State As String Public Percent As UInteger Finally, here's the While..End routine that plugs the data into the ListBox called lstQueryResults. While sqlReader.Read() currec += 1 Dim msg As THREAD_MESSAGE msg.msgid = 101 Dim message As New LIST_CTRL_DATA message.WksName = sqlReader("Workstation_Name").ToString message.InstallStatus = sqlReader("Install_Status").ToString message.InstallDate = sqlReader("Install_Date").ToString message.AppName = sqlReader("Name").ToString message.Version = sqlReader("Version").ToString message.Build = sqlReader("Build").ToString message.PackageName = sqlReader("Package_Name").ToString message.City = sqlReader("City").ToString message.State = sqlReader("State").ToString message.Percent = (currec * 100) / maxrecs msg.msgdata = message m_ParentWindow.Invoke(m_NotifyMainWindow, msg) End While Any ideas on whether or not you can write an If..Then Else statement within the SQL Select Statement and if so how? I'm still somewhat new to SQL. Any assistance would be great. Regards, NBK
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
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 !
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 !
First Time SELECT CASE Statement User
Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears. Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname. I have been advised to use CASE statements, yet I do not know how to use them. Below have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then Dim tkt As FormsAuthenticationTicket Dim cookiestr As String Dim ck As HttpCookie tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _ DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data") cookiestr = FormsAuthentication.Encrypt(tkt) ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr) If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration ck.Path = FormsAuthentication.FormsCookiePath() Response.Cookies.Add(ck) Dim strRedirect As String strRedirect = Request("ReturnURL") If strRedirect <> "" Then Response.Redirect(strRedirect, True) Else strRedirect = "webform1.aspx" Response.Redirect(strRedirect, True) End If Else Response.Redirect("webform3.aspx", True) End If End Sub
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 !
EXEC Inside CASE Inside SELECT
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant. @val1 and @val2 are passed in CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL ) INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2 SELECT COUNT(*) FROM #TEMP WHERE mybool = 1 If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that. Any suggestions?
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 !
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 !
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 !
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 !
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 !
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 !
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 !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
|
TRACKER
