[Solved] Impossible compound Primary Key?

[dreamquartz]

Hi All,

I am wondering if something like the following can be set up as a compound primary key with auto numbering.
1. The pk is 6 digits
2. First 2 digits are to be the last two digits of a year, like 13, 14, 15
3. Next two digits are to be category based, like 11, 56, 83
4. Last 2 digits are to be auto numbering, with the category, and the year in mind.

The moment a category is selected, in a certain year, with default of current year, the pk should automatically look for the latest entry and increment it by 1, store it as a next record and present it.

Criteria:
1. mixed table
2. auto numbering continued from last increment, per category
3. reset numbering at start of new year

Example pk structure, starting with last year (2013)
131101
135601
135602
138301
........
131186
131187
138309
141101
141102
141103
148301
141104
145601

I hope the structure is clear.

Dream

[DACM]

I am wondering if something like the following can be set up as a compound primary key with auto numbering.

No. Or code an appropriate trigger. But it won't be easy with your annual reset on the incremented values, per category.

But you can use SQL facilities to formulate something similar without procedural code:

1. Separate fields for YEAR, CATEGORY, and INCREMENT (plus a normal AutoValue primary key which is not part of the 6 digit-display scheme)
2. For YEAR (VARCHAR(2)) set to DEFAULT RIGHT(YEAR(CURRENT_TIMESTAMP),2)
3. For CATEGORY (VARCHAR(2)) use a List Box selection
4. Click a Push Button see the MAX INCREMENT (VARCHAR(2)) value for the selected category in a box, allowing the user to type-in or select the next value from a List Box manually. Or utilize cascading List Box filtering to determine the MAX value based on the selected CATEGORY while adding '1' such that the only selection is the next incremented-value.
5. Use SQL concatenation, case-when, and cast(VARCHAR) to display the "pk" structure as desired, complete with a leading zero in the INCREMENT value portion if necessary (01,02,...09, 10, 11,...).

[dreamquartz]

Hi DACM,

I figured the 1st 4 digits out somewhat.

Code: Select all   Expand viewCollapse view

Select
RIGHT (EXTRACT ( YEAR FROM CURDATE () ), 2) || RIGHT ("Service"."ServiceID", 2) AS "PK"
From "Service"

it is similar to parts of your answer.

I will pursue.

Dream

[DACM]

I was still editing above as I thought more about it.

But the idea is to save the YEAR as a field while setting it to DEFAULT to the current, 2-digit year -- per you're requirements.

You would then CONCAT the YEAR || CATEGORY || INCREMENT as "pk". But to be clear, this is not the Primary Key, which should be an AutoValue key behind the scenes (unrelated to this scheme). The concatenated value might be a calculated field (?), or part of a separate query/view.

And you would probably use a VARCHAR(2) field as the INCREMENT field, so you can let the user add the leading zero as necessary. Otherwise, you could use a 2-digit numerical field but the SQL to generate the Query/View would involve CAST(CASE WHEN... AS VARCHAR) to add the leading zero as necessary. No problem either way.

[dreamquartz]

Hi DACM,

Step further in the right direction.
Attached you will find the latest version of my attempts.
What I do not understand, that if I use the form frmServiceCounter, I can see the results, but they are not stored in the table tblServiceCounter.
Any idea what I am doing wrong?

Secondly,
When I created the Query, based on your comments, I used the CASE (...) argument, and was not paying close attention, so I created it the way you see below.
However there is something not making sense. At closer observation I noticed that I appeared to have done something with "ELSE"

Code: Select all   Expand viewCollapse view

SELECT
    CAST
       ( RIGHT
          ( EXTRACT
             ( YEAR FROM CURDATE ( )
             ), 2
          ) ||
          RIGHT ( "Service"."ServiceID", 2 ) ||
          ( CASE
             WHEN "ServiceCounter"."Counter" < 10
             THEN '0' || "ServiceCounter"."Counter"
             ELSE '' || "ServiceCounter"."Counter"
             END
          ) AS INTEGER
       )
AS
    "ProjectNumber",
    "ServiceCounterID"
FROM
    { OJ "Service"
    RIGHT OUTER JOIN "ServiceCounter" ON
    "Service"."ServiceID" = "ServiceCounter"."FKServiceID" }
ORDER BY
    "ProjectNumber" ASC

Looking at "ELSE", I wanted to remove

Code: Select all   Expand viewCollapse view

'' ||

but that gives an ERROR in CAST.

Any idea what is causing that?

Thirdly,
When I look at your SQL, it is nicely laid out, with indents for extra readability.
How is that accomplished?

Dream

Edit: Uploaded latest modified file

[Sliderule]

Code: Select all   Expand viewCollapse view

-- As requested by user dreamquartz at https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=69091
-- NOTE: this is NOT recommended by user Sliderule . . . use of CURRENT_DATE . . . since . . .
--       in the year 2018, for example, the first two digits will all be . . . 18

-- User dreamquartz said:
-- I am wondering if something like the following can be set up as a compound primary key with auto numbering.
--    1. The pk is 6 digits
--    2. First 2 digits are to be the last two digits of a year, like 13, 14, 15
--    3. Next two digits are to be category based, like 11, 56, 83
--    4. Last 2 digits are to be auto numbering, with the category, and the year in mind.

SELECT
   (MOD(YEAR(CURRENT_DATE), 100) * 10000) + (MOD("tblService"."ServiceID", 100) * 100) + "tblServiceCounter"."Counter" AS "ProjectNumber",
   "ServiceCounterID"

FROM { OJ "tblService" RIGHT OUTER JOIN "tblServiceCounter" ON "tblService"."ServiceID" = "tblServiceCounter"."FKServiceID" }

ORDER BY "ProjectNumber" ASC

Find as an attachment . . . one Base file, with the Query, and, a form using the SQL from the Query.

Company_Cascading_Address_5.odb

(47.48 KiB) Downloaded 59 times

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[dreamquartz]

Code: Select all   Expand viewCollapse view

-- As requested by user dreamquartz at https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=69091
-- NOTE: this is NOT recommended by user Sliderule . . . use of CURRENT_DATE . . . since . . .
--       in the year 2018, for example, the first two digits will all be . . . 18

-- User dreamquartz said:
-- I am wondering if something like the following can be set up as a compound primary key with auto numbering.
--    1. The pk is 6 digits
--    2. First 2 digits are to be the last two digits of a year, like 13, 14, 15
--    3. Next two digits are to be category based, like 11, 56, 83
--    4. Last 2 digits are to be auto numbering, with the category, and the year in mind.

SELECT
   (MOD(YEAR(CURRENT_DATE), 100) * 10000) + (MOD("tblService"."ServiceID", 100) * 100) + "tblServiceCounter"."Counter" AS "ProjectNumber",
   "ServiceCounterID"

FROM { OJ "tblService" RIGHT OUTER JOIN "tblServiceCounter" ON "tblService"."ServiceID" = "tblServiceCounter"."FKServiceID" }

ORDER BY "ProjectNumber" ASC

Find as an attachment . . . one Base file, with the Query, and, a form using the SQL from the Query.

Company_Cascading_Address_5.odb

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Hi Sliderule,

Thanks for your input.
The ProjectNumber is used for making sure that the information is kept together.
All the documents, related to a Project, are coded this way.
The client destroys all the Project related paperwork within a set time frame, which is shorter than 10 years.
The choice for 6 digits was made by the client, because there are stamps available with 6 numbers. The client did not want to go for an other type of coding.
The client has no more than 99 categories as Service available to the Client, and that Service will highly likely not be provide more than 99 times a year.

There is however a chance that the Client wants to increase the Counter from 2 to 3 digits to accommodate the amount of Services provided per year, and therefore the YEAR will change from 2 to 1 digit.

In the database, however, there will be the ProjectDate, and StartTime recorded, combined with the ProjectNumber.
It would be nice to have the ProjectNumber as the Primary Key, but as DACM already indicated that is not a Primary Key.
The ProjectNumber will therefore only be used to be shown on the screen, and related paperwork, like reports, if printed.

I hope this clarifies why I asked for this method.

Concerning "CURRENT_DATE".
Your comment, is that because it should not be used, and I should use something like "Current_TIMESTAMP", or was it related to what I was trying to accomplish?

I created the following, based on input from DACM,

Code: Select all   Expand viewCollapse view

SELECT
    CAST
       ( RIGHT
          ( EXTRACT
             ( YEAR FROM CURDATE ( )
             ), 2
          ) ||
          RIGHT ( "Service"."ServiceID", 2 ) ||
          ( CASE
             WHEN "ServiceCounter"."Counter" < 10
             THEN '0' || "ServiceCounter"."Counter"
             ELSE '' || "ServiceCounter"."Counter"
             END
          ) AS INTEGER
       )
AS
    "ProjectNumber",
    "ServiceCounterID"
FROM
    { OJ "Service"
    RIGHT OUTER JOIN "ServiceCounter" ON
    "Service"."ServiceID" = "ServiceCounter"."FKServiceID" }
ORDER BY
    "ProjectNumber" ASC

I will check out your proposal to see what works more efficient.

Dream.

[dreamquartz]

I was trying to solve an other problem, related to this topic, but that did not go the way I wanted https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=69233.
Villeroy was kind enough to prompt me into a direction, but after implementation, it turned out that the question I asked was stipulated wrong.

I am looking for a counter that auto increases when a Service is selected.
In the attached file, I created a form called: frmServiceCounter. In this form there is a column, called Counter, and when that is changed, after Refresh, the ProjectNumber will adjust.
The Service is a ListBox, and over the period of a calendar year the ProjectNumber must be generated.
What I am looking for, is a method of Selecting a Service, and after Refresh, the ProjectNumber is automatically updated.
In the background the Current_Time/Date Stamp will be recorded, but more importantly there is a need to record the, updated, counter.
If the counter is recorded, the ProjectNumber can be retraced, if so required.

There is a second question to this issue.
There is a need to reset the Counter back to "01" when the Calendar year has changed.

Any help will be much appreciated.

Dream.

[Sliderule]

As stated by user DACM above, create an INSERT Trigger ( and if necessary an UPDATE Trigger ) to define the database value.

Update the HSQL version, since you will have to / want to do this anyway, to an EXTERNAL ( compared to EMBEDDED ) database. As the date I am writing this . . . that would be HSQLDB.jar version 2.3.2 .

Additional note, comment: Returning the value and storing it as an INTEGER is NOT appropriate ( wrong ). It should, according to what you described, be defined as: CHAR(6) . Since the first character of your desired six characters . . . could /might / will start with a zero ( year 2000, or, year 2009, or, if only using the first character as a year (that is what you said might happen) the year 2010 or 2020 ) and when you define it as an INTEGER ( rather than text ) the first number will not display, NOR be stored.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this issue has been resolved.

[dreamquartz]

As stated by user DACM above, create an INSERT Trigger ( and if necessary an UPDATE Trigger ) to define the database value.

Update the HSQL version, since you will have to / want to do this anyway, to an EXTERNAL ( compared to EMBEDDED ) database. As the date I am writing this . . . that would be HSQLDB.jar version 2.3.2 .

Additional note, comment: Returning the value and storing it as an INTEGER is NOT appropriate ( wrong ). It should, according to what you described, be defined as: CHAR(6) . Since the first character of your desired six characters . . . could /might / will start with a zero ( year 2000, or, year 2009, or, if only using the first character as a year (that is what you said might happen) the year 2010 or 2020 ) and when you define it as an INTEGER ( rather than text ) the first number will not display, NOR be stored.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this issue has been resolved.

Hi Sliderule,

Thank you so much.

Did not think about the leading "ZERO" for the year. It will be an issue, and very important.
I will implement it.

I still hope to be able to use and show it, because the Client requires it.

You know, you have to be to spend so much time on a database development, but the Client allows it.
However the Client is King, even if I am Emperor.

Dream

[Sliderule]

I am not at all sure, what you are asking from me ( if anything ) . . . but . . . below is a Query:

Code: Select all   Expand viewCollapse view

SELECT
   (MOD(YEAR(CURRENT_DATE), 100) * 10000) + (MOD("tblService"."ServiceID", 100) * 100) + "tblServiceCounter"."Counter" AS "ProjectNumber",
   RIGHT(TO_CHAR( CURRENT_DATE,'YY'),2) || RIGHT('0' || "tblService"."ServiceID",2) || RIGHT('0' || "tblServiceCounter"."Counter",2) AS "ProjectNumberCHAR62",
   RIGHT(TO_CHAR( CURRENT_DATE,'YY'),1) || RIGHT('0' || "tblService"."ServiceID",2) || RIGHT('00' || "tblServiceCounter"."Counter",3) AS "ProjectNumberCHAR61",

   "tblServiceCounter"."ServiceCounterID"

FROM { OJ "tblService" RIGHT OUTER JOIN "tblServiceCounter" ON "tblService"."ServiceID" = "tblServiceCounter"."FKServiceID" }

ORDER BY "ProjectNumber" ASC

Explanation 1:

1. The first column returns your definition of a "ProjectNumber" as an integer, I strongly recommend against using this, as stated by Sliderule above.
2. The second column returns your definition of a "ProjectNumber62" as a CHAR(6) field, with the year as the first two characters, and, "tblServiceCounter"."Counter" as the last two characters.
3. The third column returns your definition of a "ProjectNumber61" as a CHAR(6) field, with the year as the first character, and, "tblServiceCounter"."Counter" as the last three characters.

Explanation 2:

• Just to reiterate, if it were me, I would create a table, and, yes, ProjectNumber can be a Primary Key . . . and . . . it can increase the last characters ( 2 or 3 ) by one, USING A TRIGGER . And, just to repeat, this canNOT be accomplished ( using a TRIGGER with HSQL Version 1.8.0.10 as an Embedded Database ) . . . but . . . it can be accomplished with HSQL Version 2.3.2 ( as of the date I am writing this, the most recent HSQL version . . . and . . . any other HSQL 2. version too. ).
  
  
• And, if you upgrade to HSQL Version 2.3.2 . . . you can create your own, user friendly, error messages, if using a FORM, it would be a pop-up . . . with the words you choose, when a user attempts to insert or update a value incorrectly. By storing the message in the database, rather than a form, whether the data is entered in the OpenOffice / LibreOffice Base TABLE listing, an OpenOffice / LibreOffice Base Form, or, from an application external to OpenOffice / LibreOffice Base . . . the error messages will be as YOU want to define it ( user friendly ).

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[dreamquartz]

I am not at all sure, what you are asking from me ( if anything ) . . . but . . . below is a Query:

Code: Select all   Expand viewCollapse view

SELECT
   (MOD(YEAR(CURRENT_DATE), 100) * 10000) + (MOD("tblService"."ServiceID", 100) * 100) + "tblServiceCounter"."Counter" AS "ProjectNumber",
   RIGHT(TO_CHAR( CURRENT_DATE,'YY'),2) || RIGHT('0' || "tblService"."ServiceID",2) || RIGHT('0' || "tblServiceCounter"."Counter",2) AS "ProjectNumberCHAR62",
   RIGHT(TO_CHAR( CURRENT_DATE,'YY'),1) || RIGHT('0' || "tblService"."ServiceID",2) || RIGHT('00' || "tblServiceCounter"."Counter",3) AS "ProjectNumberCHAR61",

   "tblServiceCounter"."ServiceCounterID"

FROM { OJ "tblService" RIGHT OUTER JOIN "tblServiceCounter" ON "tblService"."ServiceID" = "tblServiceCounter"."FKServiceID" }

ORDER BY "ProjectNumber" ASC

Explanation 1:

1. The first column returns your definition of a "ProjectNumber" as an integer, I strongly recommend against using this, as stated by Sliderule above.
2. The second column returns your definition of a "ProjectNumber62" as a CHAR(6) field, with the year as the first two characters, and, "tblServiceCounter"."Counter" as the last two characters.
3. The third column returns your definition of a "ProjectNumber61" as a CHAR(6) field, with the year as the first character, and, "tblServiceCounter"."Counter" as the last three characters.

Explanation 2:

• Just to reiterate, if it were me, I would create a table, and, yes, ProjectNumber can be a Primary Key . . . and . . . it can increase the last characters ( 2 or 3 ) by one, USING A TRIGGER . And, just to repeat, this canNOT be accomplished ( using a TRIGGER with HSQL Version 1.8.0.10 as an Embedded Database ) . . . but . . . it can be accomplished with HSQL Version 2.3.2 ( as of the date I am writing this, the most recent HSQL version . . . and . . . any other HSQL 2. version too. ).
  
  
• And, if you upgrade to HSQL Version 2.3.2 . . . you can create your own, user friendly, error messages, if using a FORM, it would be a pop-up . . . with the words you choose, when a user attempts to insert or update a value incorrectly. By storing the message in the database, rather than a form, whether the data is entered in the OpenOffice / LibreOffice Base TABLE listing, an OpenOffice / LibreOffice Base Form, or, from an application external to OpenOffice / LibreOffice Base . . . the error messages will be as YOU want to define it ( user friendly ).

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

Hi Sliderule,

Thank you so much.
It appears that I have to move away from the embedded HSQL to the latest version to make use of all the functions I am looking for.
I really would like to stay with the embedded version for the duration of the design process, because it is easier that way.
I will change to the latest version, when all the design issues are solved.
I am getting very close, so ......

Dream

[dreamquartz]

Hi All,

As Sliderule and DACM are working with me to try to solve an issue concerning numbering, it popped in my mind that I might be able to use the COUNT and GROUP functions for displaying the ProjectNumber.

I thought I had something, based on the input of DACM.
I tried to use the COUNT function in the tblServiceUsage.
This is based on the "Media_without_Macros" database, used in the Base Handbook.
The error I get is:

SQL Status: 37000
Error code: -67

Not in aggregate function or group by clause: org.hsqldb.Expression@1ea5b96 in statement [SELECT COUNT( "FKServiceID" ) AS "ServiceTotal", "FKServiceID" FROM "tblServiceUsage"]

Wherever I search, I cannot find an answer to the error.
What does it mean, and can I solve it?

Dream

[Sliderule]

Hi All,

As Sliderule and DACM are working with me to try to solve an issue concerning numbering, it popped in my mind that I might be able to use the COUNT and GROUP functions for displaying the ProjectNumber.

I thought I had something, based on the input of DACM.
I tried to use the COUNT function in the tblServiceUsage.
This is based on the "Media_without_Macros" database, used in the Base Handbook.
The error I get is:

SQL Status: 37000
Error code: -67

Not in aggregate function or group by clause: org.hsqldb.Expression@1ea5b96 in statement [SELECT COUNT( "FKServiceID" ) AS "ServiceTotal", "FKServiceID" FROM "tblServiceUsage"]

Wherever I search, I cannot find an answer to the error.
What does it mean, and can I solve it?

Dream

1. You asked: "what does it mean":
   
   Answer: It means, . . . just as the error message clearly states: "Not in aggregate function or group by clause"
   
   
2. You asked: "and can I solve it.":
   
   Answer: Yes.

[dreamquartz]

Hi All,

As Sliderule and DACM are working with me to try to solve an issue concerning numbering, it popped in my mind that I might be able to use the COUNT and GROUP functions for displaying the ProjectNumber.

I thought I had something, based on the input of DACM.
I tried to use the COUNT function in the tblServiceUsage.
This is based on the "Media_without_Macros" database, used in the Base Handbook.
The error I get is:

SQL Status: 37000
Error code: -67

Not in aggregate function or group by clause: org.hsqldb.Expression@1ea5b96 in statement [SELECT COUNT( "FKServiceID" ) AS "ServiceTotal", "FKServiceID" FROM "tblServiceUsage"]

Wherever I search, I cannot find an answer to the error.
What does it mean, and can I solve it?

Dream

1. You asked: "what does it mean":
   
   Answer: It means, . . . just as the error message clearly states: "Not in aggregate function or group by clause"
   
   
2. You asked: "and can I solve it.":
   
   Answer: Yes.

Hi Sliderule,

I should have to searched on the error, which I forgot.
Your comments put me on the right track.

Thanks,

Dream

[dreamquartz]

Hi All,

Based on the database, I would like to know how to combine CASE, WHEN, ELSE and COUNT.

Dream.

[Sliderule]

Hi All,

Based on the database, I would like to know how to combine CASE, WHEN, ELSE and COUNT.

Dream.

Very easily.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this issue has been resolved.

[dreamquartz]

Hi All,

Based on the database, I would like to know how to combine CASE, WHEN, ELSE and COUNT.

Dream.

Very easily.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this issue has been resolved.

Hi Sliderule,

Could have guessed your response.
Trying to follow posts like https://forum.openoffice.org/en/forum/viewtopic.php?t=40051.
The issue that I have, is that the condition where the CASE WHEN THEN ELSE is to be based upon, is also the one that needs to be used.
I am still dealing with the same issue where I need to be able to show the ProjectNumber, and DACM demonstrated the use of COUNT.
I am trying to combine the 2, because of the 6 digit requirement by the Client and the COUNT can be less than 10, or if the decision is made to move to 3 places, the COUNT can be upto 999.
The chance to that will be relatively simple, because the principle is the same.

My Code:

Code: Select all   Expand viewCollapse view

SELECT
    COUNT ( "FKServiceID" ),
    RIGHT ( "tblService"."ServiceID", 2 ),
    RIGHT ( EXTRACT ( YEAR FROM CURDATE ( ) ), 2 ) ||
       RIGHT ( "tblService"."ServiceID", 2 ) ||
       CASE
          WHEN COUNT ( "FKServiceID" ) < 10
          THEN '0' || COUNT ( "FKServiceID" )
          ELSE '' || COUNT ( "FKServiceID" )
       END
    AS "ServiceUsage",
    "FKServiceID"
FROM
    "tblService",
    "tblServiceUsage"
WHERE
    RIGHT ( EXTRACT ( YEAR FROM CURDATE ( ) ), 2 ) = RIGHT ( EXTRACT ( YEAR FROM "TimeStamp" ), 2 )
GROUP BY
"FKServiceID"

Gives me the error:
Not a condition in statement [SELECT COUNT( "FKServiceID" ), RIGHT( "tblService"."ServiceID", 2 ), RIGHT( EXTRACT( YEAR FROM CURDATE( ) ), 2 ) || RIGHT( "tblService"."ServiceID", 2 ) || CASE WHEN COUNT( "FKServiceID" ) < 10 THEN '0' || COUNT( "FKServiceID" ) ELSE '' || COUNT( "FKServiceID" ) END AS "ServiceUsage", "FKServiceID" FROM "tblService", "tblServiceUsage" WHERE RIGHT( EXTRACT( YEAR FROM CURDATE( ) ), 2 ) = RIGHT( EXTRACT( YEAR FROM "TimeStamp" ), 2 ) GROUP BY "FKServiceID"]

I have researched this, but could not find an answer.

What am I not doing correctly?

Dream

[dreamquartz]

Hello All,

Just an update on the previous post.
When looking at the section:

Code: Select all   Expand viewCollapse view

CASE
          WHEN COUNT ( "FKServiceID" ) < 10
          THEN '0' || COUNT ( "FKServiceID" )
          ELSE '' || COUNT ( "FKServiceID" )
       END

This particular section can be simplified, like:

Code: Select all   Expand viewCollapse view

CASE
          WHEN COUNT ( "FKServiceID" ) < 5
          THEN 'A'
          ELSE 'B'
       END

This 2nd one does work.
Why does 1st one not work, whilst the 2nd one does?

Can anyone point me in the right direction?

Dream

[eremmel]

DO NOT POST ISSUES TWICE AND ONE TOPIC PER THREAD.

Please tell us what is not working (I guess it is the COUNT(...) in the ELSE part: query error ).

A way to move forward when complex things are not working is breaking it into pieces; to do something like (but there is a much simples solution and that is already shown in the above code amples...):

Code: Select all   Expand viewCollapse view

select X.A,
CASE
   WHEN X.A < 10
   THEN X.B
   ELSE X.C
END as "Y"
from ( select count(*) as "A" , '00'||count(*) as "B", '0'||count(*) as "C"
   from "table" ) "X"

[dreamquartz]

Hi All,

I think I found a solution, thanks to MTP ( see: https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=69523#p310213).
MTP made me realize that I could remove 'COUNT' from the Condition.

Code: Select all   Expand viewCollapse view

SELECT
    CAST ( RIGHT( EXTRACT( YEAR FROM CURDATE( ) ), 2 ) ||
          RIGHT( "tblServiceUsage"."FKServiceID", 2 ) ||
          ( CASE
             WHEN COUNT( "FKServiceID" ) < 10
             THEN '0'
             ELSE ''
          END )
           || COUNT( "FKServiceID" )
    AS INTEGER )
    AS "Total",
    "FKServiceID"
FROM
    "tblServiceUsage"
WHERE
    RIGHT( EXTRACT( YEAR FROM CURDATE( ) ), 2 ) = RIGHT( EXTRACT( YEAR FROM "tblServiceUsage"."TimeStamp" ), 2 )
GROUP BY
    "FKServiceID"

Dream