[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Multi-Union query Order by Sections?

Posted on 2013-02-05
10
Medium Priority
?
403 Views
Last Modified: 2013-02-06
Hello,

So I have a Union query with 7 different queries. It is currently working great except for one small detail which is when it orders all of the results, it doesn't sectionalize them as how the SQL code is sectioned.......Here is the SQL code:

PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT rvs_prov_company.full_name AS Provider, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT rvs_prov_company.full_name AS Provider, "see above" AS Street, "see above" AS Street2, "see above" AS City, "see above" AS State, "see above" AS ZipCode, "see above" AS County, "see above" AS Phone, "see above" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT vend_master_v.vendornm AS Vendor, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT rvs_prov_company.full_name AS Provider, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT rvs_prov_company.full_name AS Provider, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT rvs_prov_company.full_name AS Provider, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION SELECT DISTINCT rvs_prov_company.full_name AS Provider, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null));


So you see I need them to be ordered into their respective sections instead of finding for example, ABC MED GRP in 4 out of the 7 queries and ordering them one after another in the result set.

Can someone help me with how to order them please?
0
Comment
Question by:IEHP1
[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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38857604
You could add another field '"1" as orderField', '"2" as orderField' etc  to each section
and order by that.
0
 

Author Comment

by:IEHP1
ID: 38857629
Yes like this:

PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT Category, Entity, Street, Street2, City, State, Zip5, County, Phone, Fax
FROM (
SELECT "Active Provider Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct Providers" AS Category, rvs_prov_company.full_name AS Entity, "see above" AS Street, "see above" AS Street2, "see above" AS City, "see above" AS State, "see above" AS ZipCode, "see above" AS County, "see above" AS Phone, "see above" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct Vendors" AS Category, vend_master_v.vendornm AS Entity, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT "Active PCP Sites-No OB" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct PCPs-No OB" AS Entity, rvs_prov_company.full_name AS Provider, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT "Active PCP Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct PCPs" AS Category, rvs_prov_company.full_name AS Entity, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null));


They don't order themselves as I need them to be seen and I don't want to have to put 1, 2, 3, etc. in front of each one just to order it the way I need it. Is there any way to avoid having to put 1, 2, 3, etc. in front of each one??
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38857646
id you don't specify an order by clause on a select statement then the order in which the result set is return is RANDOM (well down to what the database engine determines is the most efficient manner in which to retrieve the data at that time...)

SQL is not processed in any particular top down manner (apart from the case statement where when expression ordering is important....)

so if you want rows return grouped by and in the order of each unioned select...
then you must ensure that each select has an Indicator value on ... (as jerryb30 has suggested) this however could mean that rows you currently lose as duplicates (you're using UNION not UNION all so an implicit DISTINCT is being applied presently) will no longer be removed so your query may need further modifications to produce the result you desire...

again since you are using UNION there is no point in have  the DISTINCT statement on some of the individual selects as deduplication is going to occur anyway...
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 26

Expert Comment

by:jerryb30
ID: 38857659
If you want to be able to separate out distinct records, you'll have to add some field to retain source ofeach record.
Try UNION ALL between sections, and see if that  helps.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38857686
try this


PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT A.Category, A.Entity, A.Street, A.Street2, A.City, A.State, A.Zip5, A.County, A.Phone, A.Fax
FROM (
SELECT "Active Provider Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct Providers" AS Category, rvs_prov_company.full_name AS Entity, "see above" AS Street, "see above" AS Street2, "see above" AS City, "see above" AS State, "see above" AS ZipCode, "see above" AS County, "see above" AS Phone, "see above" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct Vendors" AS Category, vend_master_v.vendornm AS Entity, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT "Active PCP Sites-No OB" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct PCPs-No OB" AS Entity, rvs_prov_company.full_name AS Provider, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT "Active PCP Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION
SELECT DISTINCT "Active Distinct PCPs" AS Category, rvs_prov_company.full_name AS Entity, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.term_date) Is Null) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))) As A
Order By A.Category
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38859012
My system doesn't like union queries, One way around this is to add the Section to each part of your query, then make that into a subquery and drop the Section from the results of that, similar to:union query
0
 

Author Comment

by:IEHP1
ID: 38860371
Sorry for the late response, but I have been tinkering with the query and have it the way it should be except for one important part, which is that it now has

1. OrderID as the first column (in the order of 1 through 7 sequentially sectionalized as it should be)
2. Category as the second column (in the order sequentially sectionalized as it should be)
3. Entity as the third column (some sections are ordered sequentially while others aren't???) (this is the Provider's [full_name]).  (Sidenote:  not sure why it gave me a 'Duplicate output Alias name' error for "Entity" because the Original version I have still didn't give me any error about that when I had it set up the same??)

I can't figure out why some sections order the [full_name] in Ascending order while others don't??

Anyway here is my SQL for this problem:

PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT "1" AS OrderID, "Active Provider Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
ORDER BY rvs_prov_company.full_name
UNION ALL
SELECT DISTINCT "2" AS OrderID, "Active Distinct Providers" AS Category, rvs_prov_company.full_name AS Entity2, "see above" AS Street, "see above" AS Street2, "see above" AS City, "see above" AS State, "see above" AS ZipCode, "see above" AS County, "see above" AS Phone, "see above" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "3" AS OrderID, "Active Distinct Vendors" AS Category, vend_master_v.vendornm AS Entity3, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT "4" AS OrderID, "Active PCP Sites-No OB" AS Category, rvs_prov_company.full_name AS Entity4, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "5" AS OrderID, "Active Distinct PCPs-No OB" AS Category, rvs_prov_company.full_name AS Entity5, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT "6" AS OrderID, "Active PCP Sites" AS Category, rvs_prov_company.full_name AS Entity6, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "7" AS OrderID, "Active Distinct PCPs" AS Category, rvs_prov_company.full_name AS Entity7, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null));


Notice I have tried to insert an ORDER BY clause to get the first section to order it correctly, but that doesn't work???
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38860462
you only have 1 order by clause and that always comes at the end of the whole statement

your order by clause

order by orderid,category,entity,fullname
0
 

Author Comment

by:IEHP1
ID: 38860469
So hey, I have been working with it and here is the Winner: (capricorn 1 hit the nail on the head here -- details about the number of parentheses helped-like 3 parentheses just before the "AS A")

PARAMETERS [Enter Counties sep by commas, Blank=ALL] Text ( 255 );
SELECT ['Provider Network Snapshot'].OrderID, ['Provider Network Snapshot'].Category, ['Provider Network Snapshot'].Entity, ['Provider Network Snapshot'].street, ['Provider Network Snapshot'].street2, ['Provider Network Snapshot'].city, ['Provider Network Snapshot'].state, ['Provider Network Snapshot'].zip5, ['Provider Network Snapshot'].county, ['Provider Network Snapshot'].phone, ['Provider Network Snapshot'].fax
FROM (SELECT "1" AS OrderID, "Active Provider Sites" AS Category, rvs_prov_company.full_name AS Entity, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "2" AS OrderID, "Active Distinct Providers" AS Category, rvs_prov_company.full_name AS Entity2, "see OrderID 1" AS Street, "see OrderID 1" AS Street2, "see OrderID 1" AS City, "see OrderID 1" AS State, "see OrderID 1" AS ZipCode, "see OrderID 1" AS County, "see OrderID 1" AS Phone, "see OrderID 1" AS Fax
FROM (rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode
WHERE (((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "3" AS OrderID, "Active Distinct Vendors" AS Category, vend_master_v.vendornm AS Entity3, "" AS Street, "" AS Street2, "" AS City, "" AS State, "" AS ZipCode, "" AS County, "" AS Phone, "" AS Fax
FROM rvs_prov_company INNER JOIN ((rv_prov_vendinfo INNER JOIN vend_master_v ON rv_prov_vendinfo.vendor_id = vend_master_v.vendor_id) INNER JOIN zipcode_lu ON vend_master_v.zip5 = zipcode_lu.zipcode) ON rvs_prov_company.prov_keyid = rv_prov_vendinfo.prov_keyid
WHERE (((rv_prov_vendinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT "4" AS OrderID, "Active PCP Sites-No OB" AS Category, rvs_prov_company.full_name AS Entity4, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "5" AS OrderID, "Active Distinct PCPs-No OB" AS Category, rvs_prov_company.full_name AS Entity5, "see OrderID 4" AS Street, "see OrderID 4" AS Street2, "see OrderID 4" AS City, "see OrderID 4" AS State, "see OrderID 4" AS ZipCode, "see OrderID 4" AS County, "see OrderID 4" AS Phone, "see OrderID 4" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((rv_prov_addinfo.sequence)=1) AND ((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT "6" AS OrderID, "Active PCP Sites" AS Category, rvs_prov_company.full_name AS Entity6, rv_prov_addinfo.street, rv_prov_addinfo.street2, zipcode_lu.city, zipcode_lu.state, rv_prov_addinfo.zip5, zipcode_lu.county, rv_prov_addinfo.phone, rv_prov_addinfo.fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null))
UNION ALL
SELECT DISTINCT "7" AS OrderID, "Active Distinct PCPs" AS Category, rvs_prov_company.full_name AS Entity7, "see OrderID 6" AS Street, "see OrderID 6" AS Street2, "see OrderID 6" AS City, "see OrderID 6" AS State, "see OrderID 6" AS ZipCode, "see OrderID 6" AS County, "see OrderID 6" AS Phone, "see OrderID 6" AS Fax
FROM ((rvs_prov_company INNER JOIN rv_prov_addinfo ON rvs_prov_company.prov_mpi_no = rv_prov_addinfo.prov_mpi_no) INNER JOIN zipcode_lu ON rv_prov_addinfo.zip5 = zipcode_lu.zipcode) INNER JOIN rv_prov_spec_info ON rvs_prov_company.prov_keyid = rv_prov_spec_info.prov_keyid
WHERE (((zipcode_lu.preferred)="P") AND ((rv_prov_addinfo.sequence)=1) AND ((rvs_prov_company.from_date)<=[Enter Date Entity was Active]) AND ((rvs_prov_company.term_date) Is Null Or (rvs_prov_company.term_date)>[Enter Date Entity was Active]) AND ((rv_prov_spec_info.spec_code) In ("FP","GP","PD","IM","OBG","OB")) AND ((InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county]))>0 Or (InStr([Enter Counties sep by commas, Blank=ALL],[zipcode_lu].[county])) Is Null)))  AS ['Provider Network Snapshot']
ORDER BY ['Provider Network Snapshot'].OrderID, ['Provider Network Snapshot'].Category, ['Provider Network Snapshot'].Entity;
0
 

Author Closing Comment

by:IEHP1
ID: 38860473
Perfect!! I love it when the actual SQL is written out (copy and paste helps so much!!)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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