I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
Hi, In my database i have a column Called EntryDates which has 'Jan1' etc.. stored in it. and i am getting an Xml object and in the object i am getting @EntryDAtesMatch and @EntryDatesPS and they have different months stored in it. So i want to insert into Entrydates or set the Entrydates as to whats been passed in following scenarios If @Entrydates is Not Null then i want @EntryDates to be set as @EntryDates. Suppose @EntryDates is NULL But @EntryDatesMatch OR @entryDAtesPS are not null then i want @EntryDates to be = @EntryDatesMatch..
Suppose is @EntryDates @EntryDatesMatch and null i want @EntryDates to be set as @EntryDatesPS... This is what ihave tried doing.. but doesnt work as the way i want it..
SET @EntryDates = CASE --When @EntryDatesMatch = NULL Then @EntryDates When @EntryDates = NULL and @EntryDatesMatch = NULL Then @EntryDatesPS When @EntryDates = NULL and @EntryDatesPS = NULL then @EntryDatesMatch End Any help will be appreciated.. Regards, Karen
I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value. Right now if the value is anything other than 1 or 0, it will leave the cell blank. CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus Thank You.
In my Database, I do not have a [Last Visit Date]. I have had to pull it by doing the following:
(select top 1 visit from patientvisit pv where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
My client would like to have a listing of patients with a visit within the past 2 years and without a visit in the past 2 years. What I would like to do is have a case statement that evaluates like:
Case When dateadd(y,[LastVisitDate],getdate())<2 then 'Less Than 2' When dateadd(y,[LastVisitDate],getdate())>=2 then '2 or more' else 'No detected visit' END
So basically, either your in 2 yrs or your not.
My Current Query:
/* Patient List*/ SET NOCOUNT ON
DECLARE @Zip varchar(40) SELECT @Zip = LTRIM(RTRIM('NULL')) + '%'; WITH cteMedlitsPatientStatus AS ( SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus' )
SELECT PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0), PatientName=CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) END, PatientAddr1=pp.Address1, PatientAddr2=pp.Address2, PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip, PatientRespName=CASE WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) END, PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City, PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'), Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate, Status = ml1.Description, pp.BirthDate, (select top 1 visit from patientvisit pv where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
FROM PatientProfile pp LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
Can someone tell me what is wrong with my syntax? I am getting several errors:
Incorrect syntax near keyword 'CASE' Incorrect syntax near keyword 'WHEN'
Incorrect syntax near '@Tablename'
CREATE PROCEDURE al_readcampsignup
@User_ID int,
@Pagenumber smallint,
@Tablename varchar(10)
AS
CASE @Mypagenumber WHEN 1 THEN SELECT Blah FROM tempcampsignup WHERE Camp_ID = @User_ID WHEN 2 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID WHEN 3 THEN SELECT Blah FROM @Tablename WHERE Camp_ID = @User_ID END
I am attempting to mask a user's SS# based on Users.DisplaySSN. I have gone over the code time and time again and can't find where the error is.
Error I'm getting: Incorrect syntax near the keyword 'AS'.
Here's where the error lies: CASE (SELECT DisplaySSN FROM Users WHERE Users.UserID = @UserID) WHEN False THEN 'xxx-xx-xxxx' + right(SocialSecurityNumber,0) AS SocialSecurityNumber ELSE vwEmp.SocialSecurityNumber END
What is the T-SQL command to check for NULL or '' in a field in one statement? I would like to change the following code to be more readable (without the OR).
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?
Hi, I have a simple problem that I need a quick solution for. I hope someone can help.I have a list of email addresses that contain many different email providers (hotmail, yahoo, gmail, etc)What I'm trying to do, it select all emails that are not from a select list of providers...Here is the code I have so far... (this isnt giving me what I want)SELECT EmailAddress as Email FROM TBL_EmailAddresses WHERE EmailAddress <> '%aim.com%' OR EmailAddress <> '%hotmail.com%'OR EmailAddress <> '%msn.com%'OR EmailAddress <> '%yahoo.com%'OR EmailAddress <> '%gmail.com%'OR EmailAddress <> '%aol.com%'I want to select all emails that are not of the above types. Thanks in advance.
Case When ( Case When ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End )>IsNull(Date3,'1900-01-01') Then ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End ) Else IsNull(Date3,'1900-01-01') End)>IsNull(Date4,'1900-01-01') Then ( Case When ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End )>IsNull(Date3,'1900-01-01') Then ( Case When IsNull(Date1,'1900-01-01')>IsNull(Date2,'1900-01-01') then IsNull(Date1,'1900-01-01') Else IsNull(Date2,'1900-01-01') End ) Else IsNull(Date3,'1900-01-01') End) Else IsNull(Date4,'1900-01-01') End MaxDate, Case When COALESCE(Date1,Date2,Date3,Date4) > DATEADD(M,-1,CONVERT(CHAR(8),GETDATE(),112)) then 'false' When COALESCE(Date1,Date2,Date3,Date4) is null then 'false' else 'true' end ysncontact From ( Select A.lngpatientid, ysnenroll,lngtype, lngprimaryprovider, strreason,dtmenroll, lngenrollmentid, 'Date2'=(Select max(dtmcontact) FROM tblMHCPcontact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ), 'Date4'=(Select max(B.dtmenroll) FROM tblMHCPEnrollment B where A.lngpatientid=B.lngpatientid and B.lngtype = @type), 'Date3'=(Select max(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type ), 'psymdconsults'=(Select count(visitdate) FROM tblMHCPEncounter B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and B.providerid = '1'), 'countlocus'=(Select case when count(dtmdate)= 0 then 'N' else 'Y' end FROM tblMHCPLOCUS B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmdate between dateadd(month, -6, getdate()) and getdate() ), 'counttplan'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPtreatmentplan B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmstart between dateadd(month, -6, getdate()) and getdate()), 'countmrpt'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -1, getdate()) and getdate()), 'counttplanCBT'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, -1, getdate()) and getdate()), 'counttplanCBT16'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and dtmintake between dateadd(month, +3, getdate()) and getdate()), 'countgroupattenance'= (Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and dtmgroup between dateadd(day, +7, getdate()) and getdate()), 'contactdate'=(Select max(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()), 'dtmbaseline'=(Select max(dtmintake) FROM tblMHCPCaseMngt B where A.lngpatientid=B.lngpatientid and ysnbaseline = 1), 'dtmenrollment'=(Select max(dtmenroll) FROM tblMHCPenrollment B where A.lngpatientid=B.lngpatientid and A.lngtype=b.lngtype and ysndisenroll is null),
'contactcount'=(Select case when count(dtmcontact)= 0 then 0 else 1 end FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()), 'countcontact'=(Select count(dtmcontact) FROM tblMHCPContact B where A.lngpatientid=B.lngpatientid and A.lngtype = @type and dtmcontact between dateadd(day, -7, getdate()) and getdate()), 'satsurvey'=(Select case when count(dtmSurvey)= 0 then 'N' else 'Y' end FROM tblMHCPClientSat B where A.lngpatientid=B.lngpatientid and dtmSurvey between dateadd(day, -70, getdate()) and getdate()), 'encountercount'=(Select case when count(visitdate)= 0 then 0 else 1 end FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()), 'encounterdate'=(Select Max(visitdate) FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()), 'countencounter'=(Select count(visitdate)FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()), 'countSatSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()), 'countQOLSurvey'=(Select case when count(*) = 0 then 'N' else 'Y' end FROM tblMHCPSurveyQOL B where A.lngpatientid=B.lngpatientid and dtmdate between dateadd(month, -6, getdate()) and getdate()), 'Date1'=(Select max(dtmgroup) FROM tblMHCPGroupAttenance B where A.lngpatientid=B.lngpatientid and A.lngtype = @type), 'fname' = (Select strfname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) , 'lname' = (Select strlname FROM tblPatient as P where A.lngpatientid=P.lngpatientid ) , 'Type' = (Select strtype FROM tbllkpMHCPEnrollment as P where A.lngtype=P.lngtype ), 'Provider' = (Select fname + ' ' + lname as pName FROM tbllkpMHCPprovider as P where A.lngprimaryprovider=P.staffid) FROM tblMHCPencounter B where A.lngpatientid=B.lngpatientid and visitdate between dateadd(day, -7, getdate()) and getdate()))
From tblMHCPEnrollment A where A.lngtype = @type and ysnDisEnroll IS NULL) AS lngpatientidDate
HiCan someone please tell me whats wrong with the last line of the querybelow. The first three lines work fine but when i add the fourth line i getan error message (see text at ERROR MESSAGE)sql_HTTermijnRecords = "select * from Orders where FaktuurGeprint ='J'" & _"AND dathergestuurd Is Not Null " & _"AND PerBankKas Is Null " & _"AND " & HTdatumMinAantalDagen & " > " & dathergestuurdThe last line is a comparison between two dates.ERROR MESSAGE============================================Run-time error 3075Syntax error (missing operator) in query expression 'FaktuurGeprint ='J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND22-11-2005 >'========= end error message==============================The first date (22-11-2005) is visible in the error message but the seconddate is missing.As you can see there is nothing after the > but there should be dateinformation from a DB-cell named dathergestuurd.I tried a lot of things but i keep getting the same message.What is wrong with the syntax of the last line ???T.i.a.RegardsTino WintershovenThe Netherlands
I have the following SQL Statement and I am trying to pass in a wildcard for the "Branch Parameter". The Branch Parameter is a numeric field. I need (Branch = %) wildcard to select all the records. Can someone tell me the proper syntax for this? SELECT EmployeeID, CountryCode, FName, LName, Branch, Title, Status, Required, Total, PercentageFROM dbo.vw_Compliance_Percentage_EmployeeWHERE (Branch = @branch)ORDER BY Branch I receive the message failed to convert parameter value from string to an int32
Hi all,I am a newbie to asp and have been using VWD to make a database for an assignment but I am having big problems trying to extract some data to a datalist view. I intend to use this page to display all information of hotel rooms. I know its probably really obvious to some of you but its driving me mad!!! Any help would be greatly appreciated. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim ds As New DataSet() Dim sGetRooms As String = "SELECT RoomID, RoomType, " _ & "RoomName FROM Rooms2 " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomType" Dim sGetroomsizeandprice As String = "SELECT ID, RoomSize, RoomPrice, @RoomType " _ & "FROM roomprices JOIN Rooms2 ON Rooms2.ID = roomprices.ID " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomPrice" Dim sConnect As String = ConfigurationManager.ConnectionStrings("White Sand's Hotel - Dan MahilConnectionString").ConnectionString Using con As New OleDbConnection(sConnect) Dim da As New OleDbDataAdapter(sGetRooms, con) Dim param As New OleDbParameter("RoomType", OleDbType.VarChar, 10) param.Value = Request.QueryString("RoomType") & "%" da.SelectCommand.Parameters.Add(param) Try da.Fill(ds, "Rooms2") da.SelectCommand.CommandText = sGetroomsizeandprice da.Fill(ds, "roomprices") Catch ex As Exception Label4.Text = "ERROR: " & ex.Message Exit Sub End Try End Using Dim pkcol As DataColumn = ds.Tables("Room2").Columns("RoomID") Dim fkcol As DataColumn = ds.Tables("roomprices").Columns("ID") Dim dr As New DataRelation("MenuLink", pkcol, fkcol) ds.Relations.Add(dr) DataList1.DataSource = ds DataList1.DataMember = "Rooms2" DataList1.DataBind() End Sub
Hi AllI am updating a local table based on inner join between local tableand remote table.Update LocalTableSET Field1 = B.Field1FROM LinkedServer.dbname.dbo.RemoteTable BINNER JOIN LocalTable AON B.Field2 = A.Field2AND B.Field3 = A.Field3This query takes 18 minutes to run.I am hoping to speed up the process by writing in OPENQUERY syntax.ThanksRS
I am getting the following syntax error to my query below. This query is similar to many I currently use and I have not been able to track down the error. Can anyone spot the error?
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 'data'.
Select
e.account_number,
epi.Provider_Role = [1],
epi.Provider_Role = [2],
epi.Provider_Role = [3],
epi.Provider_Role = [4],
epi.Provider_Role = [5],
epi.Provider_Role = ,
epi.Provider_Role = [7],
epi.Provider_Role = ,
epi.Provider_Role = [9],
epi.Provider_Role = [10],
epi.Provider_Role = [11]
from (SELECT e.account_number,
row_number() over (partition by epi.PROVIDER_ROLE order by e.account_number asc) as rownum,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(pt.patient_sname)), '')
AS SRM_PatientName,
CONVERT(int,pm.PatientAge),
left(e.admission_date,11) as Admit_Date,
left(e.episode_date,11) as Disch_Date,
(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1
ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,
pm.PrinOpPhys,
epi.PROVIDER_CODE,
epi.PROVIDER_ROLE,
pe.PERSON_NAME as physician_name
From srm.episodes e inner join
dbo.PtMstr pm on pm.accountnumber=e.account_number inner join
srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN
srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join
srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join
srm.providers p on p.provider_key = epi.provider_key inner join
srm.person_element pe on pe.item_key = p.provider_key
Where e.episode_date is not null and pm.AnyProc like '%4495%'
I'm getting the following error messages: Incorrect syntax near the keyword 'in'. Server: Msg 156, Level 15, State 1, Line 38 Incorrect syntax near the keyword 'group'.
/* create temp tables */ select distinct d_vst_id as 'DRW_ID' ,d_vst_instid as 'DRW_INSTID' into temp_tb1 from dnr_vst_db_rec where d_vst_instid = '' and d_vst_dontyp = 'WB' and d_vst_status = 'DN' and d_vst_date between 20020301 and 20030228 order by d_vst_id
Select distinct n_per_id as 'ID1' ,n_per_gender as 'GENDER' ,n_per_birth as 'BIRTH1' ,d_bty_abo + d_bty_rhesus as 'ABO1' ,n_adr_city as 'CITY1' ,n_adr_zip as 'ZIP1' into temp_tb3 from temp_tb1 right outer join nat_per_db_rec on DRW_ID = n_per_id right outer join dnr_bty_db_rec on DRW_ID = d_bty_id right outer join nat_adr_db_rec on DRW_ID = n_adr_id where DRW_INSTID = '' order by n_per_id
select distinct getdate() ,d_aaa_insthdg , case (d_vst_btcdte - n_per_birth) / 10000 when in (14,15,16) the '14-16' when in (17,18,19,20) then '17-20' when in (21, 22,23,24,25) then '21-25' when in (26,27,28,29,30) then '26-30' when in (31,32,33,34,35) then '31-35' when in (36,37,38,39,40) then '36-40' when in (41,42,43,44,45) then '41-45' when in (46,47,48,49,50) then '46-50' when in (51,52,53,54,55) then '51-55' when in (56,57,58,59,60) then '56-60' when in (61,62,63,64,65) then '61-65' when in (66,67,68,69,70) then '66-70'*/ else 71+ end as 'AGE' ,sum(case a.d_vst_dontyp when '1' then 1 else 0 end ) as 'DRAW1' ,sum(case a.d_vst_dontyp when 'xx' then 1 else 1 end ) as 'TOTAL' from dnr_aaa_db_rec, dnr_dud_db_rec, temp_tb3, dnr_vst_db_rec a where a.d_vst_instid = '' and a.d_vst_instid = d_aaa_instid and a.d_vst_id = ID1 and a.d_vst_instid = n_per_instid and a.d_vst_id = n_per_id and n_per_gender = 'M' and a.d_vst_btcdte between 20020301 and 20030228 and a.d_vst_btcdte = (Select max(b.d_vst_btcdte) from dnr_vst_db_rec b where b.d_vst_instid = '' and b.d_vst_status = 'DN' and b.d_vst_dontyp = 'WB' and b.d_vst_id = a.d_vst_id and b.d_vst_btcdte between 20020301 and 20030228)
group by d_aaa_insthdg ,case (d_vst_btcdte - n_per_birth) / 10000 when in (14,15,16) the '14-16' when in (17,18,19,20) then '17-20' when in (21, 22,23,24,25) then '21-25' when in (26,27,28,29,30) then '26-30' when in (31,32,33,34,35) then '31-35' when in (36,37,38,39,40) then '36-40' when in (41,42,43,44,45) then '41-45' when in (46,47,48,49,50) then '46-50' when in (51,52,53,54,55) then '51-55' when in (56,57,58,59,60) then '56-60' when in (61,62,63,64,65) then '61-65' when in (66,67,68,69,70) then '66-70'*/ else 71+ end as
First of all, hello and good morning, my question is, you can check SQL syntax in SQL Server with secondary button mouse or "Check SQL" button in toolbar (Microsoft Management Console 1.2). I´d like to know if there´s a way to use these Server tools from Visual Basic 6 SP6, something like APIs ... If there´s no solution, can anybody give me an idea of how to check SQL syntax in VB. The application wants the users to make their own SQL sentences, (they just can write whatever they want ?????)
SELECT whatever_field, CASE LEN(DrAccount) WHEN 12 THEN DrAccount ELSE CASE (Note1) WHEN NULL THEN Location + DrAccount ELSE Note1 + DrAccount END END AS Account FROM Table1 The purpose of the CASE(Note1) is when Note1 column is null, return Location+DrAccount. The actual result is when Note1 column is null, it always returns null, Location+DrAccount is not executed. When Note1 column is not null, it returns correctly Note1+DrAccount. The problem seems to reside in validating the null value in WHEN NULL How to check for null in CASE(fieldname) WHEN ???
Hello i wrote this case statement to choose the password for a certain user but seems not to work. I have to get custom1 from another table. select cast(custom1 as varchar(5)) from dbo.newhiresraw where id =userid) when custom1 'C%' then password = abcd4321 when not 'C%' then password = [postalcode] end [postalcode].
Not sure if custom1 = C then password =abcd4321 If custom1 not = C then it will be a zipcode.
when I try to create this SP I get: "incorrect syntax near @MyResult" I have tried INT and different variable names, but get same error.
CREATE PROCEDURE sp_IsValidLogon @UserName varchar(16), @Password varchar(16) , @MyResult varchar(3) OUTPUT As if exists(Select * From User_Table Where UserName = @UserName And Password = @Password) begin @MyResult = 1 end else begin INSERT INTO FailedLogons(UserName, Password) values(@UserName, @Password)
declare @totalFails int Select @totalFails = Count(*) From FailedLogons Where UserName = @UserName And dtFailed > GetDate()-1
if (@totalFails > 5) UPDATE User_Table Set Active = 0 Where UserName = @UserName
DECLARE @NAME AS varchar(1000) DECLARE @ADDRESS AS varchar(1000) Declare CUR_C Cursor For Select Rolename From DCJ_SecurityRole Open CUR_C Fetch Next From CUR_C into @NAME While @@fetch_status =0 Begin IF @NAME not in('All','PUBLIC') Begin SET @ADDRESS = 'cn='+'''' + @NAME +''''+',OU=Groups, OU=AJP,DC=XYZ,DC=com' INSERT INTO #UserDetail EXEC (' SELECT SAMAccountName as NTUSER,name as FULLNTNAME,givenname as FIRSTNAME, initials as MIDDLENAME,sn as LASTNAME,''' + @NAME + ''' as Rolename FROM OPENQUERY (ADSI, ''SELECT sAMAccountName,name,givenname,initials,sn FROM ''''LDAP://DC=XYZ,DC=com'''' WHERE MemberOf=''''' + @ADDRESS +''''' '') ') END Fetch Next From CUR_C into @NAME END CLOSE CUR_C DEALLOCATE CUR_C
When I executes this code I am getting some syntax error like Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'DCJAppDev'.where APJPRD is one of the role, Can anyone suggest what dynamic sql part has error??
if(@b='1') begin select * into #example from example select * from #example drop table #example end else begin select * into #example from example select * from #example drop table #example end With syntax check, I always get "'#example already exist'" But why? Just because of two "select into temp table" operation? I am confused. Thank you.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
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.
SELECT OH.OHONR, CASE WHEN LV.LVTYP='VIL' THEN LV.LVT30 ELSE NULL END AS LE, CASE WHEN LV.LVTYP='SÄL' THEN LV.LVT30 ELSE NULL END AS LS INNER JOIN LV ON OHONR=LVONR
The result gives one row for each hit on EITHER of the CASE statements. (Two rows if both case stantements are true) OHONR LE OHONR LV
I want to have one row with the two fields after each other. OHONR LE LV
Hi, I require some help with a SP. I have written the following CASE block but it is giving me an syntax error with Case statement.
Here @loc_id, @id_val and @inid are input parameters to the SP.
Basically what I am doing is I am selecting the column from table based on the input parameter @loc_id I want to use Case and avoid IF statements as Case will reduce the execution time.
If any of you have faced such problem and have a sloution then do let me know.
-- SP section starts
CASE @loc_id WHEN 1 THEN select @id_val = val1 from id_val where name = @inid WHEN 2 THEN select @id_val = val2 from id_val where name = @inid WHEN 3 THEN select @id_val = val3 from id_val where name = @inid WHEN 4 THEN select @id_val = val4 from id_val where name = @inid WHEN 5 THEN select @id_val = val5 from id_val where name = @inid WHEN 6 THEN select @id_val = val6 from id_val where name = @inid WHEN 7 THEN select @id_val = val7 from id_val where name = @inid WHEN 8 THEN select @id_val = val8 from id_val where name = @inid WHEN 9 THEN select @id_val = val9 from id_val where name = @inid END -- SP section ends
I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..
I have my SPROC configured as below, can anyone help me out as to why its not working?
btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)
IF @genderID > 2 .. ELSE ........WHERE genderID = @genderID
