?
Solved

Help with union all on select top x statements!

Posted on 2006-04-19
8
Medium Priority
?
251 Views
Last Modified: 2008-03-03
The following runs fine without the union (returns 3 sets), but how do I union these together? I need the 5 most recent records in each select, and I don't know how to union a dynmaic sql statement in to the mix.

TIA!


set @cmd = 'select top 5 isnull(u.userid,'''') as uid, ''CALL'' as type, cdatetime as date,
case when len(rtrim(cext))=3 then ''CALL OUT'' else ''CALL IN'' end + '' (''+ rtrim(pro30.dbo.seconds(cdur)) + '')'' as note from pro30..calls c, pro30..syuuser u where c.cext *= u.phone and
(' + @outc + ' or ' + @inc + ') order by cdatetime desc'


select top 5 adduser as uid, 'NOTE' as type, adddate as date, note as note from arnote where custno = @custno order by adddate desc
 
union all

select top 5 adduser as uid, 'SALE' as type, adddate as date,
('INVNO: ' + ltrim(invno) + '|SONO: ' + rtrim(ltrim(ornum)) + '|AMOUNT: ' + str(invamt,8,2) + '|TERMS: ' + ltrim(pterms)) as note
 from armast where custno = @custno order by adddate desc

union all

exec sp_executesql @cmd
0
Comment
Question by:TerryBurger
  • 5
  • 3
8 Comments
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 16492978
If one of the SELECTs is dynamic SQL, they will all have to be part of the dynamic SQL string. Like so:

    set @cmd = 'select top 5 isnull(u.userid,'''') as uid, ''CALL'' as type, cdatetime as date,
    case when len(rtrim(cext))=3 then ''CALL OUT'' else ''CALL IN'' end + '' (''+ rtrim(pro30.dbo.seconds(cdur)) + '')'' as note from pro30..calls c, pro30..syuuser u where c.cext *= u.phone and
    (' + @outc + ' or ' + @inc + ')
    union all
    select top 5 adduser as uid, ''NOTE'' as type, adddate as date, note as note from arnote where custno = @custno
    union all
    select top 5 adduser as uid, ''SALE'' as type, adddate as date,
    (''INVNO: '' + ltrim(invno) + ''|SONO: '' + rtrim(ltrim(ornum)) + ''|AMOUNT: '' + str(invamt,8,2) + ''|TERMS: '' + ltrim(pterms)) as note
    from armast where custno = @custno
    order by date desc'
   
    exec sp_executesql @cmd

Jeff
0
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 16492996
Sorry, slight change to handle the other local variable (@custno):

    set @cmd = 'select top 5 isnull(u.userid,'''') as uid, ''CALL'' as type, cdatetime as date,
    case when len(rtrim(cext))=3 then ''CALL OUT'' else ''CALL IN'' end + '' (''+ rtrim(pro30.dbo.seconds(cdur)) + '')'' as note from pro30..calls c, pro30..syuuser u where c.cext *= u.phone and
    (' + @outc + ' or ' + @inc + ')
    union all
    select top 5 adduser as uid, ''NOTE'' as type, adddate as date, note as note from arnote where custno = ''' + @custno + '''
    union all
    select top 5 adduser as uid, ''SALE'' as type, adddate as date,
    (''INVNO: '' + ltrim(invno) + ''|SONO: '' + rtrim(ltrim(ornum)) + ''|AMOUNT: '' + str(invamt,8,2) + ''|TERMS: '' + ltrim(pterms)) as note
    from armast where custno = ''' + @custno + '''
    order by date desc'
   
    exec sp_executesql @cmd

Jeff
0
 

Author Comment

by:TerryBurger
ID: 16493039
Hi Jeff, the union works, but without the order by in each catagory I don't get the 5 most recent items from each. Any suggestions?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 16493157
Hi TerryBurger,

Are you sure? I get the 5 top values per the ORDER BY when I run it. Of course, I don't have your exact table structure and data, but I ran something like this to test it:

    SET @cmd = 'SELECT TOP 5 Field1,
        Field2
    FROM Table1
    WHERE Field2 = 123
    UNION ALL SELECT TOP 5 Field1,
        Field2
    FROM Table1
    WHERE Field2 = 234
    UNION ALL SELECT TOP 5 Field1,
        Field2
    FROM Table1
    WHERE Field2 = 345
    ORDER BY Field1 DESC'
   
    EXEC sp_executesql @cmd

The results I get are:

    Field1      Field2
    397612    123
    397611    123
    397610    123
    397609    123
    397608    123
    397460    234
    397426    234
    397222    234
    397221    234
    397187    234
    393931    345
    372534    345
    371617    345
    371258    345
    371257    345

Field1 is an identity column, and those values do correspond to the 5 highest for each of the Field2 "categories" represented. If you're still getting incorrect results, could you tell me about your table schema and some representative data? I will try to figure out what's going wrong.

Jeff
0
 

Author Comment

by:TerryBurger
ID: 16493277
There is really nothing special about the data, as in each case adddate resolves to a datetime (might be a small datetime in one of the selects, could short datetime not play well with datetime?)

Here are my results:

<dynamic union as we discussed:>

ALBL      NOTE      2006-04-17 07:15:00.000      MORE ON THE WAY VIA 2 DAY -                                                                  
ALBL      NOTE      2006-04-11 09:15:00.000      SENT A BACKORDER OF NS AND RYO -                                                                                                                                                                        
ALBL      NOTE      2006-04-10 07:14:00.000      2 DAY FOR MIKE - CALL WHEN NS ARE BACK-IN-                                                                                                                                                              
ALBL      NOTE      2006-04-05 10:26:00.000      .                                                                                                                                                                                                      
ALBL      NOTE      2006-04-05 07:31:00.000      MIKE WILL GET HIS B/O STUFF ON MONDAY - TOO MANY CHECKS IN 1 WEEK -                                                                                                                                    
ALBL      CALL      2006-03-10 08:09:45.000      CALL OUT (0:00)
ALBL      CALL      2006-03-09 12:30:30.000      CALL OUT (0:01)
ALBL      CALL      2006-03-06 07:23:26.000      CALL OUT (0:00)
ARTW      SALE      2005-10-13 11:50:00.000      INVNO: 626576|SONO: 624919|AMOUNT:    14.55|TERMS: Net 15 Days        
ALBL      CALL      2005-09-29 12:03:21.000      CALL OUT (0:00)
ALBL      CALL      2005-09-21 12:05:37.000      CALL OUT (0:01)
CARM      SALE      2005-06-13 14:02:00.000      INVNO: 608834|SONO: 607344|AMOUNT:  3969.25|TERMS: C.O.D.              
ALBL      SALE      2005-04-11 00:00:00.000      INVNO: 600935|SONO: 599588|AMOUNT:  3036.71|TERMS: C.O.D.              
AUTO      SALE      2004-07-19 00:00:00.000      INVNO: 568433|SONO: 567999|AMOUNT:  3070.55|TERMS: C.O.D.              
ALOC      SALE      2003-05-27 00:00:00.000      INVNO: 700766|SONO: 700813|AMOUNT:  2355.80|TERMS: C.O.D.  


<< Without union, 3 seperate sets:>

ALBL      NOTE      2006-04-17 07:15:00      MORE ON THE WAY VIA 2 DAY -                                                                                                                                                                            
ALBL      NOTE      2006-04-11 09:15:00      SENT A BACKORDER OF NS AND RYO -                                                                                                                                                                        
ALBL      NOTE      2006-04-10 07:14:00      2 DAY FOR MIKE - CALL WHEN NS ARE BACK-IN-                                                                                                                                                              
ALBL      NOTE      2006-04-05 10:26:00      .                                                                                                                                                                                                      
ALBL      NOTE      2006-04-05 07:31:00      MIKE WILL GET HIS B/O STUFF ON MONDAY - TOO MANY CHECKS IN 1 WEEK -


ALBL      SALE      2006-04-17 00:00:00      INVNO: 652803|SONO: 765691|AMOUNT:  2959.11|TERMS: C.O.D.              
ALBL      SALE      2006-04-11 14:46:00      INVNO: 651940|SONO: 764929|AMOUNT:   343.42|TERMS: C.O.D.              
ALBL      SALE      2006-04-10 14:57:00      INVNO: 651657|SONO: 764600|AMOUNT:  3026.07|TERMS: C.O.D.              
ALBL      SALE      2006-04-03 11:08:00      INVNO: 650485|SONO: 763433|AMOUNT:  2287.11|TERMS: C.O.D.              
MMAC      SALE      2006-03-30 09:05:00      INVNO: 650001|SONO: 762998|AMOUNT:   473.66|TERMS: C.O.D.          


            CALL      2006-04-17 06:15:19.000      CALL IN (0:11)
ALBL      CALL      2006-04-11 08:18:06.000      CALL OUT (0:00)
          CALL      2006-04-10 06:14:38.000      CALL IN (0:10)
ALBL      CALL      2006-04-05 06:33:28.000      CALL OUT (0:03)
          CALL      2006-04-03 06:14:46.000      CALL IN (0:10)
 
                                                                                                                                 

Note the "SALE" category, I get 4 from April 06, and 1 from March 06. In the combined statement above, all sales seem to fall in 2005.
I seem to remember generally you can't use order by within a union statement unless you also have a top x command in the same statement, but in this it doesn't allow the order by in each. Maybe I need to rearrange some parentheses?
0
 
LVL 8

Accepted Solution

by:
Jeff Tennessen earned 2000 total points
ID: 16493413
I see what you're saying. Try this and see if you get what you're expecting:

    set @cmd = 'select * from (select top 5 isnull(u.userid,'''') as uid, ''CALL'' as type, cdatetime as date,
    case when len(rtrim(cext))=3 then ''CALL OUT'' else ''CALL IN'' end + '' (''+ rtrim(pro30.dbo.seconds(cdur)) + '')'' as note from pro30..calls c, pro30..syuuser u where c.cext *= u.phone and
    (' + @outc + ' or ' + @inc + ') order by cdatetime desc) q1
    union all select * from
    (select top 5 adduser as uid, ''NOTE'' as type, adddate as date, note as note from arnote where custno = ''' + @custno + ''' order by adddate desc) q2
    union all select * from
    (select top 5 adduser as uid, ''SALE'' as type, adddate as date,
    (''INVNO: '' + ltrim(invno) + ''|SONO: '' + rtrim(ltrim(ornum)) + ''|AMOUNT: '' + str(invamt,8,2) + ''|TERMS: '' + ltrim(pterms)) as note
    from armast where custno = ''' + @custno + '''
    order by adddate desc) q3'
   
    exec sp_executesql @cmd

Jeff
0
 

Author Comment

by:TerryBurger
ID: 16493444
Works great! I just added an additonal 'order by date' and I'm set. Thanks Jeff!


Terry
0
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 16493492
You're welcome, Terry. Glad I could help!

Jeff
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

864 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