SQLite Forum

I lost my password !
Login

I lost my password !

(1) By anonymous on 2021-02-23 11:38:48 [link] [source]

Hi, I have lost my password and it seems there is no solution to receive a new one into my E-mail address ? I Don't want to stay Anonymous ! Thank you for your help. J'ai une question que je pose en français: dans une requête select je construis la donnée suivante: (CASE WHEN activity_value = 0 then 0 else ((activity_cost * 1.0000) / activity_value) END) AS activity_unit_cost . Elle me donne de bonnes lignes de résultats par exemple: 183.1808569 ou 4395.1234567 Ce sont des données numériques mais il semble que SQLite les considère de par la formule d'obtention comme de l'aphanumérique. Queqlqu'un peut-il me dire comment faire pour obtenir plutôt un séparateur virgule plutôt qu'un point, et de faire en sorte que ces données exportées sur excel soient immédiatement reconnues comme numériques. J'ai essayé REPLACE pour changer . en , j'ai aussi essayé CAST pour transformer en numérique mais au final je n'arrive pas à obtenir satisfaction. Je dois mal m'y prendre. Merci pour votre aide si possible sur les deux aspects mot de passe et question posée. Cordialement Didier Riche

(2) By Ryan Smith (cuz) on 2021-02-23 14:54:40 in reply to 1 [link] [source]

I don't have a thought on the Password issue, but thought I would format the rest of your question a bit and translate it so that others might assist:

(Please forgive my broken French)

In a select query which I construct as follows:

 (CASE
   WHEN activity_value = 0 then 0 
   ELSE ((activity_cost * 1.0000) / activity_value)
  END) AS activity_unit_cost

It gives me good result rows, for example: 183.1808569 or 4395.1234567

These are numeric, but SQLite seems to consider them as alphanumeric due to the formula.
Could someone tell me how to obtain a comma separator instead of a dot, and get that data seen as digital numeric when exported to excel.

I tried REPLACE to change . into ,
I've also tried CAST to transform it into digital, but in the end I could not get a satisfactory result. I must be doing it wrong.

Thanks for your possible help on both the password and [SQL] question aspects.

Sincerely,
Didier Riche

(3) By Ryan Smith (cuz) on 2021-02-23 15:20:31 in reply to 1 [source]

About the SQL question:

SQLite thinks that your decimal separator is a decimal point, which holds with the SQL standard I think (not entirely sure), and Excel thinks that your regional settings dictate that a comma is to be used as the real decimal separator.

This is a setting found within your operating system's Regional settings (Windows I surmise).

This means that if you cannot fix the Regional settings (because in your Country a comma might actually be the legitimate decimal separator), then there is nothing really to do except to try what you already did: Forcibly replace the decimal point with a comma.

REPLACE operates on strings though, so perhaps this might work better:

 (CASE
   WHEN activity_value = 0 then '0' 
   ELSE REPLACE(
          CAST(
            (activity_cost / (activity_value * 1.0)) AS TEXT
          ), '.', ','
        )
  END) AS activity_unit_cost

Best of luck!

(4) By Ryan Smith (cuz) on 2021-02-23 15:28:55 in reply to 3 [link] [source]

Actually, having tried this, for me the REPLACE works just fine on the numeric result too:

WITH A(activity_value,activity_cost) AS (
  SELECT 2,4
  UNION ALL
  SELECT activity_value*2, activity_cost*2.5 FROM A WHERE activity_value < 100
)

SELECT activity_value, activity_cost, 
       (CASE
         WHEN activity_value = 0 then '0'
         ELSE REPLACE(activity_cost / (activity_value * 1.0), '.', ',')
        END) AS activity_unit_cost
  FROM A


  -- activity_value|activity_cost|activity_unit_cost
  -- --------------|-------------|------------------
  --        2      |            4|               2,0
  --        4      |         10.0|               2,5
  --        8      |         25.0|             3,125
  --       16      |         62.5|           3,90625
  --       32      |       156.25|         4,8828125
  --       64      |      390.625|       6,103515625
  --       128     |     976.5625|     7,62939453125