dreamquartz wrote:I am wondering if something like the following can be set up as a compound primary key with auto numbering.
Select
RIGHT (EXTRACT ( YEAR FROM CURDATE () ), 2) || RIGHT ("Service"."ServiceID", 2) AS "PK"
From "Service"
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
'' ||
-- 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
Sliderule wrote:
- 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.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
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
Sliderule wrote: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.
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
Sliderule wrote: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:Explanation 2:
- The first column returns your definition of a "ProjectNumber" as an integer, I strongly recommend against using this, as stated by Sliderule above.
- 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.
- 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.
Sliderule
- 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
).
Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.
dreamquartz wrote: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 wrote:dreamquartz wrote: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
- You asked: "what does it mean":
Answer: It means, . . . just as the error message clearly states: "Not in aggregate function or group by clause"- You asked: "and can I solve it.":
Answer: Yes.
dreamquartz wrote:Hi All,
Based on the database, I would like to know how to combine CASE, WHEN, ELSE and COUNT.
Dream.
Sliderule wrote:dreamquartz wrote: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.
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"
CASE
WHEN COUNT ( "FKServiceID" ) < 10
THEN '0' || COUNT ( "FKServiceID" )
ELSE '' || COUNT ( "FKServiceID" )
END
CASE
WHEN COUNT ( "FKServiceID" ) < 5
THEN 'A'
ELSE 'B'
END
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"
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"
Users browsing this forum: No registered users and 3 guests