473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

PL/SQL: Dynamically passing/changing value in WHERE clause ???

Is there someway I can make a parameter or variable of some sort
to pass into my PL/SQL cursor shown below to change the value of the
date without having to rebuild the package each time??

Can I alter this value dynamically ??

and trx_date > '31/MAR/04'
any ideas would be appreciate

thanks in advance

George Lewycky
=========================================

CURSOR c_invh IS
SELECT CUSTOMER_TRX_ID,
BILL_TO_CUSTOMER_NUMBER,
substr(BILL_TO_CUSTOMER_NAME,1,45) customer_name,
DEFAULT_BILL_ATTN,
BILL_TO_ADDRESS1,
BILL_TO_ADDRESS2,
BILL_TO_CITY,
BILL_TO_STATE,
BILL_TO_POSTAL_CODE,
BILL_TO_COUNTRY,
BILL_TO_COUNTRY_NAME,
TRX_NUMBER,
TRX_DATE,
TERM_NAME,
CLASS_NAME,
TOTAL_AMOUNT,
substr(PURCHASE_ORDER_NUMBER,1,5) jobno,
BATCH_SOURCE_NAME,
PRINTING_ORIGINAL_DATE,
PRINTING_LAST_PRINTED,
PRINTING_OPTION,
PRINTING_PENDING,
PRINTING_COUNT
FROM AR_INVOICE_HEADER_V INV
where class_name = 'Invoice'
and trx_date > '31/MAR/04' <--------------
and BILL_TO_CUSTOMER_NUMBER not in (1404, 1480)
order by trx_number;
Jul 19 '05 #1
1 8077
Yes there is a way :)

from the PL/SQL Users Guide....

The following example computes the total wages paid to employees in a
specified department.

DECLARE
CURSOR c1 (name VARCHAR2, max_wage NUMBER) IS
SELECT * FROM employees WHERE last_name = name and salary < max_wage;
BEGIN
FOR person IN c1('Austin', 30000)
LOOP
-- process data record
dbms_output.put_line('Name = ' || person.last_name ||
', salary = ' || person.salary);
END LOOP;
END;
/

hope it helps,
pedro
george lewycky wrote:
Is there someway I can make a parameter or variable of some sort
to pass into my PL/SQL cursor shown below to change the value of the
date without having to rebuild the package each time??

Can I alter this value dynamically ??

and trx_date > '31/MAR/04'
any ideas would be appreciate

thanks in advance

George Lewycky
=========================================

CURSOR c_invh IS
SELECT CUSTOMER_TRX_ID,
BILL_TO_CUSTOMER_NUMBER,
substr(BILL_TO_CUSTOMER_NAME,1,45) customer_name,
DEFAULT_BILL_ATTN,
BILL_TO_ADDRESS1,
BILL_TO_ADDRESS2,
BILL_TO_CITY,
BILL_TO_STATE,
BILL_TO_POSTAL_CODE,
BILL_TO_COUNTRY,
BILL_TO_COUNTRY_NAME,
TRX_NUMBER,
TRX_DATE,
TERM_NAME,
CLASS_NAME,
TOTAL_AMOUNT,
substr(PURCHASE_ORDER_NUMBER,1,5) jobno,
BATCH_SOURCE_NAME,
PRINTING_ORIGINAL_DATE,
PRINTING_LAST_PRINTED,
PRINTING_OPTION,
PRINTING_PENDING,
PRINTING_COUNT
FROM AR_INVOICE_HEADER_V INV
where class_name = 'Invoice'
and trx_date > '31/MAR/04' <--------------
and BILL_TO_CUSTOMER_NUMBER not in (1404, 1480)
order by trx_number;

Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a...
8
by: Lauren Quantrell | last post by:
In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID: UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON...
26
by: David W. Fenton | last post by:
A client is panicking about their large Access application, which has been running smoothly with 100s of thousands of records for quite some time. They have a big project in the next year that will...
6
by: BerkshireGuy | last post by:
Does anyone know of a good function that will parse out parts of an SQL statement that is passed to it in seperate variables? It should be able to parse statements that contain ORDERBY, WHERE,...
5
by: WebMatrix | last post by:
Hello, I am developing ASP.NET 1.1 application against SQL Server 2000 db, where users have ability to construct their own select criteria (for a where clause). I already developed a user...
1
by: karups | last post by:
Hi, I've got listbox in my .aspx page where the users can make multiple selection. So, Users can select 7 items in listbox, I have to take value from items and pass it to stored procedure to...
2
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
7
by: Roger | last post by:
This is the vba code Set db = CurrentDb On error goto fErr …. loop strSql = "UPDATE tblInvAnalysisWhse" & _ " SET ltDeviation = " & Sqr(dblError / intPeriods) & _ " WHERE item = '" &...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.