Add Count into sql

acerwert · September 22, 2022, 12:47pm

Hello!
I have an Sql where I want to count packages and add these values into to generated tables. I have tried to add count into the sql but only get error messages.

I have tried to add the following sql into the sql
SELECT COUNT(ecarrno) AS Total_packages
FROM O40T2
WHERE O40T2.stato40= '50';

The SQL
select O40T2., R08T1., R04T1., O04T1.custno o04_custno, O04T1., SHIPTO., ASTRO_VIEW_TBL_OEPARTY.,

CASE
WHEN d50zipc = 11821 THEN 'Zon 1'
WHEN d50zipc = 11822 THEN 'Zon 1'
WHEN d50zipc = 11823 THEN 'Zon 1'
WHEN d50zipc = 11824 THEN 'Zon 1'
WHEN d50zipc = 11825 THEN 'Zon 1'
WHEN d50zipc = 11847 THEN 'Zon 1'
WHEN d50zipc = 11848 THEN 'Zon 1'
WHEN d50zipc = 11849 THEN 'Zon 1'
WHEN d50zipc = 11850 THEN 'Zon 1'
WHEN d50zipc = 11851 THEN 'Zon 1'
WHEN d50zipc = 11852 THEN 'Zon 1'
WHEN d50zipc = 11853 THEN 'Zon 1'
WHEN d50zipc = 11854 THEN 'Zon 1'
WHEN d50zipc = 11855 THEN 'Zon 1'

	WHEN d50zipc = 11620 THEN 'Zon 2'
	WHEN d50zipc = 11621 THEN 'Zon 2'
	WHEN d50zipc = 11622 THEN 'Zon 2'
	WHEN d50zipc = 11623 THEN 'Zon 2'
	WHEN d50zipc = 11624 THEN 'Zon 2'
	WHEN d50zipc = 11625 THEN 'Zon 2'
	WHEN d50zipc = 11628 THEN 'Zon 2'
	WHEN d50zipc = 11629 THEN 'Zon 2'
	WHEN d50zipc = 11630 THEN 'Zon 2'
	WHEN d50zipc = 11632 THEN 'Zon 2'
	WHEN d50zipc = 11634 THEN 'Zon 2'
	WHEN d50zipc = 11636 THEN 'Zon 2'
	WHEN d50zipc = 11645 THEN 'Zon 2'
	WHEN d50zipc = 11646 THEN 'Zon 2'
	WHEN d50zipc = 11729 THEN 'Zon 2'
	WHEN d50zipc = 11737 THEN 'Zon 2'
	WHEN d50zipc = 11738 THEN 'Zon 2'
	WHEN d50zipc = 11820 THEN 'Zon 2'
	WHEN d50zipc = 11826 THEN 'Zon 2'
	WHEN d50zipc = 11846 THEN 'Zon 2'
	WHEN d50zipc = 11872 THEN 'Zon 2'
	
	WHEN d50zipc = 11726 THEN 'Zon 3'
	WHEN d50zipc = 11727 THEN 'Zon 3'
	WHEN d50zipc = 11728 THEN 'Zon 3'
	WHEN d50zipc = 11730 THEN 'Zon 3'
	WHEN d50zipc = 11731 THEN 'Zon 3'
	WHEN d50zipc = 11732 THEN 'Zon 3'
	WHEN d50zipc = 11733 THEN 'Zon 3'
	WHEN d50zipc = 11734 THEN 'Zon 3'
	WHEN d50zipc = 11735 THEN 'Zon 3'
	WHEN d50zipc = 11736 THEN 'Zon 3'
	WHEN d50zipc = 11739 THEN 'Zon 3'
	WHEN d50zipc = 11740 THEN 'Zon 3'
	WHEN d50zipc = 11741 THEN 'Zon 3'
	WHEN d50zipc = 11750 THEN 'Zon 3'
	
	WHEN d50zipc = 11631 THEN 'Zon 4'
	WHEN d50zipc = 11633 THEN 'Zon 4'
	WHEN d50zipc = 11635 THEN 'Zon 4'
	WHEN d50zipc = 11637 THEN 'Zon 4'
	WHEN d50zipc = 11638 THEN 'Zon 4'
	WHEN d50zipc = 11639 THEN 'Zon 4'
	WHEN d50zipc = 11640 THEN 'Zon 4'
	WHEN d50zipc = 11641 THEN 'Zon 4'
	WHEN d50zipc = 11642 THEN 'Zon 4'
	WHEN d50zipc = 11643 THEN 'Zon 4'
	WHEN d50zipc = 11647 THEN 'Zon 4'
	WHEN d50zipc = 11648 THEN 'Zon 4'
	WHEN d50zipc = 11661 THEN 'Zon 4'
	WHEN d50zipc = 11662 THEN 'Zon 4'
	WHEN d50zipc = 11663 THEN 'Zon 4'
	WHEN d50zipc = 11664 THEN 'Zon 4'
	WHEN d50zipc = 11665 THEN 'Zon 4'
	WHEN d50zipc = 11666 THEN 'Zon 4'
	WHEN d50zipc = 11667 THEN 'Zon 4'
	WHEN d50zipc = 11668 THEN 'Zon 4'
	
	WHEN d50zipc = 11827 THEN 'Zon 5'
	WHEN d50zipc = 11828 THEN 'Zon 5'
	WHEN d50zipc = 11829 THEN 'Zon 5'
	WHEN d50zipc = 11830 THEN 'Zon 5'
	WHEN d50zipc = 11842 THEN 'Zon 5'
	WHEN d50zipc = 11856 THEN 'Zon 5'
	WHEN d50zipc = 11857 THEN 'Zon 5'
	WHEN d50zipc = 11858 THEN 'Zon 5'
	WHEN d50zipc = 11859 THEN 'Zon 5'
	WHEN d50zipc = 11860 THEN 'Zon 5'
	WHEN d50zipc = 11861 THEN 'Zon 5'
	WHEN d50zipc = 11862 THEN 'Zon 5'
	WHEN d50zipc = 11863 THEN 'Zon 5'
	WHEN d50zipc = 11864 THEN 'Zon 5'
	WHEN d50zipc = 11865 THEN 'Zon 5'
	WHEN d50zipc = 11866 THEN 'Zon 5'
	WHEN d50zipc = 11867 THEN 'Zon 5'
	WHEN d50zipc = 11869 THEN 'Zon 5'
	
	WHEN d50zipc = 11127 THEN 'Zon 6'
	WHEN d50zipc = 11128 THEN 'Zon 6'
	WHEN d50zipc = 11129 THEN 'Zon 6'
	WHEN d50zipc = 11130 THEN 'Zon 6'
	WHEN d50zipc = 11131 THEN 'Zon 6'

