Solved

Oracle query syntax

Posted on 2013-06-12
27
329 Views
Last Modified: 2013-06-12
Im trying to figure out what Im doing wrong here

select 'PAGES' as Bucket,FUNC_OMNI_DEPT_PAGE_OWNER(a.PAGE_ID)  as Topic,
'CONTENT_CHANGE' as subTopic,    
PAGE_TITLE as Headline,
'web page content changed on ' || LAST_UPDATE as Summary,
           
----THIS IS RETURNING BLANKS(NULLS?)      
(select link_id from omni_ent_link  where link_type='INTRANET' and link_url like '%?page=' || a.PAGE_ID and rownum=1 and link_id is not null) as Link_ID

FROM
OMNI_ENT_PAGES

I guess my question is why is this returning values that are blank or NULL??
'and link_id is not null'

PLEASE HELP!!!    Thanks
0
Comment
Question by:jknj72
  • 11
  • 8
  • 6
  • +2
27 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
There has to be something wrong with the where clause in the inner select that isn't returning rows.

As for why it is returning 'null', on an inline view like this it will return a null where there is no data found.

For example:
select 'Hello', (select 'World' from dual where 1=2) from dual;

The inline view will never return data based on the where clause, so it's a null value.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
If it cannot find a record for this where clause, then it will only return null

(select link_id from omni_ent_link  where link_type='INTRANET' and link_url like '%?page=' || a.PAGE_ID and rownum=1 and link_id is not null) as Link_ID

So my guess some data issue here. Just take out 1 sample page id and hard code it in this subquery and test it to see what it returns.

Thanks,
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
where is the alias a defined?

SELECT
      'PAGES' AS Bucket
    , FUNC_OMNI_DEPT_PAGE_OWNER(a.PAGE_ID) AS Topic
    , 'CONTENT_CHANGE' AS subTopic
    , PAGE_TITLE AS Headline
    , 'web page content changed on ' || LAST_UPDATE AS Summary
    ,
    ----THIS IS RETURNING BLANKS(NULLS?)      
    (
        SELECT link_id
        FROM omni_ent_link
        WHERE link_type = 'INTRANET'
            AND link_url LIKE '%?page=' || a.PAGE_ID
            AND rownum = 1
            AND link_id IS NOT NULL
        ) AS Link_ID
FROM OMNI_ENT_PAGES

I see no alias a defined in the above
Is there more to this query?

e.g. If a.id is null then the subquery will probably not meet the filter condition

(but I'd expect a.id to fail the parser if the alias isn't defined)

{1 edit for corrections}
0
 

Author Comment

by:jknj72
Comment Utility
everyone...
If the value is NULL then I dont want to return the record. Im trynig to fix this query for the guy Im working with. I dont like the way he setup these tables but this is what I have to deal with.
Is there anyway with that subquery that if it does not find a match that the record does not come back?
0
 

Author Comment

by:jknj72
Comment Utility
I want to just do a Join instead of the subquery but I was having trouble with it. Would that be a possible way to go?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Is there anyway with that subquery that if it does not find a match that the record does not come back?

Not with an inline view.  The example I posted shows that.

>>Would that be a possible way to go?

Possibly.  Can you provide table information?

Sample data and expected results also help.
0
 

Author Comment

by:jknj72
Comment Utility
a.PAGEID= 650022
b.LINK_URL= '../../Goto/Page.aspx?page=650022'

This is how he has the data and wants to match PAGEID to the end of the string in LINK_URL
0
 

Author Comment

by:jknj72
Comment Utility
If it does not match I dont want the entire rrecord to return
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
The information you've posted doesn't help me.  Maybe it will help the other Experts.

I need table information and more sample data and expected results.

For table information, the columns, and how the tables are related.

for sample data:  Raw data in the columns provided in the table descriptions and what you want the results from that sample data to look like.

I can then set up a test case and test the actual query before posting.
0
 

Author Comment

by:jknj72
Comment Utility
Well thats my problem too.....I have to somehow make a relation from one table to another via the fields I displayed. Other than that there is no relation?

The PAGE_ID(OMNI_ENT_PAGES) has to match the value in the LINK_URL(OMNI_ENT_LINK) field
ex...
a.PAGEID= 650022
b.LINK_URL= '../../Goto/Page.aspx?page=650022'

There are many different types of values in the Link_Url table but when there is a pageid at the end of the value I want to bring it back?

Am I fighting a losing battle?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Am I fighting a losing battle?

I don't know.  I'm still not understanding the end results you are after.

Please provide some sample raw data and expected results.

For example:
OMNI_ENT_PAGES
PAGE_ID,SOME_OTHER_COLUMN
1,'a'
2,'b'
650022, 'zzz'

OMNI_ENT_LINK
LINK_URL, SOME_OTHER_COLUMN_ALSO
'../../Goto/Page.aspx?page=650022', 555
'bob', 666

I would like to get this as output:
?????
0
 

Author Comment

by:jknj72
Comment Utility
Ok check out the pics I sent. One of each table. Hopefully this will help

Thanks
OMNI-ENT-PAGES.jpg
OMNI-ENT-LINK.jpg
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>If the value is NULL then I dont want to return the record.
then use a joined subquery within the from clause instead - maybe like this
SELECT
      'PAGES' AS Bucket
    , FUNC_OMNI_DEPT_PAGE_OWNER(a.PAGE_ID) AS Topic
    , 'CONTENT_CHANGE' AS subTopic
    , PAGE_TITLE AS Headline
    , 'web page content changed on ' || LAST_UPDATE AS Summary
    , L.link_id
