MapInfo Pro

Hi I wonder whether anyone has a quicker way of combing multiple queries into one query selection. In the example below I query 5 separate columns from a table producing a query selection for each, which I than have to append one by one to a table. Is there a way to end up with only one query selection?

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min1_Code, DR_GE_WS_BOD.Min1_pct
From DR_GE_WS_BOD
Where DR_GE_WS_BOD.Min1_Code= "TO"
Into GE_MIN1_TO_Q1

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min2_Code,DR_GE_WS_BOD.Min2_pct
From DR_GE_WS_BOD
Where DR_GE_WS_BOD.Min2_Code= "TO"
Into GE_MIN1_TO_Q2

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min3_Code, DR_GE_WS_BOD.Min3_pct
From DR_GE_WS_BOD
Where min3_code = "TO"
Into GE_MIN1_TO_Q3

Select hole_id, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min4_Code, DR_GE_WS_BOD.Min4_pct
From DR_GE_WS_BOD
Where min4_code = "TO"
Into GE_MIN1_TO_Q4

Select hole_id, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min5_Code, DR_GE_WS_BOD.Min5_pct
From DR_GE_WS_BOD
Where min5_code = "TO"
Into GE_MIN1_TO_Q5

Cheers,
Victor

------------------------------
Victor Kakebeeke
Global Prospecting Limited
Lichfield
------------------------------

Hi Victor

You may be able to use the IIf() or Cond() function to extract values depending on the value in your code columns.
Something along these lines:

Select t.Hole_ID, t.Depth_From, t.Depth_To
  , IIf(t.Min1_Code= "TO", t.Min1_Code, IIf(t.Min2_Code= "TO", t.Min2_Code, IIf(t.Min3_Code= "TO", t.Min3_Code
      , IIf(t.Min4_Code= "TO", t.Min4_Code, t.Min5_Code)))) As "Min_Code"
  , IIf(t.Min1_Code= "TO", t.Min1_pct, IIf(t.Min2_Code= "TO", t.Min2_pct, IIf(t.Min3_Code= "TO", t.Min3_pct
      , IIf(t.Min4_Code= "TO", t.Min4_pct, t.Min5_pct)))) As "Min_pct"
  , IIf(t.Min1_Code= "TO", t.Min1_pct, )
From DR_GE_WS_BOD As "t"
Where DR_GE_WS_BOD.Min1_Code= "TO"
   Or DR_GE_WS_BOD.Min2_Code= "TO"
   Or DR_GE_WS_BOD.Min3_Code= "TO"
   Or DR_GE_WS_BOD.Min4_Code= "TO"
   Or DR_GE_WS_BOD.Min5_Code= "TO" Or 
Into GE_MIN1_TO

Now, the first IIF() expression might be unnecessary as they all seem to return "TO", right?
Can one record have the code "TO" for multiple of the MinX_Code columns? If yes, then this might not work.

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------

Original Message:
Sent: 09-09-2021 06:21
From: Victor Kakebeeke
Subject: Merge multiple queies into one selection

Hi I wonder whether anyone has a quicker way of combing multiple queries into one query selection. In the example below I query 5 separate columns from a table producing a query selection for each, which I than have to append one by one to a table. Is there a way to end up with only one query selection?

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min1_Code, DR_GE_WS_BOD.Min1_pct
From DR_GE_WS_BOD
Where DR_GE_WS_BOD.Min1_Code= "TO"
Into GE_MIN1_TO_Q1

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min2_Code,DR_GE_WS_BOD.Min2_pct
From DR_GE_WS_BOD
Where DR_GE_WS_BOD.Min2_Code= "TO"
Into GE_MIN1_TO_Q2

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min3_Code, DR_GE_WS_BOD.Min3_pct
From DR_GE_WS_BOD
Where min3_code = "TO"
Into GE_MIN1_TO_Q3

