Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle query syntax

Posted on 2013-06-12
27
Medium Priority
?
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 6
  • +2
27 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39240852
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
ID: 39240855
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 49

Expert Comment

by:PortletPaul
ID: 39240885
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:jknj72
ID: 39240909
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
ID: 39240913
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39240921
>>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
ID: 39240927
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
ID: 39240932
If it does not match I dont want the entire rrecord to return
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39240947
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
ID: 39240979
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39241008
>>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
ID: 39241059
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 49

Expert Comment

by:PortletPaul
ID: 39241081
>>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
 

Author Comment

by:jknj72
ID: 39241128
Paul,

a aliases OMNI_ENT_PAGES

Thanks
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39241137
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
ID: 39241212
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
ID: 39241245
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39241283
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 49

Expert Comment

by:PortletPaul
ID: 39241304
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
ID: 39241310
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
ID: 39241313
nice work Paul
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39241320
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 49

Expert Comment

by:PortletPaul
ID: 39241342
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 32

Expert Comment

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

Expert Comment

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

looks fine to me
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39241366
think we have dispensed with the like filtering anyway - but yes - ooops
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39241374
thank you, cheers, Paul
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

597 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