Can someone please tell me whats wrong with the last line of the query
below. The first three lines work fine but when i add the fourth line i get
an 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 & " > " & dathergestuurd
Syntax error (missing operator) in query expression 'FaktuurGeprint =
'J' AND dathergestuurd Is Not Null AND PerBankKas Is Null AND
22-11-2005 >'
========= end error message==============================
The first date (22-11-2005) is visible in the error message but the second
date is missing.
As you can see there is nothing after the > but there should be date
information 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 ???
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
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 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.
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
Hi, TABLES (Association CONTAINS COLUMNS AssociationID), (Group CONTAINS COLUMNS GroupID, GroupName, AssociationID), (GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved), (UserProfiles CONTAINS COLUMNS UserProfileID, UserID), (AllUsers CONTAINS COLUMNS UserID, FirstName, LastName) I am trying to write a query to collect information about from the tables. I need to collect all the Users who are not members of Group A in Association I. Note that Users can belong to more than one group and have more than one profile. I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance
Hi I have a function which returns the periodId from the period table and the function is as follows: ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime) RETURNS int AS
BEGIN
DECLARE @month int SET @month = Month(@Date) RETURN CASE WHEN @month BETWEEN 1 AND 3 THEN 5 WHEN @month BETWEEN 4 AND 6 THEN 6 WHEN @month BETWEEN 7 AND 9 THEN 7 WHEN @month BETWEEN 10 AND 12 THEN 8 END END Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
I have three parameters called @rsLengthofservice, @rsLengthofServicematch and @rsLenghtofservicePS. How do i check if all the three parameters have the same value.. Any help will be appreciated.. Regards, Karen
Hi, I have this query SELECT o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId --cp.ClientId FROM [Order] o Inner Join Plan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId Inner Join User c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC Which gives the list of order from the start date to the end date..but it doesnt List the orders which are some mins apart. For eg. 16524/24/2008 10:48:05 AM4252840 16534/25/2008 10:15:42 AM378550 16544/25/2008 10:51:24 AM3147110 16614/25/2008 1:35:54 PM425045 16624/25/2008 2:30:43 PM4174387130 16634/25/2008 2:47:14 PM154825 16644/25/2008 2:49:10 PM265085 it listed order no. 1652 and 1654 but not 1653 and in the next 4 it didnt list 1663.. why is this happening.. I am using a reports in the report server. any help will be appreciated. Regards,Karen
my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button or user can fill 2 parameter and hit enter so on the other hand (2nd page) how should i query the database accordingly ....
I have some data that looks like this: field1(pk) field2 field3 563136 199535840A D2119562408 199615461C D2119562806 199748610A D2119547463 199748610A D2368562278 200255598A D2368562286 200255598A D2468 Field2 can have the same value. What I need is to return all records where field3 is not 'D2468' ever for field2. So with the above data, I'd want the first 4 records returned, but not 5 and 6 because for those field2 is the same value and in 6, field3 is 'D2468'. So, I can't simply say: SELECT a.field2FROM table1 a inner join table2 b on a.field2 = b.field2 and a.field5 = b.field5where a.field3 not like 'D2468' because it will still return record 5. Can anyone help me with this? Thank you!
I need help here. I have two queries one on each server and one does not work the way it should be. Can some help me here in figuring out if both these queries are same or no. If there is any difference, what is causing that difference. Thanks
Query1: SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName,Shipment.Zip, Shipment.WarehouseCd, Phone FROM Orders INNER JOIN ((NewWarehouse INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd) INNER JOIN Warehouse ON (Shipment.CountryCd = Warehouse.CountryCd) AND (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd)) ON Orders.OrderNum = Shipment.OrderNum WHERE BE = 5 AND AddressTy = 1 AND WarehouseCurrency.CurrencyCd ='USD' AND Active = -1 GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName, Shipment.Zip, Shipment.WarehouseCd, Phone ORDER BY Min(Shipment.OrderNum) DESC)
Query 2: (This is working) SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName FROM ((Orders INNER JOIN (NewWarehouse
INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) ON Orders.OrderNum = Shipment.OrderNum) INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd) INNER JOIN Warehouse ON (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd) AND (Shipment.CountryCd = Warehouse.CountryCd) WHERE (((Orders.BE)=5) AND ((Shipment.AddressTy)=1) AND ((WarehouseCurrency.CurrencyCd)='USD') AND ((NewWarehouse.Active)=-1)) GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName ORDER BY Min(Shipment.OrderNum) DESC
I am sorry I am not that good in sql as some of you may be. Thanks.
I have 2 tables: Table1 has a field [Description](varchar) 100 Table2 has a field [IDDescription] int and field [Description] varchar(100)
In table 2 I have IDDescription store but some time also description wich is not store in table2 I need to pull all description (if it is from table2 or table1). How do I do this?
I have a query that need to do something like this.
About the data: --------------- I have a rules table which has got all the rules, and a log table that has the logs which says whether the rules are satisfied or not over a point of time.
select rules, sum(decode(status,'pass','1')) as passed, sum(decode(status,'fail',1)) as failed from rulestable,logstable where rulestable.logid=logstable.id
Lets say i have 2 tables. One called 'Leads' and the other called 'Sales_Reps'. When a new lead comes in would like to assign it to the sales rep who has the least number of active leads. If there is a tie, I'll just assign it alphabetically between the sales reps who are tied with the least.
Can someone point me in the right direction here? I would assume I'd need to do some sort of select count, but I'm not sure how to begin.
The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).
consider i have these tables: tbl_requests id description owner
tbl_owners id name
tbl_RequestStatus id description
am looking for output look like this: OwnerName New Completed x 2 5 y 4 3
table requests holds number of requests where each request has an owner, table owners holds the owners names, table status holds values for a request status for example : new, in progress and completed, the query am looking for should show each owner with the number of request he/she is handling grouped by the status, so we can say that owner X has 2 new requests and 5 completed requests.
hi i have a table with below structure id no.of visit no of sales no of delivery.
I want all records to be selected with the following data also average( no.of visit),average( no of sales ), average( no of delivery) in a single query ?
hi all i have table which has a userid,email and other fields. And i have a text file called email.txt,this text file has userid and email values corresponding to those in the table.And this file has only this userid and email fields separated by commas..
hi all I know a constraint's name,say cst_name .And i know which database it is existing,say db.then can i know which table belongs to this constraint in tat db with help of a query?
In the table below for Query3, I need to be able to extract the last time "each' RN_TEST_ID was tested for each month. For example, for RN_TEST_IDs '1453' and '1420', the rows in the results set would look like this because they are the most recent records for that specific ID for each Month. Thank you, Thank you, Thank you!
I have been working on the query that is supposed to pull out data to do the following thing:
there are orders that are both complete and not complete yet in the same table.
there is startDate and endDate for complete ones and forecastStartDate and forecastEndDate for not complete one.
I need to pull out data from the same table but using two different conditions in order to create monthly income.
Is there any easy way of doing it? Or do i need to separate my main table. However the last would result in me changing the whole application because of one stupid statement!
