Need Calculation Performed Without Modifying Base Table(s)

Question

Post reply

Need Calculation Performed Without Modifying Base Table(s)

btodd 61953

SSC Journeyman

Points: 94
More actions
November 4, 2010 at 4:07 pm

#142327

Hello,
Problem: We have a database that splits records based on UnitCost and rounding. What occurs is when Receiving enters a transaction for 100 bags, two rows appear in the table. One with a Quantity of 1 and another row with a Quantity of 99. I am unable to configure Crystal Reports to perform a SUM on the Quanity field because all of the other fields in the row are the same. Only the Quantity is unique. Because of this, a DISTINCT clause is not working and two pages per report are printed, one with a Quantity of 1 and the next with a Quantity of 99. I am UNABLE TO MODIFY BASE TABLES.
Attempted Solution: I tried to create a View with a QtyTotal column that would SUM the data for those rows where the transactions split. I continue to receive errors when I try to perform a calculation similar to:
QtyTotal = SUM(Quantity)
Where SerialNum = SerialNum and Recipt=Receipt
If I use the above in a query against the table I receive the data I am looking for, but when I attempt an ALTER VIEW I receive errors. From research on this it appears that I will not be able to use a View to achieve what I want to do but wanted to check to see what other options I might have available to me.
I'm not necessarily looking for anyone to do the work for me, just maybe provide some suggestions on the best, or least intrusive to the database, method to use to accomplish what I need to have done.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please post the error message you get as well as the statement you want to use to create the view.

I guess a view is the right way to do it.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

btodd 61953 SSC Journeyman Points: 94 More actions · Answer 2

Thanks Lutz.

Here is the update I've been trying to make...

UPDATE viewname

SET QtyTotal = SUM(QUANTITY)

WHERE SERIALNUM = SERIALNUM and RECEIPT = RECEIPT

and here is the error I receive...

"Msg 4406, Level 16, State 1, Line 1

Update or insert of view or function 'viewname' failed because it contains a derived or constant field."

btodd 61953 SSC Journeyman Points: 94 More actions · Answer 3

Here's the statement I used to create the view. I'm not too familiar with the SQL needed to do things in one shot.

CREATE VIEW [dbo].[viewname]

AS

SELECT '' AS QtyTotal, RECEIPTNUM, RCPTLNNM, SLTSQNUM, SERIALNUM, QUANTITY, DATERECD, DTSEQNUM, UNITCOST, TRXSORCE, ITEMNMBR, QTYTYPE, BIN, MFGDATE, EXPNDATE, ROW_ID

FROM tablename

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

A view doesn't store data. A view is just a saved SQL statement. If you update a view the update is pushed to the base table. Since you don't want to update the base table and the column you're trying to assign to doesn't exist there, it's not going to work well.

What you need to do is define the column with the appropriate calculation in the view's definition (the CREATE VIEW).

Now, I'm not even sure what you were trying to do in the update

WHERE SERIALNUM = SERIALNUM and RECEIPT = RECEIPT

Might as well say WHERE 1 = 1 AND 0 = 0, that's filtering absolutely nothing. I think we're going to need a fair bit more detail here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 5

How about the following approach?

CREATE VIEW [dbo].[viewname]

AS

SELECT SUM(QUANTITY) AS QtyTotal, RECEIPTNUM, RCPTLNNM, SLTSQNUM, SERIALNUM, QUANTITY, DATERECD, DTSEQNUM, UNITCOST, TRXSORCE, ITEMNMBR, QTYTYPE, BIN, MFGDATE, EXPNDATE, ROW_ID

FROM tablename

group by RECEIPTNUM, RCPTLNNM, SLTSQNUM, SERIALNUM, QUANTITY, DATERECD, DTSEQNUM, UNITCOST, TRXSORCE, ITEMNMBR, QTYTYPE, BIN, MFGDATE, EXPNDATE, ROW_ID

You might need to either remove the ROW_ID column from your table or change it to an aggregate function (e.g. MAX(ROW_ID) in your select statement and remove it from the GROUP BY clause. Don't know since I don't have any sample data... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

btodd 61953 SSC Journeyman Points: 94 More actions · Answer 6

Thanks for your help Lutz, I really appreciate it.

I was able to create the View I need with your help and a couple of modifications. I've been working on this for 3 days and finally can move on to my next challenge.

LutzM SSC Guru Points: 107049 More actions · Answer 7

Glad I could help 😀

If you get stuck with your next challenge, post back (or open another thread if not directly related). 😉

Good luck!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 8

Keep in mind that you won't be able to update this view. Since it seems like your issue is with the number of rows being printed on a report, then it shouldn't matter. But keep it in mind.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

btodd 61953 SSC Journeyman Points: 94 More actions · Answer 9

Thanks for the tip Wayne.

In trying to research and find a solution on my own, I read through A LOT of information about Views. I just want to use this for reporting an accurate Quantity Total, so updating the View shouldn't have to be done. There's A LOT of information to learn about SQL though and I think I've covered about 0.01%.

Still reading though...

I appreciate yours and everyone else's help!