[SQL] utilizzo di CASE in SELECT

[ProsoSQL]

Carissimi amici di hwupgrade, la premessa di essere un principiante in programmazione farà scappare molti e forse dopo aver letto quello che ho cercato di fare magari mi bannerete a vita. Senza alcuna conoscenza in programmazione mi sono barcamenato nella creazione di un database gestibile tramite l'applicativo a pagamento "Appmaker". Ho iniziato a fare progressi nella gestione dei miei dati sinchè erano semplici tabelle e campi non legati, ma quando ho iniziato a creare delle query allora sono iniziati i miei problemi.
Vengo al punto per il quale chiedo l'aiuto di voi esperti.
Il mio database costruito in Access contiene tre tabelle: 1) FoglioProduzione, 2) MasterTableEccipienti, 3)SubTableEccipienti. Per comodità mi riferirò alle singole tabelle con i numeri tra parentesi (1), (2), (3). (1) contiene: Key1 (chiave primaria-contatore), Numero (numerico), Prodotto (Testo). (2) contiene: Key2 (chiave primaria-contatore), AlphaID (Testo), Eccipiente (Testo), Value1 (numerico), Seleziona (Si/No). (3) contiene: Key1 e Key2 (chiavi primarie-numerico), check (Si/No), Value2 (Testo), Data2 (Testo).
Allora quello che voglio fare è avere un elenco di materie prime nella tabella (2) elencate nel campo "Eccipiente", indicare la quantità in magazzino e poi tramite la tabella (1) creare dei prodotti che utilizzano le materie prime e che automaticamente mi forniscono la quantità residua.
Ecco come ho fatto utilizzando delle stringhe sql nel programma in questione:

SELECT
Key2, Eccipiente, Ucase(Left(Eccipiente, 5)) AS VisibleCounter, Value1 AS TotalQuantity
FROM
MasterTableEccipienti

---in questo modo identifico le materie prime e la quantità totale---

Define Recordset rsMaster
Define Recordset rsSub
rsMaster.OpenReadOnly "SELECT * FROM MasterTableEccipienti"
rsSub.Open "SELECT * FROM SubTableEccipienti WHERE Key1={{Key1}}"
While rsMaster.EOF <> True Do
If rsSub.RecordCount > 0 Then
rsSub.MoveFirst
EndIf
rsSub.Find " Key2=" + rsMaster!Key2
If rsSub.EOF = True Then
rsSub.AddNew
rsSub!Key1 = {{Key1}}
rsSub!Key2 = rsMaster!Key2
rsSub.Update
EndIf
rsMaster.MoveNext
Done
rsMaster.Close
rsSub.Close
Return

--con questo comando linko key1 e key2 per i calcoli (mi sono fatto aiutare da un amico)--

SELECT Key1, Key2, Check1, Value2 AS QuantityUsed, Data2, '[[Key2]]' As QuantityRemaining FROM SubTableEccipienti WHERE Key1={Key1}

--in questo modo mi vado a leggere la quantità rimasta---

SELECT
MasterTableEccipienti.Eccipiente,
MasterTableEccipienti.Value1 AS TotalQuantity,
SUM(SubTableEccipienti.Value2) AS QuantityUsed,
TotalQuantity - QuantityUsed AS QuantityRemaining
FROM
MasterTableEccipienti
LEFT OUTER JOIN SubTableEccipienti ON SubTableEccipienti.Data2 =
MasterTableEccipienti.Eccipiente
GROUP BY
MasterTableEccipienti.Eccipiente,
MasterTableEccipienti.Value1

---così faccio i calcoli---

Ora il problema è arrivato quando le materie prime sono uguali ma appartengono, ad esempio, a lotti diversi. Nei calcoli non riesco a separare i diversi lotti o le diverse tipologie di materie prime. Allora ho cercato di inserire nel campo "Eccipiente" un nome univoco (tipo Mela - rossa e non solamente Mela) per poi separarlo con questa stringa sql:

Trim(Left(Eccipiente, InStr(1, Eccipiente, ' - '))) as Part1,
Trim(Right(Eccipiente, Len(Eccipiente) - InStr(1, Eccipiente, ' - ') - 1)) as Part2

Funziona, solamente che quando il nome non contiene il carattere ' - ' non riesco a separare i due campi. Ho pensato di usare CASE all'interno di SELECT in modo da dire: se c'è ' - ' allora segui Trim, ELSE leggi l'intero campo.
Non riesco a far funzionare CASE all'interno di SELECT. Ho fatto una prova del tipo:

SELECT Key2, CASE Eccipiente WHEN 'Ap' THEN Apple WHEN 'Or' THEN Orange ELSE 'Some Other Fruit' END AS 'New Value' FROM MasterTableEccipienti

Non funziona.
Mi rendo conto che non sono stato chiarissimo, tuttavia chiedo l'aiuto di un esperto di buona volontà. Magari mandando il mio file mdb ed utilizzando la versione shareware di Appmaker, qualche anima pia mi può dare una mano.
Grazie a tutti,
Paolo

[gugoXX]

Un paio di domande

Qual e' il Database?
Che errore ti da?
Qual e' il tipo dei vari campi coinvolti?

[ProsoSQL]

Caro gugoXX, se ti mandassi il database da testare con Appmaker? Il database è quello creato da Appmaker dopo aver fatto un merge con il mio database creato con Access. L'errore è quello generico di Appmaker quando c'è un errore nella scrittura del linguaggio sql (dal sito del produttore supportato dallo stesso software). I campi coinvolti sono di testo. Se nel campo "Eccipienti" ho vari prodotti tipo: Mele - rosse, Mele - gialle, Arance, Banane; allora devo essere in grado di poter separare Mele rosse in una sorta di Nome (rosse e gialle) dal Cognome (sempre Mele sono) o di vedere il solo Cognome (Arance). Non so se sono riuscito a spiegarmi.
Paolo

[ProsoSQL]

Allora..ho capito una cosa importantissima..in un database Access non posso usare la funzione CASE, in alternativa potrei usare IIF oppure Switch. Giusto??
Bene allora uso il mio database e se uso Switch, con un campo numerico di esempio ottengo:

SELECT Value1, Switch (Value1 <2, 1, Value1 > 1, 5) AS 'New Value' FROM MasterTableEccipienti


Se il valore nel campo Value1 è minore di 2 allora in un nuovo campo denominato "New Value" ottengo 1, se invece il valore è maggiore di 1 ottengo 5.
Bene sin qui funziona.
Adesso vorrei spostarmi sull'esempio che vi avevo riportato con un campo alphanumerico. Questa era la funzione che volevo, erroneamente, usare con CASE:

SELECT Key2, CASE Eccipiente WHEN 'Ap' THEN Apple WHEN 'Or' THEN Orange ELSE 'Some Other Fruit' END AS 'New Value' FROM MasterTableEccipienti

Key2 è la mia chiave primaria (per adesso non serve), Eccipiente è il mio campo alphanumerico. In questo campo ci possono essere dati del tipo:

Mela - verde; Mela - gialla; Limone.

Se uso la funzione:

Trim(Left(Eccipiente, InStr(1, Eccipiente, ' - '))) as Part1,
Trim(Right(Eccipiente, Len(Eccipiente) - InStr(1, Eccipiente, ' - ') - 1)) as Part2
riesco a separare Mela da verde e Mela da gialla nei due nuovi campi Part1 e Part2. Tuttavia non riesco a separare Limone da "vuoto" ovvero Part1 con la scritta Limone da Part2 con un campo vuoto.

Suggerimenti??
Vi prego datemi una mano. Forse usare in combinazione Trim con Switch può risolvermi il problema??
Grazie

[gugoXX]