END AS Zoner

from O40T2

left join R08T1 on R08T1.shortr08 = O40T2.shortr08

left join R04T1 on R04T1.routeno = R08T1.routeno

left join O06T1 on O06T1.shorto06 = O40T2.shorto06

left join O04T1 on O04T1.shorto04 = O06T1.shorto04

left join (select d13obj td13obj, sysshort tsysshort, roletype troletype,

  d10name1 tname1, d50adrs1 taddressline1, d50adrs2 taddressline2, d50adrs3 taddressline3, d50zipc tpostal, d50city tcity, d50cntry tcountry

  from D13T1

  join D10T1 on D10T1.shortd10=D13T1.shortd10

  join D50T1 on D50T1.adrid= D10T1.adrid )  SHIPTO

on SHIPTO.tsysshort=O40T2.ocarrno and SHIPTO.td13obj=170 and SHIPTO.troletype=5
left join ASTRO_VIEW_TBL_OEPARTY ON ASTRO_VIEW_TBL_OEPARTY.d13obj = 20 and ASTRO_VIEW_TBL_OEPARTY.sysshort = O06T1.shorto04

where O04T1.ordstat= 2

yosiasz · September 22, 2022, 1:25pm

Would help if you posted the error

acerwert · September 22, 2022, 1:30pm