FROM OMNI_ENT_PAGES
-- sometable with the alias a
INNER JOIN (
            SELECT link_id, link_url, row_number() over (partition by a.page_ID order by link_url) as row_ref
            FROM omni_ent_link
            WHERE link_type = 'INTRANET'
           ) as L ON L.row_ref = 1 AND L.link_url LIKE '%?page=' || a.PAGE_ID

Open in new window

but as I do not know where the alias a comes from I've used a crystal ball

please, take the time to find where that alias belongs - it's important.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:jknj72
Comment Utility
Paul,

a aliases OMNI_ENT_PAGES

Thanks
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
OK, I think the images indicate the alias A belongs to OMNI_ENT_PAGES, so my take on this is as follows:
SELECT
      'PAGES' AS Bucket
    , FUNC_OMNI_DEPT_PAGE_OWNER(a.PAGE_ID) AS Topic
    , 'CONTENT_CHANGE' AS subTopic
    , PAGE_TITLE AS Headline
    , 'web page content changed on ' || LAST_UPDATE AS Summary
    , L.link_id
FROM OMNI_ENT_PAGES as A
INNER JOIN (
            SELECT
                link_id
              , substr(link_url,(instr(link_url,'?page=') + 5) - (length(link_url)) ) as page_id_str
              , row_number() over (partition by substr(link_url,(instr(link_url,'?page=') + 5) - (length(link_url)) ) order by link_url) as row_ref
            FROM omni_ent_link
            WHERE link_type = 'INTRANET'
            AND L.link_url LIKE '%?page=' 
           ) as L ON to_char(a.page_id) = L.page_id_str AND L.row_ref = 1

Open in new window

in the subquery I take the last piece of the link_url (after '?page=') and then use this as the joining field to A

row_number() is used to emulate the "and rownum = 1" of the original. i.e. only the first matching record is returned for each page_id
0
 

Author Comment

by:jknj72
Comment Utility
I think we are on the right path Paul. One thing, I replaced your SUBSTR and replaced it but there is a small probelm with the
"AND LINK_URL LIKE '%?page=' "
portion of the INNER JOIN subquery
I attached pics of the query with and without that portion of the subquery and cant figure out why its not returning values with the LIKE?

THANKS!!!
RowsReturning.jpg
NoRowsReturning.jpg
0
 

Author Comment

by:jknj72
Comment Utility
You know what Paul, if I take out that portion of the subquery it still works the way I want. I dont think it is necessary but Im still interested why it doesnt work. Bottom line is I think it works. I will wait for your response first though

Your the man!!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I'm looking at your screen shots (I prefer actual text not images so I can copy/paste).

Are you sure it gives you what you want?

For page_id 650023, which last_update do you want?  Right now it can grab either.

I'm guessing that you want one or the other specifically not just either one randomly.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
only you can really test it out

the concept is to get that portion of the link_url that is the page_id, then link through that

if you have a better way to do that that's fine, but the same method should also be used as the 'partition by' in the row_number() function

so, if you replace line 12 of my last piece of code, also replace the equivalent portion in line 13
0
 

Author Comment

by:jknj72
Comment Utility
I actually got it. In the first query I put up it was a
"AND LINK_URL LIKE '%?page= || a.PAGE_ID "  or something like that

In the subquery you had "AND LINK_URL LIKE '%?page=' " so if it didnt have the PAGEID at the end or another  %  it wouldnt return anything. I put another % and it works

Anyway, all is fine and I really appreciate your (and everyone elses too) help!!!

Thanks
0
 

Author Closing Comment

by:jknj72
Comment Utility
nice work Paul
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Here is my attempt based on what I think you want.

It is also the test case I was hoping for.  Then if something doesn't work, you can add to the test case failed values and we can re-test

drop table my_pages purge;
create table my_pages (
	page_id number,
	last_update date
);

insert into my_pages values(650023,sysdate);
insert into my_pages values(650016,sysdate);
insert into my_pages values(111111,sysdate);
commit;

drop table my_links purge;
create table my_links (
	link_id number,
	link_url varchar2(100),
	last_update date
);


insert into my_links values(10190,'../../Goto/Page.aspx?page=650023',sysdate);
insert into my_links values(10191,'../../Goto/Page.aspx?page=650023',sysdate-1);
insert into my_links values(10056,'Page.aspx?page=650016',sysdate);
insert into my_links values(10000,'bob',sysdate);
commit;




select page_id, last_update, link_id
from my_pages,
	(
		select link_id, link_url, regexp_substr(link_url,'[0-9]+$') url_page_id,
			row_number() over(partition by regexp_substr(link_url,'[0-9]+$') order by last_update desc) myrn
		from my_links
	) my_link_view
where my_pages.page_id = my_link_view.url_page_id and myrn=1
/

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
and, yes, please post code as code - not images - makes for much more accurate communication. the acid test of your changes is that the string manipulation accurately strips out the page_id as a separate column.

note, you should be able to rum that subquery by itself, then you can inspect what it is actually providing.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>a small probelm with the
"AND LINK_URL LIKE '%?page=' "<<
Needs another % -
"AND LINK_URL LIKE '%?page=%' "
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
regexp_substr(link_url,'[0-9]+$')

looks fine to me
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
think we have dispensed with the like filtering anyway - but yes - ooops
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
thank you, cheers, Paul
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now