Answer / jini

you have to write a query like this to replace the
tablename and column name

select &ename,&sal
from &emp

but make sure emp is a table and ename,sal are columns

Answer / natarajan

1.Lexical Parameters are placeholders for text that you
embed in a SELECT statement. You can use lexical
references to replace the clauses appearing after SELECT,
FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and
START WITH.

You cannot make lexical references in a PL/SQL statement.
You can, however, use a bind reference in PL/SQL to set the
value of a parameter that is then referenced lexically in
SQL.

example:
we can use (&)lexical parameter in where cluse and inside
from clause and group by clause i enclose the query used in
reports 6i.

select
decode (UPPER(:p_order_by),'CUSTOMER',NULL,
types.cust_trx_type_id ) dummy_id_inv,
decode (UPPER
(:p_order_by),'CUSTOMER',NULL,types.name)
dummy_type_inv,


ps.trx_number
invnum ,

types.name
invoice_type_inv,

ps.amount_adjusted
amount_adjusted_inv,

ps.amount_applied
amount_applied_inv,

ps.amount_credited
amount_credited_inv,

ps.gl_date
gl_date_inv,

&lp_bal_segment
bal_segment_value_inv,
&lp_query_show_bill cons_billing_number,
from ra_cust_trx_types_all types,
hz_cust_accounts cust_acct,
hz_parties party,
&lp_ar_payment_schedules_all ps,
&lp_table_show_bill
&lp_ra_customer_trx_all trx,
hz_cust_site_uses_all site,
hz_cust_acct_sites_all addr,
hz_party_sites party_site,
hz_locations loc,
&lp_ra_cust_trx_gl_dist_all gld,

where TRUNC(ps.gl_date) <= :p_as_of_date
and ps.customer_trx_id+0 = trx.customer_trx_id
and ps.customer_id = cust_acct.cust_account_id
and cust_acct.party_id = party.party_id
&lp_customer_name_low1
&lp_customer_name_high1
&lp_customer_number_low1
&lp_customer_number_high1
and ps.cust_trx_type_id = types.cust_trx_type_id
and nvl(ps.org_id,-99) = nvl(types.org_id,-99)
&lp_invoice_type_low
&lp_invoice_type_high
and ps.customer_site_use_id+0 = site.site_use_id+)
and site.cust_acct_site_id = addr.cust_acct_site_id(+)
and addr.party_site_id = party_site.party_site_id(+)
and loc.location_id (+) = party_site.location_id
and ps.gl_date_closed > :P_AS_OF_DATE
&lp_curr_code
and ps.customer_trx_id+0 = gld.customer_trx_id
and gld.account_class = 'REC'
and gld.latest_rec_flag = 'Y'
and gld.code_combination_id = cc.code_combination_id
&lp_bal_segment_low
&lp_bal_segment_high
&lp_where_show_bill
&P_ORG_WHERE_CTT
&P_ORG_WHERE_PS
&P_ORG_WHERE_ADDR
&P_ORG_WHERE_GLD

wht is the difference between truncat,drop in sqlserver wht is the difference between function and stored procedure

3 Answers   Apollo,

---

How many types of index are there?

0 Answers  

---

What are the syntax and use of the coalesce function?

0 Answers  

---

Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin

0 Answers  

---

Describe sql comments?

0 Answers  

---

---

Can we use insert statement in function?

0 Answers  

---

Which join condition can be specified using on clause?

0 Answers  

---

what is a trigger in mysql? Define different types of trigger. : Sql dba

0 Answers  

---

What are different methods to trace the pl/sql code?

0 Answers  

---

what is heap table? : Sql dba

0 Answers  

---

What is sharding in sql?

0 Answers  

---

What is union?

0 Answers  

---