Select hole_id, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min4_Code, DR_GE_WS_BOD.Min4_pct
From DR_GE_WS_BOD
Where min4_code = "TO"
Into GE_MIN1_TO_Q4

Select hole_id, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min5_Code, DR_GE_WS_BOD.Min5_pct
From DR_GE_WS_BOD
Where min5_code = "TO"
Into GE_MIN1_TO_Q5

Cheers,
Victor

------------------------------
Victor Kakebeeke
Global Prospecting Limited
Lichfield
------------------------------

Peter,
Thank you very much the solution worked.
Cheers,
Victor

------------------------------
Victor Kakebeeke
Global Prospecting Limited
Lichfield
------------------------------

Original Message:
Sent: 09-09-2021 07:57
From: Peter Horsbøll Møller
Subject: Merge multiple queies into one selection

Hi Victor

You may be able to use the IIf() or Cond() function to extract values depending on the value in your code columns.
Something along these lines:

Select t.Hole_ID, t.Depth_From, t.Depth_To
  , IIf(t.Min1_Code= "TO", t.Min1_Code, IIf(t.Min2_Code= "TO", t.Min2_Code, IIf(t.Min3_Code= "TO", t.Min3_Code
      , IIf(t.Min4_Code= "TO", t.Min4_Code, t.Min5_Code)))) As "Min_Code"
  , IIf(t.Min1_Code= "TO", t.Min1_pct, IIf(t.Min2_Code= "TO", t.Min2_pct, IIf(t.Min3_Code= "TO", t.Min3_pct
      , IIf(t.Min4_Code= "TO", t.Min4_pct, t.Min5_pct)))) As "Min_pct"
  , IIf(t.Min1_Code= "TO", t.Min1_pct, )
From DR_GE_WS_BOD As "t"
Where DR_GE_WS_BOD.Min1_Code= "TO"
   Or DR_GE_WS_BOD.Min2_Code= "TO"
   Or DR_GE_WS_BOD.Min3_Code= "TO"
   Or DR_GE_WS_BOD.Min4_Code= "TO"
   Or DR_GE_WS_BOD.Min5_Code= "TO" Or 
Into GE_MIN1_TO

Now, the first IIF() expression might be unnecessary as they all seem to return "TO", right?
Can one record have the code "TO" for multiple of the MinX_Code columns? If yes, then this might not work.

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data

Original Message:
Sent: 09-09-2021 06:21
From: Victor Kakebeeke
Subject: Merge multiple queies into one selection

Hi I wonder whether anyone has a quicker way of combing multiple queries into one query selection. In the example below I query 5 separate columns from a table producing a query selection for each, which I than have to append one by one to a table. Is there a way to end up with only one query selection?

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min1_Code, DR_GE_WS_BOD.Min1_pct
From DR_GE_WS_BOD
Where DR_GE_WS_BOD.Min1_Code= "TO"
Into GE_MIN1_TO_Q1

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min2_Code,DR_GE_WS_BOD.Min2_pct
From DR_GE_WS_BOD
Where DR_GE_WS_BOD.Min2_Code= "TO"
Into GE_MIN1_TO_Q2

Select DR_GE_WS_BOD.Hole_ID, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min3_Code, DR_GE_WS_BOD.Min3_pct
From DR_GE_WS_BOD
Where min3_code = "TO"
Into GE_MIN1_TO_Q3

Select hole_id, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min4_Code, DR_GE_WS_BOD.Min4_pct
From DR_GE_WS_BOD
Where min4_code = "TO"
Into GE_MIN1_TO_Q4

Select hole_id, DR_GE_WS_BOD.Depth_From, DR_GE_WS_BOD.Depth_To, DR_GE_WS_BOD.Min5_Code, DR_GE_WS_BOD.Min5_pct
From DR_GE_WS_BOD
Where min5_code = "TO"
Into GE_MIN1_TO_Q5

Cheers,
Victor

------------------------------
Victor Kakebeeke
Global Prospecting Limited
Lichfield
------------------------------