Solved

SQL issue for Query Microsoft Access into Oracle SQL DEV

Posted on 2014-12-04
17
185 Views
Last Modified: 2014-12-05
Hi all

I have built an SQL that extract data from a couple of tables.

I just want to remove some duplicate informations to give the report a clean look,

The SQL i use is:
select distinct LOAD.LOAD
  ,LOAD.WEIGHT as LOAD_WEIGHT
  ,LOAD.cube as LOAD_CUBE
  ,FORMAT(LOAD.PERCENTFULL, '0.00') AS PENCENTAGE_FULL
	,LOAD.TRIP
  ,LOAD.TRAVELEDDISTANCE
  ,LOAD.CARRIER
	,LOAD.SERVICE
	,MID(stop.location, INSTR(1, stop.location, ',') + 1, INSTR(INSTR(1, stop.location, ',') + 1, stop.location, ',') - INSTR(1, stop.location, ',') - 1) AS LOCATION
	,LOC_AND_HIB.CITY
	,LOC_AND_HIB.STATE
  ,STOP.DISTANCEFROMLAST
  ,stop.sequence AS SEQUENCE
	,PICK_DROP.PICKDROP
	,SHIPMENT.SHIPMENTID
  ,SHIPMENT.COMMODITYCODE
	,SHIPMENT.WEIGHT as ORD_WEIGHT1
	,SHIPMENT.cube as ORD_CUBE1
  ,SHIPMENT.PIECES AS ORD_PIECES
  ,SHIPMENT.SKIDS as ORD_PAL

FROM (
	(
		Pick_Drop INNER JOIN Shipment ON Pick_Drop.ShipmentID = Shipment.ShipmentID
		) INNER JOIN (
		(
			LOAD INNER JOIN Stop ON LOAD.LOAD = Stop.LOAD
			) INNER JOIN loc_and_hib ON Stop.Location = loc_and_hib.LocationID
		) ON (Pick_Drop.LOAD = LOAD.LOAD)
		AND (Pick_Drop.Sequence = Stop.Sequence)
	)
  
order by LOAD.LOAD
	,stop.sequence;

Open in new window


If you look in the first 13 columns from the BEFORE Picture below, you will see that you have multiple time the same values in multiple rows.

What i would like to do in the below picture AFTER is to leave the spaces empty when it is the same value. It would only show on the first row .

How can i update the Query?

Thanks again
Before.png
after.png
0
Comment
Question by:Wilder1626
  • 6
  • 6
  • 2
  • +1
17 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40481062
Do you want an Access query or an Oracle query?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40481083
If an Oracle query try something like this:
select case when rn=1 then LOAD end LOAD
   ,case when rn=1 then LOAD_WEIGHT end LOAD_WEIGHT
   ,case when rn=1 then LOAD_CUBE end LOAD_CUBE
   ,case when rn=1 then PENCENTAGE_FULL end PERCENTAGE_FULL
   ,case when rn=1 then TRIP end TRIP
   ,case when rn=1 then TRAVELEDDISTANCE end TRAVELDISTANCE
   ,case when rn=1 then CARRIER end CARRIER
   ,case when rn=1 then SERVICE end SERVICE
   ,case when rn=1 then LOCATION end LOCATION
   ,case when rn=1 then CITY end CITY
   ,case when rn=1 then STATE end STATE
   ,case when rn=1 then DISTANCEFROMLAST end DISTANCEFROMLAST
   ,case when rn=1 then SEQUENCE end SEQUENCE
   ,SHIPMENTID
   ,COMMODITYCODE
   ,ORD_WEIGHT1
   ,ORD_CUBE1
   ,ORD_PIECES
   ,ORD_PAL
from (
   select LOAD
      ,LOAD_WEIGHT
      ,LOAD_CUBE
      ,PENCENTAGE_FULL
      ,TRIP
      ,TRAVELEDDISTANCE
      ,CARRIER
      ,SERVICE
      ,LOCATION
      ,CITY
      ,STATE
      ,DISTANCEFROMLAST
      ,SEQUENCE
      ,SHIPMENTID
      ,COMMODITYCODE
      ,ORD_WEIGHT1
      ,ORD_CUBE1
      ,ORD_PIECES
      ,ORD_PAL
      , row_number() over(partition by LOAD ,LOAD_WEIGHT ,LOAD_CUBE ,PENCENTAGE_FULL ,TRIP ,TRAVELEDDISTANCE ,CARRIER ,SERVICE ,LOCATION ,CITY ,STATE ,DISTANCEFROMLAST ,SEQUENCE order by LOAD ,LOAD_WEIGHT ,LOAD_CUBE ,PENCENTAGE_FULL ,TRIP ,TRAVELEDDISTANCE ,CARRIER ,SERVICE ,LOCATION ,CITY ,STATE ,DISTANCEFROMLAST ,SEQUENCE) rn
   from(
         select distinct LOAD.LOAD
         ,LOAD.WEIGHT as LOAD_WEIGHT
         ,LOAD.cube as LOAD_CUBE
         ,FORMAT(LOAD.PERCENTFULL, '0.00') AS PENCENTAGE_FULL
         ,LOAD.TRIP
         ,LOAD.TRAVELEDDISTANCE
         ,LOAD.CARRIER
         ,LOAD.SERVICE
         ,MID(stop.location, INSTR(1, stop.location, ',') + 1, INSTR(INSTR(1, stop.location, ',') + 1, stop.location, ',') - INSTR(1, stop.location, ',') - 1) AS LOCATION
         ,LOC_AND_HIB.CITY
         ,LOC_AND_HIB.STATE
         ,STOP.DISTANCEFROMLAST
         ,stop.sequence AS SEQUENCE
         ,PICK_DROP.PICKDROP
         ,SHIPMENT.SHIPMENTID
         ,SHIPMENT.COMMODITYCODE
         ,SHIPMENT.WEIGHT as ORD_WEIGHT1
         ,SHIPMENT.cube as ORD_CUBE1
         ,SHIPMENT.PIECES AS ORD_PIECES
         ,SHIPMENT.SKIDS as ORD_PAL
         FROM (
	         (
		         Pick_Drop INNER JOIN Shipment ON Pick_Drop.ShipmentID = Shipment.ShipmentID
		         ) INNER JOIN (
		         (
			         LOAD INNER JOIN Stop ON LOAD.LOAD = Stop.LOAD
			         ) INNER JOIN loc_and_hib ON Stop.Location = loc_and_hib.LocationID
		         ) ON (Pick_Drop.LOAD = LOAD.LOAD)
		         AND (Pick_Drop.Sequence = Stop.Sequence)
	         )
   order by LOAD.LOAD
	   ,stop.sequence
   )
);

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40481356
A query is not a report and suppression of values doesn't make sense.  

This is a trivial process in a report.  Just use the Hide duplicates property.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40481490
Hi

slightwv,
 when i try the SQL query, i have this error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'case when rn=1 then LOAD end LOAD'.

So i would probably say that i need to run an Access SQL into SQL Dev.

PatHartman,
let me take a look at the hide duplicated property option
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40481497
Wilder1626,

In your case you could group a report by the fields you want to hide the duplicates of: (ex: Load, LoadWeight, LoadCube, ...Sequence)

Then each one of those "Unique" groupings will be in its own section of the report.
The added benefit here is that with a "Group", you can now get summaries for any of the other fields.
For example (Total OrderWeight, Average OrderCube, ...etc)
...You can also set a page break after each group, ...you can add page numbering, Titles, graphics, Date/Time ...etc

Sample is attached

JeffCoachman
Database40.mdb
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40481515
PatHartman
The  hide duplicated property option hide the entire row. That will not work.

Hi Jeffrey Coachman,
Let me take a lookmat your example. I will be back soon.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40481521
sample report output with Groupings
DataData-in-Grouped-Report.png
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40481522
Data Grouped
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 11

Author Comment

by:Wilder1626
ID: 40481533
This looks very good. Let me see how i can do the same thing with my data base. I just need to understand how you did yours.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40481659
The  hide duplicated property option hide the entire row. That will not work.
It only hides the entire row if every column is identical and you put the hide duplicates on every column (which I have never seen any one do).  Sounds like you need to summarize first.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40482080
Wilder1626,
    " I just need to understand how you did yours. "
I just created a simple report with one grouping level (one of the "repeating" fields)
Then I added all the other repeating fields to that Group level.

You can go into design view of the report to see how it is structured.
So give it a try on a new report in my sample database.

It is fairly straightforward, ...if you need any help let me know.

JeffCoachman
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40483381
Jeffrey Coachman, i'm almost done. Just struggling on 1 small thing.
I can't seem to find out how to put a break line between loads, just to separate them.
Can you guide me on this?
Thanks again
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40483607
Go into design view of the report
right-click in the Load Footer
Select: Properties.
In the "Force New page" property, ...enter "After Section"
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40483802
I dont have Load Footer. How can i add it?
Load footer
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40483813
Oh, then you have to add one...
Go into design view of the form
Right click anywhere and select "Sorting and Grouping"
Select the LOAD group
Select: More
Change to: With a Footer Section
Now you may have to increase the height of this new section so that you can rightclick in it, and set the Force new page property to :After Section
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40484054
Thanks a lot Jeffrey Coachman and also PatHartman for your time.

this is perfect.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 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

20 Experts available now in Live!

Get 1:1 Help Now