Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Queries metadata are different between FB2.5 and F4.0 #7340

Closed
medi6 opened this issue Oct 9, 2022 · 1 comment
Closed

Queries metadata are different between FB2.5 and F4.0 #7340

medi6 opened this issue Oct 9, 2022 · 1 comment

Comments

@medi6
Copy link

medi6 commented Oct 9, 2022 •
edited

Hi everyone,

We're working on a big soft in Java, and we want to migrate from FB2.5 to FB4.0.
We are using our self home made ORM to create some beans.
This ORM is using queries meta data to match a class with a table name.
The problem is that we've got some differencies in metada when we're using sub queries.

Our sub query :

LEFT OUTER JOIN
(
SELECT
PRO_EVT_CYCLE.* ,
PRO_EVT_CYCLE_JOUR.ID_PROEVT_CYCLE_J AS ID_EJ ,
PRO_EVT_CYCLE_JOUR.REF_PRO_EVT_CYCLE AS REF_PRO_EVT_CYCLE_EJ ,
PRO_EVT_CYCLE_JOUR.RANG_SEM_PROEVT_CYCLE_J AS RANG_SEM_EJ ,
PRO_EVT_CYCLE_JOUR.EST_MODIF_PROEVT_CYCLE_J AS EST_MODIF_EJ ,
PRO_EVT_CYCLE_JOUR.REF_TYPE_JOUR AS REF_TYPE_JOUR_EJ ,
PRO_EVT_CYCLE_JOUR.REF_ENTITE AS REF_ENTITE_EJ ,
PRO_EVT_CYCLE_JOUR.REF_TYPE_EVT_PLANNING AS REF_TYPE_EVT_PLANNING_EJ ,
PRO_EVT_CYCLE_JOUR.REF_PARAM_JOUR_TYPE AS REF_PARAM_JOUR_TYPE_EJ ,
PRO_EVT_CYCLE_JOUR.REF_PARAM_TOUR_MODEL AS REF_PARAM_TOUR_MODEL_EJ ,
PRO_EVT_CYCLE_JOUR.REF_PARAM_MOMENT AS REF_PARAM_MOMENT_EJ ,
PRO_EVT_CYCLE_JOUR.REF_PARAM_SECTEUR AS REF_PARAM_SECTEUR_EJ ,
PRO_EVT_CYCLE_JOUR.REF_PARAM_TYPE_EVT_PLANNING AS REF_PARAM_TYPE_EVT_PLANNING_EJ,
COALESCE(PRO_EVT_CYCLE_JOUR.H_deb_PROEVT_CYCLE_J, PARAM_JOUR_TYPE.H_deb_PARAM_JOUR_TYPE,
FROM
PRO_EVT_CYCLE
LEFT OUTER JOIN
PRO_EVT_CYCLE_JOUR
ON
PRO_EVT_CYCLE_JOUR.REF_PRO_EVT_CYCLE = PRO_EVT_CYCLE.ID_PROEVT_CYCLE
LEFT OUTER JOIN
PARAM_TYPE_EVT_PLANNING PARAM_TYPE_EVT_PLANNING_EJ
ON
PARAM_TYPE_EVT_PLANNING_EJ.ID_PRM_TYPE_EVT_PLANNING = PRO_EVT_CYCLE_JOUR.REF_PARAM_TYPE_EVT_PLANNING
LEFT OUTER JOIN
PARAM_JOUR_TYPE
ON
PARAM_JOUR_TYPE.ID_PARAM_JOUR_TYPE = PRO_EVT_CYCLE_JOUR.REF_PARAM_JOUR_TYPE
LEFT OUTER JOIN
PARAM_TOUR_MODEL
ON
PARAM_TOUR_MODEL.ID_PARAM_TOUR_MODEL = PRO_EVT_CYCLE_JOUR.REF_PARAM_TOUR_MODEL
LEFT OUTER JOIN
PARAM_MOMENT
ON
PARAM_MOMENT.ID_PARAM_MOMENT = PRO_EVT_CYCLE_JOUR.REF_PARAM_MOMENT
LEFT OUTER JOIN
PARAM_MOMENT_VERSION
ON
PARAM_MOMENT_VERSION.REF_PARAM_MOMENT = PRO_EVT_CYCLE_JOUR.REF_PARAM_MOMENT
AND PARAM_MOMENT_VERSION.REF_PARAM_PLANNING_VERSION IN (1,2)
LEFT OUTER JOIN
PARAM_TOUR_MODEL_MOMENT
ON
PARAM_TOUR_MODEL_MOMENT.REF_PARAM_TOUR_MODEL = PARAM_TOUR_MODEL.ID_PARAM_TOUR_MODEL ) EVT_CYCLE

In FB 2.5, it returns original fields :
Field #48: PRO_EVT_CYCLE.ID_PROEVT_CYCLE Alias:ID_PROEVT_CYCLE Type:BIGINT
Field #49: PRO_EVT_CYCLE.REF_PRO_CONTRAT Alias:REF_PRO_CONTRAT Type:BIGINT
Field #50: PRO_EVT_CYCLE.DUREE_CYCLE_PROEVT_CYCLE Alias:DUREE_CYCLE_PROEVT_CYCLE Type:INTEGER
Field #51: PRO_EVT_CYCLE.D_REFERENCE_PROEVT_CYCLE Alias:D_REFERENCE_PROEVT_CYCLE Type:DATE
Field #52: PRO_EVT_CYCLE_JOUR.ID_PROEVT_CYCLE_J Alias:ID_EJ Type:BIGINT
Field #53: PRO_EVT_CYCLE_JOUR.REF_PRO_EVT_CYCLE Alias:REF_PRO_EVT_CYCLE_EJ Type:BIGINT
Field #54: PRO_EVT_CYCLE_JOUR.RANG_SEM_PROEVT_CYCLE_J Alias:RANG_SEM_EJ Type:INTEGER
Field #55: PRO_EVT_CYCLE_JOUR.EST_MODIF_PROEVT_CYCLE_J Alias:EST_MODIF_EJ Type:INTEGER
Field #56: PRO_EVT_CYCLE_JOUR.REF_TYPE_JOUR Alias:REF_TYPE_JOUR_EJ Type:BIGINT
Field #57: PRO_EVT_CYCLE_JOUR.REF_ENTITE Alias:REF_ENTITE_EJ Type:BIGINT
Field #58: PRO_EVT_CYCLE_JOUR.REF_TYPE_EVT_PLANNING Alias:REF_TYPE_EVT_PLANNING_EJ Type:BIGINT
Field #59: PRO_EVT_CYCLE_JOUR.REF_PARAM_JOUR_TYPE Alias:REF_PARAM_JOUR_TYPE_EJ Type:BIGINT
Field #60: PRO_EVT_CYCLE_JOUR.REF_PARAM_TOUR_MODEL Alias:REF_PARAM_TOUR_MODEL_EJ Type:BIGINT
Field #61: PRO_EVT_CYCLE_JOUR.REF_PARAM_MOMENT Alias:REF_PARAM_MOMENT_EJ Type:BIGINT
Field #62: PRO_EVT_CYCLE_JOUR.REF_PARAM_SECTEUR Alias:REF_PARAM_SECTEUR_EJ Type:BIGINT
Field #63: PRO_EVT_CYCLE_JOUR.REF_PARAM_TYPE_EVT_PLANNING Alias:REF_PARAM_TYPE_EVT_PLANNING_EJ Type:BIGINT
Field #64: . Alias:H_DEB_EJ Type:TIME
Field #65: . Alias:H_FIN_EJ Type:TIME
Field #66: . Alias:J1_EJ Type:INTEGER
Field #67: PARAM_TYPE_EVT_PLANNING.ID_PRM_TYPE_EVT_PLANNING Alias:ID_PRM_TYPE_EVT_PLANNING_EJ Type:BIGINT
Field #68: PARAM_TYPE_EVT_PLANNING.CODE_PRM_TYPE_EVT_PLANNING Alias:CODE_PRM_TYPE_EVT_PLANNING_EJ Type:STRING(5)
Field #69: PARAM_TYPE_EVT_PLANNING.LIB_PRM_TYPE_EVT_PLANNING Alias:LIB_PRM_TYPE_EVT_PLANNING_EJ Type:STRING(64)
Field #70: PARAM_TYPE_EVT_PLANNING.COULEUR_PRM_TYPE_EVT_PLANNING Alias:COULEUR_PRM_TYPE_EVT_PLANNING_E Type:INTEGER
Field #71: PARAM_JOUR_TYPE.ID_PARAM_JOUR_TYPE Alias:ID_PARAM_JOUR_TYPE Type:BIGINT
Field #72: PARAM_JOUR_TYPE.CODE_PARAM_JOUR_TYPE Alias:CODE_PARAM_JOUR_TYPE Type:STRING(5)
Field #73: PARAM_JOUR_TYPE.LIB_PARAM_JOUR_TYPE Alias:LIB_PARAM_JOUR_TYPE Type:STRING(64)
Field #74: PARAM_JOUR_TYPE.COULEUR_PARAM_JOUR_TYPE Alias:COULEUR_PARAM_JOUR_TYPE Type:INTEGER
Field #75: PARAM_JOUR_TYPE.H_DEB_PARAM_JOUR_TYPE Alias:H_DEB_PARAM_JOUR_TYPE Type:TIME
Field #76: PARAM_JOUR_TYPE.H_FIN_PARAM_JOUR_TYPE Alias:H_FIN_PARAM_JOUR_TYPE Type:TIME
Field #77: PARAM_JOUR_TYPE.J1_PARAM_JOUR_TYPE Alias:J1_PARAM_JOUR_TYPE Type:INTEGER
Field #78: PARAM_TOUR_MODEL.ID_PARAM_TOUR_MODEL Alias:ID_PARAM_TOUR_MODEL Type:BIGINT
Field #79: PARAM_TOUR_MODEL.REF_ENTITE Alias:REF_ENTITE_PARAM_TOUR_MODEL Type:BIGINT
Field #80: PARAM_TOUR_MODEL.CODE_PARAM_TOUR_MODEL Alias:CODE_PARAM_TOUR_MODEL Type:STRING(5)
Field #81: PARAM_TOUR_MODEL.LIB_PARAM_TOUR_MODEL Alias:LIB_PARAM_TOUR_MODEL Type:STRING(64)
Field #82: PARAM_TOUR_MODEL.COULEUR_PARAM_TOUR_MODEL Alias:COULEUR_PARAM_TOUR_MODEL Type:INTEGER
Field #83: PARAM_MOMENT.ID_PARAM_MOMENT Alias:ID_PARAM_MOMENT Type:BIGINT
Field #84: PARAM_MOMENT.CODE_PARAM_MOMENT Alias:CODE_PARAM_MOMENT Type:STRING(5)
Field #85: PARAM_MOMENT.COULEUR_PARAM_MOMENT Alias:COULEUR_PARAM_MOMENT Type:INTEGER
Field #86: PARAM_MOMENT.LIB_PARAM_MOMENT Alias:LIB_PARAM_MOMENT Type:STRING(64)
Field #87: PARAM_MOMENT_VERSION.H_DEB_PRM_MOMENT_V Alias:H_DEB_PRM_MOMENT_V Type:TIME
Field #88: PARAM_MOMENT_VERSION.H_FIN_PRM_MOMENT_V Alias:H_FIN_PRM_MOMENT_V Type:TIME
Field #89: PARAM_MOMENT_VERSION.J1_PRM_MOMENT_V Alias:J1_PRM_MOMENT_V Type:INTEGER

In FB4.0, all fields are ALIAS :
Field #48: . Alias:ID_PROEVT_CYCLE Type:BIGINT
Field #49: . Alias:REF_PRO_CONTRAT Type:BIGINT
Field #50: . Alias:DUREE_CYCLE_PROEVT_CYCLE Type:INTEGER
Field #51: . Alias:D_REFERENCE_PROEVT_CYCLE Type:DATE
Field #52: . Alias:ID_EJ Type:BIGINT
Field #53: . Alias:REF_PRO_EVT_CYCLE_EJ Type:BIGINT
Field #54: . Alias:RANG_SEM_EJ Type:INTEGER
Field #55: . Alias:EST_MODIF_EJ Type:INTEGER
Field #56: . Alias:REF_TYPE_JOUR_EJ Type:BIGINT
Field #57: . Alias:REF_ENTITE_EJ Type:BIGINT
Field #58: . Alias:REF_TYPE_EVT_PLANNING_EJ Type:BIGINT
Field #59: . Alias:REF_PARAM_JOUR_TYPE_EJ Type:BIGINT
Field #60: . Alias:REF_PARAM_TOUR_MODEL_EJ Type:BIGINT
Field #61: . Alias:REF_PARAM_MOMENT_EJ Type:BIGINT
Field #62: . Alias:REF_PARAM_SECTEUR_EJ Type:BIGINT
Field #63: . Alias:REF_PARAM_TYPE_EVT_PLANNING_EJ Type:BIGINT
Field #64: . Alias:H_DEB_EJ Type:TIME
Field #65: . Alias:H_FIN_EJ Type:TIME
Field #66: . Alias:J1_EJ Type:INTEGER
Field #67: . Alias:ID_PRM_TYPE_EVT_PLANNING_EJ Type:BIGINT
Field #68: . Alias:CODE_PRM_TYPE_EVT_PLANNING_EJ Type:STRING(5)
Field #69: . Alias:LIB_PRM_TYPE_EVT_PLANNING_EJ Type:STRING(64)
Field #70: . Alias:COULEUR_PRM_TYPE_EVT_PLANNING_E Type:INTEGER
Field #71: . Alias:ID_PARAM_JOUR_TYPE Type:BIGINT
Field #72: . Alias:CODE_PARAM_JOUR_TYPE Type:STRING(5)
Field #73: . Alias:LIB_PARAM_JOUR_TYPE Type:STRING(64)
Field #74: . Alias:COULEUR_PARAM_JOUR_TYPE Type:INTEGER
Field #75: . Alias:H_DEB_PARAM_JOUR_TYPE Type:TIME
Field #76: . Alias:H_FIN_PARAM_JOUR_TYPE Type:TIME
Field #77: . Alias:J1_PARAM_JOUR_TYPE Type:INTEGER
Field #78: . Alias:ID_PARAM_TOUR_MODEL Type:BIGINT
Field #79: . Alias:REF_ENTITE_PARAM_TOUR_MODEL Type:BIGINT
Field #80: . Alias:CODE_PARAM_TOUR_MODEL Type:STRING(5)
Field #81: . Alias:LIB_PARAM_TOUR_MODEL Type:STRING(64)
Field #82: . Alias:COULEUR_PARAM_TOUR_MODEL Type:INTEGER
Field #83: . Alias:ID_PARAM_MOMENT Type:BIGINT
Field #84: . Alias:CODE_PARAM_MOMENT Type:STRING(5)
Field #85: . Alias:COULEUR_PARAM_MOMENT Type:INTEGER
Field #86: . Alias:LIB_PARAM_MOMENT Type:STRING(64)
Field #87: . Alias:H_DEB_PRM_MOMENT_V Type:TIME
Field #88: . Alias:H_FIN_PRM_MOMENT_V Type:TIME
Field #89: . Alias:J1_PRM_MOMENT_V Type:INTEGER

Thanks for all,
@medi6
Copy link
Author

medi6 commented Jan 27, 2023

New issue with employee db

@medi6 medi6 closed this as not planned Won't fix, can't repro, duplicate, stale Jan 27, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@medi6