Query Help-----prob An Easy Answer Out There Somewhere
Sep 21, 2007
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'
T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
strSQL = strSQL & "ORDER.OS_T_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_T_Sort, "
strSQL = strSQL & "ORDER.OS_W_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_W_Sort, "
strSQL = strSQL & "ORDER.OS_L_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_L_Sort, "
View 10 Replies
ADVERTISEMENT
Sep 21, 2007
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'
T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
strSQL = strSQL & "ORDER.OS_T_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_T_Sort, "
strSQL = strSQL & "ORDER.OS_W_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_W_Sort, "
strSQL = strSQL & "ORDER.OS_L_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_L_Sort, "
View 8 Replies
View Related
Aug 31, 2000
Hi Folks,
Easy SQL question....first to answer correctly gets...the satisfaction of knowing he helped a fellow developer in need.
Runnning SQL 7.0 on an NT 4.0 platform in a client server network environment. Authentication is done on the NT level. Each user has his/her own name and password. Most users use the same shared software app.
QUESTION: Can the same user login to multiple machines concurrently and run applications without fear of locking or hanging or causing other mischief?
TIA.
JWB
View 3 Replies
View Related
Mar 13, 2008
the first line works, but the 2nd line doesn't. I need just the CompanyKey as the table name. Help appreciated.
sqlcreate = "CREATE TABLE Company_" + CompanyKey + " (UserID VARCHAR(50), First_Name VARCHAR(50), Last_Name VARCHAR(50), Title VARCHAR(50), Division VARCHAR(50))"
sqlcreate = "CREATE TABLE " + CompanyKey + " (UserID VARCHAR(50), First_Name VARCHAR(50), Last_Name VARCHAR(50), Title VARCHAR(50), Division VARCHAR(50))"
View 10 Replies
View Related
Jul 20, 2005
How does one get the primary key of the row that is joined in via agroup by aggregate clause when the aggregate is not performed on theprimary key?For example,Person table(PersonID int,FirstName varchar(50)LastName varchar(50))Visit table(VisitID int,PersonID int,VisitDate datetime)These are simplified versions of my tables. I'm trying to create aview that gets the first time each person Visited:selectp.PersonID,min(v.VisitDate)fromVisit vjoinPerson p on p.PersonID = v.PersonIDgroup byp.PersonIDThe problem is that I would like to return the VisitID in theresultset, but when I do it expands the query since I have to also putit in the group by clause.What are the different ways to achieve this?Subqueries?Only return the date and then join off of date on the outside?Neither of these seem too entising...Thanks in advance for any help.-Dave
View 6 Replies
View Related
Nov 30, 2006
I have a DB with 1 default defined: UW_Zerodefault
It simply puts a 0 into particular fields upon new record creation.
Is there a query I can run against a particular system table to give me a list of fields this default is applied against in the DB?
Thanks
View 3 Replies
View Related
Aug 2, 2000
Hi..,
I am using SQL Server 7.0 SP2.
I've got a table x that has fields say:
id1 id2 a b c d e f
(each field name is separated by a space.
id1 + id2 is the primary key combination for this table)
If there are say 100 records in this table I can write a SELECT query like:
SELECT (a + b + c + d) AS Item
FROM x
WHERE id1 = 'my_value1' AND id2 = 'my_value2'
and I could get up to 16 records (say) using this query. The no. of records I get is variable.
My problem is to produce a single record that contains 16 Item fields. Can anyone advise me how to do this without using a cursor?
I'll be grateful for any help,
Thanks for your patience,
Cheers,
Sudhakar
View 2 Replies
View Related
Mar 19, 2004
Hi Chaps!!
I am in serious problem that My production sql 2000 server with winSp 4 and sqlSP 2 with slammer hotfix is not executing four part distributed query well when I combine the query with begin tran statement it enter into hang mode... else without begin tran it is fine.
request to all of u to get rid of this situation as some modules of our applicaiton is not functioning...
can sp3a installation help or going back to winSp2/3 will be helpfull. (recently we have applied winsp4 but i don't think this is concerned with it)
thanks in advance to give your time...
View 9 Replies
View Related
Feb 12, 2008
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
View 3 Replies
View Related
Dec 21, 2006
hi,
I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions?
SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)
View 6 Replies
View Related
Aug 15, 2007
Hi I'm new to SQL and I'm stuggling with a simple query in MS-server 2005 and wondered if anyone can help me.
I'm trying to devide the ansewrs of two seperate queries, but both the queries use the same coloumn in the where clause to get the answer. Please can some one help!
eg.
select Sum(column1)
where column1 = 'x'
devided by
select Sum(column1)
where column1 = 'y'
View 2 Replies
View Related
Jan 19, 2006
I have a table like this:Name, SSNJoe Smith, 1111Tom Why, 2222Larry Sam, 3333Paul Tom, 4444Steve bob, 1111I want a query to pull offJoe Smith, 1111Steve bob, 1111because someone accidently put in two different names with the sameSSN. There should only be one 1111 in the SSN field in the wholedatabase. I want to pull the duplicate SSN with the name. How can onequery do this. I can write a VB program to do it but I think a queryshould work.I know how to do this:SELECT SSN, COUNT(*) AS cntFROM testGROUP BY fSSNHAVING (COUNT(*) > 1)to find the duplicate SSN but I need the name listed with the SSN also.Any help?? Thanks!!Sheila
View 3 Replies
View Related
Jul 20, 2005
First, let me apologize for how easy this probably is:DESCR TYPE SELL StartDate EndDate65048 04 Price A 4/21/2004 4/26/200465048 06 Price C 4/20/2004 4/27/200465048 08 Price B 4/22/2004 4/28/200465049 04 Price A 4/19/2004 4/24/200465049 06 Price B 4/22/2004 4/25/200465049 09 Price C 4/20/2004 4/29/200465050 07 Price A 4/21/2004 4/25/200465050 06 Price B 4/18/2004 4/28/200465050 05 Price C 4/17/2004 4/29/2004Descr, Type, Sell are CHARStartDate and EndDate are SmallDatetimeI need a simple query that would display the records with:Highest TYPE for each DESCR with:"Date I Enter" >= Startdate"Date I Enter" <= EnddateResults for ("Date I Enter" = 4/23/2004) should be:65048 08 Price B 4/22/2004 4/28/200465049 09 Price C 4/20/2004 4/29/200465050 07 Price A 4/21/2004 4/25/2004I would give you what I have done but it is such a mess I am better offstarting over.Thanks!!-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
View 3 Replies
View Related
Jun 10, 2002
Hello everyone,
I hope you mind a questions from an inexperienced SQL 2000 user.
I don't have much experience with building queries for SQL2000. So I am lookinf for a software tool that will help me create simple queries. I'm also looking for an online (either on the web or a downloadable program) query reference to help remine me how to make these queries.
Thank you,
mike
View 2 Replies
View Related
Jul 23, 2005
I have two tables (tblRequest and tblDev) whose items have many-to-manyrelationships with each other. I have set things up like this(simplified):Table 1 fields: tblRequest.PrimaryKey tblRequest.Descriptionexample: 10 45 Elm11 63 Green12 123 Main Street13 23 PineTable 2 fields: tblDev.PrimaryKey tblDev.Descriptionexample: 20 Local. No issues21 City owned and main22 Out of county23 Not seen by officerThen Table 3 keeps track of the relationships between tables 1 and 2 bytracking the primary key of the linked items in tables 1 and 2.Table 3 fields: tblLink.RequestPrimaryKey tblLink.DevPrimaryKey10 2010 2111 2212 22Items from tables 1 and 2 may or may not have a relationship listed intable 3.So given an item in tblRequest:tblRequest.PrimaryKey tblRequest.Description12 123 Main StreetI need two queries...The first query would return ALL items from tblDev that are notassociated with this item in tblRequest - in this case 20, 21, 23.The second query would return ALL items from tblDev that ARE associatedwith this item in tblRequest - in this case 22.That's it. Thanks in advance
View 1 Replies
View Related
Aug 18, 2005
hi all,I have a table of customers.I have a table of products they have ordered.How can I find all customers who have ordered productA and productB atany time.It sounds so easy, but I can't quite get my head around it!thanksTim
View 7 Replies
View Related
Jul 7, 2001
Hi,
Could someone confirm that the following query:
update table set x=1, y=1, z=1 where a = 1 or b = 1 or c = 1 or d = 1
is more efficient than this query:
update table set x=1, y=1, z=1 where e <> 1 and f <> 1 and g <> 1
Thanks!
View 1 Replies
View Related
Dec 15, 2004
Hi,
I have this query:
Code:
SELECT LB.WBS2, LB.WBS3, LedgerAR.WBS2 AS Expr1, LedgerAR.WBS3 AS Expr2, LB.AmtBud AS amtbud, SUM(LedgerAR.Amount * - 1) AS amt
FROM LB LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = LB.WBS1 AND LedgerAR.WBS2 = LB.WBS2 AND LedgerAR.WBS3 = LB.WBS3
WHERE (LB.WBS1 = '001-298')
GROUP BY LB.WBS2, LB.WBS3, LedgerAR.WBS2, LedgerAR.WBS3, LB.AmtBud
it produces the following output:
Code:
WBS2WBS3Expr1Expr2amtbudamt
014101014101300095
1217010121701080007290
12170804000
121709012170903200065960
121711012171101800034450
121712012171204400038010
12171402000
1217170121717013500935
12171804500
1217220500
12172601000
12175001217500800622.5
12221604000
I want to sum the amtbud column like I did for the amt column. and group everything based on WBS2. However, I keep getting an outrageous amount for the amtbud. This is what is seems to be summing up:
Code:
01410101410130000
014101014101300047.5
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
etc....
Any help will be appreciated I am just stumped.
View 2 Replies
View Related
Aug 7, 2007
How do I rewrite this query so that I do not use a temp table?
SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1
INTO #TEMP1
FROM SITECONTROLDATA
WHERE CALC_DATE > GETDATE() - 12
GROUP BY SITEID
SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1
View 3 Replies
View Related
Aug 28, 2006
SQL Server 2000. Here's what I have so far. The section of the query I need help with is highlighted in blue.
CREATE PROCEDURE dbo.GetByVersion
(
@targetVersion varchar(30),
@product varchar(50)
)
AS
SET NOCOUNT ON;
SELECT * FROM MyTable
WHERE (product = @product)
AND
CASE
WHEN @targetVersion='' THEN (targetVersion='')
ELSE (targetVersion LIKE @targetVersion + '%')
END
GO
I get a syntax error in the Stored Procedure editor on an equal sign in this line:
WHEN @targetVersion='' THEN (targetVersion='')
What I want is this (in psuedocode):
if @targerVersion is blank
search for records where the targetVersion column = blank
else
search for records where the targetVersion column starts with @targetVersion
Can anyone offer any suggestions as to how I might modify my query to do what I want? Any help is very much welcome - Thanks in advance! :-)
View 5 Replies
View Related
Jan 19, 2007
I am looking for a simple example or guidance on how to move the results of a query from a mysql database to sql server 2005 using SSIS.
It seems rather difficult to do this or at least I am stuggling to figure it out. It was easy to do in DTS, how do you do it in SSIS?
View 3 Replies
View Related
Oct 29, 2007
Hi,
we've created a ata Flow task to execute several aggregations. Our Task access database using OLE DB source and selects data out of our staging tables (we've analyzed the query using MS SQL Management Studio which didn't showed any issues). But when we try to run our dataflow task using SSIS (debug mode and DTEXEC from command line) we experince that tasks seem to stop during processing.
Unfortunately we didn't found a way to see long logfile entries which explain the issue to us.
We do use several aggregation tasks divided in 4 sequences. Unfortunately we just see one logical processor out of 4 logical processors working. It is a Windows 2003 SP2 machine with SQL 2005 SP2 on top of it.
Is there any solution to use all processors to one package for parallel execution?
So basically we experience two issues:
- SSIS seems to stop somewhere in thre middle
- SSIS just uses one processor instaed of all four
your advice is appreciated
View 1 Replies
View Related
Mar 27, 2006
Unbelieveible!
I can not seem to find any source for questions database related that don't include using 2.0 controls just as the come out of the box!
HELP!!!!
Simple issue (or so it seems) but I can't get an anwer:
Example:
TextBox.Text has information that I want placed in a table in a sql database. So I build a SQLDataSource control, and the triggers to fire the insert method. I modify the InsertCommand of the SQLDataSource so that it will write my record to the table. Something like...
InsertCommand="INSERT INTO MyTable(Description, Status) VALUES ('TextBox.Text', 'Default New Record Status)"
Now... the problem is with 'TextBox.Text' . When formated as is. the text string TextBox.Text is inserted into a record in the target table. My problem, the simple one remember, is refering to the text string stored inside of TextBox.Text and have that value stored in the new row under the Description column.
I can't believe that no one can help with this simple problem. Once resolved then my page is done... this is ALL that holds it up and I can't find a reference to how to go about it.
Thanks,Ken....
View 2 Replies
View Related
Mar 25, 2004
Hi
I can help you but tommorow because i am very busy know I'd like to answer you .
View 1 Replies
View Related
Jun 10, 2007
Hi,
I'm working on both VWDEE and VBEE and, in each, when I try to add a database diagram I get the following error message 'Invalid Class String' (after the 'Do you want to create required objects' prompt).
Can someone postulate a reason? If I don't get an answer i'll be pretty much dead in the water
Thanks in advance of your help.
View 1 Replies
View Related
Mar 18, 2004
"Falling back on cursors because they remind you of datasets will result in poor performance "
Hi, The above quote was from an article which advice to use store procedure and avoid T-sql in the body of a webpage.
Anyway, To me, I always use cursor when I want to process one row at a time. Is there an alternative to cursor in MS sql server to have similar results.?
thanks
Al
View 2 Replies
View Related
Mar 3, 2003
Please suggest correct answer (A,B,C,D) for the following Question
You are developing an application for a worldwide furniture wholesaler. You need to create an inventory table on each of the databases located in New York, Chicago, Paris, London, San Francisco, and Tokyo. In order to accommodate a distributed environment, you must ensure that each row entered into the inventory table is unique across all location. How can you create the inventory table?
A.Use the identity function. At first location use IDENTITY(1,4), at second location use IDENTITY(2,4), and so on.
B.Use the identity function. At first location use IDENTITY(1,1), at second location use IDENTITY(100000,1), and so on.
C.CREATE TABLE inventory ( Id Uniqueidentifier NOT NULL DEFAULT NEWID(), ItemName Varchar(100) NOT NULL, ItemDescription Varchar(255) NULL, Quantity Int NOT NULL, EntryDate Datetime NOT NULL).
D.Use TIMESTAMP data type.
View 3 Replies
View Related
Apr 9, 2003
Give the suitable answer for the below question and explain the answer.
1.You have designed the database for a Web site (or online ticketing agency) that is used to purchase concert tickets. During a ticket purchase, a buyer view a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor.
For popular concerts, thousands of buyers might attempt to purchase tickets at the same time. Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data. How should you design the cursor?
(A). Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ.
(B). Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.
(C). Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an implicit transaction.
(D). Create a cursor that uses positioned updates. Include the SCROLL_LOCKS argument in the cursor definition to enforce pessimistic concurrency. In the cursor, place the positioned UPDATE statements within an implicit transaction.
View 1 Replies
View Related
Jun 17, 2004
do not have time to go through BOL .. but whats the command beside dbcc freeproccache to clear the cache .
View 1 Replies
View Related
May 20, 2008
batch processing means
View 3 Replies
View Related
Nov 10, 2005
Hows does SQL server works internally:
What are his steps in the execution of a query?
Where/how does he storage the information while executing a query?
What type of Index he uses?
and so on ...
If anyone knows a website that contains this type of information about the SQL Server SGBD and could share it i would be most appreciated!
View 9 Replies
View Related
Oct 17, 2007
1)Which statement shows the maximum salary paid in each job category of each department?_______
A. select dept_id, job_cat,max(salary) from employees where salary > max(salary);
B. select dept_id, job_cat,max(salary) from employees group by dept_id,job_cat;
C. select dept_id, job_cat,max(salary) from employees;
D. select dept_id, job_cat,max(salary) from employees group by dept_id;
E. select dept_id, job_cat,max(salary) from employees group by dept_id,job_cat,salary;
2)description of the students table:
sid_id number
start_date date
end_date date
which two function are valid on the start_date column?_________¡£
A. sum(start_date)
B. avg(start_date)
C. count(start_date)
D. avg(start_date,end_date)
E. min(start_date)
F. maximum(start_date)
View 3 Replies
View Related
Nov 12, 2007
Hi,
I'm trying to write a "GetQuote" procedure with a single @Quantity parameter. The purpose of the query is to sum the total cost (price * quantity) of a given (quantity) given the available "Sale" items. The sale table has the following structure:
[SellOrderID] [uniqueidentifier] DEFAULT (newid()),
[OrderDate] [datetime] DEFAULT (getdate()),
[UserID] [nvarchar](50) ,
[SellPrice] [float] ,
[SellQuantity] [int] ,
[QuantRemaining] [nchar](10) NULL,
**QuantRemaining is the sell quantity remaining after existing partial trades on the Sell Order - intentionally denormalised.
A given quantity may be satisfied by multiple 'Sell Orders' and these should be processed from the lowest price first.
e.g. give the following:
SellOrderID QuantRemaining Price
A 100 $2.00
B 300 $3.00
C 200 $1.75
D 350 $2.50
GetQuote 400 should return:
Sum((200*$1.75)+(100*$2.00)+(100*$2.50))
I am relatively new to the DB game and am having problems creating a SQL statement to do this. If anyone has a solution for this it would be greatly appreciated.
Thanks
Asderex
View 5 Replies
View Related
