Oracle query syntax

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.

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,

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}

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?

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?

>>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.

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

If it does not match I dont want the entire rrecord to return

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.

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?

>>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:
?????

Ok check out the pics I sent. One of each table. Hopefully this will help

Thanks
OMNI-ENT-PAGES.jpg
OMNI-ENT-LINK.jpg

>>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

Select all 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.

Paul,

a aliases OMNI_ENT_PAGES

Thanks

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

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!!

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.

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

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

nice work Paul

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
/

Select all Open in new window

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.

>>a small probelm with the
"AND LINK_URL LIKE '%?page=' "<<
Needs another % -
"AND LINK_URL LIKE '%?page=%' "

regexp_substr(link_url,'[0-9]+$')

looks fine to me

think we have dispensed with the like filtering anyway - but yes - ooops

thank you, cheers, Paul