473,372 Members | 1,335 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

I am struggling through views

Hi there, it's me again. I am having trouble with a view again. I am
trying to do a calculation, but there are some checks that need to be
taken into consideration. Maybe a view is not the right way to deal
with this. I don't know.

This is the beginning of my query.

SELECT coalesce(f.filenumber, i.filenumber) as filenumber,
i.InvoiceNumber, i.InvoiceValue, il.lineid, MPF = .21 * (il.UnitCost *
il.UnitQty + il.AddMMV - il.MinusMMV - il.MinusNDC + il.ErrorAmt)

FROM tblFILE f inner join tblINVOICE i on (f.filenumber =
i.filenumber) left outer join tblINVOICE_LINE il on (i.Invoiceid =
il.invoiceid)

This works just as it should. However, if the Sum of all MPFs per
file total less than 25.00 or more than 485.00 then each MPF has to be
recalculated as:
Percentage of TotalEnteredValue = (InvoiceValue / (il.UnitCost *
il.UnitQty + il.AddMMV - il.MinusMMV - il.MinusNDC + il.ErrorAmt)
Percentage of TotalEnteredValue * 25.00 = MPF or
Percentage of TotalEnteredValue * 485.00 = MPF

Can you do something like this in a View? Or do I need to do
something like a trigger?
I greatly appreciate all help. I am struggling to get a foothold on
views. I am getting there.
Jul 20 '05 #1
4 1987
Well, I am getting desperate. I must have explained myself poorly. I
apologize. I absolutely cannot figure out which approach would best
suit my need because I don't know about any. I am still a newbie. I
am pretty good at the basics of sql server 2000, but I am totally an
idiot when it comes to anything complicated or outside of what I know,
which is tables, a little stored procedure and some view stuff. I
have been looking on Books Online but still cannot figure out what my
best approach should be.

I have tables and a view...

CREATE TABLE tblFILE (
FileNumber bigint not null primary key
)

CREATE TABLE tblINVOICE(
InvoiceID bigint not null primary key
identity,
InvoiceNumber varchar(35) not null,
FileNumber bigint not null,

CONSTRAINT FK_tblFILE FOREIGN KEY (FileNumber) REFERENCES
tblFILE(FileNumber)
)

CREATE TABLE tblINVOICE_LINE (
LineID bigint not null primary key
identity,
InvoiceID bigint not null,
UnitCost money,
UnitQty money,
AddMMV money,
MinusMMV money,
MinusNDC money,
ErrorAmt money,

CONSTRAINT FK_tblINVOICE FOREIGN KEY (InvoiceID) REFERENCES
tblINVOICE(InvoiceID)

)

CREATE VIEW TEV_view AS (

SELECT i.FileNumber, COALESCE (i.InvoiceID, il.InvoiceID) AS
InvoiceID, i.InvoiceNumber, il.LineID, il.UnitQty, il.UnitCost,
il.AddMMV, il.MinusNDC, il.ErrorAmt, il.UnitQty * il.UnitCost +
il.AddMMV - il.MinusMMV - il.MinusNDC + il.ErrorAmt AS LineEV, .0021 *
(il.UnitQty * il.UnitCost + il.AddMMV - il.MinusMMV - il.MinusNDC +
il.ErrorAmt) AS MPFbyLine
FROM dbo.tblINVOICE i INNER JOIN
dbo.tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
)
So, my MPFbyLine is where my headache, confusion, frustration,
desperation is coming from. A file has many invoices...each invoice
has many lines. Each line has an MPFbyLine value. This is all fine
and dandy...except if the sum of all MPFbyLine values for a file is
less than 25.00 or more than 485.00.

If this is the case then the MPFbyLine for each line in that file must
be recalculated using a formula other than the (.0021 * LineEV).
Instead, the sum of all LineEVs for the file must be deteremined, and
then each LineEV for the file must be divided by the Sum of all to get
each line's percentage of value of the file. Then the new MPFbyLine
is calculated as the line's percentage of value * 25.00min or
485.00max to get its new value.

So, I figured I must need to use Transact SQL somehow, possibly in a
trigger. Or maybe i need a calculated field, which I have found
reference to. Either way I am stumped. I have been messing with the
below trigger, but I feel like I am on the wrong track. I am sorry if
this is longwinded...I really need help. I think I am better off
doing this in sql than programming it directly into by vb application.
I would really, really appreciate any help.

CREATE TRIGGER CalcMPF_trg ON [dbo].[TEV_view]
FOR INSERT, UPDATE, DELETE
AS

DECLARE @Min money,
@Max money,
@TEV money,
@TotMPF money,
@PercentOfTEV float
Set @Min = 25.00
Set @Max = 485.00

Select SUM(MPFByLine), SUM(LineEV) from dbo.TEV_view
Group by Filenumber
Set @TEV = SUM(LineEV)
Set @TotMPF = SUM(MPFByLine)

If @TotMPF < @Min then
@PercentOfTEV = @TEV
Jul 20 '05 #2
Without any DDL, all anyone can do is guess. And under reasonable
assumptions, the code looks bad. For example, why are you writing
"COALESCE(F.file_nbr, I.file_nbr) AS file_nbr" when you join the two
tables on (F.file_nbr = I.file_nbr) so neither can be NULL??

Why do you do a left outer join on invoices and invoice lines do you
have invoices without any lines? As an aside, I hope that your data
model is not so screwed up that you mimicked the PHYSICAL layout of
the paper order form and actually copied the lines from the form
instead of putting each product in a row in an "Invoice Details" or
"Invoice Items" table. But with things like "line_id", it certainly
looks like it!

Think about the same item appearing in multiple PHYSICAL lines of a
paper form and all the screws up it causes.

Why do you have both an "invoice_nbr" and an "invoiceid"? Never use
two names for one data element.

Why do you have "add_mmv" and "minus_mmv", but no plain, simple, mmv?
Why is "unit_qty" not plain old "qty_ordered"? In fact try to define
what "unit_qty" means. Data element names like this make no sense;
please read the ISO-11179 rules.

Please tell me that "I.invoice_value" is not a computed value, like
the total of the items, mixed into the same level of aggregation as
its components.

You mix Standard SQL and proprietary code, use silly prefixes, and
singular names for tables, etc. in short, this needs some clean up
so I made few guesses

Since a change to any order could cause you to re-calculate
everything, put this in a VIEW and then filter the view when you use
it. First calculate everything you will need let the optimizer
worry about factoring sub-expressions.

CREATE VIEW Foobar
(file_nbr, invoice_nbr, invoice_value, line_id, normal_mpf, low_mpf,
high_mpf)
AS
SELECT F.file_nbr, I.invoice_nbr, I.invoice_value, L.line_id,
(0.21 * (L.unit_cost * L.qty_ordered + L.add_mmv - L.minus_mmv -
L.minus_ndc + L.error_amt) AS normal_mpf,

((I.invoice_value / (L.unit_cost *
L.unitqty + L.add_mmv - L.minus_mmv - L.minus_ndc + L.error_amt)
* 25.00) AS low_mpf,

((I.invoice_value / (L.unit_cost *
L.unitqty + L.add_mmv - L.minus_mv - L.minus_ndc + L.error_amt)
* 485.00) AS high_mpf

FROM Files AS F, Invoices AS I, Invoice_items AS L
WHERE F.file_nbr = I.file_nbr
AND I.invoice_nbr = L.invoice_nbr);

Now the view needs to do a self-reference, and you could make this
into a VIEW also.

SELECT file_nbr, invoice_nbr, invoice_value, line_id,
CASE WHEN (SELECT SUM(normal_mpf) FROM Foobar AS F1
WHERE F1.file_nbr = F0.file_nbr) < 25.00
THEN low_mpf
WHEN (SELECT SUM(normal_mpf) FROM Foobar AS F1
WHERE F1.file_nbr = F0.file_nbr) > 485.00
THEN high_mpf
ELSE normal_mpf END AS mpf
FROM Foobar AS F0;

Was that sufficiently brutal?
Jul 20 '05 #3
[posted and mailed, please reply in news]

Rowan (ph********@yahoo.com) writes:
So, my MPFbyLine is where my headache, confusion, frustration,
desperation is coming from. A file has many invoices...each invoice
has many lines. Each line has an MPFbyLine value. This is all fine
and dandy...except if the sum of all MPFbyLine values for a file is
less than 25.00 or more than 485.00.

If this is the case then the MPFbyLine for each line in that file must
be recalculated using a formula other than the (.0021 * LineEV).
Instead, the sum of all LineEVs for the file must be deteremined, and
then each LineEV for the file must be divided by the Sum of all to get
each line's percentage of value of the file. Then the new MPFbyLine
is calculated as the line's percentage of value * 25.00min or
485.00max to get its new value.


First, I am sorry that the answer you got from Joe Celko was so completely
inappropriate. He knows his SQL (which is the the same SQL that you and
I use), but his social capabilities is lacking. And apparently he has not
the slightest understanding that some people who post here have a fairly
weak experience of SQL.

Anyway, I looked at your problem, and I think I have a solution. I could
completely make out the exact rules when the total exceeded 485 or was
below 25. But hopefully this gives you some help to find out the rest
yourself.

You discuss the possibility of a computed column, and I have introduced
one. Not so much that it helps us to write the view, but only to make
it less verbose. So this is how tblINVOICE_LINE looks like:

CREATE TABLE tblINVOICE_LINE (
LineID bigint not null primary key identity,
InvoiceID bigint not null,
UnitCost money,
UnitQty money,
AddMMV money,
MinusMMV money,
MinusNDC money,
ErrorAmt money,
LineEV AS UnitQty * UnitCost + AddMMV - MinusMMV - MinusNDC + ErrorAmt,
CONSTRAINT FK_tblINVOICE FOREIGN KEY (InvoiceID) REFERENCES
tblINVOICE(InvoiceID)

)

(By the way, I recommend to always include explicit NULL and NOT NULL
indicators for all columns.)

And here is the view:

CREATE VIEW TEV_view AS
SELECT i.FileNumber, COALESCE (i.InvoiceID, il.InvoiceID) AS
InvoiceID, i.InvoiceNumber, il.LineID, il.UnitQty, il.UnitCost,
il.AddMMV, il.MinusNDC, il.ErrorAmt, il.LineEV,
MPFbyLine = CASE WHEN t.totalEV <= 25 THEN il.LineEV / t.totalEV * 25
WHEN t.totalEV >= 485 THEN il.LineEV / t.totalEV * 480
ELSE 0.0021 * il.LineEV
END
FROM tblINVOICE i
JOIN tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
JOIN (SELECT i.FileNumber, totalEV = SUM(il.LineEV)
FROM tblINVOICE i
JOIN tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
GROUP BY i.FileNumber) AS t ON i.FileNumber = t.FileNumber

I've added a derived table which gives you the totalEV per file.
Derived tables is a very powerful feature in SQL. You can see them
as temporary tables within the query. Logically that is. They are
never materialized, and the optimizer may perform shortcuts with
the rest of the query as long as the result is correct.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Thank you very much, Erland. Your answer here and in previous posts
have been greatly appreciated.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
[posted and mailed, please reply in news]

Rowan (ph********@yahoo.com) writes:
So, my MPFbyLine is where my headache, confusion, frustration,
desperation is coming from. A file has many invoices...each invoice
has many lines. Each line has an MPFbyLine value. This is all fine
and dandy...except if the sum of all MPFbyLine values for a file is
less than 25.00 or more than 485.00.

If this is the case then the MPFbyLine for each line in that file must
be recalculated using a formula other than the (.0021 * LineEV).
Instead, the sum of all LineEVs for the file must be deteremined, and
then each LineEV for the file must be divided by the Sum of all to get
each line's percentage of value of the file. Then the new MPFbyLine
is calculated as the line's percentage of value * 25.00min or
485.00max to get its new value.


First, I am sorry that the answer you got from Joe Celko was so completely
inappropriate. He knows his SQL (which is the the same SQL that you and
I use), but his social capabilities is lacking. And apparently he has not
the slightest understanding that some people who post here have a fairly
weak experience of SQL.

Anyway, I looked at your problem, and I think I have a solution. I could
completely make out the exact rules when the total exceeded 485 or was
below 25. But hopefully this gives you some help to find out the rest
yourself.

You discuss the possibility of a computed column, and I have introduced
one. Not so much that it helps us to write the view, but only to make
it less verbose. So this is how tblINVOICE_LINE looks like:

CREATE TABLE tblINVOICE_LINE (
LineID bigint not null primary key identity,
InvoiceID bigint not null,
UnitCost money,
UnitQty money,
AddMMV money,
MinusMMV money,
MinusNDC money,
ErrorAmt money,
LineEV AS UnitQty * UnitCost + AddMMV - MinusMMV - MinusNDC + ErrorAmt,
CONSTRAINT FK_tblINVOICE FOREIGN KEY (InvoiceID) REFERENCES
tblINVOICE(InvoiceID)

)

(By the way, I recommend to always include explicit NULL and NOT NULL
indicators for all columns.)

And here is the view:

CREATE VIEW TEV_view AS
SELECT i.FileNumber, COALESCE (i.InvoiceID, il.InvoiceID) AS
InvoiceID, i.InvoiceNumber, il.LineID, il.UnitQty, il.UnitCost,
il.AddMMV, il.MinusNDC, il.ErrorAmt, il.LineEV,
MPFbyLine = CASE WHEN t.totalEV <= 25 THEN il.LineEV / t.totalEV * 25
WHEN t.totalEV >= 485 THEN il.LineEV / t.totalEV * 480
ELSE 0.0021 * il.LineEV
END
FROM tblINVOICE i
JOIN tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
JOIN (SELECT i.FileNumber, totalEV = SUM(il.LineEV)
FROM tblINVOICE i
JOIN tblINVOICE_LINE il ON i.InvoiceID = il.InvoiceID
GROUP BY i.FileNumber) AS t ON i.FileNumber = t.FileNumber

I've added a derived table which gives you the totalEV per file.
Derived tables is a very powerful feature in SQL. You can see them
as temporary tables within the query. Logically that is. They are
never materialized, and the optimizer may perform shortcuts with
the rest of the query as long as the result is correct.

Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Mike N. | last post by:
Hello: I am new to T-SQL programing, and relativly new to SQL statements in general, although I have a good understanding of database theory. I'm a little confused as to the fundamental...
3
by: dbtoo_dbtoo | last post by:
One of the databases has 50 views and when I do a db2look I only get schema for 40 of them. If I select from the sysviews, I can see all 50 (the text column contains schema for all 10 (missing)...
8
by: Mike | last post by:
Hello, I have a few rather urgent questions that I hope someone can help with (I need to figure this out prior to a meeting tomorrow.) First, a bit of background: The company I work for is...
3
by: KemperR | last post by:
Hello Experts outhere, may be someone can tell me whats going wrong with my ADOX trial. I have an Access 2002 database with some tables and queries (views) The code listed below works well up...
2
by: dbuchanan52 | last post by:
Hello, I am building an application for Windows Forms using. I am new to SQL Server 'Views'. Are the following correct understanding of their use? 1.) I believe a view can be referenced in a...
28
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...
15
by: rod.weir | last post by:
Fellow database developers, I would like to draw on your experience with views. I have a database that includes many views. Sometimes, views contains other views, and those views in turn may...
33
by: Peter | last post by:
People are telling me it is bad to put select * from <atable> in a view. I better should list all fields of the table inside the definition of the view. I dont know exactly why but some...
7
by: Gary | last post by:
Hello guys! Bear with me, I am a newbie. She is the Data Warehouse manager. She has about 50 users to use the Oracle database from M$ Access via ODBC connection. All those users have only...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.