No mi spiace, non ho AppMaker.
E non uso Access da tanto tempo. Ci sono svariati altri database piu' standard, piu' efficienti e altrettanto free to use.
A partire da SqlServer e Oracle stessi.

[ProsoSQL]

Una mano o un suggerimento su come usare Trim con Switch (vedi messaggo precedente) con un database access (putroppo non posso cambiare database)?

Paolo

[ProsoSQL]

Se provo a convertire il mio database access in mysql? Forse usando MS Access to MySQL? Cerco di trovare una soluzione da solo ma non ci riesco. Mi date una mano??
Grazie,
Paolo

[gugoXX]

pena x pena
Io userei IIF

IFF(a=b, "pippo", IIF(c=d, "Pluto", IIF(.....)))

Se invece decidessi di cambiare database, oltre Access, anche MySql non e' dei piu' consigliabili.

[ProsoSQL]

Grazie gugoXX,
stò provando ad usare il tuo suggerimento con iif. Ma come faccio a combinarlo con trim?
Come ho scritto ieri, con trim sono riuscito a separare, in due nuovi due campi, il nome composto agendo sul carattere ' - ' che separa le due parole e che trim mi riesce ad individuare. Trim però mi dè i due campi vuoti quando nel campo principale c'è un nome singolo. Come posso dunque usare anche iif nel caso in cui non è presente il carattere separatore?

p.s. ho provato con mysql ma la mia ignoranza ha spaventato anche me e sono caduto in una profonda depressione

[ProsoSQL]

Posso usare con iif caratteri che mi indicano presente o assente? Posso usare = < >, ma come faccio a dire: diverso? Inoltre come faccio a dire: è presente questa stringa all'interno del nome?
Forse combinando iif con ifnull??

[ProsoSQL]

solo adesso ho capito che la frase "pena x pena" si riferiva a me e non al tipo di database usato.
Scusate se ho cercato aiuto, credevo che anche un newbie come me avrebbe potuto contare sull'aiuto dei più esperti.
Grazie comunque,
Paolo

[gugoXX]

Originariamente inviato da ProsoSQL

solo adesso ho capito che la frase "pena x pena" si riferiva a me e non al tipo di database usato.
Scusate se ho cercato aiuto, credevo che anche un newbie come me avrebbe potuto contare sull'aiuto dei più esperti.
Grazie comunque,
Paolo

Era per il database.
Il fatto che tu non abbia avuto piu' aiuto da me e' che mi spiace, ma non posso proprio aiutarti con Access.

[ProsoSQL]

Caro gugoXX, grazie per i tuoi consigli. Vorrei davvero passare ad un diverso database ma non saprei come cominciare.
Comunque, ho risolto il mio problema (anche se in modo un pò naif).
Magari può servire come spunto per future discussioni per cui posto lo script che mi permette di risolvere il problema dei doppi nomi (vedi nei post precedenti):


SELECT Eccipiente, VisibleCounter, TotalQuantity, Part1, Part3 AS Part2 FROM (SELECT
Key2, Eccipiente, Eccipiente + ' - ' AS Eccipiente2, Ucase(Left(Eccipiente2, 5)) AS VisibleCounter, Value1 AS TotalQuantity,
Trim(Left(Eccipiente2, InStr(1, Eccipiente2, ' - '))) as Part1,
Trim(Right(Eccipiente2, Len(Eccipiente2) - InStr(1, Eccipiente2, ' - ') - 1)) as Part2, Trim(Left(Part2, Len(Part2) - InStr(1, Part2, ' - ') - 1)) as Part3
FROM
MasterTableEccipienti)

Ho risolto inserendo il valore ' - ' in Eccipiente AS Eccipiente2, poi i due Trim li ho fatti puntare su Eccipiente2 ed infine ho inserito un altro Trim (Part3) per avere un campo pulito. In fine, per avere tutto più chiaro ho fatto un select parziale del select che puntava al database.

Grazie ancora a tutti ,
Paolo