Android Question change column sql

harinder

Active Member
Licensed User
Longtime User
I want the correct syntax for changing column name of a database. Following not working where I want to change names of 2 columns, one from old to new, other from good to bad..thnx

B4X:
Dim chngQuery As String= "ALTER TABLE " &Main.DBTable & " CHANGE COLUMN  old new CHANGE COLUMN good bad"
        Main.SQL1.ExecNonQuery(chngQuery)
 

Claudio Oliveira

Active Member
Licensed User
Longtime User
Hi @harinder

As a general rule for debugging this, I would run each query separately and check and analyze the results. Then I would run the UNION ALL subquery and check and analyze results again.
Apparently DBTableprior is empty, and if that's the case all your Sum() in the main query will return null.
It's hard to help without knowing the tables' structures and what data they contain...
 
Upvote 0

harinder

Active Member
Licensed User
Longtime User
Hi..DBTableprior gets correctly filled up via a previous routine. The last code posted by me is unable to join the 2 tables and result is same as DBTableprior.
I have tried the following code too. Same result!!
B4X:
 Dim c As String
        c=$" SELECT A.ACType as ACType ,A.DualDay as DualDay,A.DualNite as DualNite, A.P1Day as P1Day, A.P1Nite as P1Nite, A.P2Day
         as P2Day, A.P2Nite as P2Nite, A.InstrFlg as InstrFlg, A.TypeTotal as TypeTotal, A.DualDayhr as DualDayhr, A.DualDaymin as
         DualDaymin, A.DualNitehr as DualNitehr, A.DualNitemin as DualNitemin, A.P1Dayhr+B.P1Dayhr as P1Dayhr,A.P1Daymin+B.P1Daymin as P1Daymin,
         A.P1Nitehr+B.P1Nitehr as P1Nitehr,A.P1Nitemin+B.P1Nitemin as P1Nitemin,A.P2Dayhr as P2Dayhr,A.P2Daymin as P2Daymin,
         A.P2Nitehr as P2Nitehr,A.P2Nitemin as P2Nitemin,A.InstrFlghr+B.InstrFlghr as InstrFlghr,
         A.InstrFlgmin+B.InstrFlgmin as InstrFlgmin from prior A left join Pee1 B on (A.ACType=B.ACType)
         union all
        Select * from prior where ACType Not in(Select ACType from Pee1)"$
        Main.SQL1.ExecQuery(c)
        Log(c)
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
What is the result of this
B4X:
SELECT A.ACType as ACType ,A.DualDay as DualDay,A.DualNite as DualNite, A.P1Day as P1Day, A.P1Nite as P1Nite, A.P2Day
            as P2Day, A.P2Nite as P2Nite, A.InstrFlg as InstrFlg, A.TypeTotal as TypeTotal, A.DualDayhr as DualDayhr, A.DualDaymin as
            DualDaymin, A.DualNitehr as DualNitehr, A.DualNitemin as DualNitemin, A.P1Dayhr+B.P1Dayhr as P1Dayhr,A.P1Daymin+B.P1Daymin as P1Daymin,
            A.P1Nitehr+B.P1Nitehr as P1Nitehr,A.P1Nitemin+B.P1Nitemin as P1Nitemin,A.P2Dayhr as P2Dayhr,A.P2Daymin as P2Daymin,
            A.P2Nitehr as P2Nitehr,A.P2Nitemin as P2Nitemin,A.InstrFlghr+B.InstrFlghr as InstrFlghr,
            A.InstrFlgmin+B.InstrFlgmin as InstrFlgmin 
from prior A 
left join Pee1 B on (A.ACType=B.ACType)
What is the result of this
B4X:
Select * from prior where ACType Not in(Select ACType from Pee1)
 
Upvote 0

harinder

Active Member
Licensed User
Longtime User
ok..same structure in 2 tables as below:

prior:
A Time P1 P2
B7 5:20 4 0

pee1:
A P1 P2
A3 5 5
B7 4 3

desired result:
A Time P1 P2
A3 0:0 5 5
B7 5:20 8 3
 
Last edited:
Upvote 0

harinder

Active Member
Licensed User
Longtime User
Hi..Both these queries are joined by UNION ALL. Result is same table as TBTableprior
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Both these queries are joined by UNION ALL. Result is same table as TBTableprior
is not working as Master table shows a null !! (Only column heads visible)
The last code posted by me is unable to join the 2 tables and result is same as DBTableprior.
I have tried the following code too. Same result!!
So what is it? Does your query work or not? If it does not work, then please post the result (if it is a very large result, post column headers and a couple rows of data) of the two separate queries (actual results of the queries as ran, not typed up results, please). I realize I left the UNION ALL out. That's the point of the whole exercise. It's just a re-iteration/implementation of:
As a general rule for debugging this, I would run each query separately and check and analyze the results. Then I would run the UNION ALL subquery and check and analyze results again.

Next point:
ok..same structure in 2 tables as below:

prior:
A Time P1 P2
What is the structure? What do you have your columns defined as? Again:
It's hard to help without knowing the tables' structures and what data they contain...
 
Upvote 0

harinder

Active Member
Licensed User
Longtime User
(actual results of the queries as ran, not typed up results, please)
Hi..can you guide me how to post actual results? I'm a little lost here..I am seeing the results on a Table on my phone by using Table.bas Library incorporated in my programme..Thnx
 
Last edited:
Upvote 0

harinder

Active Member
Licensed User
Longtime User
Result of this is all data in Tableprior with all column heads in query-ACType ,DualDay,DualNite, P1Day, P1Nite, P2Day, P2Nite, InstrFlg, TypeTotal, DualDayhr, DualDaymin, DualNitehr, DualNitemin
 
Upvote 0

harinder

Active Member
Licensed User
Longtime User
What is the result of this
B4X:
Select * from prior where ACType Not in(Select ACType from Pee1)
Result of this is all data except in Tableprior with all column heads in query-ACType ,DualDay,DualNite, P1Day, P1Nite, P2Day, P2Nite, InstrFlg, TypeTotal, DualDayhr, DualDaymin, DualNitehr, DualNitemin
 
Upvote 0

harinder

Active Member
Licensed User
Longtime User
ok..same structure in 2 tables as below:

prior:
A Time P1 P2
B7 5:20 4 0

pee1:
A P1 P2
A3 5 5
B7 4 3

desired result:
A Time P1 P2
A3 0:0 5 5
B7 5:20 8 3




B4X:
Select A , Time, sum(P1) as P1,sum(P2) as P2 from (Select * from  prior UNION ALL Select A,0,P1,P2 from pee1) GROUP BY A
This is the closest I got:

A3 0 5 5
B7 0 8 3
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Hi..can you guide me how to post actual results? I'm a little lost here..I am seeing the results on a Table on my phone by using Table.bas Library incorporated in my programme..Thnx
You can use something like this to play with your SQL statements and get instant feedback (this is just one of many tools out there): http://sqlitebrowser.org/
 
Upvote 0

harinder

Active Member
Licensed User
Longtime User
Why did you not sum(Time) as Time?
OK..i reframe my query as follows, to exactly what I want:
i have 2 tables, First table is master table having columns in left in HH:MM format taking info from columns in right which are in hh and mm formats

B4X:
ACType   A     B        C      Ahr    Amin     Bhr    Bmin     Chr      Cmin

A320   12:34 85:45    07:23     12      34      85     45      7         23
B777   20:00 30:00    10:00     20      0       30     0      10         0
Second table has columns in hh and mm format

B4X:
ACType       Bhr   Bmin      Chr      Cmin

A320          10       20     46       31
How can I get final result:

B4X:
ACType    A    B    C     Ahr     Amin   Bhr     Bmin     Chr    Cmin

A320  12:34  96:05 53:54  12       34     95      65       53      54
B777  20:00  30:00 10:00  20       0      30      0        10      0
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
B4X:
Select first.ACType, first.A, (x.Bhr + x.Bmin/60) || ':' || printf("%02d", x.Bmin % 60) as B, (x.Chr + x.Cmin/60) || ':' || printf("%02d", x.Cmin % 60) as C, first.Ahr, first.Amin, x.Bhr, x.Bmin, x.Chr, x.Cmin
from first
left join
(select ACType, sum(Bhr) As Bhr, sum(Bmin)As Bmin, sum(Chr) As Chr, Sum(Cmin) As Cmin
from (select ACType , Bhr, Bmin, Chr, Cmin
from first
union all
select ACType , Bhr, Bmin, Chr, Cmin
from second)
group by ACType) As x Where first.ACType = x.ACType

Formatted version (via https://www.freeformatter.com/sql-formatter.html):
B4X:
SELECT
   first.ACType,
   first.A,
   (
     x.Bhr + x.Bmin / 60
   )
   || ':' || printf(" % 02d", x.Bmin % 60) AS B,
   (
     x.Chr + x.Cmin / 60
   )
   || ':' || printf(" % 02d", x.Cmin % 60) AS C,
   first.Ahr,
   first.Amin,
   x.Bhr,
   x.Bmin,
   x.Chr,
   x.Cmin
FROM
   first
   LEFT JOIN
     (
        SELECT
           ACType,
           SUM(Bhr) AS Bhr,
           SUM(Bmin)AS Bmin,
           SUM(Chr) AS Chr,
           SUM(Cmin) AS Cmin
        FROM
           (
              SELECT
                 ACType,
                 Bhr,
                 Bmin,
                 Chr,
                 Cmin
              FROM
                 first
              UNION ALL
              SELECT
                 ACType,
                 Bhr,
                 Bmin,
                 Chr,
                 Cmin
              FROM
                 SECOND
           )
        GROUP BY
           ACType
     )
     AS x
WHERE
   first.ACType = x.ACType

Update:
Note: Technically you would not need to store A, B or C in the table since they can be build from the information provided by Ahr, Amin, Bhr, Bmin and Chr, Cmin. This would also keep you from having to update the values (A,B, or C) when you change the other columns (Ahr etc.) for any reasons.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
exactly do?

% is the Modulo operator (https://en.wikipedia.org/wiki/Modulo_operation) for SQLite and x.Bmin % 60 will return a range of 0 through 59 for the minute portion of the output. Since you have a two digit display after the :, the numbers 0-9 need to be left padded with a 0.This is where I use printf() (https://www.sqlite.org/printf.html) and the following modifiers: %d for printing integers, 2 for the length of the output, and 0 for zero padding the output on the left (for a combined modifier/formatting string of "%02d"). Note: This is just one way to zero pad an output. You could also use concatenation (see https://stackoverflow.com/a/9603175). Either way would be very SQLite specific.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
(x.Bhr + x.Bmin/60) || ':' || printf("%02d", x.Bmin % 60) as B
Why does he need the printf as shown:
B4X:
(x.Bhr + x.Bmin/60) || ':' || printf('%02d', x.Bmin % 60) as B
When he could simply use the following without printf and get the same result unless he wants to prefix the minutes with a 0 if 1 digit:
B4X:
((x.Bhr + x.Bmin/60) || ':' ||  (x.Bmin % 60)) as B
printf looks intimidating and not too common.
By the way printf("%02d", should be: printf('%02d',. @OliverA did a great job though solving this complex puzzle. I agree, he should not store HH:MM in a table. Calculated fields should not be stored in a table. That can be calculated easily via a query if H and M are stored separately.
 
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…