0

I'm pretty new at , and hoping that someone can help me out. What I'm hoping for is some advice on how to get locations that have not received, or purchased an item for there collection. In addition to the ones that have. I would like to put a general number or null into the value field.

select 
    location, count(*) as [value]
from 
    item
where 
    creation_date >= datediff (dd, '1 Jan 1970', '1 January 2017')
    and creation_date <= datediff (dd, '1 Jan 1970', '31 December 2017')
    and collection in ('ab', 'blu', 'cd', 'dvd', 'eab', 'ecd', 'edvd', 'frdvd', 'jab', 'jcd', 'jdvd', 'mus', 'vg', 'yaab', 'yacd', 'yadvd')
    and location in ('aal', 'aali', 'aami')
    and item# not in (select item# from po_line_item where item# is not null)
    ---insert horrible stuff here to get total counts by location
    or (item# in (select item# 
                  from po_line_item 
                  where receive_date >= datediff (dd, '1 Jan 1970', '1 January 2017')
                    and receive_date <= datediff (dd, '1 Jan 1970', '31 December 2017')
                 )
        and collection in ('ab','blu','cd','dvd','eab','ecd','edvd','frdvd','jab','jcd','jdvd','mus','vg','yaab','yacd','yadvd')
        and location IN ('aal', 'aali', 'aami')
       )
 group by 
     location
 order by 
     location
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steverino
  • 1
  • Could you please include a bit more detail about what you are trying to accomplish, and provide a simplified reproducible example of the issue you are encountering? – sǝɯɐſ Nov 16 '17 at 20:17
  • I'm trying to pull reports of all locations that have received an item, and have ordered the item. Counting the items that they have ordered or received in the last year. Putting the count next to the location as [value]. The problem is that if they haven't ordered any of the items of this type they will not show in the result. I would like them to show but have a NULL or predefined [value] of 1 – Steverino Nov 16 '17 at 20:21
  • 1
    "I would like to put a general number or null into the value field" which field ? – DaniDev Nov 16 '17 at 20:21
  • I think with your current query, your choices are UNION with a query that returns everything else that your first query doesn't, or you could try something like [this answer](https://stackoverflow.com/a/1400115/1579626), and move your where conditions to the case statement in the count. – sǝɯɐſ Nov 16 '17 at 20:33
  • This query looks bad. I recommend you to learn more simple data with OUTER JOIN and UNION topics. And a hint: you can use a table like two tables like this select ... from item t1, item t2 where t1.id = t2.id ... – missionMan Nov 16 '17 at 20:39
  • Your Where clause is very complex and most likely could be simplified but it would very challenging for us not knowing the Schema structure and underlying logic. It is not clear from your question if you are trying to replace a 'default' in your 'result' (select fields) or in your 'where' clause – DaniDev Nov 16 '17 at 21:28

0 Answers0