Convertir requête PostgreSQL en Oracle

**jules_diedhiou** · 13/05/2013, 12h32

Bonjour je veux creer traduire cette requéte creer en postgres en oracle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
CREATE OR REPLACE VIEW rm_documents_view AS 
SELECT 
    rm_documents.*,
 
    rm_items.archival_agency_it_ident,
    rm_items.description_language,
    rm_items.name,
    rm_items.item_type,
    rm_items.originating_agency_it_ident,
    rm_items.service_level,
    rm_items.transferring_agency_it_ident,
    rm_items.schedule_id, 
 
    rm_content_descriptions.description as content_description,
    rm_content_descriptions.description_level,
    rm_content_descriptions.file_plan_position,
    rm_content_descriptions.language,
    rm_content_descriptions.latest_date,
    rm_content_descriptions.oldest_date,
    rm_content_descriptions.other_descriptive_data,
 
    rm_custodial_history.custodial_history_item,
 
    doctypes.description as type_label,
 
    doctypes_first_level.doctypes_first_level_id, 
    doctypes_first_level.doctypes_first_level_label, 
    doctypes_first_level.css_style as doctype_first_level_style, 
 
    doctypes_second_level.doctypes_second_level_id, 
    doctypes_second_level.doctypes_second_level_label, 
    doctypes_second_level.css_style as doctype_second_level_style,
 
    file_plan_position.folders_system_id,
 
	schedule.folder_id as schedule_name,
 
    originating_agency.identification as originating_ag_ident,
    originating_agency.entity_id as originating_ag_entity_id,
    originating_agency.name as originating_ag_name,
 
    rm_contacts.department_name as dest_user,
 
    rm_appraisal_rules.code as appraisal_code,
    rm_appraisal_rules.duration as appraisal_duration,
    rm_appraisal_rules.start_date as appraisal_start_date,
 
    rm_access_restriction_rules.code as access_restriction_code,
    rm_access_restriction_rules.start_date as access_restrict_start_date
 
FROM rm_documents
    LEFT JOIN rm_items on rm_items.item_id = rm_documents.item_id
    LEFT JOIN rm_content_descriptions on rm_items.item_id = rm_content_descriptions.item_id
    LEFT JOIN rm_custodial_history on rm_items.item_id = rm_custodial_history.item_id
    LEFT JOIN doctypes on rm_documents.type_id = doctypes.type_id
    LEFT JOIN doctypes_first_level ON doctypes.doctypes_first_level_id = doctypes_first_level.doctypes_first_level_id
    LEFT JOIN doctypes_second_level ON doctypes.doctypes_second_level_id = doctypes_second_level.doctypes_second_level_id
    LEFT JOIN rm_organizations AS originating_agency ON originating_agency.organization_id = 
        (
        SELECT organization_id 
        FROM rm_organizations 
        WHERE rm_organizations.parent_id = rm_items.item_id AND rm_organizations.role = 'OriginatingAgency'
        ORDER BY organization_id
        LIMIT 1
        )
    LEFT JOIN rm_contacts on originating_agency.organization_id = rm_contacts.organization_id and contact_id = 
        (
        SELECT contact_id 
        FROM rm_contacts 
        WHERE rm_contacts.organization_id = originating_agency.organization_id
        ORDER BY contact_id
        LIMIT 1
        )
    LEFT JOIN rm_appraisal_rules ON rm_appraisal_rules.parent_id = rm_items.item_id
    LEFT JOIN rm_access_restriction_rules ON rm_access_restriction_rules.parent_id = rm_items.item_id
    LEFT JOIN folders file_plan_position ON rm_content_descriptions.file_plan_position = file_plan_position.folder_id AND foldertype_id = '101'
    LEFT JOIN folders schedule ON rm_items.schedule_id = schedule.folders_system_id
WHERE item_type = 'ArchiveObject';

et j'ai cette erreur

Erreur à la ligne de commande : 63, colonne : 8
Rapport d'erreur :
Erreur SQL : ORA-00907: parenthèse de droite absente
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:

**ojo77** · 13/05/2013, 13h08

Remplacez les Select ... From ... where ... order by .... limit 1 sur le modèle suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT organization_id 
FROM rm_organizations 
WHERE rm_organizations.parent_id = rm_items.item_id AND rm_organizations.role = 'OriginatingAgency'
ORDER BY organization_id
LIMIT 1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Select min(organization_id)
FROM rm_organizations 
WHERE rm_organizations.parent_id = rm_items.item_id 
  AND rm_organizations.role = 'OriginatingAgency'

**al1_24** · 13/05/2013, 13h08

C'est la forme des sous-requête qui n'est pas adaptée.

il faudrait remplacer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT  organization_id 
FROM    rm_organizations 
WHERE   rm_organizations.parent_id = rm_items.item_id 
    AND rm_organizations.role = 'OriginatingAgency'
ORDER BY organization_id
LIMIT 1

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT  MIN(organization_id) 
FROM    rm_organizations 
WHERE   rm_organizations.parent_id = rm_items.item_id 
    AND rm_organizations.role = 'OriginatingAgency'

**jules_diedhiou** · 13/05/2013, 13h53

maintenant voila ce que j'ai comme erreur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
CREATE OR REPLACE VIEW drs.rm_documents_view AS 
SELECT 
    rm_documents.*,
 
    drs.rm_items.archival_agency_it_ident,
    drs.rm_items.description_language,
    drs.rm_items.name,
    drs.rm_items.item_type,
    drs.rm_items.originating_agency_it_ident,
    drs.rm_items.service_level,
    drs.rm_items.transferring_agency_it_ident,
    drs.rm_items.schedule_id, 
 
    drs.rm_content_descriptions.description as content_description,
    drs.rm_content_descriptions.description_level,
    drs.rm_content_descriptions.file_plan_position,
    drs.rm_content_descriptions.language,
    drs.rm_content_descriptions.latest_date,
    drs.rm_content_descriptions.oldest_date,
    drs.rm_content_descriptions.other_descriptive_data,
 
    drs.rm_custodial_history.custodial_history_item,
 
    drs.doctypes.description as type_label,
 
    drs.doctypes_first_level.doctypes_first_level_id, 
    drs.doctypes_first_level.doctypes_first_level_label, 
    drs.doctypes_first_level.css_style as doctype_first_level_style, 
 
    drs.doctypes_second_level.doctypes_second_level_id, 
    drs.doctypes_second_level.doctypes_second_level_label, 
    drs.doctypes_second_level.css_style as doctype_second_level_style,
 
    drs.file_plan_position.folders_system_id,
 
	drs.schedule.folder_id as schedule_name,
 
    drs.originating_agency.identification as originating_ag_ident,
    drs.originating_agency.entity_id as originating_ag_entity_id,
    drs.originating_agency.name as originating_ag_name,
 
    drs.rm_contacts.department_name as dest_user,
 
    drs.rm_appraisal_rules.code as appraisal_code,
    drs.rm_appraisal_rules.duration as appraisal_duration,
    drs.rm_appraisal_rules.start_date as appraisal_start_date,
 
    drs.rm_access_restriction_rules.code as access_restriction_code,
    drs.rm_access_restriction_rules.start_date as access_restrict_start_date
 
FROM drs.rm_documents
    LEFT JOIN drs.rm_items on drs.rm_items.item_id = drs.rm_documents.item_id
    LEFT JOIN drs.rm_content_descriptions on drs.rm_items.item_id = drs.rm_content_descriptions.item_id
    LEFT JOIN drs.rm_custodial_history on drs.rm_items.item_id = drs.rm_custodial_history.item_id
    LEFT JOIN drs.doctypes on drs.rm_documents.type_id = drs.doctypes.type_id
    LEFT JOIN drs.doctypes_first_level ON drs.doctypes.doctypes_first_level_id = drs.doctypes_first_level.doctypes_first_level_id
    LEFT JOIN drs.doctypes_second_level ON drs.doctypes.doctypes_second_level_id = drs.doctypes_second_level.doctypes_second_level_id
    LEFT JOIN drs.rm_organizations  originating_agency ON drs.originating_agency.organization_id = 
        (
       SELECT  MIN(organization_id) 
      FROM    drs.rm_organizations 
      WHERE   drs.rm_organizations.parent_id = drs.rm_items.item_id 
      AND drs.rm_organizations.role = 'OriginatingAgency'
        )
    LEFT JOIN drs.rm_contacts on drs.originating_agency.organization_id = drs.rm_contacts.organization_id and contact_id = 
        (
        SELECT MIN(contact_id) 
        FROM drs.rm_contacts 
        WHERE drs.rm_contacts.organization_id = drs.originating_agency.organization_id
 
        )
    LEFT JOIN drs.rm_appraisal_rules ON drs.rm_appraisal_rules.parent_id = rm_items.item_id
    LEFT JOIN drs.rm_access_restriction_rules ON drs.rm_access_restriction_rules.parent_id = drs.rm_items.item_id
    LEFT JOIN drs.folders file_plan_position ON drs.rm_content_descriptions.file_plan_position = drs.file_plan_position.folder_id AND foldertype_id = '101'
    LEFT JOIN drs.folders schedule ON drs.rm_items.schedule_id = drs.schedule.folders_system_id
WHERE item_type = 'ArchiveObject';

Erreur à la ligne de commande : 1, colonne : 0
Rapport d'erreur :
Erreur SQL : ORA-01799: une colonne ne peut pas être jointe extérieurement à une sous-interrogation
01799. 00000 - "a column may not be outer-joined to a subquery"
*Cause: <expression>(+) <relop> (<subquery>) is not allowed.
*Action: Either remove the (+) or make a view out of the subquery.
In V6 and before, the (+) was just ignored in this case.

**mnitu** · 13/05/2013, 15h51

Votre jointure avec la sous-requête d'origine

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
LEFT JOIN rm_organizations AS originating_agency ON originating_agency.organization_id = 
        (
        SELECT organization_id 
        FROM rm_organizations 
        WHERE rm_organizations.parent_id = rm_items.item_id 
          AND rm_organizations.role = 'OriginatingAgency'
        ORDER BY organization_id
        LIMIT 1
        )

doit être réecrite de la façon

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
Left Join
        (
        Select t.*
          From (
                  SELECT organization_id,
                         parent_id,
                         role,
                         identification,
                         name, 
                         entity_id
                         row_number() over (partition by parent_id order by organization_id) rn
                  FROM rm_organizations 
                  WHERE rm_organizations.role = 'OriginatingAgency'
               )  t
         Where t.rn = 1
        ) originating_agency
    On originating_agency.organization_id = rm_items.item_id

**jules_diedhiou** · 13/05/2013, 17h56

je ne comprend pas vraiment que represente le select t*?

**mnitu** · 13/05/2013, 18h05

Vous sélectionnez toutes les colonnes de la table qui vous intéresse, vous les attribuées un rang en les ordonnant de la manière souhaité et vous gardez le premier enregistrement (top N).

Convertir requête PostgreSQL en Oracle

SQL Oracle

Discussions similaires

Partager

Partager