Migration ''Group BY'' SQL, from Sybase to MS SQLServer 2000
-
Hi Experts
Please Help me migrate to SQLServer Sybase Script :
I Have SQLScript with Group By IN SYBASE its work good:-
-------------------------------------------------------
SELECT dbo.moved.Awb,
dbo.moved.Flight_no,
dbo.moved.Flight_date,
dbo.moved.Origin,
dbo.moved.Dest,
dbo.moved.Carrier,
dbo.moved.Status_date,
dbo.moved.Pcs,
dbo.moved.Weight,
dbo.moved.Message_date,
dbo.moved.Status,
min(dbo.moved.Message_date ),
leg_sort=10,status_sort=20,
dbo.moved.Text
FROM dbo.moved
WHERE dbo.moved.Awb ='11111'
GROUP BY dbo.moved.Awb,
dbo.moved.Status,
dbo.moved.Carrier,
dbo.moved.Weight,
dbo.moved.Origin,
dbo.moved.Flight_no,
dbo.moved.Flight_date,
dbo.moved.Pcs,
dbo.moved.Dest
HAVING ( dbo.moved.Message_date = max( dbo.moved.Message_date) )
ORDER BY dbo.moved.Flight_date ASC
------------------------------
------------------------------ -------------- but in SQLServer I getting Message :-
------------------------------
------------------------------ -------------- Server: Msg 8120, Level 16, State 1, Line 1
Column 'dbo.fsa.Status_date' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dbo.fsa.Message_date' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY
clause.
<script></script>Server: Msg 8120, Level 16, State 1, Line 1
Column 'dbo.fsa.Text' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8121, Level 16, State 1, Line 1
Column 'dbo.fsa.Message_date' is invalid in the HAVING clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
------------------------------
------------------------------ --------------How I can convert It to SQLServer 2000 with same result Set(Include Group BY) 
-
The error messages, in this case, are accurate and I presume that you understand why they are appearing. I have no experience of Sybase, so I cannot comment on why it worked there.
If your fields are always the same within the different groups (eg status_date does not vary within each grouping), you could use an aggregate function - try
max(dbo.moved.Status_date)
instead of dbo.moved.Status_date and see what happens. If any of the 'error' field values vary within groups, the select is logically flawed and should be re-worked.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The first of all, thanks for Phill for try to help me
but,
If I Use Agregate functions the Select will Return wrong Value (Min or Max :angry
and of cose different what I getting in SYbase table identical as in MS SQL. -
OK - can you provide a small sample of the data you have and what you want to be returned. No need to include all of the existing GROUP BY fields. I am trying to understand what values Sybase returned - max, min, average, first, last, random etc? It must have had to choose a value somehow from the underlying records in the group.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here fore Gury ask
CREATE TABLE dbo.moved
(
Awb varchar(12) NOT NULL,
Message_date datetime NOT NULL,
Status varchar(3) NOT NULL,
Origin varchar(3) NOT NULL,
Dest varchar(3) NOT NULL,
Status_date smalldatetime NULL,
Pcs numeric(5,0) NULL,
Weight numeric(10,2) NULL,
Carrier varchar(3) NULL,
Flight_no varchar(4) NULL,
Flight_date smalldatetime NULL,
Text varchar(255) NULL,
Remarks varchar(255) NULL,
CONSTRAINT fsa_Moved
PRIMARY KEY CLUSTERED (Awb,Message_date,Status,Origin,Dest)
)
INSERT INTO moved VALUES (
'.ALFMAZ 290',
'7-29-2004 11:48:41.936',
'HDR',
'843',
'',
NULL,
0,
0,
'',
'',
NULL,
'.ALLFMAZ 290843',
'');
INSERT INTO moved VALUES (
'.ALFMAZ 290',
'7-29-2004 11:48:41.936',
'OSI',
'',
'',
NULL,
0,
0,
'',
'',
NULL,
'OSI/NO RECORD FOUND',
'NO RECORD FOUND');
INSERT INTO moved VALUES (
'01441558451',
'11-12-2004 6:8:53.436',
'HDR',
'',
'',
'11-12-2004 5:3:0.000',
0,
0,
'AC',
'',
NULL,
'014-41558451',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-12-2004 6:8:53.436',
'OSI',
'',
'',
NULL,
0,
0,
'',
'',
NULL,
'OSI/NO RECORD FOUND',
'NO RECORD FOUND');
INSERT INTO moved VALUES (
'01441558451',
'11-14-2004 6:9:18.530',
'HDR',
'',
'',
'11-14-2004 5:3:0.000',
0,
0,
'AC',
'',
NULL,
'014-41558451',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-14-2004 6:9:18.546',
'OSI',
'',
'',
NULL,
0,
0,
'',
'',
NULL,
'OSI/NO RECORD FOUND',
'NO RECORD FOUND');
INSERT INTO moved VALUES (
'01441558451',
'11-15-2004 6:7:48.656',
'HDR',
'',
'',
'11-15-2004 5:2:0.000',
0,
0,
'AC',
'',
NULL,
'014-41558451',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-15-2004 6:7:48.670',
'OSI',
'',
'',
NULL,
0,
0,
'',
'',
NULL,
'OSI/NO RECORD FOUND',
'NO RECORD FOUND');
INSERT INTO moved VALUES (
'01441558451',
'11-16-2004 6:7:51.576',
'HDR',
'',
'',
'11-16-2004 5:2:0.000',
0,
0,
'AC',
'',
NULL,
'014-41558451',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-16-2004 6:7:51.593',
'OSI',
'',
'',
NULL,
0,
0,
'',
'',
NULL,
'OSI/NO RECORD FOUND',
'NO RECORD FOUND');
INSERT INTO moved VALUES (
'01441558451',
'11-17-2004 6:8:27.733',
'HDR',
'',
'',
'11-17-2004 5:2:0.000',
0,
0,
'AC',
'',
NULL,
'014-41558451',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-17-2004 6:8:28.030',
'OSI',
'',
'',
NULL,
0,
0,
'',
'',
NULL,
'OSI/NO RECORD FOUND',
'NO RECORD FOUND');
INSERT INTO moved VALUES (
'01441558451',
'11-18-2004 6:28:26.656',
'HDR',
'',
'',
'11-18-2004 5:3:0.000',
0,
0,
'AC',
'',
NULL,
'014-41558451',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-18-2004 6:28:26.670',
'OSI',
'',
'',
NULL,
0,
0,
'',
'',
NULL,
'OSI/NO RECORD FOUND',
'NO RECORD FOUND');
INSERT INTO moved VALUES (
'01441558451',
'11-19-2004 6:39:31.516',
'HDR',
'TLV',
'YOW',
'11-19-2004 5:2:0.000',
1,
7.00,
'AC',
'',
NULL,
'014-41558451TLVYOW/T1K7',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-19-2004 6:39:31.530',
'DEP',
'TLV',
'YYZ',
'11-19-2004 5:2:0.000',
1,
7.00,
'AC',
'087',
'11-19-2004 0:0:0.000',
'DEP/AC087/19NOV/TLVYYZ/T1K7/A0109/E0640',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-21-2004 7:9:56.186',
'HDR',
'TLV',
'YOW',
'11-21-2004 5:2:0.000',
1,
7.00,
'AC',
'',
NULL,
'014-41558451TLVYOW/T1K7',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-21-2004 7:9:56.186',
'NFD',
'',
'YOW',
'11-21-2004 5:2:0.000',
1,
7.00,
'',
'',
'11-19-2004 0:0:0.000',
'NFD/19NOV2344/YOW/T1K7/ISRAEL EMBASSY OF',
'');
INSERT INTO moved VALUES (
'01441558451',
'11-21-2004 7:9:56.203',
'RCF',
'',
'YOW',
NULL,
1,
7.00,
'AC',
'470',
'11-19-2004 0:0:0.000',
'RCF/AC470/19NOV2344/YOW/T1K7//A2258',
'');
thanks for Help
-
The following query works in MS SQL Server and produces the same result. You may want to run showplan to determine whether additional indexes are needed.
SELECT m2.Awb,
m2.Flight_no,
m2.Flight_date,
m2.Origin,
m2.Dest,
m2.Carrier,
m1.Status_date,
m2.Pcs,
m2.Weight,
m1.Message_date,
m2.Status,
(select min(m3.Message_date)
from dbo.moved m3
where m2.Awb = m3.Awb and
m2.Status = m3.Status and
m2.Carrier = m3.Carrier and
m2.Weight = m3.Weight and
m2.Origin = m3.Origin and
m2.Flight_no = m3.Flight_no and
m2.Flight_date = m3.Flight_date and
m2.Pcs = m3.Pcs and
m2.Dest = m3.Dest) min_Message_date,
leg_sort = 10,
status_sort = 20,
m1.Text
FROM dbo.moved m1,
(select Awb,
Status,
Carrier,
Weight,
Origin,
Flight_no,
Flight_date,
Pcs,
Dest,
max(Message_date) Message_date
FROM dbo.moved
GROUP BY Awb,
Status,
Carrier,
Weight,
Origin,
Flight_no,
Flight_date,
Pcs,
Dest) m2
WHERE m2.Awb *= m1.Awb and
m2.Status *= m1.Status and
m2.Carrier *= m1.Carrier and
m2.Weight *= m1.Weight and
m2.Origin *= m1.Origin and
m2.Flight_no *= m1.Flight_no and
m2.Flight_date *= m1.Flight_date and
m2.Pcs *= m1.Pcs and
m2.Dest *= m1.Dest and
m2.Message_date *= m1.Message_date
ORDER BY m1.Flight_date ASC
go
Hope this help
-- Peter Lo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply