Solved

using MAX to eliminate second empty (almost) record.

Posted on 2013-12-05
6
224 Views
Last Modified: 2013-12-27
I have a question. I am running a query in which I join two tables based on a relationship defined on a third table.

I am getting two rows when I add the Item column. I have used the MAX function around the Item Number column and that eliminates the second row which is empty on all relevant columns with the exemption of Item Number, but I am not sure if using this function is recommended.

I am including my query, and the results I get when I run it specifying some where and and conditions.

SELECT
	gle.[Posting Date] AS PostingDate
,	gle.[G_L Account No_] AS GL_AccountNumber
,	ve.[Item No_] AS ItemNumber
,	gle.[Document No_] AS DocumentNumber
,	SUM(gle.Amount) GL_Balance
,	SUM(ve.[Item Ledger Entry Quantity]) AS Quantity
,	SUM(ve.[Cost Amount (Actual)]) + SUM(ve.[Cost Amount (Expected)]) AS Cost
,	SUM(ve.[Cost Amount (Actual)] + ve.[Cost Amount (Expected)]) / NULLIF(SUM(ve.[Item Ledger Entry Quantity]), 0) AS UnitCost
FROM 
	NAV.[dbo].[G_L - Item Ledger Relation] rel FULL OUTER JOIN 
	NAV.dbo.[G_L Entry] gle ON 
	rel.[G_L Entry No_] = gle.[Entry No_] FULL OUTER JOIN
	NAV.dbo.[Value Entry] ve ON 
	rel.[Value Entry No_] = ve.[Entry No_] 
GROUP BY 
	gle.[Posting Date]
,	gle.[G_L Account No_]
,	ve.[Item No_] 	
,	gle.[Document No_]

Open in new window


SELECT * from GL_InventoryValuation] WHERE GL_AccountNumber = '13110' AND PostingDate BETWEEN '1753-01-01' AND '2013-11-23' AND DocumentNumber = 'SPI03164'

I get:


2010-05-28 00:00:00.000	13110	NULL	SPI03164	-24995.95000000000000000000	NULL	NULL	NULL
2010-05-28 00:00:00.000	13110	10828	SPI03164	0.00000000000000000000	0.00000000000000000000	0.00000000000000000000	NULL

Open in new window


Really the second row only shows the Item Number, Is there an efficient method to merge the results?
0
Comment
Question by:metropia
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39698606
So you think you are getting the results you want but you think there may be a more efficient way of going about it?  Or, are you worried that your approach is giving you bad results?
0
 

Author Comment

by:metropia
ID: 39698749
hi sl8rz:

to answer, I think I am getting the results you want but you think there may be a more efficient way of going about it.

Thank you.
0
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 250 total points
ID: 39698868
I will often force the one row that I'm after to the top (by using an "ORDER BY" clause, sometimes using DESC for descending order) then I can grab it by using "TOP 1" like this:

SELECT TOP 1 * FROM myTable ORDER BY dollarAmount DESC

Open in new window

This code would return only the row with the maximun dollarAmount (MAX could be used but there are times when this is better - ie. maybe you want the top 10).  Remember that ORDER BY works on text too.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:metropia
ID: 39698876
can you put an ORDER BY within a view?
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39698894
No, a View is treated like a table when querying occurs against it (even though it doesn't actually exist as a table).  So you can either handle the ORDER BY in code (C#, VB, PHP, whatever) or you can make a simple stored procedure in the database that performs the query using the ORDER BY clause.  Then you just execute that stored proc.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39700198
PostingDate        GL_AccountNumber col3    DocumentNumber
2010-05-28 00:00:00.000    13110    NULL    SPI03164    -24995.95    NULL    NULL    NULL
2010-05-28 00:00:00.000    13110    10828   SPI03164    0.00         0.00    0.00    NULL
                                    ^^^^

Open in new window

what is "col3"?
If you were to run that query for all account numbers would you always get 2 records per account?
is "col3" always NULL for one of the records?
SELECT
      *
FROM GL_InventoryValuation
WHERE GL_AccountNumber = '13110'
      AND PostingDate BETWEEN '1753-01-01' AND '2013-11-23'
      AND DocumentNumber = 'SPI03164'

-- try these (one at a time)
-- and "col3" is null
-- and "col3" is NOT null

Open in new window

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now