Solved

using MAX to eliminate second empty (almost) record.

Posted on 2013-12-05
6
234 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 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

690 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