Solved

Oracle 10g query Need Help!

Posted on 2011-03-16
11
382 Views
Last Modified: 2012-05-11
Im fetching  books or publication  from oracle 10g but issue is author profile could be 2, 3 or more  if I need 10 record so it could be 13 or 14 it depend on author some books may have only one author and some books may have 2 or three I need 10 records of books with 10 records maybe authors could be more then 10  please have a look my query what im writing in this query im getting 10 records but im losing some books coz, of 10 limit …


select * from (
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YEAR editionYear ,
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAGE WHATISNEWIMAGE,
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM
USA_BOOKS_en USA_BOOKS,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
ORDER BY TO_DATE(USA_BOOKS.PUB_POSTED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC, profiles.PRF_TYPE ASC
)
where rownum <11
0
Comment
Question by:xeondxb
11 Comments
 
LVL 7

Accepted Solution

by:
MrNed earned 250 total points
ID: 35154029
I'm assuming USA_BOOKS_PROFILES contains the authors - so one book may have multiple rows here?

First get your top 10 rows, then grab the associated author details. If I didn't mess it up it will be something like:

SELECT x.id,
 x.detailFileURL,
 x.editionYear ,
 x.TITLE,
 x.WHATISNEWIMAGE,
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM (
 select * from (
 SELECT
 USA_BOOKS.PUB_ID id,
 USA_BOOKS.DETAIL_FILE_URL detailFileURL,
 USA_BOOKS.PUB_FIRST_EDN_YEAR editionYear ,
 USA_BOOKS.TITLE,
 USA_BOOKS.WHAT_IS_NEW_IMAGE WHATISNEWIMAGE,
 USA_BOOKS.IS_USA
 FROM
 USA_BOOKS_en USA_BOOKS
 ORDER BY TO_DATE(USA_BOOKS.PUB_POSTED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC
 )
 where rownum <11
) x,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(x.IS_USA LIKE '1' ||'%' OR x.IS_USA LIKE '0%') AND
x.id = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35154053
Can you explain with some sample data on what exactly are you looking for?
0
 

Author Comment

by:xeondxb
ID: 35154153
if you check highlighted records you will see there is 2 authors and im not using  
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YEAR editionYear ,
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAGE WHATISNEWIMAGE,
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM
USA_BOOKS_en USA_BOOKS,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
ORDER BY TO_DATE(USA_BOOKS.PUB_POSTED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC, profiles.PRF_TYPE ASC

Open in new window

screen.jpg
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:xeondxb
ID: 35154654
@MrNed:  i tried your given query it is giving me only 2 results :(
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35154844
OK, not sure why. Do you understand what I am trying to do with my query? It's not clear what you want, I'm assuming you want 10 distinct books, each with one row per author. So the final output will be at least 10 rows, maybe more.
0
 

Author Comment

by:xeondxb
ID: 35154872
@MrNed:
10 books records but one book author can be more then 1 so records will be more then 10 which book have more then one author for example "AB book" have 2 author  1:XY  2:YZ except this book all books written by one author so record will be 11 because of "AB book"  because its duplicating ...
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35154891
So what does the inner part of my query give you:

 select * from (
 SELECT
 USA_BOOKS.PUB_ID id,
 USA_BOOKS.DETAIL_FILE_URL detailFileURL,
 USA_BOOKS.PUB_FIRST_EDN_YEAR editionYear ,
 USA_BOOKS.TITLE,
 USA_BOOKS.WHAT_IS_NEW_IMAGE WHATISNEWIMAGE,
 USA_BOOKS.IS_USA
 FROM
 USA_BOOKS_en USA_BOOKS
 ORDER BY TO_DATE(USA_BOOKS.PUB_POSTED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC
 )
 where rownum <11
0
 

Author Comment

by:xeondxb
ID: 35155112
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YEAR editionYear ,
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAGE WHATISNEWIMAGE,
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM
USA_BOOKS_en USA_BOOKS,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
ORDER BY TO_DATE(USA_BOOKS.PUB_POSTED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC, profiles.PRF_TYPE ASC


when im writing this im getting result which u can see screen shoot above from that result i want 10 records but as i told u the condition 10 records should be books but it'll not apply on authors 10 books but if more then one author of one book it could be 12 records or 11 records because of author record
0
 
LVL 11

Assisted Solution

by:yuching
yuching earned 250 total points
ID: 35390296
hi xeondxb:,
in this case you can only get one author, or you will need to create a function to return all the author in the form of "Bilal Barakat, Anne Goujon"

Else you can try the below to return the max author details
Select USA_BOOKS.PUB_ID id,
	USA_BOOKS.DETAIL_FILE_URL detailFileURL,
	USA_BOOKS.PUB_FIRST_EDN_YEAR editionYear ,
	USA_BOOKS.TITLE,
	USA_BOOKS.WHAT_IS_NEW_IMAGE WHATISNEWIMAGE,
	max(PRF.PRF_ID) AS prfId,
	max(PRF.PRF_NAME) AS prfName,
	max(PRF.PRF_IS_CLICKABLE) AS prfIsClickable,
	max(profiles.PRF_TYPE) prfType
From USA_BOOKS_en USA_BOOKS,
	USA_BOOKS_PROFILES_en profiles,
	PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
Group By USA_BOOKS.PUB_ID,  USA_BOOKS.DETAIL_FILE_URL, 
    USA_BOOKS.PUB_FIRST_EDN_YEAR, USA_BOOKS.TITLE, USA_BOOKS.WHAT_IS_NEW_IMAGE
ORDER BY TO_DATE(USA_BOOKS.PUB_POSTED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC, 9 ASC

Open in new window

0
 

Author Closing Comment

by:xeondxb
ID: 35726062
it did not work but thanks guys....  just closing question
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 60
having some issue on pl sql procedure 1 23
Formula for calculating ROI on training 6 49
sum of columns in a row in oracle 3 32
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

839 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