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

Add Calculated Field In Order Table Based On Price Column In Product Table

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

Query File Table And Get Document For Specific Product

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

Identify Genuine Stock Quantity Discrepancies Between ERP And WMS?

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

Database Constraint Discrepancies Between 2000 And 2005

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

Transact SQL :: Get All Children Of A Product With Product ID In Result Set

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

SQL Server 2012 :: Series Of Records - Identifying Discrepancies

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

Transact SQL :: Calculate Column Purchasing Rate Per Customer For Product And Region

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

Product Version Query

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

Optimizing Query - Product Tips

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

Case: Sql Query For Product Items

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

Table Group Product

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

Naming Query Column Name Of Resulting Table Name From Query

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

Web Scrapping - Populate Price Of Product Match With Name In Table

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

Edit / Update Query - Change Current Sales Information For A Specific Product

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

Query Table Name / Column Name Given Value

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

Query Table And Its Column Details

Apr 23, 2008

How to query Table and its column details

View 2 Replies View Related

Get Column Or Table Description Query

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

Get Max Length Of Every Column Of A Table In One Query

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

Query Data Type Of Column In DB Table

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

I Want To Save Two Texts In One Column Of Table.What Is The Query For This?

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

Multiple Selects On Same Column, Same Table, One Query

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

Insert Query On Table With Identity Column

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

How To Write A SQL Query To Sort A Table With The Priority Column As Well?

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

SQL Server 2008 :: Query A Column Of XML Files In A Table

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

T-SQL (SS2K8) :: Query To Display Different Values From Same Column In Same Table?

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

Single Query - Update Same Column In Diff Table

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

Problem Referencing A Column In A Table Variable In A Query.

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

SQL Msg 107 Error... The Column Prefix Does Not Match With A Table Name Or Alias Name Used In The Query.

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

SQL 2012 :: Query Based On Column Name / Setting Up Database Table

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

Column Prefix Contacts Does Not Match With A Table Name Or Alias Used In Query

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

Correlated Subquery - Column Prefix 'OJ' Does Not Match With A Table Name Or Alias Name Used In The Query.

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

Query To Update Flag Column In Second Table Based On Adder Names

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







Copyrights 2005-15 www.BigResource.com, All rights reserved