Hi,
Is there a way I can get this select Union statement to work in Access.
SELECT '' AS Router UNION SELECT DISTINCT Router FROM IPVPNRouterUpgradeCharges WHERE SchemeID = 12 AND
Router <> 'IPVPN Lite' AND Router <> 'VPN Bridge'
AND Router <> 'IPVPN Aggregated Bandwidth' ORDER By Router
I get this message in Access: Query input must contain at least input of query
Thanks for any help
Chris
I've got a popular problem so i get a message that server acces denied! ..
But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...
On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by
RETTO - name of my server
server=RETTO;uid=sa;pwd=password;database=db1;
or by
Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;
I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!
PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!
I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??
hi all
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin
declare @current_date datetime
set @current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc
end
Getting a weird error while trying out a query from Access 2003 on aSQL Server 2005 table.Want to compute the amount of leave taken by an emp during the year.Since an emp might be off for half a day (forenoon or afternoon), havethe following computed field:SessionOff: ([ForenoonFlag] And [AfternoonFlag])The query works fine when there's no criterion on SessionOff.However, when I try to get the records where the SessionOff equals 0, Iget the following error:~~~~~ODBC--call failed. [Microsoft][SQL Native Client][SQL server]Incorrect syntax near the keyword 'NOT'. (#156)~~~~~I checked the SQL of the Access query, but there's no NOT anywhere init:~~~~~SELECT tblWorkDateAttendance.*FROM tblWorkDate INNER JOIN tblWorkDateAttendance ONtblWorkDate.WorkDate = tblWorkDateAttendance.WorkDateWHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND((tblWorkDateAttendance.WorkDate)<Date()) AND((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND((tblWorkDate.HolidayFlag)=False));~~~~~What gives?
View 4 Replies View RelatedWe have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.
In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.
When we access Guider we have no problems getting data.
When we access Talker we have a login failure:
Cannot open database 'Talker' requested by the login. The login failed.
Login failed for user 'AcornCommunicationServices'.
The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.
Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!
Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using
Management Studio Express to manage the database.
I have TSQL query using UNION ALL. First subset returns 92 rows, second subset return 0 (ZERO) rows. Now if I execute them separately they execute subsecond. If I put UNION ALL between them then it hangs forever exactly on 88s row.
I understand I need to provide execution plans, actual queries etc but before going into all this details, why on earth even in theory UNION ALL with empty result will result in a hang of the first subset?
Based on estimated execution plan 2 subqueries are performed in parallel and then CONCATENTION is being used which shall result with just first subset immediately displayed in result.
Estimated execution plan attached, I can not get actual execution plan because query never completes.
Hi,
I'm attempting to extract some yearly average figures from our DB. I've written a SELECT statement in SQL Server MS which returns exactly what I need:
SELECT YEAR, CAllSource, AVG(CallTotal) AS [Average calls per day]
FROM (SELECT COUNT(CallID) AS CallTotal, DATEPART(YEAR, Recvddate) AS Year, CASE WHEN CallSource IN ('Auto Ticket', 'Email')
THEN 'Email' WHEN CallSource IN ('Phone') THEN 'Phone' ELSE 'Other' END AS CallSource
FROM Calllog where
DATEPART(MONTH, Recvddate) = 3
AND DATEPART(dw, RecvdDate) NOT IN ('7', '1')
GROUP BY RecvdDATE, callsource) as sub
GROUP BY YEAR, CallSource
ORDER BY YEAR, CallSource
The problem is that when I attempt to use this in SSRS I get the following error:
"sub.Year is not a recognised DATEPART Option".
Now as you can see, "sub.Year" is not even mentioned in the expression. However I noticed that when running the query, SSRS automatically adds "sub." before the YEAR in the section highlighted in yellow above. a) Why is it doing this, and b) does anyone know of a workaround/fix?
Thanks
Matt
When our production site was deployed on the client's WinServer2003, my webservice is throwing a "server does not exist or access denied" exception. I'm using the same connection string (typed once) as i'm using in my web forms on the user visible sections of the site. the service also works fine on my XP testing machine. unfortunately, I'm not a 2003 admin. If anyone can help, i would greatly appreciate it, trying to find what is misconfigured on the client's server is driving me bonkers.
View 2 Replies View RelatedHeys
a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key
of a datarow you had just inserted into the database.
After a long set of trial and error i came up with the following:
- add the tablemappings of a table
- call the dataadapte.fillschema method
then after inserting a new row into the database the primary key gets filled in automatically!
now thing is
i was hoping to duplicate this in sql server
but it doesn't seem to work at all
so after i insert a row into my datatable
and update it
the row is in the database
but in vb the datarow primary key is not filled in!
anyone have an idea?
prefereabely one that does not resort to stored procedures with return parameters etc
thx a million in advance!
I am runing Windows 2003 which has both SQL2000 and SQL2005.
The following works in 2000 but not 20005
Code Snippet
DECLARE @sql nvarchar(4000)
set @sql = 'sp_addlinkedserver @server = N''dbAccessPO'',
@provider = N''Microsoft.Jet.OLEDB.4.0'',
@srvproduct = N''OLE DB Provider for Jet'',
@datasrc = N''C:Temppopts.mdb'''
exec sp_executesql @sql
set @sql = 'sp_addlinkedsrvlogin ''dbAccessPO'', FALSE, ''sa'', ''Admin'', NULL'
exec sp_executesql @sql
Running this:
Code Snippetselect * from dbAccessPO...MyTable
Produces this error:
Code Snippet
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO".
I login using sa for both instances.
I could really use your help.
Thanks.
Hello,Im wondering if someone can offer some advice, Im trying to get a union in the statement below to it will return all the data as rows in one table..... DECLARE @counter INT
SET @counter = 0
WHILE @counter < 24
BEGIN
SELECT "Hour"=(SELECT @counter),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
SET @counter = @counter + 1
ENDIf i write each statment out with the values as apose to @counter and union under each i get the result i am looking for... SELECT "Hour"=(SELECT 0),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
UNION
SELECT "Hour"=(SELECT 1),
"HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND (datestamp < getdate())) / (@numberofdays),
"HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE()))
UNION
etc. etc. Does doing a while loop have any performace advantages to my other example? Thanks Bart
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
Help, I had my entire DB created and when i thought i was done, i upsized to SQL and now almost none of my queries work?
The below works when i remove Distinct, but then i have doubles?
Code:
SELECT DISTINCT
Equip_ProductName.ProductName, Equip_ProductName.ProductInfoID, Equip_ProductName.ProductDesc, Equip_ProductName.ProductSearchTerm,
Equip_ProductName.ProductMore, Equip_ProductName.Visible, Equip_Products.ProductID, Equip_Products.CategoryID
FROM Equip_Products INNER JOIN
Equip_ProductName ON Equip_Products.ProductInfoID = Equip_ProductName.ProductInfoID
WHERE (Equip_ProductName.Visible = 1) AND (Equip_Products.CategoryID = 1)
ORDER BY Equip_ProductName.ProductName
I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed
This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);
This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;
I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;
Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.
NAMEADDR_1ADDR_2ADDR_3CITYSTATEZIPCODECOUNTRYSHIP_VIAID
DIEBOLD INC (4076A)ATTN: RANCE AARON343 MANOR DRPACIFICACA9404418932
DIEBOLD, INCOHUPS #88X08X18932
MY POINT: Is there a way to select a over all DISTINCT order ID.
Thank you for any help hope this make sense!
I have the following query and for some reason when I try to run it, it tells me there is an incorrect syntax near the 'union' statement. Can you tell me what is wrong with this?
SELECT MDN,
DATEPART(d,CallDate) as CallDate,
DATEPART(hh,CallDate) as Hour,
sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3
AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00'))
OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN
UNION
SELECT MDN,
DATEPART(d,CallDate) as CallDate,
DATEPART(hh,CallDate) as Hour,
sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3
AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00'))
OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN
I am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this:
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _
"UNION ALL" & _
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1"
This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption:
Invalid column name 'Towing'.
Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!
I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.
What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.
But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"
Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?
Thanks
I have a pretty complex SQL statement that looks like this:
SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact, SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username) AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM aspnet_Employers LEFT OUTER JOIN aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active
It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null".
Why does it do this?
What would i change to prevent this from happening?
Thanks!
here is my update statement in a stored procedure:
create proc proc_add_comp
@comp_answer nvarchar(300),
@admin nvarchar(100),
@comp_id int
as
update tbComp set
comp_answer = comp_answer + ' - ' + @admin + ', ' + @comp_answer
where comp_id=@comp_id
then I try it like this :
exec proc_add_comp 'new answer','by me',1
result is : (1 row(s) affected)
but when I look in the db, nothing was changed, comp_answer still has its old value..
comp_answer is nvarchar type column..isnt add operation allowed in update statement?
thanks...
have this update statement that works for one record. How do I write it to include multiple records at once. see sample below.
update
mklopt
set
FRMDAT = '12/31/2014'
where
JOBCOD = 'PH14789'
I also want to include the following instead of running it one at a time
PH17523
PH17524
PH17525
PH17553
PH17555
PH17556
PH17557
PH17558
PH17571
PH17573
PH17574
PH17575
PH17576
PH17577
PH1757
Hi,
I'm trying to write a UNION statement with 2 selects and can't figure out for the life of me how to do it.
select top 10 * from tblusers ORDER BY dateJoined
UNION
select top 10 * from tblusers ORDER BY lastLogin
The union works fine without the order by's, but I really need the order by's in there and I don't understand why its so difficult to do. Surely there must be some other strategy?
Any help is much appreciated.. thanks!!
mike123
How can I reproduce this results ?
hi all,
i have the following union statement, which works like a charm:SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel ORDER BY [SortDate]
now i want to ad a limit to this statement, but i can't get it working. i have tried this:SELECT * FROM (SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel) ORDER BY [SortDate] LIMIT 2
but i keep getting an error message saying "Incorrect syntax near the keyword "ORDER". what am i doing wrong?
-bm
Hi,I have a database stored in MS SQL 2000 and an application written inVB5, which connects the database via JET/ODBC.I have a problem with the UNION statement.When I run a simple query like:"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"I get the following error:"Runtime error 3078 - The Microsoft Jet database engine cannot find theinput table or query 'select field1 from table1'. Make sure it existsand that its name is spelled correctly."I can run the queries separately "SELECT field1 FROM table1" and "SELECTfield1 FROM table2", so that I'm sure table and field names are correctand I have permission to access them.Both field1 and field2 are the same type (int).If I run the query in MS SQL Query Analyzer, it works fine.It doesn't work only when I run it from VB/JET/ODBC.Has anyone already had this kind of problem?Any help will be highly appreciated!Thank you so much for the attention.--Posted via http://dbforums.com
View 1 Replies View RelatedIs it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO
View 5 Replies View RelatedHi
Im beginer in sql,Please guide
can insert statement works fine in case when function
for example
case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition)
end
I hope this is a easy one. We are trying to find a fix for a select statement that works in 2000 but not in 2005 with a simple select statement.
The easiest statement that will duplicate the error is:
TestTable has 3 columns: Primary, strTest, strTest2
SELECT strTest, strTest AS Name
FROM TestTable
ORDER BY strTest2
If you sort by the Primary column you will not receive an error.
How can you select the same column twice and then sort in the SQL statement?
I have a bit of an odd problem in an sql statement where it works in management studio but not in c# when setting up a table adapter the sql is:
DECLARE @week char(2)
SET @Week = 7
SELECT student_id, acad_period, register_id, register_group, week_no, absence_code, attendance_type
FROM dbo.sttdstud
WHERE (student_id LIKE '%') AND (register_id LIKE '%') AND (register_group LIKE '%') AND
week_no = Case @Week when null Then '#' Else @Week End
the idea is if they don't enter a number it would bring up all records. This works in mangaement studio but c# brings up the error sql server doesn't support udt on excecution of the sql. This is sql server 2000. Any ideas why this doesn't work and how to fix it.
I am using MS Access 2002 (SP-2) and I am trying to take fields from two different tables and send the output to a new table. My UNION logic works, but when I try to add logic to have the result set dumped into a new table, I am having trouble. The following two options have failed. Please help.
Thanks,
Mark
Option 1:
SELECT *
INTO xrmdurtn_auto_new
FROM
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);
Option 2:
CREATE TABLE xrmdurtn_auto_new
(
[RXC CODE] Text,
PXC Text,
DAY Text
);
INSERT INTO xrmdurtn_auto_new
VALUES
(
SELECT [RXC CODE], PXC, DAY
FROM [AUTOSEND DURATION]
WHERE ([RXC CODE] like 'Z2*')
UNION ALL
SELECT [RXC CODE], PXC, DAY
FROM [DURATION MM]
);
Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark
View 1 Replies View RelatedI am trying to fill a table from 2 other tables in MS SQL 2000
the structure ::
Table 1 --> Info
InfoID
Name
Table 2 --> Item
InfoID
Num
Value
TRANSFORM Max(Item.Value) AS MaxValue
SELECT Info.Name
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID
WHERE Item.Num In (10,12,15,100)
GROUP BY Info.Name
PIVOT Item.Num
in ACCESS 2000 it works fine I get a View with 5 columns --> Name,10,12,15,100
but in MS SQL it doesnt work at all
does someone knows how to translate it for MS SQL (the table structures are exactly the same)?
thank you
I have written a intranet page that writes some info into a sql database, basically following the 'SQL Server 2005 Express for Beginners' video.When I debug the application from within 'Visual Web Develop 2005 express' it works fine entries are entered into the DB and I can then edit the db using the admin page.But when I host the site using IIS I doesn't work, submissions to the database seem to fail I can see the DB in the admin page but if I try to edit them or delete them it fails. What could I doing wrong could I be missing a setting in IIS? Any ideas??Here's my webconfig if that helps at all: <?xml version="1.0"?><configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <connectionStrings> <add name="studentprofilesConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|studentprofiles.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <roleManager defaultProvider="AspNetWindowsTokenRoleProvider" /> <compilation debug="true" defaultLanguage="c#" /></system.web></configuration>
View 1 Replies View RelatedI have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.
This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
Hoping you can help
Alex
PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;
SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp
FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));
Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430'
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha