Row_number selecting from a complex select statement

Hi,

Code Snippet

This is difficult to explain in words, but the following code outlines what I am trying to do:

with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)

select * from myTableWithRowNum

Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?

The following query might help you,

Code Snippet

;with UnionResult(myvalue,insertdate)

as

(

select table1Id As myValue,insertdate from myTable1

union

select table2Id As myValue,insertdate from myTable2

),

OrderedResult(myValue,Row)

as

(

select myValue, row_number() over (order by insertdate desc)

from UnionResult

)

select * from OrderedResult

|||

I m not sure I understand your requirment correctly,anyhow your query throws error,

Try the following

Code Snippet

;with myTableWithRowNum as

(

select 'row' = row_number() over (order by insertdate desc), myValue

from

(

select insertdate,table1Id As myValue from myTable1

union

select insertdate,table2Id As myValue from myTable2

) as temp

)

select * from myTableWithRowNum

|||Thanks that's exactly what I'm looking for.
sql

row column repetition help

I've made a view from a (complex) select statement of 4 tables in my database.

lest say I get results like these...
colname1 colname2 colname3 colname4
----------------
patient1 med1 usage1 diagnum
patient1 med2 usage2 diagnum
patient1 med3 usage3 diagnum

I would like to cortrect my query so that the result is the following...
colname1 colname2 colname3 colname4
----------------
patient1 med1 usage1 diagnum
med2 usage2
med3 usage3

since the patient number is the same and the diag num is the same
i want ot be able to avoid the repetition...

this might not help but heres is my original query

CREATE OR REPLACE VIEW PRESCRIPTIONS AS
SELECT DISTINCT d.NoAssMaladie, p.prenompatient || ' ' || UPPER(p.nompatient) AS NomPatient, l.nomedicament AS NoMedic,m.libmedicament AS Libelle, l.quantite || ',' || l.prises || ',' || l.duree AS "Desc. d.usage", l.nodiagnostic AS Diag
FROM lignes_prescriptions l, patients p, diagnostics d, medicaments m
WHERE l.nodiagnostic = d.nodiagnostic AND
d.noassmaladie = p.noassmaladie AND
m.nomedicament = l.nomedicament AND
d.RESULTATDIAGNOSTIC = 'P' AND
d.NoAssMaladie = 'SANL 6005 1218'

where my patient is 'SANL 6005 1218'

And secondly I would like to know how to make a view that will ask for a value that I can apply to my where statement.

lets say I wanted to ask for the patient number 'SANL 6005 1218' instead of putting it into my query directly.Technically speaking the view you created contains no duplicates, where a duplicate is defined as the row projected from the select statement. If you select the primary key then the rows are already distinct. I don't think you can return 4 columns with different row depths as the dbms would not know what to place in the 'Empty' cells.|||You should check your schema and ensure the FD's are correct in your view for example the view you created leeds one to believe that possibly column1, column2, column3 are the key with column4 being dependent on this key.|||Hi,
First you cannot give parameters to a VIEW as they are just structures stored with no data.

When you query the view you should include the parameter in the where clause.

As for your requirement, you should use SQL REPORTING utility to get the report in that format.

use the following commands

BREAK ON PATIENT_ID ON DIAGRAMID

SELECT PATIENT_ID, DIAGRAMID ,....
FROM <<VIEW NAME>> ORDER BY PATIENT_ID, DIAGRAMID

If needed spool the result into a text file.
then use CLEAR BREAKS command to clear the break settings.

Regars
Shelva