Android Question change column sql

harinder · Aug 14, 2018

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 · Aug 16, 2018

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...

harinder · Aug 16, 2018

Claudio Oliveira said:
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...

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)

OliverA · Aug 16, 2018

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)

harinder · Aug 16, 2018

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

harinder · Aug 16, 2018

OliverA said:

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)

Hi..Both these queries are joined by UNION ALL. Result is same table as TBTableprior

OliverA · Aug 16, 2018

harinder said:
Both these queries are joined by UNION ALL. Result is same table as TBTableprior

harinder said:
is not working as Master table shows a null !! (Only column heads visible)

harinder said:
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:

Claudio Oliveira said:
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:

harinder said:
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:

Claudio Oliveira said:
It's hard to help without knowing the tables' structures and what data they contain...

harinder · Aug 16, 2018

OliverA said:
(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

harinder · Aug 16, 2018

OliverA said:

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

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

harinder · Aug 16, 2018

OliverA said:
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

harinder · Aug 16, 2018

OliverA said:
What is the structure? What do you have your columns defined as? Again:

ACType is TEXT, Rest all are INT

harinder · Aug 16, 2018

harinder said:
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

OliverA · Aug 16, 2018

harinder said:
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/

OliverA · Aug 16, 2018

harinder said:
This is the closest I got:

Why did you not sum(Time) as Time?

Computersmith64 · Aug 18, 2018

OliverA said:
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/

SQLFiddle is also good for defining structures, testing queries, etc... (http://sqlfiddle.com/)

- Colin.

harinder · Aug 20, 2018

OliverA said:
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

OliverA · Aug 20, 2018

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.

harinder · Aug 20, 2018

Thanks Oliver..this is great learning for me..can you please explain what does

printf("%02d", x.Bmin % 60)

exactly do?

OliverA · Aug 20, 2018

harinder said:
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.

harinder · Aug 21, 2018

Truly Fantastic Oliver...I cracked my head over this riddle for 3 days..and you have solved it...

Mahares · Aug 21, 2018

OliverA said:
(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.

Android Question change column sql

Active Member

Active Member

Active Member

Expert

Active Member

Active Member

Expert

Active Member

Active Member

Active Member

Active Member

Active Member

Expert

Expert

Well-Known Member

Active Member

Expert

Active Member

Expert

Active Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency