[semi ot - SQL] MSSMS ordinare un campo alfanumerico come fosse numerico

Post by Ammammata
ieri ho chiesto su microsoft.public.it.office.access , ma di là non c'è
tutta la competenza che trovo qui :-) ergi ri-posto, con minime
correzioni per chiarire meglio la cosa
Select c.docnum, b.numatcard, b.U_Man_date_inv, 'txt' Testo
into #tmp
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
Select distinct
STUFF((Select distinct ' ' + CHAR(10) +
cast(cast(substring(T1.numatcard,1,6) as int) as varchar(100)) + ' dd.
' + format(t1.u_man_date_inv, 'dd.MM.yyyy')
from #tmp T1
where T1.Testo = T2.Testo And t1.docnum = t2.docnum
For XML PATH('')),1,3,'')
from #tmp t2
drop table #tmp
in pratica legge ls testata di tutte le bolle associate a una fattura
(300100), recupera il numero di riferimento (p.e. 000008/2024/V2 oppure
000193/2024/V2) e lo mette in una tabella temporanea, poi legge la
tabella temporanea e mette tutti i numeri riferimento opportunamente
rielaborati in fila, con la data a seguito
148 dd. 21.02.2024
17 dd. 15.01.2024
175 dd. 29.02.2024
176 dd. 29.02.2024
177 dd. 29.02.2024
193 dd. 05.03.2024
228 dd. 15.03.2024
266 dd. 26.03.2024
39 dd. 23.01.2024
40 dd. 23.01.2024
46 dd. 24.01.2024
47 dd. 24.01.2024
8 dd. 10.01.2024
è evidente che i numeri di riferimento sono ordinati "alfabeticamente"
e questo non piace
ho letto qua e là sul web, ci sono millanta soluzioni, ce ne fosse una
che ha funzionato :-(
nota bene (ho scoperto cosa non funziona, adesso cerco una
"order by" non me lo accetta nella seconda parte, quella che legge la
tabella temporanea, quindi ho provato a metterlo nella prima, quella
che legge le bolle, ma non funziona, vedi qui sotto le righe con
177-175-176-148
Select c.docnum, cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv, 'txt' Testo
...
e finisce con
order by cast(substring(b.numatcard,1,6) as int)
docnum NumAtCard U_Man_date_inv Testo
300100 8 2024-01-10 00:00:00.000 txt
300100 17 2024-01-15 00:00:00.000 txt
300100 39 2024-01-23 00:00:00.000 txt
300100 47 2024-01-24 00:00:00.000 txt
300100 40 2024-01-23 00:00:00.000 txt
300100 46 2024-01-24 00:00:00.000 txt
300100 177 2024-02-29 00:00:00.000 txt
300100 175 2024-02-29 00:00:00.000 txt
300100 176 2024-02-29 00:00:00.000 txt
300100 176 2024-02-29 00:00:00.000 txt
300100 148 2024-02-21 00:00:00.000 txt
300100 193 2024-03-05 00:00:00.000 txt
300100 228 2024-03-15 00:00:00.000 txt
300100 232 NULL txt
300100 266 2024-03-26 00:00:00.000 txt
Select b.numatcard
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by b.numatcard
ottengo
numatcard
000008/2024/V2
000017/2024/V2
000039/2024/V2
000040/2024/V2
000046/2024/V2
000047/2024/V2
000148/2024/V2
000175/2024/V2
000176/2024/V2
000176/2024/V2
000177/2024/V2
000193/2024/V2
000228/2024/V2
000232/2024/V2
000266/2024/V2
ora si tratta di capire come fargli creare la tabella temporanea in
modo che sia ordinata in partenza...
oppure una alternativa a tutto questo ambaradan per avere la lista che
mi serve
ringrazio in anticipo :-)

Per ordinare correttamente i numeri di riferimento nella tua query SQL,
puoi modificare la prima parte della query che legge le bolle e crea la
tabella temporanea.

Puoi aggiungere l'ordinamento direttamente durante l'inserimento dei dati
nella tabella temporanea. La query modificata dovrebbe essere simile a
questa:

```sql
Select c.docnum, cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv, 'txt' Testo
into #tmp
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by cast(substring(b.numatcard,1,6) as int)
```

In questo modo, i dati vengono inseriti nella tabella temporanea in ordine
crescente in base ai primi sei caratteri del campo `numatcard`. La tabella
temporanea `#tmp` conterra' i dati ordinati, e la seconda parte della query
dovrebbe funzionare correttamente, dando come risultato la lista ordinata
di numeri di riferimento e date associate.

Se desideri una soluzione alternativa alla tabella temporanea, puoi
utilizzare una subquery per ordinare i dati direttamente nella clausola
`SELECT` della seconda parte della query. La query modificata sara' simile
a questa:

```sql
Select distinct
STUFF((Select distinct ' ' + CHAR(10) +
cast(cast(substring(T1.numatcard,1,6) as int) as varchar(100)) + ' dd.
' + format(t1.u_man_date_inv, 'dd.MM.yyyy')
from (
Select c.docnum, cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv, 'txt' Testo
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by NumAtCard
) T1
where T1.Testo = T2.Testo And t1.docnum = t2.docnum
For XML PATH('')),1,3,'')
from (
Select c.docnum, cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv, 'txt' Testo
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by NumAtCard
) T2
```

In questo modo, la subquery legge i dati dalla tabella `inv1`, `odln`, e
`OINV` e li ordina in base ai primi sei caratteri del campo `numatcard`. I
dati ordinati vengono quindi utilizzati nella seconda parte della query per
generare la lista desiderata.

Ammammata

2024-04-19 09:26:51 UTC

Post by Ghost
Per ordinare correttamente i numeri di riferimento nella tua query SQL,
puoi modificare la prima parte della query che legge le bolle e crea la
tabella temporanea.
Puoi aggiungere l'ordinamento direttamente durante l'inserimento dei dati
nella tabella temporanea. La query modificata dovrebbe essere simile a
```sql
Select c.docnum, cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv, 'txt' Testo
into #tmp
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by cast(substring(b.numatcard,1,6) as int)
```
In questo modo, i dati vengono inseriti nella tabella temporanea in ordine
crescente in base ai primi sei caratteri del campo `numatcard`. La tabella
temporanea `#tmp` conterra' i dati ordinati, e la seconda parte della query
dovrebbe funzionare correttamente, dando come risultato la lista ordinata
di numeri di riferimento e date associate.

ciao

comincio da qui, ti dico subito che NON funziona :( e lo avevo già
scritto il motivo

Select c.docnum, cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv, 'txt' Testo
into #tmp
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by cast(substring(b.numatcard,1,6) as int)

select * from #tmp
drop table #tmp

risultato:

docnum NumAtCard U_Man_date_inv Testo
300100 8 2024-01-10 00:00:00.000 txt
300100 17 2024-01-15 00:00:00.000 txt
300100 39 2024-01-23 00:00:00.000 txt
300100 47 2024-01-24 00:00:00.000 txt
300100 40 2024-01-23 00:00:00.000 txt
300100 46 2024-01-24 00:00:00.000 txt
300100 177 2024-02-29 00:00:00.000 txt
300100 175 2024-02-29 00:00:00.000 txt
300100 176 2024-02-29 00:00:00.000 txt
300100 176 2024-02-29 00:00:00.000 txt
300100 148 2024-02-21 00:00:00.000 txt
300100 193 2024-03-05 00:00:00.000 txt
300100 228 2024-03-15 00:00:00.000 txt
300100 232 NULL txt
300100 266 2024-03-26 00:00:00.000 txt

invece se *NON CREO* la tabella temporanea... funziona:

Select c.docnum, cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv, 'txt' Testo
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by cast(substring(b.numatcard,1,6) as int)

e ottengo

docnum NumAtCard U_Man_date_inv Testo
300100 8 2024-01-10 00:00:00.000 txt
300100 17 2024-01-15 00:00:00.000 txt
300100 39 2024-01-23 00:00:00.000 txt
300100 40 2024-01-23 00:00:00.000 txt
300100 46 2024-01-24 00:00:00.000 txt
300100 47 2024-01-24 00:00:00.000 txt
300100 148 2024-02-21 00:00:00.000 txt
300100 175 2024-02-29 00:00:00.000 txt
300100 176 2024-02-29 00:00:00.000 txt
300100 176 2024-02-29 00:00:00.000 txt
300100 177 2024-02-29 00:00:00.000 txt
300100 193 2024-03-05 00:00:00.000 txt
300100 228 2024-03-15 00:00:00.000 txt
300100 232 NULL txt
300100 266 2024-03-26 00:00:00.000 txt

ho poi guardato la seconda parte della tua risposta

Se desideri una soluzione alternativa alla tabella temporanea, puoi
utilizzare una subquery per ordinare i dati direttamente nella clausola
`SELECT` della seconda parte della query.

ma esce un errore legato all'ORDER BY :(

Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived
tables, subqueries, and common table expressions, unless TOP, OFFSET or
FOR XML is also specified.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'order'.

ho provato a mettere un "top 1000" nei tre punti della query dove c'è
un select, ma restituisce sempre un errore, anche se cambia di volta in
volta

grazie per la risposta, mi sa che devo cercare una ulteriore
alternativa

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

Ghost

2024-04-19 13:08:19 UTC

Post by Ghost
Per ordinare correttamente i numeri di riferimento nella tua query
SQL, puoi modificare la prima parte della query che legge le bolle e
crea la tabella temporanea.
Puoi aggiungere l'ordinamento direttamente durante l'inserimento dei
dati nella tabella temporanea. La query modificata dovrebbe essere
```sql
Select c.docnum, cast(substring(b.numatcard,1,6) as int) as
NumAtCard, b.U_Man_date_inv, 'txt' Testo
into #tmp
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by cast(substring(b.numatcard,1,6) as int)
```
In questo modo, i dati vengono inseriti nella tabella temporanea in
ordine crescente in base ai primi sei caratteri del campo
`numatcard`. La tabella temporanea `#tmp` conterra' i dati ordinati,
e la seconda parte della query dovrebbe funzionare correttamente,
dando come risultato la lista ordinata di numeri di riferimento e
date associate.

Hmm ... daro' un'occhiata ... nel frattempo prova con una Utilizzando una
sottoquery, dovresti ottenere direttamente i risultati senza la necessita'
di creare una tabella temporanea, esempio:

language-sql
Copy code
Select c.docnum, cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv, 'txt' Testo
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by cast(substring(b.numatcard,1,6) as int)

Post by Ammammata
ho poi guardato la seconda parte della tua risposta
Se desideri una soluzione alternativa alla tabella temporanea, puoi
utilizzare una subquery per ordinare i dati direttamente nella
clausola `SELECT` della seconda parte della query.
ma esce un errore legato all'ORDER BY :(
Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived
tables, subqueries, and common table expressions, unless TOP, OFFSET
or FOR XML is also specified.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'order'.
ho provato a mettere un "top 1000"

Esempio, cosi'?

SELECT *
FROM (
SELECT TOP 1000 column1, column2
FROM your_table
ORDER BY column1
) AS subquery_alias

... prova con PERCENT, esempio:

ELECT *
FROM (
SELECT TOP 100 PERCENT column1, column2
FROM your_table
ORDER BY column1
) AS subquery_alias

Post by Ammammata
nei tre punti della query dove c'è
un select, ma restituisce sempre un errore, anche se cambia di volta
in volta
grazie per la risposta, mi sa che devo cercare una ulteriore
alternativa

Se ottieni posta qui, nel frattempo faro' dei test

Ammammata

2024-04-22 11:01:57 UTC

Post by Ghost

Post by Ammammata
grazie per la risposta, mi sa che devo cercare una ulteriore
alternativa

Se ottieni posta qui, nel frattempo faro' dei test

per adesso nulla
ho provato a leggere #tmp, ordinandola e creando #tmp1 e poi concludere
rileggendo #tmp1 ma non se ne viene a capo:

148 / 17 / 175 / 176 / 177 / 193 / 228 / 232 / 266 / 39 / 40 / 46 / 47
/ 8

il limite, per quanto scoperto fino a oggi, è che ORDER BY non funziona
con SELECT ... INTO ...

poi ho provato anche questa, invertendo l'ordine di uno dei tuoi
suggerimenti, ma parimenti senza esito positivo:

select *
into #tmp
from (
Select top 1000 c.docnum, b.numatcard, b.U_Man_date_inv, 'txt' Testo
from inv1 a
inner join odln b On a.U_I_BaseEntry = b.docentry
inner join OINV c On c.DocEntry = a.DocEntry
where c.docnum = '300100'
order by cast(substring(b.numatcard,1,6) as int)
) t

Select distinct
STUFF((Select distinct ' / ' + cast(cast(substring(T1.numatcard,1,6) as
int) as varchar(100)) -- + ' dd. ' + format(t1.u_man_date_inv,
'dd.MM.yyyy')
from #tmp T1
where T1.Testo = T2.Testo And t1.docnum = t2.docnum
For XML PATH('')),1,3,'')
from #tmp t2

drop table #tmp

il TOP 1000 mi accetta l'ORDER BY ma il rislultato non cambia :-(

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

Ammammata

2024-04-22 11:12:11 UTC

il risultato

mi domando a questo punto se non ci sia un metodo alternativo per la
seconda parte, quella che mi crea con STUFF una stringa contenente i
dati

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

Ghost

2024-04-23 21:01:08 UTC

il risultato

mi domando a questo punto se non ci sia un metodo alternativo per la
seconda parte, quella che mi crea con STUFF una stringa contenente i
dati

Daro' un'occhiata ...

Ghost

2024-04-28 22:03:47 UTC

Post by Ghost

il risultato

mi domando a questo punto se non ci sia un metodo alternativo per la
seconda parte, quella che mi crea con STUFF una stringa contenente i
dati

Daro' un'occhiata ...

Ho dato un'occhiata al prob ma non ho avuto modo di provare.

Pare che il problema principale sia legato all'utilizzo dell'istruzione
ORDER BY all'interno della query che crea la tabella temporanea #tmp , come
hai notato SQL Server non consente di utilizzare ORDER BY in questo
contesto.

Pero' c'e' una soluzione alternativa, da provare, che dovrebbe funzionare
per ordinare correttamente i dati prima di creare la stringa concatenata
con STUFF.

Quindi:

1) crea una vista o una tabella derivata ordinata contenente i dati
desiderati:

'''sql
WITH CTE AS (
SELECT c.docnum,
cast(substring(b.numatcard,1,6) as int) as NumAtCard,
b.U_Man_date_inv,
'txt' Testo
FROM inv1 a
INNER JOIN odln b ON a.U_I_BaseEntry = b.docentry
INNER JOIN OINV c ON c.DocEntry = a.DocEntry
WHERE c.docnum = '300100'
ORDER BY cast(substring(b.numatcard,1,6) as int)
)
```

2) quindi, utilizza questa vista o tabella derivata ordinata nella query
principale:

'''sql
SELECT DISTINCT
STUFF((
SELECT DISTINCT ' ' + CHAR(10) +
cast(T1.NumAtCard as varchar(100)) + ' dd. ' +
format(T1.U_Man_date_inv, 'dd.MM.yyyy')
FROM CTE T1
WHERE T1.Testo = T2.Testo AND T1.docnum = T2.docnum
FOR XML PATH('')
), 1, 3, '')
FROM CTE T2;
```

Questa soluzione si sta utilizzando una CTE (Common Table Expression) o una
vista derivata per ordinare i dati in base al campo NumAtCard e
successivamente la query principale utilizza questa vista ordinata per
generare la stringa concatenata desiderata utilizzando STUFF e FOR XML
PATH.

Questa tecnica dovrebbe consentire di ottenere i numeri di riferimento
ordinati correttamente nella stringa finale, si spera.

Se invece preferisci non utilizzare una CTE puoi anche creare una vista
temporanea ordinata e utilizzarla nella query principale.

Prova e fai sapere, in caso contrario forniscimi ulteriori dettagli che,
senza promesse e appena posso :-), daro' l'ennesima occhiata al code.

Ghost

2024-04-18 14:07:49 UTC

OVVERO:

Per ordinare correttamente i numeri di riferimento nella tabella
temporanea, e' possibile utilizzare la funzione ORDER BY nella query che
popola la tabella temporanea. Tuttavia, poiché la query originale non
accetta l'uso di ORDER BY nella seconda parte, è necessario apportare
alcune modifiche.

language-sql
Copy code
SELECT c.docnum, CAST(SUBSTRING(b.numatcard, 1, 6) AS INT) AS NumAtCard,
b.U_Man_date_inv, 'txt' AS Testo
INTO #tmp
FROM inv1 a
INNER JOIN odln b ON a.U_I_BaseEntry = b.docentry
INNER JOIN OINV c ON c.DocEntry = a.DocEntry
WHERE c.docnum = '300100'
ORDER BY CAST(SUBSTRING(b.numatcard, 1, 6) AS INT)

SELECT DISTINCT
STUFF((SELECT DISTINCT ' ' + CHAR(10) +
CAST(CAST(SUBSTRING(T1.numatcard, 1, 6) AS INT) AS VARCHAR(100)) + '
dd. ' + FORMAT(t1.u_man_date_inv, 'dd.MM.yyyy')
FROM #tmp T1
WHERE T1.Testo = T2.Testo AND t1.docnum = t2.docnum
FOR XML PATH('')), 1, 3, '')
FROM #tmp t2

DROP TABLE #tmp

In questa soluzione avendo spostato la clausola ORDER BY nella prima parte
della query, che popola la tabella temporanea #tmp. In questo modo, i
numeri di riferimento saranno ordinati correttamente all'interno della
tabella temporanea.

CMQ ricorda di eseguire la query completa per creare la tabella temporanea
e ottenere la lista ordinata dei numeri di riferimento.

Ammammata

2024-04-19 09:29:05 UTC

Post by Ghost
In questa soluzione avendo spostato la clausola ORDER BY nella prima parte
della query, che popola la tabella temporanea #tmp. In questo modo, i
numeri di riferimento saranno ordinati correttamente all'interno della
tabella temporanea.

(15 rows affected)
Msg 8116, Level 16, State 1, Line 10
Argument data type int is invalid for argument 1 of substring function.

Completion time: 2024-04-19T11:27:41.0145049+02:00

più tardi cerco di capire se quello che ho copia/incollato è tutto
giusto, MSSMS non segnala errori di sintassi

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

Ghost

2024-04-19 13:08:20 UTC

Post by Ghost
In questa soluzione avendo spostato la clausola ORDER BY nella prima
parte della query, che popola la tabella temporanea #tmp. In questo
modo, i numeri di riferimento saranno ordinati correttamente
all'interno della tabella temporanea.

(15 rows affected)
Msg 8116, Level 16, State 1, Line 10
Argument data type int is invalid for argument 1 of substring
function.
Completion time: 2024-04-19T11:27:41.0145049+02:00
più tardi cerco di capire se quello che ho copia/incollato è tutto
giusto, MSSMS non segnala errori di sintassi

ok ...

Continua a leggere su narkive:

Discussioni interessanti ma non correlate

Come chiamo questo connettore filo-scheda?

iniziato 2018-11-10 05:10:30 UTC

Fornitura di riscaldatori per tubi a vuoto, AC o DC?

iniziato 2013-06-20 19:40:48 UTC

Qualsiasi svantaggio nell'usare una pompa di carica per un'alimentazione negativa dell'amplificatore operazionale

iniziato 2016-01-08 22:37:54 UTC

Perché il riposizionamento di questo resistore non influisce sulla resistenza totale del circuito?

iniziato 2013-03-25 11:41:28 UTC