Here is what i have in ms-sql, how to convert this into t-sql ?
@MortgagePurposeID is parameter with comma seperated values ('1,2,3,4')
if(substring(@MortgagePurposeID, LEN(@MortgagePurposeID)-1,1)<>'','')
Set @MortgagePurposeID = @MortgagePurposeID + '',''
Set @pos=0
I have the following query : for :P_LEG_NUM Parameter when i am passing values like 1,2,5 as string type i am getting invalid number error... I have defined in clause for it but still it does not work.. For individual values like 2, etc it works... how can i pass comma separated values for this bind variable
select trip_number as prl_trip_number, flight_number as prl_f_number, trip_leg_id as prl_trip_leg_id, leg_number as prl_leg_num, dicao as prl_dicao, [code]........
I have a few questions about querying using ranges and comma separated lists. The basic situation is a request comes in with part numbers that can be formatted in a range, comma separated lists or both. For an example, the request contains the following part numbers:
<pnum> 1-10, 14, 17, 11, 21-24 </pnum>
I can muster a basic SQL statement to query for this by hand (more then one way to do this)-
SELECT * FROM part_table WHERE pnum BETWEEN '1' AND '10' OR pnum BETWEEN '21' AND '24' OR pnum IN (14, 17, 11);
is there a way to create the BETWEEN statement so that the dash doesnt need to be parsed out of the request? (like BETWEEN '1-10') or something that functions to that extent? Is it also possible to nest the BETWEEN statements (or the functionality of the BETWEEN) in the IN statement?
Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?
I am trying to split comma separated string. My table has more than 5 lacks data. I have tried the following SQL but its taking more than 5 minutes. Any Alternative solution to return data quickly ?
SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id FROM order_detail CONNECT BY REGEXP_SUBSTR(order_id,'[^,]+',1,LEVEL) IS NOT NULL
SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id FROM order_detail CONNECT BY LEVEL <= LENGTH(order_id) - LENGTH(REPLACE(order_id, ',')) + 1
No err-----------------------------1 rishi,rahul2 rishi,ak I want output like:
No ERR1 rishi1 rahul2 rishi2 ak i am using the below query for this:
select no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but this query is giving me output:
1rishi1rahul2ak2rishi1rahul2ak if i am using distinct then only desired output is coming. select distinct no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but i don't want to use distinct because my table has millions of rows and err contains comma separated varchar(6000);
I have a requirement to sort a comma seperated string. For example if I pass '1234,432,123,45322,56786' as string, then it should return '123,432,1234,45322,56786', after sorting the numbers inside the string.
I have done it creating Global Temporary table. Is there a way without creating the Temp table. I understand I can write the whole logic to sort and append the string, but if there is any direct way.
CREATE GLOBAL TEMPORARY TABLE TEMP_TAB(COL1 VARCHAR2(100)) ON COMMIT DELETE ROWS; CREATE OR REPLACE FUNCTION func_sort_string(pi_string IN VARCHAR2, pi_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; l_str VARCHAR2(2000) DEFAULT pi_string || ',';
I am building a search for use in one of our major applications. I have written a PL/SQL package that deals with it. I would like to present the requirement list to the group and see what, if anything, you may have done differently than I have.
1.) The search interface must have a single box, like google.
2.) Multiple search terms will be separated by a comma.
3.) The table has the following columns: -- Name -- Title -- addr -- addr2 -- city -- state -- zip -- phone -- email
4.) Number of Search Terms per query will be unlimited. (for now, as practicality dictates)
5.) Each search term will be checked against various columns.
6.) Search terms must not have a preference in order. Name, Address = Address, Name
7.) Records will be returned only for the rows where all search terms are found.
1) Split values from "INST" Column : suppose 23 2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 , It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM 23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
with t as ( select 1 id, 101 book_id, 'MICROBIOLOGY' book_type, 1 category, 'sCIENCE AND TECH' category_name from dual union all select 1 , 101 , 'MICROBIOLOGY', 2 , 'HEALTHCARE' from dual union all
[Code]....
id book_id BOOK_TYPE category category_name
1 101 MICROBIOLOGY 1 SCIENCE AND TECH 1 101 MICROBIOLOGY 2 HEALTHCARE 1 102 CHEMISTRY 5 CHEMICAL ENGINEERING 2 105 COMP SC 1 SCIENCE AND TECH
The above is the output for a query after joining multiple tables. I have just put here the output I am getting after joining the tables.Now I want to achieve the below result.
Expected output: it should be | delimited
1|101|MICROBIOLOGY|102|CHEMISTRY|1|sCIENCE AND TECH|2|HEALTHCARE 2|105|COMP SC|1|SCIENCE AND TECH
Is there any alternative way other than SYS_CONNECT_BY_PATH? I also tried to use CONCAT_ALL but its not working.
I have two page , from first page to second page I am passing some values using url parameter passing but some values contains comma ex :- P1_ NATION text field contains INDIA,USA,UK
but apex treated that as these are separate values and assign it to separate items
suppose my intention is like P2_NATION,P2_EMPLOYEE,P2_EMPID :INDIA,USA,UK,SAGAR ,123
but apex treating it as P2_NATION = INDIA :P2_EMPLOYEE=USA :P2_EMPID = UK
SELECT id, pob,exp FROM emp where exp= (:exp)or @exp is null O/P: id pob exp _____________________ 1 CT 2 2 NJ 3 3 NY 2
It takes only one value of :exp but, I would like to give multiple values separated by ','. My problem is the parameter can be either single value or Blank or multiple values. My code do work for single value and Blank, Now how to implement multiple values .
How to pass a values from one to another, but without using parameters or globals. I've used parameters and every time i have to list the values of the form that values have been passed to, it ask do you want to save... even if I didn't passed any values. It's because my code in where-new-form instance, where i give values to a parameters.
If I have to create a database on server which has already 5 databases running , how will I set the memory (pga,sga) values . AFAIK it depends on o/s is linux and database version is 10g and 11g .
1)free avaialble memory on server . 2)kernel parameters .
what else has to be considering while initialize memory parameters .
I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:
declare TYPE crMain_record is RECORD ( v_code dummy.v_code%type, n_no dummy.n_no%type,
[Code].....
END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..
And lv_character values are commung from a setup table lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')'; --And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values... And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the
I am trying to select multiple values from a parameter form based on a select statement.
I created the parameter and write the select statement under list-of-value property However what I want is to let users choose multiple values from the select statement not only one value.
I have a user requirement where they want to be able to select multiple values for a single parameter that will act as a filter for a report. The possible values for a parameter can come from almost any master data Oracle eBusiness table. I don't want to just present the user with a LOV where they search down a list selecting what they want. That wouldn't be very practical for selecting part numbers from 1000's of rows. I also want the user to easily see what values they have selected. I thought about providing a button that would navigate to a multi-row form where they could record their values and use those values in the where clause of the SQL for the report, but that is a somewhat complex solution I have never tried before. how to do this. I'm working in the latest version 4 of APEX.