Thanks for your help Richard. I'm glad you understand where Shoeb is coming
from with the SQL Server and Sybase background because it's all very strange
to me.
Regards,
Mike
On Tue, Feb 15, 2011 at 10:33 AM, Richard Pascual <richg...@gmail.com>wrote:
> Incidentally, I noticed that Shoeb's initial solution looks a lot like how
> coding is done in SQL Server and Sybase. We first narrow down the result set
> into dynamically created temp tables then query the result from the temp
> tables once the data has been queried. For example, in pseudo T/SQL:
>
> begin
>
> select a.column_a, a.column_b, a.column_c
> into #temp_table_1
> from table_a a
> where a.column_a = ..something...
>
> select column_a, column_b, column_c from #temp_table_1
>
> end
>
>
>
> of course, this is a PL/SQL list and not a T/SQL list so this approach
> probably isn't appropriate. What Shoeb's probably implying is this dynamic
> temp table which I am not convinced is necessary.
>
> The create table and alter table commands look like they can be handled by
> implementing an analytical function. Shoeb's querying data, appending a
> column then serializing the value in the appended column using a sequence
> which the procedure starts from the value "1" every time. This is an ideal
> candidate for an analytical function like row_number.
>
> try:
>
> select row_number() over ( partition by InternalDocumentNumber order by
> PhotoTime ) as rowid,
> column_a,
> column_b,
> column_c, etc.
> from ...
> where ...
> order by PhotoTime
>
> My next question is what will you be using to accept this data set? Another
> stored procedure? An external process such as a Crystal Report or some other
> application? This will decide the next step in how to package the above
> query in the appropriate data structure for delivery.
>
>
> Rich Pascual
>
>
>
> On Tue, Feb 15, 2011 at 8:09 AM, Michael Moore <michaeljmo...@gmail.com>wrote:
>
>> Hi Shoeb,
>> One problem is, that you are attempting to both describe your problem as
>> well as the solution. For example, you are saying
>>
>> Here is my problem ...
>> Here is my proposed solution ...
>> How can I make my proposed solution work ...
>>
>> I am guessing that your proposed solution is based on how you think Oracle
>> should work, and not how it actually works. For example, I don't see why you
>> think you need temporary tables. You probably don't, but I can't say for
>> sure because I still don't understand the problem you are trying to solve.
>>
>> Can you state your problem something like this:
>> I am trying to provide a result set to another procedure. The source of
>> the data is the following tables bla bla bla ... The join criteria for these
>> tables is bla bla bla. I need to return the following columns ...
>> Column XXX is derived from this calculation ...
>>
>>
>> You get the idea?
>>
>> Regards,
>> Mike
>>
>>
>>
>>
>> On Tue, Feb 15, 2011 at 12:56 AM, Shoeb Bojagar
>> <shoeb.boja...@gmail.com>wrote:
>>
>>> Sorry for not providing the code in previous mail, this is what i am
>>> trying,
>>> Friends i really need help on this one, i need to create a temporary
>>> table to store all the date i select based on where clause and i need
>>> to return that data, here i am facing two problems,
>>> 1st one is that i cant create a temporary table in the procedure and
>>> 2nd one is that i am not sure if sys_refcursor i am using will be able
>>> to retun the compete data.
>>> Please Help me friends.
>>>
>>>
>>>
>>> create or replace procedure
>>> spdastgoshwara2r(para_internaldocumentnumber IN number, para_srocode
>>> IN number) is
>>>
>>> ref1 sys_refcursor;
>>>
>>> /*
>>> MODULE NAME
>>> 1.INPUT FORM ENTRY
>>>
>>> PURPOSE OF FUNCTION
>>> ->USED TO GET FEE DETAILS DATA FROM DOCFEEPAID AND
>>> DOCFEECALCULATED
>>> TABLE IN INPUT FORM ENTRY
>>> */
>>>
>>>
>>> begin
>>>
>>> --DROP TABLE IF EXISTS tempPhotoDatatable ;
>>> CREATE GLOBAL TEMPORARY TABLE tempPhotoDatatable AS
>>> SELECT
>>> RegistrationMaster.InternalDocumentNumber,
>>>
>>> DATE_PART('year',RegistrationMaster.Stamp2DateTime) AS RegYear,
>>> SROMaster.SROMname as SROName,
>>> ArticleMaster.M_article_name as ArticleName,
>>> PartyDetails.FName,
>>> PartyDetails.MName,
>>> PartyDetails.LName,
>>> PartyDetails.Address,
>>> PartyDetails.PinCode,
>>> PartyDetails.Age,
>>> PartyDetails.PANNo,
>>> PartyDetails.PartySRNO AS PartyNumber ,
>>> PartyDetails.PhotoData,
>>> PartyDetails.ThumbData,
>>> PartyMaster.M_party_name as E_party_name,
>>> to_char(RegistrationMaster.Stamp3DateTime,'DD / MM
>>> / YYYY HH12 :
>>> MI : SS AM') as Stamp3DateTime ,
>>> to_char(RegistrationMaster.Stamp4DateTime,'DD / MM
>>> / YYYY HH12 :
>>> MI : SS AM') AS Stamp4DateTime ,
>>> to_char(RegistrationMaster.Stamp5DateTime,'DD / MM
>>> / YYYY HH12 :
>>> MI : SS AM') AS Stamp5DateTime,
>>> RegistrationMaster.DocNumber,
>>> RegistrationMaster.ArticleDescription,
>>> SROMaster.SROCode,
>>> ConfigurationSettings.SROStamp,
>>> SROMaster.SROMshortName,
>>> SROMaster.SROEshortName,
>>> PartyDetails.PhotoTime,
>>> PartyDetails.PhotoPath,
>>> PartyDetails.ThumbPath,
>>> to_char(current_date,'DD / MM / YYYY HH12 : MM :
>>> SS') as Stamp2DateTime,
>>> to_char(RegistrationMaster.DateOfExecution,'Month
>>> DD YYYY') as
>>> DateOfExecution,
>>> 1 AS ROWID,
>>> registrationmaster.bookcode
>>> FROM RegistrationMaster INNER JOIN PartyDetails ON
>>> RegistrationMaster.InternalDocumentNumber =
>>> PartyDetails.InternalDocumentNumber AND
>>> RegistrationMaster.SROCode = PartyDetails.SROCode
>>> INNER JOIN
>>> SROMaster ON RegistrationMaster.SROCode =
>>> SROMaster.SROCode INNER JOIN
>>> PartyMaster ON PartyDetails.Party_Code =
>>> PartyMaster.party_code INNER JOIN
>>> ArticleMaster ON RegistrationMaster.Article_code =
>>> ArticleMaster.article_code INNER JOIN
>>> ConfigurationSettings ON
>>> RegistrationMaster.SROCode =
>>> ConfigurationSettings.SROCode
>>> WHERE RegistrationMaster.InternalDocumentNumber =
>>> para_InternalDocumentNumber
>>> AND RegistrationMaster.SROCode = para_SROCode --AND
>>> PartyDetails.PhotoData IS NOT NULL
>>> ORDER BY PhotoTime;
>>>
>>>
>>>
>>> /*-------------------------------------------------------------------------------------------
>>>
>>> ALTER TABLE tempPhotoDatatable ADD ROWID SERIAL;
>>>
>>> DROP INDEX IF EXISTS
>>> tempPhotoDatatable_auto_increment_key;
>>>
>>> ALTER SEQUENCE tempPhotoDatatable_ROWID_seq START WITH 1
>>> INCREMENT BY 1;
>>>
>>>
>>>
>>> -------------------------------------------------------------------------------------------*/
>>>
>>>
>>> OPEN ref1 FOR
>>> SELECT * FROM tempPhotoDatatable ORDER BY ROWID;
>>> RETURN;
>>>
>>>
>>>
>>>
>>> end spdastgoshwara2r;
>>>
>>>
>>>
>>> ======================================================================================================================
>>>
>>> 2nd eg.
>>>
>>> CREATE OR REPLACE PROCEDURE spctspatrak(para_fromdate IN timestamp
>>> with time zone, para_todate IN timestamp with time zone,
>>> para_villagecode IN number, para_distcode IN number, para_srocode IN
>>> number)
>>> RETURNS SETOF refcursor AS
>>> $BODY$
>>> ref1 sys_refcursor;
>>> tmp_date timestamp without time zone;
>>> BEGIN
>>>
>>>
>>> --DROP TABLE IF EXISTS temp1 ;
>>> CREATE GLOBAL TEMPORARY TABLE temp1 AS
>>> SELECT DISTINCT InternalDocumentNumber,srocode
>>> FROM receiptdetails
>>> WHERE InternalDocumentNumber = param_internaldocumentnumber AND
>>> SROCode = param_srocode;
>>>
>>> select into tmp_date towithoutTZ(param_receiptdate);
>>>
>>> OPEN ref1 FOR
>>> SELECT temp1.InternalDocumentNumber,
>>> FeeRules.DescriptionD AS
>>> Description,
>>> DocFeePaid.AmountPaid,
>>> RegistrationMaster.NoOfPages
>>>
>>> FROM
>>> temp1,RegistrationMaster,FeeRules,DocFeePaid
>>>
>>> where temp1.InternalDocumentNumber =
>>> RegistrationMaster.InternalDocumentNumber AND
>>> temp1.SROCode =
>>> RegistrationMaster.SROCode and
>>> temp1.InternalDocumentNumber =
>>> DocFeePaid.InternalDocumentNumber AND
>>> temp1.SROCode = DocFeePaid.SROCode
>>> and
>>> DocFeePaid.FeeRuleCode =
>>> FeeRules.FeeRuleCode
>>> and (DocFeePaid.AmountPaid > 0.0)
>>> AND
>>> (DocFeePaid.InternalDocumentNumber = param_internaldocumentnumber)
>>> AND (DocFeePaid.SROCode =
>>> param_srocode ) AND
>>> DocFeePaid.ReceiptDate =tmp_date
>>> order by FeeRules.feerulecode;
>>> RETURN;
>>>
>>> END spctspatrak;
>>>
>>>
>>>
>>>
>>> --
>>> Shoeb Bojagar.
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Oracle PL/SQL" group.
>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com
>>> To unsubscribe from this group, send email to
>>> oracle-plsql-unsubscr...@googlegroups.com
>>> For more options, visit this group at
>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>
>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "Oracle PL/SQL" group.
>> To post to this group, send email to Oracle-PLSQL@googlegroups.com
>> To unsubscribe from this group, send email to
>> oracle-plsql-unsubscr...@googlegroups.com
>> For more options, visit this group at
>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
