Click here to Skip to main content
15,302,193 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Experts,

I have simple question yet confusing(for me)...

I have a table which results after grabbing a select statement on that table are as follows


select * from VINBINI where  BABLOC='AE084'


BAITEM	               BABLOC	       BAQOH
DU266004817         	AE084     	7
DU266027621         	AE084     	3
DU266029704         	AE084     	4
DU290124118         	AE084     	36
GY407478374         	AE084     	34
GY766723358         	AE084     	0
KU1774913           	AE084     	5
KU1803313           	AE084     	0
KU1905013           	AE084     	0
KU1905213           	AE084     	0
KU1908713           	AE084     	0
KU1915313           	AE084     	7
KU2100663           	AE084     	0
KU2103303           	AE084     	0
KU2137223           	AE084     	0
KU2161003           	AE084     	0
KU2170103           	AE084     	0
KU2176683           	AE084     	6



I have another query where it returns a result set say
Select * from TABLE1

Now is there any way I can call the first select statement to know if there are multiple BAITEM(s) in same BABLOC
Meaning AE084 contains different Items here. like DU, GY,KU etc...
Now I want to add something like
1)
  BABLOC SHARED_BIN
  AE084    "YES"
  AE124     "NO"
  AF158     "YES"

So on...

2)
This should be a value that must be ultimately in reusltset of the select * from TABLE1 query (very huge text here to place)

Can anyone help me with this please?
Posted

I don't know DB2, but in MySql you can do seomething like this:
SQL
select babloc,
if(
  (select count(distinct left(BAITEM,2)) from VINBINI i where i.BABLOC = e.BABLOC) > 1,
  "yes",
  "no"
) as SHARED_BIN
from VINBINI e
group by BABLOC

See: http://sqlfiddle.com/#!9/21716/8[^]

I don't really understand topic 2. But if I see it right, you can simply create a view[^] on top of this query. If regular view would take too much to recalculate, you can use materialized view approach (see: http://www.fromdual.ch/mysql-materialized-views[^])
   
v4
Comments
Maciej Los 26-Oct-15 14:47pm
   
Almost exactly the same as solution 1, but worth a 5!
Zoltán Zörgő 26-Oct-15 14:51pm
   
Thank you. I took some time to make it in sqlfiddle. But it is working at least.
Maciej Los 26-Oct-15 14:52pm
   
I understand it. A 5 is for effort, Zoltan.
sudevsu 27-Oct-15 10:01am
   
Thank you ...
You didn't provide an example of a BABLOC that doesn't have a shared bin, so anything that follow is just a guess.

SQL
select BABLOC, Shared_Bin = case when BinUse > 1 then "Yes" else "No"
from (
select BABLOC, BinUse = count(BAITEM) from VINBINI
group by BABLOC) dt
   
v2
Comments
Maciej Los 26-Oct-15 14:45pm
   
A5!
It was my first thought!
Zoltán Zörgő 26-Oct-15 14:53pm
   
Look promissing, but does not seem to execute. Try it in my sqlfiddle.
Tim Carmichael 26-Oct-15 15:19pm
   
I didn't know that it would be 'exact', but wanted to provide a premise... my background is SQL Server, and it doesn't look like the OP identified the environment.
sudevsu 26-Oct-15 16:34pm
   
This actually didn't work. I have used exactly same thing I get the below error
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case when BinUse > 1 then "Yes" else "No" from (select BABLOC, BinUse = count(BA' at line 1"


BTW, I did this in MYSQL instead of DB2 to check .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900