I have table A with one column as char.
storing data as 1,2,4,7,12
I need to convert to number out put.
The output value has to passed to different table column in number.
Please guide me
Its not clear if you have one row with multiple values all concatenation together to form an impossible to work with string or if you have many rows.
Please share DDL and insert statements to produce what you have.
Is this a one off excercise as you are fixing your data model or will you be doing this multiple times and both tables need to be kept in sync?
2 ) Query
select xpm.pharmacy_code, xpm.description, sum(qoh) qoh
from stock_master xsm, pharmacy_master xpm
where itemcode = '31PGDE0216'
and xpm.pharmacy_key = xsm.pharmacy_key
and xpm.pharmacy_key in (1,2,4,7,12,13,15,16,17,18,19,24,25,26)
group by xpm.pharmacy_code, xpm.description, xpm.pharmacy_key
First query give me recods which is varchar and then need to pass this values in the 2nd query which in number formart.
urgent pls any one can help
B'lve me if you say "urgent" nobody will answer ;-)
You are facing consequences of wrong design as I understand. Data is not normalized.
Anyways, now that you are already into that......
A small hint, tokenize the string and then use for comparison, there are many techniques to split and compare... One of them is like :
WITH dataset AS (SELECT '1,2,3,4,5,6' col FROM DUAL)
select column_value col from dataset, XMLTABLE (REPLACE (col, '''' || col || ''''));
If you have only numbers separated by commas you can do that:
with xxmnc_pos_stock(location_code,pharmacy_key) as ( select 'ABU DHABI','1,2,4,7,12,13,15,16,17,18,19,24,25,26' from dual ) select pkey from xxmnc_pos_stock, xmltable(pharmacy_key passing pharmacy_key columns pkey number path '.') where location_code = 'ABU DHABI' PKEY ---------- 1 2 4 7 12 13 15 16 17 18 19 24 25 26
For other requirements there are other solutions possible.
urgent pls any one can help
Hang on, I'll just check my "urgent" calendar.... erm... I can fit you in Next Wednesday, about 3.30pm.
Seriously, nothing is "urgent" here.
"Urgent" issues are where there is a problem on a live database/server/application, that is causing the company to lose money or potentially causing a data breach etc. For such issues you should be contacting Oracle Support directly using your Oracle Support Identifiers, over on support.oracle.com and not here on the public community manned by volunteers with their own day jobs.
Avoid saying your needs are urgent. Everyone would like their questions answered as soon as possible, but saying it's urgent is you suggesting that those volunteers should drop what they are doing to help you, and that your issue is somehow more important than other people who have already asked questions and are patiently waiting. That's just rude.
Cormaco has shown one way to split your data. There are others ways, as this is traditionally known as the "varying in clause" issue, which is covered in the FAQ:
ok big boss, thanks