Home » SQL & PL/SQL » SQL & PL/SQL » fucntion/view/design question
fucntion/view/design question [message #257614] Wed, 08 August 2007 15:19 Go to next message
mrsmithq
Messages: 2
Registered: August 2007
Junior Member
I have 2 simple tables:

CREATE TABLE FISCAL_QTR
( "FISCAL_YEAR" NUMBER(*,0) NOT NULL ENABLE,
"FISCAL_QTR" NUMBER(*,0) NOT NULL ENABLE,
CONSTRAINT "FISCAL_QTRS_PK" PRIMARY KEY ("FISCAL_YEAR", "FISCAL_QTR")



CREATE TABLE AIRLINE_TERR_ALIGNMENT_DUMP
( "TERRITORY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"NATL_ACCT_NBR" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"EFFECTIVE_DTTM" DATE NOT NULL ENABLE,
"EXPIRATION_DTTM" DATE NOT NULL ENABLE,
CONSTRAINT "AIRLINE_TERR_ALIGNMENT_DUMP_PK" PRIMARY KEY ("TERRITORY", "NATL_ACCT_NBR", "EFFECTIVE_DTTM", "EXPIRATION_DTTM")


I need my data by fiscal_qtr, fiscal_year ie QTR 4 of year 2007,
but my dump table only has data with a start to end date.

So, I wanted to create a view that basically took each row in the fiscal_qtr table and called a function which I have already written that if you give it the fiscal year, and the fiscal quarter, and the account it would give you the territory from the dump table.

Everything works correctly, but I am stuck trying to the sql for the view.

The code for the view looks something like this:

SELECT b.natl_acct_nbr,
function(a.fiscal_qtr, a.fiscal_year, b.natl_acct_nbr) AS
territory,
a.fiscal_qtr,
a.fiscal_year,
FROM
fiscal_qtr a,
airline_terr_alignment_dump b
WHERE b
and function(a.fiscal_qtr, a.fiscal_year, b.natl_acct_nbr) like '8-%'

The view works but it has repeates of the same data for exactly the number of entiries that I have in my fiscal_qtr table. I know I am just doing something wrong. What could that be?

A loop seems natural, but I don't know how it fits in

Re: fucntion/view/design question [message #257618 is a reply to message #257614] Wed, 08 August 2007 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
No Operating System name or version.
No Oracle version to 4 decimal places.
No code formatting (see http://www.orafaq.com/forum/t/59964/74940/)
No DDL.
No error message.
No help. [You're On Your Own (YOYO)!]
Re: fucntion/view/design question [message #262741 is a reply to message #257614] Tue, 28 August 2007 03:42 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
May be no join between table
Re: fucntion/view/design question [message #262778 is a reply to message #262741] Tue, 28 August 2007 05:00 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

There are no scope open to join these tables.

Cheers
Soumen
Re: fucntion/view/design question [message #269634 is a reply to message #262778] Sun, 23 September 2007 22:46 Go to previous message
srikanthvijay8
Messages: 16
Registered: July 2007
Location: Chennai pattanam
Junior Member
This where condition is not convinced for me
since you put b (b is the table name only)
WHERE b
and function(a.fiscal_qtr, a.fiscal_year, b.natl_acct_nbr) like '8-%'

Previous Topic: Why is the keyword 'in' so much slower than '=' ?
Next Topic: problem in time manipulation in query..
Goto Forum:
  


Current Time: Sat Dec 10 10:42:40 CST 2016

Total time taken to generate the page: 0.10396 seconds