1

Let say I've two tables,

Table A 

PK       SIZE  
89733     5 
83644     3  
87351     8
84423     11

Table B

 ID      Table_A_PK  
  1     89733,83644,86455   
  2     87351,89542  
  3     84132
  4     84566,84646

Note: Column Table_A_PK is of collection type, that's why it has many values.

I want to select value of size column of Table A if column PK value exits in Table B's column Table_A_PK

For this I tried this but it's not working and throwing an error 

Select {a.SIZE} 
from {A as a} where {a.PK}  
in ({{ SELECT {b.Table_A_PK} FROM {B as b}

Actual Result: ORA-01722: invalid number

Expected Result

        SIZE  
         5  
         3  
         8
Junaid
  • 664
  • 5
  • 18
  • 35
  • 1
    Storing comma separated values in a column is a **really**, really bad idea to begin with. –  Apr 29 '19 at 14:25
  • Sometimes you have no other option. When working with hybris, you have to work with a lot of legacy stuff you cannot change. – Johannes von Zmuda Apr 29 '19 at 14:44
  • 2
    When you say `table_a_pk` is of collection type, do you actually mean that, in the technical sense? "Collection" is a technical term, and it definitely does **not** mean a comma-separated string of values. And columns of data type `collection` are indeed valid and are better than comma-separated strings. –  Apr 29 '19 at 14:55
  • @JohannesNolte - you always do have other options. If you (or your bosses) decide not to adopt those other options, that's OK, but that's by choice. For example: given a legacy table with comma-separated strings, one can create a proper relational table in first normal form by splitting the strings; rename the old table (for archival purposes) and create a **view** with the old table's name and holding code to recreate the comma-separated strings from the normalized data, so that legacy code doesn't break. All **new** code uses normalized data. Trivial solution, rarely adopted. –  Apr 29 '19 at 14:57
  • Show us the definition of your table or the ddl used to create it. – Kaushik Nayak Apr 29 '19 at 15:08
  • @mathguy a collectiontype in hybris is a datatype that stores a collection as comma-separated-values into a column. It is a (deprecated) option of hybris' own ORM and hybris uses it in his own code to declare complex types that are used throughout the system. You could create a view, of course. However you need a query for that which is part of this question. – Johannes von Zmuda Apr 29 '19 at 16:35
  • Though in case someone uses collectiontypes by choice, I am going to add a hint to my answer. – Johannes von Zmuda Apr 29 '19 at 16:42

2 Answers2

1

First, collectiontypes are deprecated. If you use them by choice, prefer relations. They are much easier to work with.

I realized this once with the LIKE operator: 

... WHERE Table_A_PK LIKE '%MYPK%'

However this is NOT best practice.

You might be able to use the Concat-Funktion to concatenate the % signs with the PK in the original table for a join. However I have not tried this. 

SELECT {a.SIZE} 
FROM {A AS a JOIN B AS b 
ON {b.TABLE_A_PK} LIKE Concat('%', {a.pk}, '%') }
Johannes von Zmuda
  • 1,814
  • 8
  • 14
0

I would suggest to use Relation instead of CollectionType. If you are in the situation where you can't modify the itemType then you can search using LIKE operator

SELECT {a.SIZE}
FROM
{
   B AS b JOIN A AS a
   ON {b.TABLE_A_PK} LIKE CONCAT( '%', CONCAT( {a.PK} , '%' ) )
}
HybrisHelp
  • 5,518
  • 2
  • 27
  • 65
  • Isn't `ON {b.TABLE_A_PK} LIKE CONCAT( '%', CONCAT( {a.PK} , '%' ) )` ambiguous? Ideally, primary keys must be compared with other primary keys to create a join. – Farrukh Chishti May 14 '19 at 06:43
  • 1
    Unfortunately, that is the way here. Because for collection type there is no reference between elements. The collection attribute is stored as a list of PK. Refer the same example [here](https://wiki.hybris.com/display/release5/FlexibleSearch+Tips+and+Tricks) – HybrisHelp May 14 '19 at 06:52