Orderitem ListPrice demystification, please...TSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Orderitem ListPrice demystification, please...TSQL

Author

Topic

kamikaze
Starting Member

8 Posts

Posted - 2010-03-10 : 21:05:59

in the MS Access version of Northwind to get the Unit price on selected index change of product. they intoduce a GetListPrice in the code behind of Order Subform. Like:

Option Compare Database
Option Explicit


Private Sub Product_ID_AfterUpdate()
    'Initialize price and discount for each product change
    If Not IsNull(Me![Product ID]) Then
        Me![Quantity] = 0
        Me.Quantity.Locked = False
        Me![Unit Price] = GetListPrice(Me![Product ID])
        Me![Discount] = 0
        Me![Status ID] = None_OrderItemStatus
        
        
    'Empty Product records mean user wants to delete line item
    Else
        eh.TryToRunCommand acCmdDeleteRecord
    End If
End Sub

Now on the SQL server 2008 version there a similar database however everything seems to work somehow like a well oiled engine without the code behind. ummm...
How is this achieved automatically calling the ListPrice from the Product table based on OrderItem selected product. Am I missing s

mething? Do I need to get me some gl

sses? Can anyone demystify this?

ummm...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-11 : 03:53:38

Umh...what?? I have absolutely no clue what you're really asking here... This looks like some VB-code or something...where does it come from? What is it you really want to know? The code you posted has nothing to do with SQL Server as far as I can see.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

kamikaze
Starting Member

8 Posts

Posted - 2010-03-11 : 04:44:09

quote:
Originally posted by Lumbago

Umh...what?? I have absolutely no clue what you're really asking here... This looks like some VB-code or something...where does it come from? What is it you really want to know? The code you posted has nothing to do with SQL Server as far as I can see.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

I am new to SQL and it's the reason iam here scrapping my brain but anough with the obvious.
What I would like to know is: I set up a quick sample project Tables(Customers, Orders, OrderItems, Products) I arrive at a deadock when making a view for the table OrderIt all em go with the the master table "Oder". the question is how do i call the ListPrice field value from the "Products" table in the "OrderItem" UnitPrice field upon selected Product(ProductID)?

ummm...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-11 : 04:55:38

Something like this perhaps...? Still confused though...

SELECT o.OrderID, oi.OrderItemID, oi.Quantity, p.ProductName, p.ListPrice
FROM orders o
  INNER JOIN orderitems oi
    ON oi.OrderID = o.OrderID
  INNER JOIN products p
    ON oi.ProductID = p.ProductID
WHERE OrderID = ...

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources