Syntax Check Needed On Case Statement
Feb 26, 2008
Hello experts!
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.
Any help is appreciated.
View 3 Replies
ADVERTISEMENT
Mar 4, 2008
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
View 6 Replies
View Related
Aug 14, 2007
Hi,
here is what i am trying to do, when @call=2, then use table1.call
else use table2.call
and get syntax error no matter what I try,
case @services=2 then table1.call else table2.call end
View 3 Replies
View Related
Jan 3, 2008
I want to Add in a Case Statement to my Stored Procedure to Identify 5 classifications:
A: Birth to 6 yrs old
B: Female 7 yrs to 18 yrs
C: Male 7 yrs to 18 yrs
D: Female over 18 yrs old
E: Male over 18 yrs old
So Im looking for something like this:
CASE
WHEN [Patient Age] >= 6 THEN 'A'
WHEN [Patient Age] between .....
Any help is appreciated!
Declare @ApptDate datetime
Select @ApptDate = a.ApptStart
FROM Appointments a
WHERE a.AppointmentsId = @AppointmentsId
SELECT '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''),
'291.PatLast'=IsNull(pp.Last,''),
'292.PatFirst'=IsNull(pp.First,''),
'293.PatMiddle'=IsNull(pp.Middle,''),
'294.PatientAddr1'=IsNull(pp.Address1,''),
'295.PatientAddr2'=IsNull(pp.Address2,''),
'296.PatientCity'=IsNull(pp.City,''),
'297.PatientState'=IsNull(pp.State,''),
'298.PatientZip'=IsNull(pp.Zip,''),
'299.PatientCountry' = ISNULL(pp.Country,''),
'300.PatientBirthdate' = pp.Birthdate,
'301.PatientSex'=IsNull(pp.Sex,''),
'302.PatientPhone1'=IsNull(pp.Phone1,''),
'303.PatientSSN'=IsNull(pp.SSN,''),
'304.PatOccupation'=IsNull(pp.EmpOccup,''),
'305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),
'306.PatBudget'=IsNull(g.Budget,0),
'307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),
'308.PatSuffix'=IsNull(pp.Suffix,''),
'400.PatientAge' = CASE WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'
WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'
ELSE '' END
FROM PatientProfile pp
LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
WHERE pp.PatientProfileID = @PatientProfileId
View 3 Replies
View Related
Mar 1, 2007
Hello,
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.
View 1 Replies
View Related
Jan 29, 2008
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
WHERE etc ......
View 1 Replies
View Related
Jun 21, 2000
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
Thanks
View 1 Replies
View Related
Jan 16, 2008
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
View 6 Replies
View Related
May 5, 2008
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).
IF @agent IS NULL OR @agent = ''
Thanks!
View 1 Replies
View Related
Nov 5, 2007
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 1 Replies
View Related
Sep 26, 2006
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.
View 5 Replies
View Related
Jul 4, 2007
I am querying a SQL Server 2000 server remotely and have couple of small syntax problems using the WHERE Clause in a Select statement
1. I wish to extract all records where the first 2 characters of a Varchar field (postcode) do not start with 2 specific characters (BT)
2. I wish to extract all records where a Char field is not blank (ie has at least 1 character in it)
Many thanks
Mike
View 3 Replies
View Related
Dec 26, 2007
I cannot work out what I need to do to fix the query attached. I am getting the error Line 105: Incorrect syntax near ')'.
Thank you for your helpCREATE PROCEDURE spMHCPall
(
@type int
)
AS
Select
fname, lname,
dtmenroll,
ysnenroll,
lngtype,
lngprimaryprovider ,
Type,
strreason,
provider,
lngenrollmentid,
encounterdate,
encounterdate2,
countencounter,
countlocus,
counttplan,
countmrpt,
counttplanCBT,
counttplanCBT16,
countgroupattenance,
dtmbaseline,
dtmenrollment,
satsurvey,
encountercount,
countcontact,
contactdate,
psymdconsults,
countQOLSurvey,
lngpatientid,
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
View 4 Replies
View Related
Nov 30, 2005
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
View 4 Replies
View Related
Dec 20, 2007
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
View 7 Replies
View Related
Jun 8, 2008
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
View 2 Replies
View Related
Apr 25, 2007
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
View 1 Replies
View Related
Nov 13, 2007
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%'
) data
PIVOT
(max(epi.Provider_Role) for rownumber
in ( [1], [2], [3], [4], [5], , [7], , [9], [10], [11] )) pvt
order by e.account_number
Thanks!
View 10 Replies
View Related
Oct 12, 2004
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
View 1 Replies
View Related
Oct 27, 2004
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 ?????)
View 5 Replies
View Related
Jan 10, 2008
default is tha constraint?
if i want to add default value to my column this is corect?
alter table tab1 alter column a add default 0
View 2 Replies
View Related
May 19, 2006
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 ???
View 1 Replies
View Related
May 6, 2008
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.
not sure on the syntax any suggestions
View 4 Replies
View Related
Sep 13, 2007
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
@MyResult = 0
end
View 3 Replies
View Related
May 14, 2008
CREATE TABLE #XYZ(
NTUser varchar(20),
FullNTName varchar(50),
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(40),
Rolename varchar(30))
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??
View 2 Replies
View Related
Aug 23, 2007
declare @b nvarchar(1)
set @b = '1'
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.
View 5 Replies
View Related
Aug 13, 2014
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
What is the best way to script this
View 1 Replies
View Related
Jul 4, 2006
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 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
 UPDATE TABLE1
  SET TABLE1.FIELD2 = 1
 ELSE
  UPDATE TABLE2
  SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Dec 18, 2001
I use the following statement:
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
I've also tried to use ' ' instead of NULL
How can I change the statement to achieve this!
/matss
View 3 Replies
View Related
Jan 9, 2001
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
Thanks & Regards
____________________
Mukund Joshi
J.Mukund@zensar.com
View 1 Replies
View Related
Mar 15, 2005
Im looking to strip all non numeric values out of a string at the SQL level in order to compare it to similar user entered data.
Something like
SELECT REReplace(PhoneNumber,"[^0-9]","","ALL") AS NewPN
FROM Numbers
WHERE NewPN = 'UserEnteredNumber'
View 3 Replies
View Related
Mar 4, 2008
Hi,
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
thanks very much once again!
mike123
CREATE PROCEDURE [dbo].[select_123]
(
@genderID tinyint
)
AS SET NOCOUNT ON
SELECT
CASE @genderID
WHEN 1 THEN
SELECT TOP 40 *
FROM tbl
WHERE .......
WHEN 2 THEN
SELECT TOP 40 *
FROM tbl
WHERE .......
ELSE
SELECT TOP 40 *
FROM tbl
WHERE .......
END
View 3 Replies
View Related
