cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vanessa250919
Helper II
Helper II

Error power bi ora-01722 invalid numbe

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)

1 ACCEPTED SOLUTION
themistoklis
Super User II
Super User II

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

View solution in original post

3 REPLIES 3
themistoklis
Super User II
Super User II

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

View solution in original post

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

@Vanessa250919 

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.