Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
5
Erster Beitrag:
vor 9 Monaten
Letzter Beitrag:
vor 8 Monaten, 3 Wochen
Beteiligte Autoren:
R.R.Siva Sangavi, ccc2, Fabrice Harari, Erik Schwarz

[WD 21], Subquery with union(s) for report

Startbeitrag von R.R.Siva Sangavi am 20.03.2017 12:51

Need to create a query file which consist of subquery like below,

Select Column_Name1,Sum(Total) as Total,Sum(Quantity) as Quantity
From
(Select Column_Name1,Count(Column_Name2) as Total,0 as Quantity
From Table_Name1
Group by Column_Name1

union all

Select Column_Name1,0 as Total,Count(Column_Name2) as Quantity
From Table_Name2
Group by Column_Name1)

Group by Column_Name1
having sum(Total) > 0

This query runs successfully in HFSQL control center. When i try to create in windev Query Editor is not allowing to create it.

How to do it Please help me.

I want to create a report based on this query.

Regards,
R.R.Siva Sangavi

Antworten:

Hi

that may not be possible... I'm not sure the query editor supports subqueries like that.

The good news is that it's not mandatory to do so. You can just store your query in a string and execute it instead of using the query editor...

Best regards

von Fabrice Harari - am 20.03.2017 13:12
Hi,

and you can do that directly in the code or with the query Editor -> select 'SQL code' as the type of query.

Best regards

Erik

von Erik Schwarz - am 20.03.2017 15:02
I get the following error, when i write the query in query editor -> select 'SQL code'

Your query has been analyzed: some syntax points require an action

Consult the information and check the default choices.

Subquery in from - No action to perform
Having Clause : Item.Alias - Use the alias

Subquery found in From clause. Subqueries are not managed by Query Editor.

Any suggestion?

Best Regards

R.R.Siva Sangavi

von R.R.Siva Sangavi - am 28.03.2017 07:19
there is a trick you can use .
1. create a dummy query for the report
------- query1 ---------------------
Select
top 1
"" as Column_Name1,
0 as Total,
0 as Quantity
From
Table_Name1


2. overwrite the query1 contents
-------at opening of report1 ----
m_sql is string = [
Select
Column_Name1,
Sum(Total) as Total,
Sum(Quantity) as Quantity
From
(Select
Column_Name1,
Count(Column_Name2) as Total,
0 as Quantity
From
Table_Name1
Group by
Column_Name1

union all

Select
Column_Name1,
0 as Total,
Count(Column_Name2) as Quantity
From
Table_Name2
Group by
Column_Name1)

Group by
Column_Name1

having
sum(Total) > 0

]

HExecuteSQLQuery(query1 ,MyConnection, hQueryWithoutCorrection+hQueryWithoutHFCorrection, M_sql)


it's not very nice method but it work

von ccc2 - am 28.03.2017 09:29
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.