Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have one error in Power BI "power bi ora-01722 invalid number" the query is SQL View, in my SQL I used the DECODE fonction it's possible this is the problem ?
The datatype seems be correct :
SERVICE_REQUEST_XM_ID VARCHAR2(15 CHAR)
SERVICE_REQUEST_REF_XM_ID VARCHAR2(15 CHAR)
Match Client 1 Mediator VARCHAR2(12 CHAR)
Match Client 2 Mediator VARCHAR2(17 CHAR)
Match Problem Mediator VARCHAR2(122 CHAR)
Match Product Mediator VARCHAR2(60 CHAR)
Match Nat Int Mediator VARCHAR2(8 CHAR)
Solved! Go to Solution.
Hello @Vanessa250919 ,
It is not a PowerBI generated error.
It is an oracle database error.
You executed a SQL statement that tried to convert a string to a number, but it was unsuccessful.
See link below:
https://www.techonthenet.com/oracle/errors/ora01722.php
Best to check your sql statement and the problem will be there.
Run the SQL statement in a db client to see if you will be able to spot it easier
Hello @Vanessa250919 ,
It is not a PowerBI generated error.
It is an oracle database error.
You executed a SQL statement that tried to convert a string to a number, but it was unsuccessful.
See link below:
https://www.techonthenet.com/oracle/errors/ora01722.php
Best to check your sql statement and the problem will be there.
Run the SQL statement in a db client to see if you will be able to spot it easier
Thanks for your tips, @themistoklis
This is the Query, you think it's about DECODE function or / used in text :
-- Unable to render VIEW DDL for object RSF_STAR.V_PBI_DIM_MEDIATOR with DBMS_METADATA attempting internal generator.
CREATE VIEW RSF_STAR.V_PBI_DIM_MEDIATOR
AS SELECT SERVICE_REQUEST_XM_ID,
SERVICE_REQUEST_REF_XM_ID,
DECODE(MEDIATOR_CLIENT_1, 10, 'expediteur', 20, 'destinataire', 30, 'un tiers', 40, 'le client', 50, 'le personnel', 60, 'autre') AS "Match Client 1 Mediator",
DECODE(MEDIATOR_CLIENT_2, 1, 'particulier', 2, 'affaires', 3, 'le service public', 4, 'association', 5, 'autre') AS "Match Client 2 Mediator",
DECODE(MEDIATOR_PROBLEM, 10, 'Retard', 11, 'Disparition totale / non reçu ', 12, 'Disparition partielle ', 13, 'Avarie (endommagement/ouvert) ', 14, 'Erreur de distribution (pas à la bonne adresse) ', 15, 'Mode de distribution/distribue dans la BAL (pour un rde) ', 16, 'Erreur de titre (pour un journal)', 17, 'Signature (pour un rde ou un colis)', 18, 'Pas de pub SVP', 19, 'Preferences du destinataire', 20, 'Changement (adresse/Conservation provisoire/Poste restante)', 21, 'Aucun avis de passage reçu', 22, 'Avisage d office', 23, 'Avis de passage mal complete (erreur de bureau, erreur date, illisible, incomplet, …) ', 24, 'Erreur d avisage du destinataire', 25, 'Renvoye à l expediteur', 26, 'Seconde presentation', 27, 'Carte avis de reception mal completee (signature, procuration, date,…) ', 28, 'Tracking', 29, 'Envoi refuse selon l operateur postal', 30, 'Indemnisation', 31, 'Prix/Tarifs ', 32, 'Frais/Coûts', 33, 'Facturation', 34, 'Lingusitique', 35, 'Secret des Lettres ', 36, 'Confidentialite/vie privee/RGDP ', 37, 'Recepisse de depôt', 38, 'Conditions Generales ', 39, 'Reglementation', 40, 'Identification de la BAL', 41, 'Non effectue', 42, 'Organisation (heure de passage tardif prevu, GeoRoute, rotation des agents)', 43, 'Greve interne', 44, 'Interruption de service', 45, 'SVP Facteur', 50, 'Facilites d acces/Accessibilite/Localisation/Fonctionnement 022/BP inaccessible/Contact gestionnaire responsable ', 51, 'Heures douverture ', 52, 'Delais/Attente au telephone, au guichet ', 53, 'Suppression (BAL/BP/PP)', 54, 'Accueil/Attitude/Gentillesse ', 55, 'Connaissance/Competence ', 56, 'Renvoye vers l expediteur (aucune enquete effectuee) ', 57, 'Traitement reclamation (pas traitee serieusement/reponse du SC dans l heure/plainte recurrente/Maniere dont bpost traite)', 58, 'Absence de reponse/Pas de rappel comme promis/St Nicolas ne repond pas ', 59, 'Information liee aux plaintes (reponses standardisees, lettres type, reponses inadequates, questionnaire de satisfaction) ', 60, 'Communication en generale/Lettre du CEO/spot TV/Action publicitaire (carte à gratter)', 61, 'Divers (materiel defectueux)', 70, 'Obliteration ', 71, 'Emissions/choix des sujets/types de timbres ', 72, 'Stocks/disponibilite', 73, 'Ventes (rachats perimes) ', 74, 'Abonnement philatelie (livraison : pas reçu, incomplet, inexact, …) ', 75, 'Eshop ', 80, 'Divers (validite timbres)', 90, 'Execution tardive/erronee/non execution ', 91, 'Paiement ', 92, 'Ouverture Compte ', 93, 'Procuration Financiere', 94, 'Fermeture Comtpe ', 95, 'Divers', 96, 'Virement ', 97, 'Rechargement', 98, 'Retrait ', 99, 'pas applicable') AS "Match Problem Mediator",
DECODE(MEDIATOR_PRODUCT, 100, 'Lettres', 120, 'Paquets', 121, 'Envois contre remboursement', 122, 'bpack Secur', 130, ' Mailing adresse', 140, 'Quotidiens', 150, 'Magazines', 160, 'Liasse First Service', 170, 'Imprimes electoraux adresse', 171, 'Imprimes electoraux non adresses', 172, 'Convocations electorales', 200, 'Collecte', 201, 'Triage/Centre de tri', 202, 'Distribution', 203, 'Reseau / points de contact', 204, 'Boîte aux lettres privee', 205, 'Dedouannement', 206, 'Changements d adresse/conservation provisoire/Poste restante', 207, 'Sacs de surcharge', 208, ' Cubee/distributeur auto. de paquets', 209, 'Systeme preference', 300, 'Distributeur', 302, 'Employe du guichet', 303, 'Responsable', 305, 'Service clients', 306, 'Site Web', 307, 'Services centraux', 400, 'Envois non adresses', 401, 'Procuration', 402, 'Codes postaux', 403, 'Boîtes postales', 404, 'Boites aux lettres rouges', 405, 'Recommande administratif', 406, 'Recommande commercial', 407, 'Valeurs declarees', 700, 'Valeurs postales', 701, 'Postogram', 702, ' Ma Carte Hallmark', 710, 'Philatelie', 720, 'Boîtes vendues par les operateurs postaux', 750, 'Franchise de port', 760, 'Machines à affranchir', 761, 'Coupon reponse international', 762, 'Port Paye', 770, 'Permis de peche', 801, 'recharges telephoniques', 802, 'Mobil Postcard', 810, 'Amendes polices', 900, 'Comptes 679', 901, 'Assignation', 903, 'Assignations de pension', 904, 'Versements pour compte de tiers', 905, 'Cheque circulaire postal', 906, ' Western Union', 907, 'bpaid', 908, 'Jetons de presence', 909, 'Retrait appareil self banking bpost', 910, 'Divers', 950, 'Comptes 000', 951, 'Comptes 299', 999, ' pas applicable') AS " Match Product Mediator ",
CASE
WHEN MEDIATOR_NAT_INT IN('be', 'B', 'BE', 'Be', 'b')
THEN 'Belgique'
WHEN MEDIATOR_NAT_INT IN('IN', 'In', 'in')
THEN 'Inbound'
WHEN MEDIATOR_NAT_INT IN('OUT', 'Out', 'out')
THEN 'Outbound'
END AS "Match Nat Int Mediator "
FROM RSF_STAR.DIM_SERVICE_REQUEST_MEDIATOR
I would suggest checking the fields first. Make sure there are all numeric because you use numeric values in the decode statement.
e.g.
MEDIATOR_CLIENT_1
MEDIATOR_PROBLEM
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |