Home » SQL & PL/SQL » SQL & PL/SQL » build sql
build sql [message #202490] Fri, 10 November 2006 00:47 Go to next message
sudharshan
Messages: 48
Registered: November 2006
Member
i have a table say - rule_component

which has two columns

1.db_table
2.from_clause

The two columns contains tables for example

db_table
------------
emp
dept
bonus
emp
def

from_clause
----------------

emp,dept,bonus,dept,emp,abc


My requirement

i want to find the duplicate tables in both the from_clause and db_table column
and populate in my from clause which are unique to both the columns


say example

select ......

from emp,dept,bonus,abc,def --like this


i am using forms6i with oracle 9i as database.
Re: build sql [message #202513 is a reply to message #202490] Fri, 10 November 2006 02:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry, I can't understand your requirement.
Can you provide a clearer explanation of what you want.
An example showing the data that you have and the results that you expect would be useful. (Don't forget the table creation scripts);
icon2.gif  Re: build sql [message #202525 is a reply to message #202490] Fri, 10 November 2006 03:40 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
i have a feeling that you posted in oracle forums and there too you had to face a similar kind of question..why dont you be clearer?
Re: build sql [message #202528 is a reply to message #202490] Fri, 10 November 2006 03:42 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
SQL> descr sswms_rule_components;
Name Null? Type
----------------------------------------- -------- ---------------------
RULE_COMPONENT_ID NOT NULL NUMBER
RULE_COMPONENT_CODE NOT NULL VARCHAR2(30)
RULE_COMPONENT_NAME NOT NULL VARCHAR2(100)
ENABLED_FLAG NOT NULL VARCHAR2(1)
DB_TABLE VARCHAR2 (100)
DB_COLUMN VARCHAR2 (100)
DB_FUNCTION VARCHAR2(100)
WHERE_CLAUSE VARCHAR2 (2000)
FROM_CLAUSE VARCHAR2 (2000)


The table contains the following data


db_table
--------------------------------
sswms_shipment_lines
wsh_carrier_ship_method --- the data inside the db_table column is a table


db_column
--------------------------------
Ship to
Carrier_id


Where_clause
-----------------------------
Oe_order_headers_all.header_id = sswms_shipment_lines.order_header_id



From_clause

-------------------------

Oe_order_headers_all, sswms_shipment_lines -- the data inside the From_clause is a table


Now my requirement is to build a dynamic sql in forms 6i.when I click the build sql it should update the sql

So --- I have to write a procedure --

Select db_table || . || db_column || ‘’ || group_key

---group_key is an alias

From db_table, From_clause

--- Here the logic should be

1. I should remove the commas from the “FROM_CLAUSE” and check for duplicate values
2. i should check for duplicate values for the “DB_TABLE “ column
3. I should compare both the FROM_CLAUSE and DB_TABLE for DUPLICATE VALUES
4. After doing this I should add the result to the “FROM” in the select statement



Re: build sql [message #202531 is a reply to message #202490] Fri, 10 November 2006 03:53 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
here...
Re: build sql [message #202532 is a reply to message #202528] Fri, 10 November 2006 04:05 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi i have just posted this question in two places to get the result
Re: build sql [message #202540 is a reply to message #202532] Fri, 10 November 2006 04:45 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, but why did you limit number of exactly the same posts to only 2 forums, when you have 41 of them on OraFAQ?

Multiplying your posts all over the OraFAQ Forum is the best way for most (all?) of them to be either deleted or locked, not mentioning the fact that this can be considered as some kind of spamming.

Really, there's no reason to do that. Just put your question to the place that suits the best its subject and wait; people will see it and reply if they know the answer.

Good luck with the problem!
icon13.gif  Re: build sql [message #202548 is a reply to message #202540] Fri, 10 November 2006 05:23 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi

It involves both oracle forms and plsql so i posted it.Instead of arguing with me you could have given me the solution.anyhow dont waste time by sedning some messages like this and try to be productive.
Re: build sql [message #202559 is a reply to message #202548] Fri, 10 November 2006 06:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
For someone who is asking us to spend our spare time providing help, you have a remarkably poor attitude.

Re: build sql [message #202565 is a reply to message #202548] Fri, 10 November 2006 06:56 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
Instead of suggesting where to put the message you could have given some valuable suggestions.even it is wrongly posted the moderator will take care of putting it in the right place.Stop commenting about the questions asked.
Re: build sql [message #202568 is a reply to message #202565] Fri, 10 November 2006 07:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oddly, I feel no particular inclination to comply with your demands. Instead, I shall go and see if there are any questions asked by more polite people that I can answer instead.

Razz

(You should probably note that @Littlefoot IS a moderator)
Re: build sql [message #202571 is a reply to message #202565] Fri, 10 November 2006 07:26 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
sudharshan wrote on Fri, 10 November 2006 13:56
Instead of suggesting where to put the message you could have given some valuable suggestions.even it is wrongly posted the moderator will take care of putting it in the right place.Stop commenting about the questions asked.

Well, that's the spirit.
I somehow don't expect too many valuable answers to this thread anymore, so maybe it was wise of you to post it on Oracle Forums as well.
Make sure you treat the people over there likewise...
Re: build sql [message #202580 is a reply to message #202565] Fri, 10 November 2006 07:44 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
Hi

by no means i asked particulary you to answer my question nor i demanded an answer from you.

I had a problem which i have not come across so i requested the forum to give me some valuable suggstions.

when next time i post a question i will say

My dearest and loving brother and sister

-- i think this sounds polite


sudharshan
Re: build sql [message #202582 is a reply to message #202580] Fri, 10 November 2006 07:52 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Someone has been busy and this thread is very close to be shut down.

@op: it is you who are asking advice. You should have read the forum posting guidelines (TIPS&TRICKS), one thread per subject in one forum. But you chose to ignore them. You have absolutely no reason to have a short fuse. Sarcasm won't help you either.

MHE

[Updated on: Fri, 10 November 2006 07:57]

Report message to a moderator

Re: build sql [message #202585 is a reply to message #202490] Fri, 10 November 2006 07:57 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Since I spent my time reading this thread, and sudharshan want a productive answer , I recommend this for you

http://www.ptr.co.uk/oracle-sql.html

Have a nice weekend Razz
Re: build sql [message #202590 is a reply to message #202580] Fri, 10 November 2006 08:17 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi

It involves both oracle forms and plsql so i posted it.
i am sorry i didnt read that tips and tricks but it is the moderator duty who has to tell me dont put messages on two places. i dont want any unknown person to advise me to put the message in so called forum.

No suggestion for the problem then dont post messages .please do stop with it.


Empty vessels make more noise.


sudharshan


Re: build sql [message #202595 is a reply to message #202590] Fri, 10 November 2006 08:36 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Topic closed for the time being.

MHE
Previous Topic: Chk the days
Next Topic: Simplify this query
Goto Forum:
  


Current Time: Fri Mar 29 09:05:39 CDT 2024