Taking qry and then having it order by in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Taking qry and then having it order by in a table

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-18 : 14:46:03

I have one query with 3 statments in it, which then creates a table called HCSReturnFile. My problem is that I have an order by in my query, but when I go to create a table that puts all 2 statements into 1, it does not do the order by. What am I doing wrong? Below is my Query.

USE [Impact_PROD]
GO
/****** Object: StoredProcedure [dbo].[p_GenerateHCSReturnFileUPDATE] Script Date: 01/18/2008 14:20:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Wendy & Mike
-- Create date: 11/08/2007
---Create Date to Production - 12-06-2007
-- Description: This was a touch one.
---This report drop both Queries into a tabled called HCSRetrunFile
-- =============================================
ALTER PROCEDURE [dbo].[p_GenerateHCSReturnFileUPDATE]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF OBJECT_ID('IMPACT_PROD..HCSReturnFile') IS NOT NULL
DROP TABLE HCSReturnFile

CREATE TABLE [dbo].[HCSReturnFile](
[EventNumber] [varchar](50) NULL,
[ClaimNumber1] [varchar](50) NOT NULL,
[Resolution] [varchar](50) NULL,
[Resolution2] [varchar](50) NULL,
[ProviderType] [varchar](2) NULL,
[Negotiation] [varchar](50) NULL,
[NEGOYESORNO] [varchar](50) NULL,
[ProviderID] [varchar](50) NOT NULL,
[HCSAuthorizationID] [varchar](20) NULL,
[PROLASTNAME] [varchar](20) NULL,
[proFirstName] [varchar](15) NULL,
[ProOffice] [varchar](35) NULL,
[TOTALBILLEDAMT] [varchar](50) NOT NULL,
[SAVINGS] [varchar](50) NOT NULL,
[OONNEGO] [varchar](50) NOT NULL,
[CLM_ATT1] [varchar](50) NULL,
[CLM_ATT2] [varchar](50) NULL,
[CLM_ATT3] [varchar](50) NULL,
[CLM_ATT4] [varchar](50) NULL,
[CLM_ATT5] [varchar](50) NULL,
[NoteData] [varchar](8000) NULL)

INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])
SELECT Distinct
--n.note_sys,
--c.CLM_id1 AS 'ClaimNumber',
e.EVE_id1 AS 'EventNumber',
ClaimNumber1 = '',
--e.EVE_clm As 'EventEventTable',
--c.CLM_elrc AS 'EventClaimTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
ProviderID = '',
e.EVE_EXNO AS 'HCSAuthorizationID',

--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
p.EVEP_LNAME AS PROLASTNAME,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
--c.CLM_EDID AS 'LOCKDATE',
TOTALBILLEDAMT = '',
SAVINGS = '',
OONNEGO = '',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData

FROM dbo.EVE e

RIGHT JOIN dbo.clm c
ON e.EVE_id1 = c.clm_id1
RIGHT JOIN dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1

Where
e.eve_resl1 = 'CL'and
p.EVEP_RESL1 <> 'NG' and
p.evep_reas <>'FA'--and ---means closes Events
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
--e.EVE_STAT = 'CL'and
--e.EVE_id1 IN ('00101965','00102080','00102084','00101962','00101963') --and

Order by e.eve_resl1,p.EVEP_RESL1

INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])

SELECT Distinct
--n.note_sys,
--c.CLM_id1 AS 'ClaimNumber',
e.EVE_id1 AS 'EventNumber',
ClaimNumber1 = '',
--e.EVE_clm As 'EventEventTable',
--c.CLM_elrc AS 'EventClaimTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
ProviderID = '',
e.EVE_EXNO AS 'HCSAuthorizationID',

--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
p.EVEP_LNAME AS PROLASTNAME,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
--c.CLM_EDID AS 'LOCKDATE',
TOTALBILLEDAMT = '',
SAVINGS = '',
OONNEGO = '',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData

FROM dbo.EVE e

RIGHT JOIN dbo.clm c
ON e.EVE_id1 = c.clm_id1
RIGHT JOIN dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1

Where
((e.eve_resl1 = '' and p.evep_reas = 'HO')or (e.eve_resl1 = '' and p.evep_reas = 'PH') or (e.eve_resl1 = '' and p.evep_reas = 'AN'))

Order by e.eve_resl1,p.EVEP_RESL1

--and ---means Open Events will not have claims attached to them. we just need the fields in this report.
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)

INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])
SELECT Distinct
c.CLM_elrc AS 'EventNumber',
CASE c.clm_ips
WHEN 'C' THEN c.clm_pclm
ELSE c.clm_id1
END as "claimnumber1", --ClmClaims."CLAIMNUMBER",
--c.CLM_PCLM as "CLAIMNUMBER",
--n.note_sys,
--c.clm_id1,
--e.EVE_clm As 'EventEventTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
c.CLM_5 AS 'ProviderID',
e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
--p.EVEP_OFFIC AS 'PROVIDEROFFICE',
p.evep_lname AS ProLastName,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
c.CLM_MCHG AS 'TOTALBILLEDAMT',
c.CLM_SPPO AS 'SAVINGS',
c.CLM_55d AS 'OONNEGO',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData

FROM dbo.EVE e
Right Join dbo.clm c
ON e.EVE_id1 = c.clm_elrc
Right join dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1

Where
p.EVEP_RESL1 = 'NG'and
clm_adjto = '' ---"NG"This report is going to be changed to the negoitated report the field is going to be "NG"
--e.eve_resl1 = 'NG'and
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001'AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)

Order by e.eve_resl1,p.EVEP_RESL1

END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-18 : 15:03:11

ORDER BY is not for inserting data in the table but only while retrieving data using SELECT. Order of rows inserted in table is neither guaranteed nor does it matter.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-21 : 10:09:44

Ok, Thank you.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-21 : 14:13:42

Use ORDER BY to ensure your data is selected in the oreder required .

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources