Android Question SQL, Attach, Union... Dups

[mw71]

Hi,

With your help I can combine SQLFiles:
https://www.b4x.com/android/forum/threads/solved-sql-combine-attach.99371/

Unfortunately have a problem with Dups.
If I combine all the files into one (select in list and write back to a file), the Select works well:
Select * FROM log WHERE (vom LIKE '%D-282%') AND (band> = 144000 and band <= 146000) GROUP BY user ORDER BY date ASC, time ASC

When I combine the files with ATTACH and use UNION, I get dups:
Select * FROM log WHERE (vom LIKE '%D-282%') AND (Band> = 144000 and Band <= 146000)
UNION Select * FROM log WHERE (vom LIKE '%D-282%') AND (band> = 144000 and band <= 146000) GROUP BY user ORDER BY date ASC, time ASC

whats wrong?

 

[eps]

I think we're going to need a little more information about what you're doing and what the issue is.

 

[hatzisn]

I am not sure but I suppose that you have to put the entire query in a table clause. F.e. a SELECT field1, field2,... FROM (entire UNION query) as tbl1 GROUP BY field1, field2,...

That is because the union query only connects the two datasets as I recall. I may be wrong...

 

[Jeffrey Cameron]

I agree with @eps, without knowing what it is you're trying to accomplish it is difficult to provide suggestions. Is there a reason you are using a UNION instead of LEFT/RIGHT JOIN?

 

[mw71]

From the first Post in the Link:
i store data in SQLite Database monthly, so i have 12 db Files (with identical structure) for one Year.
How can i combine these Files to one (virtual) Database and use this for a SELECT Statement? (SELECT * FROM log WHERE ...... or ....)

The Solution there is the requests with UNION

The 'user' (e.g. Person A) can be in differend Files, but i need it only one. Normaly GROUP do this.

Whith the Query (with UNION) i get one from each File (if include)

 

[Jeffrey Cameron]

From the first Post in the Link:

Even after reading that I'm still a little unsure, especially why don't you just have one database with month as part of the key. Anyway, the UNION should remove duplicate rows by default if the columns are the same:

• There must be same number of expressions in both SELECT statements.
• Since the UNION operator by default removes all duplicate rows from the result set, providing the UNION DISTINCT modifier has no effect on the results.
• The column names from the first SELECT statement in the UNION operator are used as the column names for the result set

 

[mw71]

Thanks for the Link!

Even after reading that I'm still a little unsure, especially why don't you just have one database with month as part of the key.

The user can select Daily, Weekly, Montly, Year, Own Name....
It's better to handle by User (but not ever for Querys)

• There must be same number of expressions in both SELECT statements.

the SELECT Statemant is the same, i generate it and for the second, third... Database i only Replace log by db1.log
I see in the First Post it is not correct, but in code it is (false Copy from log to Forum)
- DBs is a List with the Name of the Database (alias)
- sbq is a stringbuilder contain the Query

    For x=1 To DBs.size-1
        sbs.Append(CRLF).append("UNION ").append(sbq.tostring.Replace("log",DBs.get(x) & ".log"))
    Next

• The column names from the first SELECT statement in the UNION operator are used as the column names for the result set

the Files are identical in structure

Since the UNION operator by default removes all duplicate rows from the result set

that's the Problem, it doesn't do that at me

 

[Jeffrey Cameron]

I suspect it is because you are retrieving all columns, and only grouping by one, I didn't think you could use GROUP BY without specifying aggregate functions or each non-aggregated column in the group by clause.

Try changing your "SELECT * " to "SELECT [User]" and see if you get the number of rows you expect?

 

[mw71]

o.k., i will test tomorrow (now no access to test).

I Need Date and Time (from first User) only for Information. Different User at same Date/Time are o.k., but each user only one time.

 

[Jeffrey Cameron]

You may need to create a Common Table Expression that returns everything from your UNIONs and then groups that result set. Something like:

WITH UsersCTE AS (SELECT db1.* ….. UNION db2.* …)
   SELECT DISTINCT User, [Date], [Time] FROM UsersCTE
   ORDER BY [Date] ASC, [Time] ASC;

depending on how exactly you wish to group the output.

 

[mw71]

hi,

testet, with add GROUP it works.

- Send to my Beta Tester, Crash
- Lock in Crash Log, Error at WITH
- his Device is Android 4.x, my is 8.1 (down to 2.x here, but dont use normal)
- found this Link: https://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android
- with is add in SQL 3.8.3 https://www.sqlite.org/changes.html#version_3_8_3

because some Deviece use my App in Android 4.x, i must go another way.

Many thanks for Help!! I have learn some new