[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

using MAX to eliminate second empty (almost) record.

Posted on 2013-12-05
6
Medium Priority
?
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 49

Accepted Solution

by:
PortletPaul earned 1000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

650 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