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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
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 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 !
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 !
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 !
SQL Query Using CASE Statement
Hi, i have a table(table1) like this BatchID Status ------- ------ 1001 1 1001 2 1001 2 1002 0 1002 3 1002 4 "Status" has value from 0 to 5.. i want to write a sql query where i get the count of batch's status in a sinle row like Batch Status-0 Status-1 Status-2 ..... ----- -------- -------- -------- 1001 0 1 2 1002 1 0 0 something like this, where Each batch's all status values is displayed in a single row.. how 2 do this, plz help me out.. thanks in adv Venu
View Replies !
Using Query In CASE Statement
Why couldn't they make SQL syntax error mistakes a little less vague. Anyway, I was wondering, is it possible to use a set in your case statement? CASE ( select distinct tbhtg.TrainingBlockHistoryTypeGroupingCd from tblTrainingBlockHistory tbh inner join tblTrainingBlockHistoryType tbht on tbh.TrainingBlockHistoryTypeCd = tbht.TrainingBlockHistoryTypeCd inner join tblTrainingBlockHistoryTypeGrouping tbhtg on tbht.TrainingBlockHistoryTypeGroupingCd = tbhtg.TrainingBlockHistoryTypeGroupingCd where (select dbo.fnTrainingBlockStatus( 1234, getdate())) = tbht.TrainingBlockHistoryTypeCd ) WHEN 'S' then (COUNT(DISTINCT TRD.TrainingBlockHistoryId) = COUNT(DISTINCT SWT.TrainingBlockHistoryId)) end This is giving me an error on the WHEN statement. The error is "Incorrect syntax near '='" Have no idea how to fix this. But the select statement seems to work, and as far as I can tell, that is how you write a CASE statement. Also, this CASE statement is inside the HAVING clause - is that going to be a problem?
View Replies !
How Do You Hold The Value Of A CASE Statement For Further Use In The Query??
I have a select statement and I would like to know which when clause(1st set of whens) was fired and the value of the then for further use in my query. I am wondering if this is possibly, and how it would be done. If i try and set a @variable at the beginning of the case statament i get an error, here is my query SELECT PIE.productID, PIE.quantity, CEILING((PIE.width/12.0)/0.5)*.5 as width, CEILING((PIE.length/12.0)/0.5)*.5 as length, ***throws an error**** @sqft = CASE --- but the then clause of this CASE is the value i want for later use in this same query WHEN CEILING((PIE.width/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.length/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.width/12.0)/0.5)*.5) * 1.5) * CEILING((PIE.length/12.0)/0.5)*.5)/1.0)*1.0 WHEN CEILING((PIE.length/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.width/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.length/12.0)/0.5)*.5) * 1.5) * CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0 ELSE CEILING((CEILING((PIE.length/12.0)/0.5)*.5) * (CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0 END AS sqft, CASE WHEN @custMarkup = 1 THEN (SELECT PML1 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) WHEN @custMarkup = 2 THEN (SELECT PML2 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) WHEN @custMarkup = 3 THEN (SELECT PML3 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) WHEN @custMarkup = 4 THEN (SELECT PML4 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) WHEN @custMarkup = 5 THEN (SELECT PML5 FROM ProductMarkup PM WHERE PIE.productID = PM.productID) END AS markup FROM ProductsInEstimate PIE WHERE estID = @estid
View Replies !
Using CASE Statement In A Prediction Query
Hi, Can i use a CASE statement in a prediction query. the following query is throwing me an error SELECT CASE [Sales Forecast Time Series].[City Code] when 'LA' then 'Los Angeles' WHEN 'CA' THEN 'California' ELSE 'OTHERS' END, PredictTimeSeries([Sales Forecast Time Series].[Sales Value],5) From [Sales Forecast Time Series] ERROR: Parser: The statement dialect could not be resolved due to ambiguity. Also Is it possible to discretize the Sales Value column using a the CASE statement, the output column of PredictTimeSeries function. Is there a link that can give me a comprehensive info on what can be achieved and what cant be using DMX queries
View Replies !
Help With Query (count With Case Statement)
Hi, I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it. select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages Basically now, it returns the "OutBoundMessages" column I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total ) I determine if the column is "checked" or "unChecked" by a column in tblMessage For example tblMessage.checked = 1 = ("checked") tblMessage.checked = 0 = ("unChecked") any help much appreciated.. thanks! mike123
View Replies !
Query Problem (w/ A Case Statement) In SS2000
In SS 2000 I want to create a sproc that returns the correct address block for a contact. I want to concatenate AddressLine1 and AddressLine2 if AddressLine2 has a value. When I run the following query I get an error (below): SELECT vwICPContacts.PersonID, vwPersons.PreferredAddress, vwPersons.Email1,vwPersons.email2, vwPersons.email3, CASE WHEN vwPersons.preferredAddress='Home Address' then case when (isnull([vwpersons].[HomeAddressLine2],'') = '') then [vwPersons].[HomeAddressLine1] when (isnull([vwpersons].[HomeAddressLine2],'') <> '') then [vwPersons].[HomeAddressLine1] + Char(13) & Char(10) & [vwPersons].[HomeAddressLine2] END ELSE case when (isnull([vwpersons].[AddressLine2],'') = '') then [vwPersons].[AddressLine1] when (isnull([vwpersons].[AddressLine2],'') <> '') then [vwPersons].[AddressLine1] + Char(13) & Char(10) & [vwPersons].[AddressLine2] END END AS MailingAddress FROM (vwPersons INNER JOIN vwICPContacts ON vwPersons.ID = vwICPContacts.PersonID) LEFT JOIN vwCompanies ON vwPersons.CompanyID = vwCompanies.ID Error Message: Server: Msg 403, Level 16, State 1, Line 1 Invalid operator for data type. Operator equals boolean AND, type equals nvarchar. I've done this before but am completely stumped. Any ideas? Jane
View Replies !
Adding Case Statement To Existing Query
I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query: SELECT A.COMPANYCODE, A.INVOICENUMBER, A.LINENUMBER, A.SONUMBER, A.CUSTOMERCODE, A.SHIPPERNUMBER, A.INVOICEDATE, A.ITEMCODE, A.QUANTITYINVOICED, A.UNITPRICE AS 'InvPrice', A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice', INVENTORY.UNITPRICE AS 'StdPrice', INVENTORY.STANDARDCOST, A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost', (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$', (INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff' FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE AND INVENTORY.ITEMCODE = A.ITEMCODE AND ((A.COMPANYCODE = 'csp') AND (A.QUANTITYINVOICED <>$ 0) AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08')) ORDER BY A.INVOICEDATE, A.ITEMCODE They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this: SELECT NewColumn = CASE WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice ELSE 'null' END, FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list. Any help would be greatly appreciated to modify the current query to display the new column.
View Replies !
SELECT QUERY Without USING CASE
I have a select query select col1-tbl2,col2-tbl3,col3-tbl4 ,col4-tbl1,col5-tbl1 from tbl1where condition, The above quey col1-tbl2,col2-tbl3,col3-tbl4 are from table1 only, but it have only id(ie) the foreign key references of tables 2,3,4 I want the column with their values to join with the tables 2,3,4. Except USING CASE Except USING CASE Please Guide me
View Replies !
Using SQL Query Columns In Select Case Statements
I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP: Select Case tCOTSSoftware("Type") Case 1 execute an SQL Update Command Case 2 execute a different SQL Update Command End Select What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
View Replies !
Problem With Select Query - Case When, Views....
Instead of showing a vast amount of code and having viewers disect it, i think I should explain my problem and what I have to accomplish (along with the design idea of an adhoc query) instead of continuing to post code and saying "help." I have a .net datagrid where i have sales results for each sale (and a salesperson that the sale was made by) to display for a day in the past that is queried by the user. Besides the salesamount for the given day, I need to show salesamount results from the previous day next to the salesamount for the present day. All the table joins (2 of them) I'm using have been verified as correct. In my WHERE clause, I will include an OR operator to include both day's data: WHERE b.theDate = '" + requestedday + "' OR b.theDate = '" + daybefore + "'whereas the day before data in c# has been configured to represent whatever the date before the queried date is. I need to assign the salesrep's previous day's salesamount an alias so I can use it in my display, and that previous day's salesamount has to be related to the sales person.I've tried CASE WHEN statement, which i'm having trouble parsing the salesamount from the previous day by the salesrep. It seems this may be more than an adhoc query. Would would be the premise to create a VIEW, STORED PROCEDURE or TRIGGER for this, if I can't do an adhoc query to accomplish this?thanks in advance for any helpnetsports
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 !
Select Statement Query
In my select statement, I return a column for 'datediff' using a CASEquery. I call this column 'Elapsed_days'is there anyway I can use this result later on in the same select? IEI want to refer to 'elasped days' in another CASE query rather thanhave to re-write something which incorporates the original one.Simpler the better - I'm new!Make any sense?Hope so
View Replies !
Select Statement Query
Hi, I have a table called notes, with thousands of rows of "notes" entered by customer services agents. Each row has an account number, date and username columns. An account can have many notes on thetable. How can I select the last two notes on the table left for each account? Trying to use select top 2, but of course it's only giving me the top 2 notes for ALL accounts. Please help. Thanks very much.
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 !
Query To Sum The Same Field Twice In The Select Statement
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
View Replies !
Logic Statement Using Select Query
I'd like to make a logic statement, that would take as arguments result of the sql select query. In more details: I would like to create a local Bool variable that would be false if some value is NULL in the table (or select query).Query example:select taskID from Users where Login=@usernameWhich classes/methods should i use to solve this problem? I use SqlDataSource to get access to database and i think i should use something like SqlDataSource.UpdateCommand and SqlDataSource.UpdateParameters but dont know how to build from this a logic statement.Thanks in advance
View Replies !
Select Statement Help:How Do I Query Two Tables
How do I Query two tables and minus the result to be displayed in a gridview. I will appreciate all the help that I can get in this regard. Find below my two select statement 1st Select StatementDim SelectString As String = "SELECT DISTINCT [Course_Code], [Course_Description], [Credit_Hr], [Course_Type], [Course_Method] FROM [MSISCourses] WHERE (([Course_Type] = Core) OR ([Course_Type] = Information Integration Project) "If radBtnView.Checked = True ThenSelectString = SelectString & " OR ([Course_Type] = 'Knowledge')"End IfIf chkGView.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Data Management')"End IfIf chkGView2.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'General')"End IfIf chkGView1.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Electronic Commerce')"End IfIf chkGView3.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Network Administration and Security')"End IfIf chkGView4.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Healthcare Information Systems')"End IfSqlDataSource3.SelectCommand = SelectString 2nd Select Statement"SELECT DISTINCT [Co_Code], [Co_Description], [Cr_Hr], [Co_Type], [Co_Method] FROM [StudentCourses] WHERE ([Co_Code] = StdIDLabel)" my gridview<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="Course_Code" DataSourceID="SqlDataSource3" GridLines="Horizontal"><Columns><asp:BoundField DataField="Course_Code" HeaderText="Course_Code" ReadOnly="True" SortExpression="Course_Code" /> <asp:BoundField DataField="Course_Description" HeaderText="Course_Description" SortExpression="Course_Description" /> <asp:BoundField DataField="Credit_Hr" HeaderText="Credit_Hr" SortExpression="Credit_Hr" /> <asp:BoundField DataField="Course_Type" HeaderText="Course_Type" SortExpression="Course_Type" /> <asp:BoundField DataField="Course_Method" HeaderText="Course_Method" SortExpression="Course_Method" /> </Columns></asp:GridView>
View Replies !
How To Use Select Statement In Insert Query
hi my self avii want to copy data from one table to other table,by giving certaincondition and i want o use insert statement .in this i want to pass somevalue directly and some value from select statement , if i try i ll geterror i.e all column of destination table (i.e in which i want to insertdata) should match with all columns in values column some thing likethis.plz give me some helpful suggetion on this
View Replies !
How Do I Use Select Statement In Update Query
hi myself avii am developing one appliacaion in which i am using vb 6 as front end,adodb as database library and sql sever 7 as backend.i want to update one table for which i required data from other table. andiretrive data from second table by giving some condition. when i get data,then to update first table i need to use do while loop. instead of that iwant to use select statement directly in update query.plz give me some help.following is the my queries and its out putStrSql = ""StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &txtTransactionID.text & ""rsMName.Open StrSql, Conn, adOpenKeysetDo While Not rsMName.EOFStrSql = ""StrSql = "Update StockTable Set Outward=Outward - " &rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'RsAdd.Open StrSql, Conn, adOpenStaticrsMName.MoveNextLooprsMName.Closeout put***main querySelect * From SalesVchMaterialDesc where TransactionID=848do while not loopUpdate StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0loop
View Replies !
Can't Query More Than 15 Fields In A Select Statement
An ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this: set rs=server.createobject("ADODB.Recordset") rs.open "select * from mytbl",application("mycon"),1,3 And the error is something like this: "you can't query more than the maximum field "
View Replies !
Select Statement Subcribed To Query Notification
I have noticed something strange on the select statement that you can use on query notification. I have a table that contains a compute column let's call it "Calculate". If I subscribe to query notification: Select ID from table the subscription fails. The column Calculate doesn't use the column ID. Why?? for me it shouldn't be the case Thankx MArina B.
View Replies !
SQL Query (SELECT Statement) And Table Design
I am wondering if there is a direct query in this case: I am developing a program to a company which simply sells services One service may have different prices for different types of clients The price of any service for any client can change at any time, and I should be able to trace these changes at any time I made the following tables (simplified): (asterisk for primary key) (Table) (Fields) CLIENT_TYPES : ID*, ClientTypeName SERVICES : ID*, ServiceName PRICES : ServiceID*, ClientTypeID*, Price, Date* ORDERS : ID*, Date, ClientTypeID ORDER_SERVICES : OrderID*, ServiceID* The field in bold is the area of the question This is a sample data in the PRICES table: ServiceID ClientTypeID Price Date1 1 100 1/1/20072 1 150 1/1/20071 2 90 1/1/20072 2 135 1/1/2007 Now if I want to update a price of service 1 for clienttype 1, I add the following row: 1 1 100 1/1/2008 So one product for one client can have any number of prices with different dates The following query: SELECT * FROM PRICES WHERE ClientTypeID = 1 will retrieve all prices with repeats for a specific client (#1 here) What I want is a query to retrieve the most recent prices for specific client for all products, even if a query on query If there is commemts on table design please tell me thanks for any one who provides help
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 !
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 !
|
TRACKER