"Error when executing Sql
Column 'O40T2.ocarrno' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ErrorNo: 8120
--> Error on line: 1"

on the following sql

select O40T2., R08T1., R04T1., O04T1.custno o04_custno, O04T1., SHIPTO., ASTRO_VIEW_TBL_OEPARTY., COUNT(ecarrno) AS Total_packages,

CASE
WHEN d50zipc = 11821 THEN 'Zon 1'
WHEN d50zipc = 11822 THEN 'Zon 1'
WHEN d50zipc = 11823 THEN 'Zon 1'
WHEN d50zipc = 11824 THEN 'Zon 1'
WHEN d50zipc = 11825 THEN 'Zon 1'
WHEN d50zipc = 11847 THEN 'Zon 1'
WHEN d50zipc = 11848 THEN 'Zon 1'
WHEN d50zipc = 11849 THEN 'Zon 1'
WHEN d50zipc = 11850 THEN 'Zon 1'
WHEN d50zipc = 11851 THEN 'Zon 1'
WHEN d50zipc = 11852 THEN 'Zon 1'
WHEN d50zipc = 11853 THEN 'Zon 1'
WHEN d50zipc = 11854 THEN 'Zon 1'
WHEN d50zipc = 11855 THEN 'Zon 1'

	WHEN d50zipc = 11620 THEN 'Zon 2'
	WHEN d50zipc = 11621 THEN 'Zon 2'
	WHEN d50zipc = 11622 THEN 'Zon 2'
	WHEN d50zipc = 11623 THEN 'Zon 2'
	WHEN d50zipc = 11624 THEN 'Zon 2'
	WHEN d50zipc = 11625 THEN 'Zon 2'
	WHEN d50zipc = 11628 THEN 'Zon 2'
	WHEN d50zipc = 11629 THEN 'Zon 2'
	WHEN d50zipc = 11630 THEN 'Zon 2'
	WHEN d50zipc = 11632 THEN 'Zon 2'
	WHEN d50zipc = 11634 THEN 'Zon 2'
	WHEN d50zipc = 11636 THEN 'Zon 2'
	WHEN d50zipc = 11645 THEN 'Zon 2'
	WHEN d50zipc = 11646 THEN 'Zon 2'
	WHEN d50zipc = 11729 THEN 'Zon 2'
	WHEN d50zipc = 11737 THEN 'Zon 2'
	WHEN d50zipc = 11738 THEN 'Zon 2'
	WHEN d50zipc = 11820 THEN 'Zon 2'
	WHEN d50zipc = 11826 THEN 'Zon 2'
	WHEN d50zipc = 11846 THEN 'Zon 2'
	WHEN d50zipc = 11872 THEN 'Zon 2'
	
	WHEN d50zipc = 11726 THEN 'Zon 3'
	WHEN d50zipc = 11727 THEN 'Zon 3'
	WHEN d50zipc = 11728 THEN 'Zon 3'
	WHEN d50zipc = 11730 THEN 'Zon 3'
	WHEN d50zipc = 11731 THEN 'Zon 3'
	WHEN d50zipc = 11732 THEN 'Zon 3'
	WHEN d50zipc = 11733 THEN 'Zon 3'
	WHEN d50zipc = 11734 THEN 'Zon 3'
	WHEN d50zipc = 11735 THEN 'Zon 3'
	WHEN d50zipc = 11736 THEN 'Zon 3'
	WHEN d50zipc = 11739 THEN 'Zon 3'
	WHEN d50zipc = 11740 THEN 'Zon 3'
	WHEN d50zipc = 11741 THEN 'Zon 3'
	WHEN d50zipc = 11750 THEN 'Zon 3'
	
	WHEN d50zipc = 11631 THEN 'Zon 4'
	WHEN d50zipc = 11633 THEN 'Zon 4'
	WHEN d50zipc = 11635 THEN 'Zon 4'
	WHEN d50zipc = 11637 THEN 'Zon 4'
	WHEN d50zipc = 11638 THEN 'Zon 4'
	WHEN d50zipc = 11639 THEN 'Zon 4'
	WHEN d50zipc = 11640 THEN 'Zon 4'
	WHEN d50zipc = 11641 THEN 'Zon 4'
	WHEN d50zipc = 11642 THEN 'Zon 4'
	WHEN d50zipc = 11643 THEN 'Zon 4'
	WHEN d50zipc = 11647 THEN 'Zon 4'
	WHEN d50zipc = 11648 THEN 'Zon 4'
	WHEN d50zipc = 11661 THEN 'Zon 4'
	WHEN d50zipc = 11662 THEN 'Zon 4'
	WHEN d50zipc = 11663 THEN 'Zon 4'
	WHEN d50zipc = 11664 THEN 'Zon 4'
	WHEN d50zipc = 11665 THEN 'Zon 4'
	WHEN d50zipc = 11666 THEN 'Zon 4'
	WHEN d50zipc = 11667 THEN 'Zon 4'
	WHEN d50zipc = 11668 THEN 'Zon 4'
	
	WHEN d50zipc = 11827 THEN 'Zon 5'
	WHEN d50zipc = 11828 THEN 'Zon 5'
	WHEN d50zipc = 11829 THEN 'Zon 5'
	WHEN d50zipc = 11830 THEN 'Zon 5'
	WHEN d50zipc = 11842 THEN 'Zon 5'
	WHEN d50zipc = 11856 THEN 'Zon 5'
	WHEN d50zipc = 11857 THEN 'Zon 5'
	WHEN d50zipc = 11858 THEN 'Zon 5'
	WHEN d50zipc = 11859 THEN 'Zon 5'
	WHEN d50zipc = 11860 THEN 'Zon 5'
	WHEN d50zipc = 11861 THEN 'Zon 5'
	WHEN d50zipc = 11862 THEN 'Zon 5'
	WHEN d50zipc = 11863 THEN 'Zon 5'
	WHEN d50zipc = 11864 THEN 'Zon 5'
	WHEN d50zipc = 11865 THEN 'Zon 5'
	WHEN d50zipc = 11866 THEN 'Zon 5'
	WHEN d50zipc = 11867 THEN 'Zon 5'
	WHEN d50zipc = 11869 THEN 'Zon 5'
	
	WHEN d50zipc = 11127 THEN 'Zon 6'
	WHEN d50zipc = 11128 THEN 'Zon 6'
	WHEN d50zipc = 11129 THEN 'Zon 6'
	WHEN d50zipc = 11130 THEN 'Zon 6'
	WHEN d50zipc = 11131 THEN 'Zon 6'

END AS Zoner

from O40T2

left join R08T1 on R08T1.shortr08 = O40T2.shortr08

left join R04T1 on R04T1.routeno = R08T1.routeno

left join O06T1 on O06T1.shorto06 = O40T2.shorto06

left join O04T1 on O04T1.shorto04 = O06T1.shorto04

left join (select d13obj td13obj, sysshort tsysshort, roletype troletype,

  d10name1 tname1, d50adrs1 taddressline1, d50adrs2 taddressline2, d50adrs3 taddressline3, d50zipc tpostal, d50city tcity, d50cntry tcountry

  from D13T1

  join D10T1 on D10T1.shortd10=D13T1.shortd10

  join D50T1 on D50T1.adrid= D10T1.adrid )  SHIPTO

on SHIPTO.tsysshort=O40T2.ocarrno and SHIPTO.td13obj=170 and SHIPTO.troletype=5
left join ASTRO_VIEW_TBL_OEPARTY ON ASTRO_VIEW_TBL_OEPARTY.d13obj = 20 and ASTRO_VIEW_TBL_OEPARTY.sysshort = O06T1.shorto04

where O04T1.ordstat= 2

yosiasz · September 22, 2022, 2:20pm

as the error says you have a count along with a few other columns and so you need to group by all of those columns.

ScottPletcher · September 22, 2022, 2:59pm


select 
    (select COUNT(ecarrno) AS Total_packages FROM O40T2 
        WHERE O40T2.stato40= '50') AS Total_packages,
    <rest_of_sql_as_before...>

acerwert · September 22, 2022, 9:04pm

Thanks! It almost works, when i add this to my excisting sql, it counts and show all the packages from Table O40T2, it doesnt count how many packages there are for seperate orders.

Here it should have been 1 total packages on each row

I also have this sql that shows the correct amount of packages for each order, but I dont know how to integrate that sql to the one i'm working with. So basically I want the whole sql below to be included in the one I first posted.

SELECT OrderKolli.ecarrno AS KolliId,
OrderKolli.stato40 AS KolliStatus,
[Order].regdate AS OrderDatum,
OrderKolli.o40user AS Plockare,
[Order].ordno AS Ordernummer,
Uppdrag.admunit AS Queue,
COUNT(0) AS total_packages

FROM ASTRO_VIEW_CNT_O40T2 AS OrderKolli
LEFT JOIN ASTRO_VIEW_CNT_O40T2 AS KolliCount ON OrderKolli.shortr08 = KolliCount.shortr08
INNER JOIN ASTRO_VIEW_CNT_O06X AS [Order] ON [Order].shortr08 = OrderKolli.shortr08
INNER JOIN ASTRO_VIEW_CNT_L54T1 AS Uppdrag ON OrderKolli.ocarrno = Uppdrag.ocarrno

WHERE OrderKolli.stato40 = 50

GROUP BY OrderKolli.ecarrno, OrderKolli.stato40, [Order].regdate, OrderKolli.o40user, [Order].ordno, Uppdrag.admunit

ScottPletcher · September 22, 2022, 10:23pm


select 
    (select COUNT(O40T2_2.ecarrno) AS Total_packages FROM O40T2 O40T2_2 
        WHERE O40T2_2.stato40= '50' AND O40T2_2.ocarrno = O40T2.ocarrno) AS Total_packages,
    <rest_of_sql_as_before...>

acerwert · September 23, 2022, 6:59am

Big thanks Scott!

acerwert · September 23, 2022, 12:12pm

One more question, I realised that I need all the info from the following sql merged into the first one I posted, any ideas for doing that?

Sql that I want merged into the original

SELECT OrderKolli.ecarrno AS KolliId,
OrderKolli.stato40 AS KolliStatus,
[Order].regdate AS OrderDatum,
OrderKolli.o40user AS Plockare,
[Order].ordno AS Ordernummer,
Uppdrag.admunit AS Queue,
COUNT(0) AS AntalKollin

FROM ASTRO_VIEW_CNT_O40T2 AS OrderKolli
LEFT JOIN ASTRO_VIEW_CNT_O40T2 AS KolliCount ON OrderKolli.shortr08 = KolliCount.shortr08
INNER JOIN ASTRO_VIEW_CNT_O06X AS [Order] ON [Order].shortr08 = OrderKolli.shortr08
INNER JOIN ASTRO_VIEW_CNT_L54T1 AS Uppdrag ON OrderKolli.ocarrno = Uppdrag.ocarrno

WHERE OrderKolli.stato40 = 60

GROUP BY OrderKolli.ecarrno, OrderKolli.stato40, [Order].regdate, OrderKolli.o40user, [Order].ordno, Uppdrag.admunit

Original SQL

select
(select COUNT(O40T2_2.ecarrno) AS Total_packages FROM O40T2 O40T2_2
WHERE O40T2_2.stato40= '50' AND O40T2_2.shortr08 = O40T2.shortr08) AS Total_packages, O40T2., R08T1., R04T1., O04T1.custno o04_custno, O04T1., SHIPTO., ASTRO_VIEW_TBL_OEPARTY.,

CASE
WHEN d50zipc = 11821 THEN 'Zon 1'
WHEN d50zipc = 11822 THEN 'Zon 1'
WHEN d50zipc = 11823 THEN 'Zon 1'
WHEN d50zipc = 11824 THEN 'Zon 1'
WHEN d50zipc = 11825 THEN 'Zon 1'
WHEN d50zipc = 11847 THEN 'Zon 1'
WHEN d50zipc = 11848 THEN 'Zon 1'
WHEN d50zipc = 11849 THEN 'Zon 1'
WHEN d50zipc = 11850 THEN 'Zon 1'
WHEN d50zipc = 11851 THEN 'Zon 1'
WHEN d50zipc = 11852 THEN 'Zon 1'
WHEN d50zipc = 11853 THEN 'Zon 1'
WHEN d50zipc = 11854 THEN 'Zon 1'
WHEN d50zipc = 11855 THEN 'Zon 1'

	WHEN d50zipc = 11620 THEN 'Zon 2'
	WHEN d50zipc = 11621 THEN 'Zon 2'
	WHEN d50zipc = 11622 THEN 'Zon 2'
	WHEN d50zipc = 11623 THEN 'Zon 2'
	WHEN d50zipc = 11624 THEN 'Zon 2'
	WHEN d50zipc = 11625 THEN 'Zon 2'
	WHEN d50zipc = 11628 THEN 'Zon 2'
	WHEN d50zipc = 11629 THEN 'Zon 2'
	WHEN d50zipc = 11630 THEN 'Zon 2'
	WHEN d50zipc = 11632 THEN 'Zon 2'
	WHEN d50zipc = 11634 THEN 'Zon 2'
	WHEN d50zipc = 11636 THEN 'Zon 2'
	WHEN d50zipc = 11645 THEN 'Zon 2'
	WHEN d50zipc = 11646 THEN 'Zon 2'
	WHEN d50zipc = 11729 THEN 'Zon 2'
	WHEN d50zipc = 11737 THEN 'Zon 2'
	WHEN d50zipc = 11738 THEN 'Zon 2'
	WHEN d50zipc = 11820 THEN 'Zon 2'
	WHEN d50zipc = 11826 THEN 'Zon 2'
	WHEN d50zipc = 11846 THEN 'Zon 2'
	WHEN d50zipc = 11872 THEN 'Zon 2'
	
	WHEN d50zipc = 11726 THEN 'Zon 3'
	WHEN d50zipc = 11727 THEN 'Zon 3'
	WHEN d50zipc = 11728 THEN 'Zon 3'
	WHEN d50zipc = 11730 THEN 'Zon 3'
	WHEN d50zipc = 11731 THEN 'Zon 3'
	WHEN d50zipc = 11732 THEN 'Zon 3'
	WHEN d50zipc = 11733 THEN 'Zon 3'
	WHEN d50zipc = 11734 THEN 'Zon 3'
	WHEN d50zipc = 11735 THEN 'Zon 3'
	WHEN d50zipc = 11736 THEN 'Zon 3'
	WHEN d50zipc = 11739 THEN 'Zon 3'
	WHEN d50zipc = 11740 THEN 'Zon 3'
	WHEN d50zipc = 11741 THEN 'Zon 3'
	WHEN d50zipc = 11750 THEN 'Zon 3'
	
	WHEN d50zipc = 11631 THEN 'Zon 4'
	WHEN d50zipc = 11633 THEN 'Zon 4'
	WHEN d50zipc = 11635 THEN 'Zon 4'
	WHEN d50zipc = 11637 THEN 'Zon 4'
	WHEN d50zipc = 11638 THEN 'Zon 4'
	WHEN d50zipc = 11639 THEN 'Zon 4'
	WHEN d50zipc = 11640 THEN 'Zon 4'
	WHEN d50zipc = 11641 THEN 'Zon 4'
	WHEN d50zipc = 11642 THEN 'Zon 4'
	WHEN d50zipc = 11643 THEN 'Zon 4'
	WHEN d50zipc = 11647 THEN 'Zon 4'
	WHEN d50zipc = 11648 THEN 'Zon 4'
	WHEN d50zipc = 11661 THEN 'Zon 4'
	WHEN d50zipc = 11662 THEN 'Zon 4'
	WHEN d50zipc = 11663 THEN 'Zon 4'
	WHEN d50zipc = 11664 THEN 'Zon 4'
	WHEN d50zipc = 11665 THEN 'Zon 4'
	WHEN d50zipc = 11666 THEN 'Zon 4'
	WHEN d50zipc = 11667 THEN 'Zon 4'
	WHEN d50zipc = 11668 THEN 'Zon 4'
	
	WHEN d50zipc = 11827 THEN 'Zon 5'
	WHEN d50zipc = 11828 THEN 'Zon 5'
	WHEN d50zipc = 11829 THEN 'Zon 5'
	WHEN d50zipc = 11830 THEN 'Zon 5'
	WHEN d50zipc = 11842 THEN 'Zon 5'
	WHEN d50zipc = 11856 THEN 'Zon 5'
	WHEN d50zipc = 11857 THEN 'Zon 5'
	WHEN d50zipc = 11858 THEN 'Zon 5'
	WHEN d50zipc = 11859 THEN 'Zon 5'
	WHEN d50zipc = 11860 THEN 'Zon 5'
	WHEN d50zipc = 11861 THEN 'Zon 5'
	WHEN d50zipc = 11862 THEN 'Zon 5'
	WHEN d50zipc = 11863 THEN 'Zon 5'
	WHEN d50zipc = 11864 THEN 'Zon 5'
	WHEN d50zipc = 11865 THEN 'Zon 5'
	WHEN d50zipc = 11866 THEN 'Zon 5'
	WHEN d50zipc = 11867 THEN 'Zon 5'
	WHEN d50zipc = 11869 THEN 'Zon 5'
	
	WHEN d50zipc = 11127 THEN 'Zon 6'
	WHEN d50zipc = 11128 THEN 'Zon 6'
	WHEN d50zipc = 11129 THEN 'Zon 6'
	WHEN d50zipc = 11130 THEN 'Zon 6'
	WHEN d50zipc = 11131 THEN 'Zon 6'

END AS Zoner

from O40T2

left join R08T1 on R08T1.shortr08 = O40T2.shortr08

left join R04T1 on R04T1.routeno = R08T1.routeno

left join O06T1 on O06T1.shorto06 = O40T2.shorto06

left join O04T1 on O04T1.shorto04 = O06T1.shorto04

left join (select d13obj td13obj, sysshort tsysshort, roletype troletype,

  d10name1 tname1, d50adrs1 taddressline1, d50adrs2 taddressline2, d50adrs3 taddressline3, d50zipc tpostal, d50city tcity, d50cntry tcountry

  from D13T1

  join D10T1 on D10T1.shortd10=D13T1.shortd10

  join D50T1 on D50T1.adrid= D10T1.adrid )  SHIPTO

on SHIPTO.tsysshort=O40T2.ocarrno and SHIPTO.td13obj=170 and SHIPTO.troletype=5
left join ASTRO_VIEW_TBL_OEPARTY ON ASTRO_VIEW_TBL_OEPARTY.d13obj = 20 and ASTRO_VIEW_TBL_OEPARTY.sysshort = O06T1.shorto04

where O40T2.stato40= 50