Sample queries for items in a catalog

These sample queries search for items in a catalog.

Sample 1

This sample query searches for all items in catalog ctg01 for which the value of the attribute pk starts with the characters item.

select item 
from catalog('ctg01') 
where item['spec01/pk'] like 'item%'

Sample 2

This sample query searches for all items in catalog ctg01 for which the value of the attribute pk is not empty.

select item 
from catalog('ctg01') 
where item['spec01/pk'] is not null

Sample 3

This sample query searches for all items in catalog ctg01 whose name attribute value starts with na and whose age attribute value is greater than 12.

select item 
from catalog('ctg01') 
where item['spec01/name'] like 'na%' and item['spec01/age'] > 12

Sample 4

This sample query searches for the primary keys of the items in catalogctg01 whose name attribute value starts with na and whose age attribute value is greater than 12.

select item.pk 
from catalog('ctg01') 
where item['spec01/name'] like 'na%' and item['spec01/age'] > 12

Sample 5

This sample query searches for the names of the items in catalog ctg01 whose age attribute value is greater than 12.

select item['spec/name'] 
from catalog('ctg01') 
where item['spec01/age'] > 12

Sample 6

This sample query searches for the names of the items in catalog ctg01 whose city address is San Jose and whose street address is 555 Bailey Ave.

select item['spec/name'] 
from catalog('ctg01') 
where item['spec01/address/street#a1'] = '555 Bailey Ave'
and item['spec01/address/city#a1'] = 'San Jose'

In this sample, ['spec01/address'] is a multi-occurrence attribute. An item (for example, a person), can have multiple addresses. Each address has fields for city and street. If a person has two addresses, such as 555 Bailey Ave/Cupertino and 666 Main Street/San Jose, then the fields for city and street each have two values. The #a1 in the query indicates that any multi-occurrence is not to be returned. If you do not use #a1 in the query, then this item is returned with both values included; that is. ['spec01/address/street'] = {'555 Bailey Ave', '666 Main Street'} and ['spec01/address/city'] = {'San Jose', 'Cupertino'}.

Sample 7

This sample query searches for the description of all items in catalog grocery store catalog with a primary spec spec.

select item[‘spec/desc']
from catalog(‘grocery store catalog')

Sample 8

This sample query searches for the description of all items that are in catalog ctg with a primary spec spec, where the price is greater than 10.

select item[‘spec/desc']
from catalog(‘grocery store catalog')
where item[‘spec/price'] > 10

Sample 9

This sample query searches for all items that are mapped to a category that uses a spec that has an attribute name that contains cookie.

select item
from catalog(‘grocery store catalog')
where item.category.spec.attribute_path like ‘%cookie%'

Sample 10

This sample query searches for the primary keys and the descriptions of all items in catalog grocery store catalog with a primary spec spec, where the price is greater than 10.

select item.pk, item[‘spec/desc']
from catalog(‘grocery store catalog')
where item[‘spec/price'] > 10

Sample 11

This sample query searches for the descriptions of all items in catalog grocery store catalog with a primary spec spec and the categories that the item that is mapped to, where the price is greater than 10.

select item[‘spec/desc'], item.category
from catalog(‘grocery store catalog')
where item[‘spec/price'] > 10

Sample 12

The primary key of all items that are for more than $100 in any location in the location hierarchy grocery chain stores.

select item.pk
from catalog(‘ctg')
where item.location.hierarchy.name = ‘grocery chain stores'
and item[location:loc spec/price'] > 100

Sample 13

This sample query searches a user-defined log by the primary key of an item to return user-defined log entries. An item might have multiple entries in a user-defined log. 

select item.logentry 
from catalog('BasicAPICatalog') 
where item.pk = ‘xyz'
and item.logentry.log.name = ‘My UDL'

Sample 14

This sample query searches multiple user-defined logs by the primary key of an item. Multiple user-defined logs can be associated with a single container such as a catalog or hierarchy. An item can have entries in multiple user-defined logs that are associated with a catalog. 

select item.logentry 
from catalog('BasicAPICatalog') 
where item.pk = ‘Item1'
and item.logentry.log.name in ( ‘My UDL1', ‘My UDL2','My UDL3')

Sample 15

This sample query searches a user-defined log by the logentry timestamp of an item.

select item.logentry 
from catalog('BasicAPICatalog') 
where 
item.logentry.timestamp > timestamp(‘08/11/21 01:12:33','yy/MM/dd hh:mm.ss')
and item.logentry.log.name = ‘My UDL'

Sample 16

This sample query searches a user-defined log for log messages by the primary key of an item. 

select item.logentry.message 
from catalog('BasicAPICatalog') 
where item.pk = ‘xyz'
and item.logentry.log.name = ‘My UDL'

Sample 17

This sample query searches for the item timestamp in a user-defined log by the primary key of an item. This is useful for identifying the modification date and time of an item.

select item.logentry.timestamp 
from catalog('BasicAPICatalog') 
where item.pk = ‘xyz'
and item.logentry.log.name = ‘My UDL'

Sample 18

This sample query searches for items based on the logentry timestamp. 

select item 
from catalog('BasicAPICatalog') 
where 
item.logentry.timestamp = timestamp(‘08/11/21 01:12:33', 'yy/MM/dd hh:mm.ss')
and item.logentry.log.name = ‘My UDL'

Sample 19

This sample query searches for items where the logentry timestamp is within a specified range.

select item 
from catalog('BasicAPICatalog') 
where 
item.logentry.timestamp >= timestamp(‘08/11/21 01:12:33', 'yy/MM/dd hh:mm.ss')
and 
item.logentry.timestamp <= timestamp(‘08/11/25 01:12:33', 'yy/MM/dd hh:mm.ss')
and item.logentry.log.name = ‘My UDL'

Sample 20

This sample query searches for log messages by the primary key of an item and the logentry timestamp. 

select item.logentry.message 
from catalog('BasicAPICatalog') 
where item.pk = ‘xyz'
and item.logentry.timestamp=timestamp(‘08/11/21 01:12:33', 'yy/MM/dd hh:mm.ss')
and item.logentry.log.name = ‘My UDL'

Sample 21

This sample query displays the average value of the number-RTS attribute for the items in the Search Ctg catalog. 

select avg(item[‘Search Ctg Spec/number-RTS'])
from catalog(‘Search Ctg')
where item.pk is not null

Sample 22

This sample query displays the maximum value of the attribute number-RTS from among the items in the Search Ctg catalog.

select max(item[‘Search Ctg Spec/number-RTS'])
from catalog(‘Search Ctg')
where item.pk is not null

Sample 23

This sample query displays the minimum value of the attribute number-RTS from among the items in the Search Ctg catalog.

select min(item[‘Search Ctg Spec/number-RTS'])
from catalog(‘Search Ctg')
where item.pk is not null

Sample 24

This sample query displays the sum of the values of the attribute number-RTS for the items in the Search Ctg catalog.

select sum(item[‘Search Ctg Spec/number-RTS'])
from catalog(‘Search Ctg')
where item.pk is not null

Sample 25

This sample query displays the number of rows that are defined by the attribute number-RTS for the items in the Search Ctg catalog.

select count(item[‘Search Ctg Spec/number-RTS'])
from catalog(‘Search Ctg')
where item.pk is not null

Sample 26

This sample query displays the number of items in the Search Ctg catalog.

select count(*)
from catalog(‘Search Ctg')
where item.pk is not null

Sample 27

This sample query displays the average value of attribute number-RTS that grouped by the attribute string-RTS in the Search Ctg catalog. 

select item[‘Search Ctg Spec/string-RTS'], 
avg(item[‘Search Ctg Spec/number-RTS'])
from catalog(‘Search Ctg')
where item.pk is not null
group by item[‘Search Ctg Spec/string-RTS']

Sample 28

This sample query displays the values of the number-RTS attribute over the items from rows 100 to 200 in the Search Ctg catalog.

select range 100 to 200 
item[‘Search Ctg Spec/number-RTS'])
from catalog(‘Search Ctg')
where item.pk is not null

Sample 29

This sample query displays the first 100 items in the Search Ctg catalog.

select first 100 item
from catalog(‘Search Ctg')
where item.pk is not null

Sample 30

This sample query displays the last 100 items in the Search Ctg catalog.

select last 100 item
from catalog(‘Search Ctg')
where item.pk is not null

Sample 31

This sample query demonstrates the use of a dynamic search attribute on the right side of a predicate.

select item
from catalog(‘Search Ctg')
where item['Search Ctg'/string] = item['Search Ctg'/string2]

Sample 32

This sample query is a generic WQL subquery.

select item
from catalog(‘Search Ctg')
where item.pk in (select item.pk from catalog('Search Ctg2')
                  where item['Search Spec 2/string'] like 'item%')

Sample 33

This sample query demonstrates the use of an embedded SQL subquery with an 'in' clause.

select item
from catalog(‘Search Ctg')
where item.pk in SQL('select itm_primary_key from itm
                      where itm_container_id = 1001')

Sample 34

This sample query demonstrates the use of a WQL subquery with an 'in' clause.

select item
from catalog(‘Search Ctg')
where item.pk in (select item.pk from catalog('Search Ctg2')
                  where item['Search Spec 2/string'] like 'item%')

Sample 35

This sample query demonstrates a generic SQL subquery in a predicate.

select item
from catalog(‘Search Ctg')
where item.pk = SQL('select itm_primary_key from itm
                     where itm_container_id = 1001')

Sample 36

This sample query demonstrates the use of location attributes in a WQL query.

select item.location
from catalog(‘Search Ctg')
where item['location:Search Loc Spec/attr1'] = 'abc'

Sample 37

This sample query demonstrates the use of the version() function to return the version ID of an item based on the version name in a WQL query. You can only use the < and > operators.

select item
from catalog('Search Ctg')
where item.version < version('version1')

Sample 38

This sample query demonstrates the use of Relationship, Link, or Timezone attributes with ALL or ANY condition.
Important: This sample query does not adhere to the standard WQL query for searching item for the multi-occurrence attributes.
Type Sample
Relationship attribute with ‘All’ condition 
select item.pk 
   from catalog('test ctg') 
   where (
   item['test spec/relation'] .catalog.name='10416 ctg' 
   and item['test spec/relation'].pk  = '1'
   ) 
   intersect 
   select range 1 to 2 item.pk 
   from catalog('test ctg') 
   where (
   item['test spec/relation'] .catalog.name='10416 ctg' 
   and item['test spec/relation'].pk  = '2'
   )
Relationship attribute with ‘Any’ condition 
select item.pk 
   from catalog('test ctg') 
   where (
   item['test spec/relation'] .catalog.name='10416 ctg' 
   and item['test spec/relation'].pk  = '1'
   ) 
   intersect 
   select range 1 to 2 item.pk 
   from catalog('test ctg') 
   where (
   item['test spec/relation'] .catalog.name='10416 ctg' 
   and item['test spec/relation'].pk  = '2'
   )
Link attribute with ‘All’ condition 
select item.pk 
   from catalog('test ctg') 
   where (
   item['test spec/link'] .pk  = '1'
   ) 
   intersect 
   select item.pk  
   from catalog('test ctg') 
   where (
   item['test spec/link'] .pk  = '2'
   )

Sample 39

This sample query demonstrates the use of a secondary attribute in the where clause of a WQL query.
select category.item.pk, category.item['Primary_Spec/Primary_key'] 
from hierarchy('Hierarchy_Name') where category.pk = 'Sample_PK' and 
category.item['Secondary_Spec/Date'] >= date('Mon Dec 01 00:00:00 CEST 2019', 'EEE MMM dd HH:mm:ss zzz yyyy') 
and category.item['Secondary_Spec/Date'] <= date('Mon Dec 15 23:59:59 CEST 2019', 'EEE MMM dd HH:mm:ss zzz yyyy') 
and category.item['Primary_Spec/Primary_key'] is not null
Important: If there is more than one attribute in the clause, select then you need to explicitly mention the same attribute in the where clause by using the NOT NULL constraint. This is a known limitation.