Add A Column If Product Is In Discrepancies Table (was Sql Query)
Jan 16, 2007
I have a transaction table (which contains details of all products on all orders)
and is have a discrepancies table which includes details on products that couldnt been found (hence error in stock count)...
Now I want to write a query returning details of all products from a specfic orders.. pretty simple
SELECT product, description, qty
from TRANSACTIONS
where order_no = 'xxx'
but i also want to add an extra boolean column (true if product is in discrepancies table and false if product is not in discrepancies table...will become checkbox at front end)... im not sure how to write this bit....
I know it would a transaction table LEFT JOIN to discrepancies table, but I do not know how to write syntax to add extra column...
Be garteful for any help :confused:
Thanks
View 2 Replies
ADVERTISEMENT
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
Feb 11, 2015
Lest Say with File-stream we have a table document where the file-stream column is located, then we have another table called product-document where the productID and the Document-node are both foreign key of the Product and the Document tables respectively. if i want to query the file-stream document for a specific product, i can just join all three tables, but with file-table there is something i probably didn't get as how to query the file table and get a document for a specific product.
View 0 Replies
View Related
Dec 11, 2014
I have been asked to report on missing Stock in my works Warehouses. My work uses SAP Business One for ERP, and Accellos for Warehouse Management. Both SAP / Accellos maintain stock levels, and whilst they do talk to each other (in real time), nothing is perfect and stock counts (within each system) sometimes develop discrepancies.
Here is the code that I developed to show stock discrepancies -
Code:
SELECT
Tx.[Item Code]
, ISNULL(Ty.Qty, 0) AS 'A1 Qty'
[Code]....
View 1 Replies
View Related
Aug 3, 2007
I am working on a project to upgrade our current server farm form SQL 2000 to 2005. To do this we have some jobs running that insert data into tables on a 2005 system from a 2000 system. I have noticed that there is a bug between 2000 and 2005 with a REAL data type.
The problem stems from how SQL handles the data when it is being transferred via INSERT statement.
If I copy the value between SQL2000 servers there is no error.
If I copy the value between SQL2000 and SQL2005, I receive this error
Msg 7339, Level 16, State 1, Procedure mf_Update_mfwebperf, Line 17
OLE DB provider 'SQLNCLI' for linked server 'MFWeb' returned invalid data for column '[MFWeb].[mfdb].[dbo].[mfwebperf].ShRFund3Yr'.
If I convert the data type to a float on 2005 (which I know can handle the value coming form 2000) I receive the same error.
The only work around is to convert the data type on both the SQL 2000 and 2005 to use floats.
Question is why can€™t I insert a REAL value into a FLOAT across two systems, especially when I know that on the destination system has a greater precision?
Data type
[RSqFund3Yr] [real] NULL,
Data Value
[RSqFund3Yr] Value
1.821688E-44
View 5 Replies
View Related
Aug 30, 2015
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT)
INSERT INTO @Hierarchy VALUES (123, 234)
INSERT INTO @Hierarchy VALUES (234, 456)
INSERT INTO @Hierarchy VALUES (456, 678)
INSERT INTO @Hierarchy VALUES (678, 891)
INSERT INTO @Hierarchy VALUES (891, NULL)
[Code] .....
View 3 Replies
View Related
Mar 17, 2014
I have a series of records based on empid where I want to identify the empid that may have discrepancies listed. I have some empids that are listed more than once and have different DOB's. In the example I am trying to Create a DOB_ERROR column and either say yes if the DOB doesn't match the other records in the file with the same empid.
SELECT
Empid,
DOB,
CASE WHEN DOB = DOB THEN 'No' ELSE 'Yes' END AS DOB_ERROR,
City,
St,
Gender
FROM Emp
WHERE EMPID IN
('12335', '23456', '545432','231245')
View 3 Replies
View Related
Oct 22, 2015
I have a table Product2 as the attachment at the bottom. Now i want to create a Column "Purchasing rate" over Product and Region like this. I tried some Code but it gave me still Error.
createtableProduct2
(
[product] [varchar](255),
[Region] [varchar](15),
[Subregion] [varchar](25),
[Code] ....
View 5 Replies
View Related
Sep 7, 2006
I've just downloaded SQL Server Express (with advanced services) from both the main SQL Server Express website and from MSDN. When I right click on the exe I downloaded and go into properties the Product Version says SQLEVALVER. To me this implies it is an evaluation.
Can I confirm this is just a mistake and if we start to redistribute the product it won't timeout in the future? I am sure it is not an evaluation but just want to make absolutely sure.
View 1 Replies
View Related
Jan 6, 2004
Hello everyone!
I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it...
The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...
query (simplificated)
-------------------------------------------------
SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE
prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394))
AND
(prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2))
-------------------------------------------------
end query
(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...
Thanks for any time spent!
View 14 Replies
View Related
Jan 10, 2008
Hi,
I am new in SQL, I can not solve this case:
I have a product table like this
Name Qty Location
-----------------------------------
Item A 2 Warehouse 1
Item A 5 Warehouse 2
Item B 3 Warehouse 1
Item C 1 Warehouse 1
Item B 6 Warehouse 2
How is the sql command/query/store procedure
to generate result like this?
Name Qty
-----------------
Item A 7
Item B 9
Item C 1
Can anyone help me?
View 5 Replies
View Related
Jun 1, 2007
Week1
Task1
58.12%
Task2
98.34%
Task3
74.45%
Task4
66.54%
Overall
28.31%
Week2
Task1
74.35%
Task2
56.68%
Task3
66.88%
Overall
28.18%
For the table above, I need to calculate the Overall Percentage
Overall = Task1*Task2*Task3*Task4 (Maximum 4 Tasks in one week)
I Used one Week Group and another Task group
If I place the "overall" in the Week group Footer, the value is coming as Zero.
How do I calculate the Overall value?I wrote custom code but didn't help. Also the overall value has to be one per week.
Thanks for u'r help.
View 2 Replies
View Related
Oct 23, 2014
I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..
Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.
I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..
View 14 Replies
View Related
Sep 19, 2013
web scrapping from the web details, to populate the price of product match with name in the Table?
View 2 Replies
View Related
Apr 11, 2012
I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
View 3 Replies
View Related
Oct 16, 2013
tablename/columnname to query the return values.I want to have this query that returns the table names and column names that contain the given value(s). Business needs dictated that I have to start from given values instead. That is to say we own the apps developed by vendors, and own the dbs, but no middle tier code, nor data dictionary.
So, I started with this
select distinct object_Name(Object_ID) myTbls, [name] from sys.columns where name like '%mycolname%'
and object_Name(Object_ID) not like 'fn%'
I have to guess the possible column name as the starter. I realize if the condition value is not very unique, the return dataset could be huge. It is a performance nightmare and it is useless. So I hope the solution will let me specify, say return the first 1000 matches.
View 6 Replies
View Related
Apr 23, 2008
How to query Table and its column details
View 2 Replies
View Related
Jun 12, 2008
How can I query/script Column or Table description (extended property MS_Description') in SQLserver 2005 db.
View 4 Replies
View Related
May 20, 2008
Hi,
I have a table with 500 columns, most of them are type of varchar. I would have the following data set:
ColumnName DataTypeLength MaxLength
-------------------- -------------------- -------------------
ColA 50 42
ColB 10 7
ColC 50 8
.... ... ...
I can have a query to get the data type length (hom many varchar) for each columns:
SELECT column_name, data_Type, character_maximum_length
FROM information_Schema.columns
WHERE table_name='***'
ORDER BY ordinal_position
but I have problem to get the actual maximum length of the each column. Anybody have the similar query?
thanks,
View 9 Replies
View Related
Aug 14, 2006
Hi All
I want to retrieve the data type of table column and validate the input data whether same as data type of table column before insert into database. Any suggestion?
I use asp.net + msde
Thank you.
View 6 Replies
View Related
Jan 21, 2008
I am trying to do one scheduling website for my company. Its contains tasks thats we scheduling for ourself each one and the assigning task by the boss to everyone. I want to do this with two tables. I need to save the task and assigned task in one column named as "Tasks" and to gave after the task name assigned or myself scheduled. Also after deletion of each assigned and scheduled it must save in the table with some name like deleted or any symbol. How can I do this?please help me to solve this issue.
View 1 Replies
View Related
Jul 20, 2005
I want to get a column count several times in one query using differentfilters but can't work out how to do it - can anyone point me in the rightdirection?For example, how would combine these two selects into one query that willlist the total and filtered actions:SELECT COUNT(actions) as actioncount, locationFROM mytableGROUP BY locationSELECT COUNT(actions) as actioncount, locationFROM mytableWHERE mycondition IS NULLGROUP BY location
View 6 Replies
View Related
Jul 20, 2005
I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE.tbl_appointment-------------------1. appt_id (pk) --- identity column, seed 25, increment 12. date_id3. time_start4. time_end5. appt_details6. lkp_emp_idPrivate Sub btnAddAppts_Click()On Error GoTo Err_btnAddAppts_ClickDim strsql As StringDoCmd.SetWarnings Falsestrsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"DoCmd.RunSQL strsqlDoCmd.SetWarnings TrueDoCmd.CloseExit_btnAddAppts_Click:Exit SubErr_btnAddAppts_Click:MsgBox Err.DescriptionResume Exit_btnAddAppts_ClickEnd Sub I did check through Access and through Enterprise Manager and it is setup correctly. So I returned all rows in enterprise manager to manually enter an appointment to the table. I get the same error when doing data-entry straight to the table. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'appt_id'. It does not automatically populate the appt_id field the way it's supposed to. When I try to manually set a value in there, i get an error: "Cannot edit this cell."
View 2 Replies
View Related
Jan 16, 2005
Hi,
Anyone can help me for the Sql query?
I want to sort a table with a priority column, e.g. in the following...
Table A
======
value
======
9
3
1
7
4
======
After sorting:
Table A
========
no value
========
1 1
2 3
3 4
4 7
5 9
========
Anyone can help me?
Thanks.
Daniel.
View 1 Replies
View Related
Apr 30, 2015
I want to query a column of xml files in a table,
use mysql1
declare @bp xml
select @bp=xml
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from (
select
m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as systolisch
from
BloodpressureMitSchema cross apply
@bp.nodes('/bp:content/bp:data/bp:events') as m(c))m
But with this "cross apply" I can only query all the values in one xml and repeat them. Is there something wrong at "declear"
View 2 Replies
View Related
Sep 18, 2015
I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:
OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C
So the logic is as follows:
I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.
Not sure how to create my Select here since the values are in the same table
View 2 Replies
View Related
Sep 25, 2014
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052
Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
View 3 Replies
View Related
Apr 23, 2006
Hello All
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.
declare @THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable
insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable
Update @THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt
UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt
FROM @THeader, mtblTransactionHeader
WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
Thanks in advance
smHaig
View 1 Replies
View Related
Nov 3, 2005
Can someone please answer a problem that I've run into. I know that it's probably something stupid. I keep getting this error:Server: Msg 107, Level 16, State 3, Line 1The column prefix 'vFirstTimeEntered' does not match with a table name or alias name used in the query.Here is my query:-----------------------------------------------------------------Update TimeSheetSectionSet TimesheetSection.SECSTARTDT = vFirstTimeEntered.schlstuidWhere timesheetsection.schlstuid = vFirstTimeEntered.schlstuid AND timesheetsection.sectionid = vFirstTimeEntered.sectionid AND Timesheetsection.secstartdt < '2005-08-01'------------------------------------------------------------------vFirstTimeEntered is a view that I created.Do I need a sub query? I know that if this was a select query I'd need to put vFirstTimeEntered in the FROM part but I don't know where it should go here.Thanks for any assistance.Scott
View 1 Replies
View Related
Oct 30, 2015
I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB
01/01/11 100 5
02/01/11 102 4
Share Price....
Date CompA CompB
01/01/11 100 100
02/01/11 101 99
Event Data...
Date Company
01/01/11 CompA
02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance:
SELECT Event.Date, Event.Company
FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
View 1 Replies
View Related
Apr 22, 2014
I get the error "The column prefix 'contacts' does not match with a table name or alias used in the query".I am trying to obtain all fields from the communications table whether it is used or NULL.
SELECT organisations.organisation_number, organisations.contact_number, organisations.name, organisations.address_number,
organisations.std_code, organisations.telephone, organisations.status, contacts.title, contacts.initials, contacts.forenames,
contacts.surname, contacts.contact_number, contacts.label_name, contact_roles.role, addresses.address, addresses.town, addresses.county,
addresses.postcode, addresses.country, communications.device, communications.notes
[code]....
View 9 Replies
View Related
Feb 2, 2007
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE (
OuterID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL,
InnerID int NULL
)
INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2
DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)
INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3
INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5
INSERT @Inner VALUES (4, 2, 1) -- InnerID 6
-- UPDATE Outer Table with best match from Inner table
UPDATE @Outer SET
InnerID = IJ.InnerID
FROM @Outer OJ
INNER JOIN
(
SELECT TOP 1 I.*
FROM @Inner I
WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID
AND IsNull(I.CompID, OJ.CompID) = OJ.CompID
AND IsNull(I.FormID, OJ.FormID) = OJ.FormID
ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC
) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID)
AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID)
AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer
The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5.
Can anyone help me? Thanks in advance.
View 6 Replies
View Related
Sep 11, 2013
I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql
App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB
OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag
App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null
App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE
View 5 Replies
View Related