Android Question change column sql

[harinder]

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

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

 

[Claudio Oliveira]

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]

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

 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]

What is the result of this

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

Select * from prior where ACType Not in(Select ACType from Pee1)

 

[harinder]

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]

What is the result of this

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

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]

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

 

[harinder]

(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]

What is the result of this

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]

What is the result of this

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]

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

ACType is TEXT, Rest all are INT

 

[harinder]

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

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]

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]

This is the closest I got:

Why did you not sum(Time) as Time?

 

[Computersmith64]

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]

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

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

ACType       Bhr   Bmin      Chr      Cmin

A320          10       20     46       31

How can I get final result:

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]

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):

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]

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

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

exactly do?

 

[OliverA]

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]

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

 

[Mahares]

(x.Bhr + x.Bmin/60) || ':' || printf("%02d", x.Bmin % 60) as B

Why does he need the printf as shown:

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

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