I Wanna Query A Purchase Order Table!...but Can Not:(
Feb 28, 2006
i use microaccess create table, there is a filed call"Complete_PO", value"yes/no"
i wrote following statement to select it, but at runtime, there is warning message"...constraint...one or more row violating non-unique and so so..." how to solve it
SqlSelectCommand2.CommandText = "SELECT Complete_PO FROM [PURCHASE ORDER] WHERE [PO_No] Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No,4) desc"
PoNum_SqlDataAdapter.Fill(PO_DataSet1)
TextBox1.Text = PO_DataSet1.Tables("PURCHASE ORDER").Rows(0).Item("Complete_PO").ToString()
View 5 Replies
ADVERTISEMENT
Mar 1, 2006
i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004
i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number
SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC"
i checked my database, last record is LP-0545381-0300
in my debuging process, surprisingly found that selected record is LP-0545381-301 !
any one hav any suggestion? ^_^
View 2 Replies
View Related
Jan 4, 2008
Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.
below is a Stored Procedure that i have wrote in creating a PO:
CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)
AS
BEGIN
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
CREATE TRIGGER trgInsertPOItem
ON PurchaseOrderItem
FOR INSERT
AS
BEGIN
'What do i entered???'
END
RETURN
help is needed asap! thanks!
View 14 Replies
View Related
Jul 24, 2006
Now i m working in a existing project. I have to do some updations. Inthat project database table contain a table(usergroups) field name as'Name'. When i am trying to insert a new record in (usergoups) tablelike as follows'insert into UserGroups(Name,modusr,moddt) values ('sam',131,'7/23/200610:02:13 PM')The response ll come as follows,Invalid column name 'FirstName'.Invalid column name 'LastName'.The usergroups table structure is as follows,UserGroupId int 4 (PK field)Name varchar 50modusr int 4moddt datetime 8Plz tell the correct insert query to add records for the abovetable....Thanks in advance
View 3 Replies
View Related
Mar 7, 2008
i want to convert the rows of one table to be the column of another table in run time......i have two tables one having student id and name and in another table there are student id, marks and subject as english. hindi and maths in rows...i want to make another a third table which contains student id , name , marks as english, hindi, maths as the column....tried a lot but didnt get the right way...do we have to do it through Join or is there some other technique by which we can solve the query........i dont know wheather i am really informative or not..but am sitting online if incase you wanna know something else even..........
View 4 Replies
View Related
Feb 10, 2014
I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.
Example:
Customer NameGoodsPriceDatePurchased
ABat2501/31/2014
ABall221/31/2014
BCarrom Board4752/2/2014
CTennis Ball502/1/2014
AFootball1502/2/2014
DBat2501/31/2014
BBall221/31/2014
AHockey Bat1252/4/2014
CChess552/4/2014
AVolley Ball552/4/2014
View 9 Replies
View Related
Jan 21, 2008
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable.
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in
-- descending order
SELECT * FROM @tblCustomRange
WHERE (RangeMonth < @Month) OR
(RangeMonth = @Month AND RangeDay <= @Day)
ORDER BY RangeMonth, RangeDay DESC
I am getting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
2 1 27
1 1 2
3 6 10
I am expecting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
3 6 10
2 1 27
1 1 2
View 1 Replies
View Related
Jun 8, 2004
I've seen on a few searches (obviously not dBforums searches :( ) that sometimes other folks are running into this, but I haven't yet seen an answer to it anywhere...so, how 'bouts our brainy folks consider it for a brief moment?
I'd 'preciate it immensely...
Trouble is, I cannot get any of my stored procs to run in debug mode through the SQL Query Analyzer. I have done this on many occasions before, but am unable to debug ANY sp's today.
Interesting thing is, that I initially named all my stored procs as "sp_<procname>" but since I read that is a no-no (since sp_ procs are assumed to be system stored procs, and the master db will be searched first at execution time - slowing things down a tad), I today changed all my procs to <procname> simply getting rid of the sp_ on each proc name.
I did this by first renaming the proc in Enterprise manager, then going into the proc (also in enterprise manager) and changing the proc name in the CREATE PROC statement (and, of course, changed all the references to other stored procs fro sp_<procname> to <procname>). then saving the proc in Enterprise Manager.
After investigating the problem further, I tried deleting the proc in EManager, then went to SQL Server and recreating it (both by scripting the proc and running it in SQL Query Analyzer AND just deleting and re-adding (as "add new proc" in EManager.
No effect.
Still can't debug. When I go into the debugger, it allows me to set up my params as usual, but then when I go to execute, it doesn't stop even at the first line of the proc...it just commences to rip through the procs and runs to the end (runs successfully, I might add...but provides no way to view the progress!!!)
Any thoughts? I know it's gotta be setup or something...but just can't figger it out.
View 8 Replies
View Related
Jan 15, 2008
I have detail table like
date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8
Result View
=======
Item_id A B O T Total
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18
where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.
Smartsys
View 4 Replies
View Related
May 19, 2015
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
View 8 Replies
View Related
Mar 27, 2008
Hi!
I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.
The table returns the data in the same order in SQL Manager "Open Table"
So I started to wonder what deterimins the sort order when there is no order by clause ?
I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.
Peace.
/P
View 5 Replies
View Related
Nov 9, 2006
Guys,
Would you mind advising me on what sql server license would be suitable?
What I am concerned about having is the ability to access a database from 10 computers.
I like to use the Enterprise Manager, Query Analyzer, write proceduers, Views and Triggers. Not sure if this affects anything.
View 3 Replies
View Related
Dec 18, 2007
I need to purchase a new computer for a small medical clinic which will basically only have one purpose: to answer to read and write queries to a SQL Server 2005 which is resident on that computer. Queries come from the current 8 stations (up to 14 stations in the future). Most of the time, only 3 stations will be active at a time. Queries are mostly to access patient file information, are not complex and are short-lived.
A friend of mine who owns a computer store just quoted me for a dual quad-core Xeon 5405 2GHz system with Windows Server 2003 10 Cals. I'm concerned about the following:
- What's the use in having 8 cores, each of them running at only
2GHz, when there's really only one service running (SQL Server
2005, likely Express Edition) on the computer. Does SQL Server
have the capability to make use of all cores? Otherwise, why
spend more for Xeon and so many cores instead of a single
C2D running at a faster speed of say 3GHz ?
- What would be the advantage of using a Windows Server over
Windows XP in a peer-to-peer configuration? I don't buy into
the 10 connection limit because the TCPIP.sys file can be
altered to move that limit up, so 14 stations does not trigger
the need for Windows Server in and of itself.
Thanks all!
View 5 Replies
View Related
Jan 21, 2004
I went in to waterstones today to try and get a copy of sql pocket reference (o'riely publishers i think) but there were none in stock, as the new edition is out on the 31st.
so i bought sam teach urself sql "in ten minutes"
unfortunately it covers the alter tag but not its use to modify columns >:(
I'm a bit stuck with this one atm
---------------------------------------
1> alter table news modify ( dateadd varchar(80))
2> go
Msg 170, Level 15, State 1, Server NEIL, Line 1
Line 1: Incorrect syntax near '('.
1>
what exactly am I doing wrong?
View 6 Replies
View Related
Oct 29, 2004
I am looking for a good reference on hardware specs for a dedicated SQL server. I don't want to talk to vendors, because I'm not looking to get snowed. Does anyone know of any resources? The server is to be a dedicated dataserver, for about 300 clients.
View 9 Replies
View Related
Oct 14, 2006
Hi,
The SQL server 2005 is needed to be put for user's access as database backend for VB.net application. We need only one system for development. Which is best suited for our application?
Any suggestions?
View 1 Replies
View Related
Sep 6, 2015
I have data concerning sale of several products, columns are two : Name_product, Purchase_day
I need to calculate with SQL the average duration (number of days) to purchase again the same product among the products
As in example, one product can be bought many times, so this product we will see it repeated several times at different days "Purchase_day" so here we can have Duration between each two successive purchases for the same product, but for the last purchase of this product the duration until today will be the day of yesterday minus the last day of purchase, and finally the average duration of this product will be the sum of all duration of this product / number of all duration of this product.
Link to the table copy and paste : [URL] .....
View 2 Replies
View Related
Aug 30, 2013
how can i exclude all id which doesn't purchase any code.
codeA,codeB,codeC,codeD,.....
ID 1 purchase codeA, this will not be pulled out.
ID 2 didnt purchase any code, this will be pulled out.
I tried select id,code from tableA where code not in ('codeA','codeB','codeC','codeD',...)
but it still pull out ID 1.
View 1 Replies
View Related
Mar 13, 2007
My company is planning to purchase a SQL2005 standard Processor license (unlimited users - about $5000)
Does anyone know if we can obtain SQL2000 media along with the purchase. Because of the app we are running and its stage of development, we have to install and run SQL2000 for about 6 months before we can run Sql2005. We dont want to purchase SQL2000 for such a short term use.
I asked a DELL rep to help me with this over a month ago and he still has no answers for me.
Any help with this would be greatly appreciated.
View 4 Replies
View Related
Sep 10, 2007
Hi,
I am new at SQL hopefully this would be a rather easy question for you guys to help me out with.
I have a table called PRODUCT with the following fields:
a. Product Name
b. Product Dept.
c. Purchase Date.
I would like to run a query to obtain all rows tha has more than one purchases on any particular day.
Any replies would be great.
View 1 Replies
View Related
Mar 13, 2007
My company is planning to purchase a SQL2005 standard Processor license (unlimited users - about $5000)
Does anyone know if we can obtain SQL2000 media along with the purchase. Because of the app we are running and its stage of development, we have to install and run SQL2000 for about 6 months before we can run Sql2005. We dont want to purchase SQL2000 for such a short term use.
I asked a DELL rep to help me with this over a month ago and he still has no answers for me.
Any help with this would be greatly appreciated.
View 3 Replies
View Related
Sep 29, 2015
I need to find the last purchase price for each product. If I run the following code, I correctly get 1 result for each productID and the last purchase order number.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrderfrom apodetail podgroup by pod.articleorder by pod.article
Now I need to add in the price for that product on that orderID. I've tried the following self join query, tried it without the join, and tried adding DISTINCT, but they all return more than 1 row per ProductID.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrder,pod2.rev_price as UnitPricefrom apodetail podjoinapodetail pod2on (pod2.order_ID = pod.order_id)group by pod.article,pod2.rev_priceorder by pod.article
How can I get it to simply add the price to the first query?
View 10 Replies
View Related
Nov 18, 2014
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
View 17 Replies
View Related
Jul 15, 2014
I need to write down a sql query wherein in one particular day(user will enter manually), i need to find out a 15 minutes slot wherein purchase order's created or updated are the highest.
i.e. out of 96 slots(15 minute slot each)-I need to find the slot which has maximum number of Purchase orders created or updated.
View 7 Replies
View Related
Jun 14, 2007
Hi, I am using below query:
SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating, tbh_Articles.ImageURL, tbh_Articles.specialFROM tbh_Lang CROSS JOIN tbh_ArticlesWHERE (tbh_Lang.LangID = @LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5)
Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...
Tables structure: tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2.... )
Any suggestions?
View 9 Replies
View Related
Sep 29, 2007
I have a Comment Table where a comment can have a reply, if the comment is replied to I want the reply to appear under the comment.
Based on the Fields CommentID and Parent ID the parentID is the Comment and the Comment with a ParentID set too that comment is the answer.
How do I build this Query?
View 7 Replies
View Related
Feb 7, 2008
Hi all,
Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!
Table Structure (abridged, relevant columns):
Orders:
Code:
[Orders](
[OrderID] [int] IDENTITY(1,1)
[OrderDateTime] [datetime]
[OrderSiteID] [nvarchar](255)
[OrderOffline] [bit]
[OrderSentToWP] [bit]
[OrderReceivedFromWP] [bit]
[OrderAuthorised] [bit]
[OrderCancelled] [bit]
[OrderApproved] [bit]
[OrderFraud] [bit]
[OrderDispatched] [bit]
OrderItems:
Code:
[OrderItems](
[OrderID] [int]
[ProductID] [nvarchar](255)
[Quantity] [int]
[Price] [real]
[Weight] [real]
Products:
Code:
[Products](
[ProductID] [uniqueidentifier]
[ProductCode] [nvarchar](255)
[ProductTitle] [nvarchar](255)
Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.
I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.
So far I have this:
Code:
SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID
WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND
(Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND
(Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid'
GROUP BY Products.ProductCode, Products.ProductTitle
Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again
I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!
Thanks in advance to anyone that looks at this for me.
Also, any recommended books/sites to learn this kind of query?
Richard
View 4 Replies
View Related
Jun 5, 2008
Greatful for any help....
Have the query below which is taking delimited address information from _Venue column. This works well apart from the order it is returned, for example, the output below has the address tittle displayed in a different column for each row
Queen Elizabeth's Hunting Lodge is in Address1
All Saints' Church is in Address2
Audley End House is in Address3
As I need to reference from the query the correct part of the address from the same location each time, is there anyway to get around this?
Thanks in advance
SELECT coalesce (PARSENAME(REPLACE(_Venue,',', '.'),4), '-') address1
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),3), '-') address2
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),2), '-') address3
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),1), '-') address4
FROM table
Address1 Address2Address3Address4
----------------------------------------------------------------------------------------
Queen Elizabeth's Hunting LodgeRangers RoadChingfordLondon E4 7 QH
- All Saints' ChurchShrub End RoadColchester
---Audley End House
View 10 Replies
View Related
Jan 20, 2014
I have table called 'UserDetails'. If I execute below select query it should display in order of uno= 7,13,5 but i get in order of
uno=5,7,13.
How to get in order of uno= 7,13,5
Select EmailAddress,EmployeeName,UNo, MobileNumber
from UserDetails where (UNo=7 or UNo=13 or UNo=5 ) group by uno,emailaddress,employeename,uno,mobilenumber
Result I am getting as
EmailAddress EmployeeName UNo MobileNumber
-----------------------------------------------------------
aaa@xxx.com ravi 5 8989898989
bbb@xxx.comramesh 79898989898
ariv@gmail.com arivu 13 8989898989
View 3 Replies
View Related
Nov 24, 2006
Hi every body.
Can u tell me how to get the order values of the SQL query
Example.
My sqlstring ="Select * from tbl_Products"
And it returns 6 rows
And I want to get order values like this 1,2,3,4,5,6
I am a beginner.
Thanks a lots
View 1 Replies
View Related
Feb 20, 2008
SELECT Column1,Column2,Column3 ....
FROM vwMyView
ORDER BY CreatedDT
View has about 10000 rows, If I remove order by query runs faster but adding an order by cause query to timeout..
All tables have clustered index based on the primary key of the table..
(a) Should I create an index view with CreatedDT as non clustered index?
(b) Or create a non clustered index on CreatedDT column on the underlying table?
I can provide DDL but if something obvious I am missing
Thanks
View 5 Replies
View Related
Jul 20, 2005
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
View 7 Replies
View Related
Nov 6, 2006
I have a query with many (approximately, 30) conditions, such as:
select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) )
My question is:
In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement.
The question is, is there the same rule in T-SQL?
I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")?
This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance...
By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan.
[Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script?
We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.]
Could someone kindly advise me on this, please?
Thank you ever so much.
View 4 Replies
View Related
