January 31, 2008 at 6:03 am
The view contains 6 joins and a random number generator newID().
I need to sort the view by the random number.
The view will be quired by a IVR system which can only select the view where userid = 'nnn' and date = 'mm/dd/yyyy'
(not sorting)
Top does not work as it does not select based on the above request, not to mention the order by option does not work.
I need you thoughts. I'll be glad to post the view if needed.;)
January 31, 2008 at 6:29 am
can you please post the view?
dragos
January 31, 2008 at 7:17 am
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER view dw_v_IVR_Eligibility as
Select
MC.Document,
Convert(Nchar(14),Replace(MemNo,'*','')) Memno,
Convert(varchar(50),FirstName+' '+LastName) MemName,
Convert(Nchar(8),Replace(CONVERT(NCHAR(10),M.Birth,101),'/','')) DOB,
Convert(Nchar(5),Left(S.Zip,5)) 'Zip',
case
When Disenr is null or Disenr='P' then 'Enrolled'
Else 'Disenrolled'
End 'Enroll Status',
Case
When Disenr is null or Disenr='P' then CONVERT(NCHAR(10),BegCov,101)
Else CONVERT(NCHAR(10),EffctDt,101)
End 'Effective Date',
Case
When GM.GrName is null then Dis.GrName
Else GM.GrName
End 'Group Name',
CONVERT(NCHAR(10),MC.DOS1,101) 'DOS',
COALESCE(MC.BillAmt,'0.00') 'Billed Charges',
Case
When P.PFirstName is null then Ltrim(P.PLastName)
Else Ltrim(P.PFirstName)+' '+Ltrim(P.PLastName)
End 'Provider',
Case
When ClStatus <> 'P' then 'Pending'
-- When ClStatus = 'P' and DenyFlag =1 AND (Right(MC.document,1) is between )MC.TotPdAmt is null then 'Denied'
else 'Processed'
End 'Claim Status',
CONVERT(NCHAR(10),MC.PayDt,101) 'Pay Date',
COALESCE(MC.TotPdAmt,'0.00') 'Paid Amount',
LTrim(RTrim(V.FedID)) 'Tax Id',
CASE
WHEN GM.LOB IS NULL THEN DIS.LOB
ELSE GM.LOB
END 'LOB',
abs(checksum(newID()))'cnt'
--replace(dbo.RandNumber(),'.','') 'cnt'
From dbo.dw_Members M with (nolock, INDEX(ak_Members_MemNo))
inner join dbo.dw_Subscribers S with (nolock) on S.Subno=LEFT(MemNo,(LEN(LTRIM(MemNo))-3))
inner join dbo.dw_GroupMaster Gm with (nolock) on GM.GroupId = S.SubGroup
LEFT JOIN
(Select DE.BeginCov, DE.MemID, DE.seqno, DE.CGrp, DE.DisenrDt, GM.Lob,GM.groupid,GM.GrName
FROM dbo.dw_Disenroll_Enroll DE with (nolock), dbo.dw_GroupMaster GM with (nolock)
WHERE DE.CGrp = GM.GroupID and DE.seqno = 1) DIS on Memno = DIS.MemID
Inner Join
(Select MC.*,MCP.PayDt,MCP.TotPdAmt, MCP.Document as CLMID
From dbo.dw_MasterClaim MC with (nolock),dbo.dw_MasterClaim_Pay MCP with (nolock)
Where mc.Document = MCP.Document and DATEDIFF(Year,MC.LastUpd, getdate())<=1)MC on MC.Member=Memno
Inner Join dbo.dw_Physician P with (nolock) on P.Phid = MC.Provider
Inner Join
(Select VendorNumber,FedID
From dbo.dw_Vendor with (nolock)) V on V.VendorNumber = MC.Vendor
WHERE MC.RevExists = '0'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
January 31, 2008 at 7:41 am
In SQL 2000, you can use a trick to order the contents of a view; In 2005, you cannot use the trick anymore, because logically, the select statement should contain the order by, not the view itself.
to order a view, you need a TOP statement; you can select TOP # or TOP percent. getting 100% was allowed in 2000, but not 2005.
In 2000:
SELECT TOP 100 PERCENT * FROM YOURVIEW ORDER BY YOURCOLUMN [edit: ORDER BY NEWID()?]
In 2005, if you choose an a number larger than the known contents of the rows int he view, you can still get it ordered:
SELECT TOP 1000000* FROM YOURVIEW ORDER BY YOURCOLUMN
(this works in 2000 as well, but it's the only way to order a view in 2005 if you want all the data)
Lowell
January 31, 2008 at 7:48 am
Okay I'll give it a try.
January 31, 2008 at 7:56 am
It worked!
THANKS!!:blush:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply