Help:Problem with DISTINCTROW.

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

Help:Problem with DISTINCTROW.

Author

Topic

Nav522
Starting Member

27 Posts

Posted - 2009-11-19 : 18:53:58

Hello Folks,
I am trying to modify the Access Sql Query that i have behind the access report to Oracle sql query. I am havinf trouble with the DISTINCTROW function. I cannot figure out the equivalent for DISTINCTROW in oracle server sql query or normal sql query.Can anybody please throw some light on this. Am pasting the code for reference.

SELECT DISTINCTROW "CUBS SNAPSHOT"."TYPE", -- how to replace this
"CUBS SNAPSHOT"."SUBTYPE",
"CUBS SNAPSHOT"."LOB",
Client.Client_Name,
"CUBS SNAPSHOT"."CLIENT" AS ClientCode,
"CUBS SNAPSHOT"."EVENT_ID" AS filenum,
"CUBS SNAPSHOT"."EVENT_ID",
"CUBS SNAPSHOT"."ASSIGNDATE",
"CUBS SNAPSHOT"."STATUS",
"CUBS SNAPSHOT".PRORATED_TRANAMT,
"CUBS SNAPSHOT".PRORATED_FEEPAID,
"CUBS SNAPSHOT".PRORATED_DIRECTPAY

FROM
"CUBS SNAPSHOT",client
WHERE ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_FEEAMT) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_DIRECTPAY) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_FEEPAID) != 0))And "CUBS SNAPSHOT".Client = Client.Client_code

Thanks a million

Nav522
Starting Member

27 Posts

Posted - 2009-11-19 : 20:50:39

Can anybody throw some light on this?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-11-19 : 21:38:26

There is no DISTINCTROW keyword in either Oracle or SQL Server, it's specific to Access. Only DISTINCT is supported. There's a good explanation of the differences here:

http://bytes.com/topic/access/answers/209027-distinct-vs-distinctrow

You'll either need to rewrite it to use DISTINCT (or no keyword at all), or pull the raw Oracle/SQL data into local Access tables and use the existing DISTINCTROW on those tables.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-19 : 21:49:04

quote:
Originally posted by robvolk

There is no DISTINCTROW keyword in either Oracle or SQL Server, it's specific to Access. Only DISTINCT is supported. There's a good explanation of the differences here:

http://bytes.com/topic/access/answers/209027-distinct-vs-distinctrow

You'll either need to rewrite it to use DISTINCT (or no keyword at all), or pull the raw Oracle/SQL data into local Access tables and use the existing DISTINCTROW on those tables.

IT'S ALIVE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Nav522
Starting Member

27 Posts

Posted - 2009-11-19 : 22:23:35

Hey rob thanks for getting back.Basically the idea is we are trying to get rid of the access reports and planning to create the same report in crystal rpt. But crystal is not accepting the DISTINCTROW that is in the query. I have used DISTINCT but the data doesnt matches between the new crystal report and the old access report. The query that i have tried is like this. but the data is not correct i guess it duplicating. Any ideas about this.Thanks a million

SELECT DISTINCT "CUBS SNAPSHOT"."TYPE",
"CUBS SNAPSHOT"."SUBTYPE",
"CUBS SNAPSHOT"."LOB",
Client.Client_Name,
"CUBS SNAPSHOT"."CLIENT" AS ClientCode,
"CUBS SNAPSHOT"."EVENT_ID" AS filenum,
"CUBS SNAPSHOT"."EVENT_ID",
"CUBS SNAPSHOT"."ASSIGNDATE",
"CUBS SNAPSHOT"."STATUS",
"CUBS SNAPSHOT".PRORATED_TRANAMT,
"CUBS SNAPSHOT".PRORATED_FEEPAID,
"CUBS SNAPSHOT".PRORATED_DIRECTPAY

FROM
"CUBS SNAPSHOT",client
WHERE ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_FEEAMT) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_DIRECTPAY) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_FEEPAID) != 0))And "CUBS SNAPSHOT".Client = Client.Client_code

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-11-19 : 23:31:04

Try it without DISTINCT or DISTINCTROW. It's also possible that DISTINCTROW returned incorrect or inconsistent results, since the source data could have actual duplicates. You should dig into it a bit, for no other reason than to verify it's correct.

If DISTINCTROW is correct, then you can simulate it in SQL Server using a subquery:

SELECT A.Type, A.SubType, A.LOB, A.Client_Name, A.Client AS ClientCode, A.Event_ID AS FileNum,
A.Event_ID, A.AssignDate, A.Status, A.Prorated_TranAmt, A.Prorated_FeePaid, A.Prorated_DirectPay 
FROM (SELECT DISTINCT * FROM
"CUBS SNAPSHOT",client
WHERE ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_FEEAMT) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_DIRECTPAY) != 0))
OR ((("CUBS SNAPSHOT".LOB) = 'MEDICAL') AND (("CUBS SNAPSHOT".PRORATED_TRANAMT) = 0) AND
(("CUBS SNAPSHOT".PRORATED_FEEPAID) != 0))And "CUBS SNAPSHOT".Client = Client.Client_code) A

You should remove the extra/redundant parentheses though. This should also work in Oracle, again if you fix the parentheses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources