SQL Queries

Aus FreiBier
Wechseln zu: Navigation, Suche

For my personal small documentation index see PostgreSQL Documentation.

Inhaltsverzeichnis

Interesting SQL Queries in PostgreSQL on the ADempiere Database

This page collects code- and other pieces I have done with PostgreSQL while working with ADempiere.

PostgreSQL

ADempiere in Version 361 uses PostgreSQL Version 8.4. All queries here are tested with 8.4. TRy to avoid using 9.x because there are some incompatible changes regarding adempiere functionality. I had problems especially with binary data. It may be that the trick with "bytea_output" (see down here) solves this issue but I haven't tried yet.


Set a password for the admin user

The admin User in PostgreSQL is always the user postgres. In the Debian standard installation this User has no password so you can only connect via ident authentication if you are the Unix-User "postgresql". So begin as root and do this to set a password:

 # su postgres
 $ psql
 postgres=# ALTER ROLE postgres UNENCRYPTED PASSWORD 'meinpasswort';
 postgres=# \q
 $ exit
 #

I wrote another description in this area in the [LUG Krefeld Wiki].

create a new database

I want to create a new database to load a Backup into. So I have a db for my development environment.

 CREATE DATABASE development WITH ENCODING='UTF8' OWNER=adempiere CONNECTION LIMIT=-1;

Find all Tables

 SELECT * FROM pg_tables;

do a version independent dump

In version 9.x the output format for literals of type bytea that pg_dump uses has changed. So you can not make a dump in a 9.x database and execute it in a 8.x server. If you want to do it you have to set the user variable 'bytea_output' to 'escape' in the 9.x server.

 ALTER ROLE username SET bytea_output TO 'escape';

You can reset it back to hex:

 ALTER ROLE username RESET bytea_output;

large and small Letters

Make the first character of every word large and the other small:

Handwritten version that does omit words with less than three letters:

 SELECT
   array_to_string( array_agg(
     CASE WHEN LENGTH(wort)>2 THEN
     UPPER(SUBSTRING(wort FROM 1 FOR 1))||LOWER(SUBSTRING(wort FROM 2))
     ELSE wort END
   ),)
 FROM regexp_split_to_table(
     'bla blaha foo FO bar   bahaaar.', 
     E'\\y'
 ) AS wort;

short version (see http://www.postgresql.org/docs/9.1/static/functions-string.html):

 initcap('bla blaha foo FO bar   bahaaar.')


compare records for equality

If you want to know wether a records has changed you can use something like this (I used it for kontinuierlicher Import:

 CREATE TEMPORARY TABLE changedrecords ON COMMIT DROP AS
   SELECT tmp.*
   FROM tmp INNER JOIN importe.lieferanten USING("LieferantenNummer")
   WHERE ROW(tmp.*) IS DISTINCT FROM ROW(importe.lieferanten.*);

The row-Constructor seems to be postgresql-specific. If you want to solve this with other databases you may read this article.


create a foreign key constraint

 ALTER TABLE adempiere.bay_tradingunit 
 ADD FOREIGN KEY (depositpackage_id) 
 REFERENCES adempiere.m_product (m_product_id)
 ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;


create a set (a table) of weeks

This query gives you the dates of all mondays in the next year.

SELECT
  generate_series(
    current_date + interval '1 day' - interval '1 day' * EXTRACT(isodow FROM current_date),
    current_date + interval '1 day' - interval '1 day' * EXTRACT(isodow FROM current_date) + interval '1 year',
    interval '1 week'
  )

do an eval in SQL

At first glance there is nothing like an "eval" command in SQL. If you dig a bit deeper you see that there is the EXECUTE command in plsql (not to be confused with the EXECUTE command in SQL). You can use it only in functions. The EXECUTE command can not return a table (the return value may be a scalar or a record, but not a table of records). There is a way out by creating a temporary table.

CREATE OR REPLACE FUNCTION executestring(text) RETURNS VOID AS $$ DECLARE BEGIN EXECUTE 'CREATE TEMP table tmp ON COMMIT DROP AS SELECT 1 AS eins, '||quote_literal($1)||' AS zwei'; END; $$ LANGUAGE plpgsql;
SELECT executestring('bla');
SELECT * FROM tmp;

If you want to return a table it is not that easy. I tried hard but I could not make it work without defining the returning record structure. You have to define columns with the OUT keyword. So you can't have a generic, dynamic method for every kind of return tables.

--DROP FUNCTION executestring(text);
CREATE OR REPLACE FUNCTION executestring(text, OUT eins text, OUT zwei text) RETURNS SETOF RECORD AS 
$$
  DECLARE 
    r record;
  BEGIN 
    EXECUTE 'CREATE TEMP table tmp ON COMMIT DROP AS SELECT 1 AS eins, '||quote_literal($1)||'::text AS zwei';
    FOR r IN 
      SELECT * FROM tmp 
    LOOP
      eins=r.eins;
      zwei=r.zwei;
      RETURN NEXT;
    END LOOP;
    RETURN;
  END;
$$ LANGUAGE plpgsql;

SELECT * FROM executestring('bla');

copy csv data into a table

You can use the \COPY directive with psql to get csv data into your database. Because very often the data has to be rearranged I prefer to read the data into a temporary table in the first step.

 CREATE TEMPORARY TABLE tmp ( a varchar, b varchar, c varchar, d varchar, e varchar);
 \COPY tmp(a,b,c,d,e) from KontenMitOPsM002-erweitert.csv delimiter ';' csv header;
 select * from tmp;
 ...

Measure size in bytes of a database schema

use one of these to measure one or all schemas:

 SELECT sum(pg_relation_size(schemaname || '.' || '"' || tablename || '"'))::bigint FROM pg_tables WHERE schemaname = blabla
 SELECT schemaname, sum(pg_relation_size(schemaname || '.' || '"' || tablename || '"'))::bigint FROM pg_tables GROUP BY schemaname


measure size of the tables in the database

To look which table eats all your space:

 select 
   pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')),
   *
 from pg_tables where schemaname='adempiere'
 order by
   pg_total_relation_size('"' || schemaname || '"."' || tablename || '"') desc

(Hint: Have a look at ad_archive, ad_changelog, ad_issue)

deal with users and passwords

List all users:

 psql -h localhost -U postgres -c '\du'

create new user

 psql -h localhost -U postgres -c "CREATE ROLE adempiere LOGIN PASSWORD 'mypassword'"

change password

 psql -h localhost -U postgres -c "ALTER USER adempiere WITH PASSWORD 'newpassowrd'"


the WITH clause is an CTE, what does that mean

At one point a with clause that dealt with a complex query to create something like a view became very slow. It seemed that the optimizer outside the "with" did not see clear and open facts inside the "with". Yeah, that is true. :-( PostgreSQL behaves like that. See this example: https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ . Additional, this page gives you an example of the syntax to avoid that by using a subquery using select instead of with that will be much better optimized.

ADempiere Database Structure

writing SQL inside ADempiere

If your code has a strange behaviour check the server logs and look whether your SQL goes without changes to the server. There is a facility to translate common Oracle SQL terms to PostgreSQL. If your code is already PostgreSQL-written this can be very annoying. If you call DB.prepareStatement(sql-Code, ...), your code will be translated via org.compiere.db.DB_PostgreSQL.convert(...) which calls org.compier.dbPort.convert_PostgreSQL.convert(...). So the following command:

DELETE FROM BAY_Umsatzstatistik USING BAY_Statistikperiode
WHERE BAY_Statistikperiode.BAY_Statistikperiode_ID=BAY_Umsatzstatistik.BAY_Statistikperiode_ID
  AND BAY_Statistikperiode.Value='bla'

in org.compier.dbPort.convert_PostgreSQL.convertAlias(...) will be destroyed. The command work if you set an table alias (this will be deleted by the conversion but the term works):

DELETE FROM BAY_Umsatzstatistik BAY_Umsatzstatistik USING BAY_Statistikperiode
WHERE BAY_Statistikperiode.BAY_Statistikperiode_ID=BAY_Umsatzstatistik.BAY_Statistikperiode_ID
  AND BAY_Statistikperiode.Value='bla'

I do not know why this happens. Perhaps in older versions of PostgreSQL you could not use alias with delete?!?


Find all Tables that deal with translations:

 SELECT * FROM pg_tables WHERE tablename LIKE '%trl%';


create a new data table in ADempiere

Copied from http://www.adempiere.com/NewWindow:

CREATE TABLE XX_Material (
 AD_CLIENT_ID   	NUMERIC(10)     NOT NULL,
 AD_ORG_ID      	NUMERIC(10)     NOT NULL,
 ISACTIVE       	CHAR(1)         DEFAULT 'Y' NOT NULL,
 CREATED        	DATE            DEFAULT NOW() NOT NULL,
 CREATEDBY      	NUMERIC(10)     NOT NULL,
 UPDATED        	DATE            DEFAULT NOW() NOT NULL,
 UPDATEDBY      	NUMERIC(10)     NOT NULL,
 XX_MATERIAL_ID     	NUMERIC(10,0) 	NOT NULL,
 MATNR     		SMALLINT 	NOT NULL,
 COLORNR    		SMALLINT 	NOT NULL,
 NAME      		VARCHAR(100)	NOT NULL,
 NAMESHORT  		VARCHAR(100)	NOT NULL
);
ALTER TABLE XX_Material ADD PRIMARY KEY (XX_MATERIAL_ID );

create a anonymized version of the database for testing purposes

To get a database that looks good for testing but does not expose secret data I choose a strategy of mangling the order of the data of single columns while keeping all other columns the old value. First I make a backup of the original data and copy it to a second (anonymized) database. Then I mangle the columns I want to anonymize one after each other.

UPDATE C_BPartner AS bp
SET name=uebersetzung.neuerwert
FROM(
  SELECT tab1.value, tab2.neuerwert
  FROM(
    select row_number() over(ORDER BY value), value
    FROM c_bpartner WHERE AD_Client_ID=1000000
  ) AS tab1
  LEFT JOIN (
    select row_number() over(ORDER BY md5(value||coalesce(name,''))), name AS neuerwert, *
    FROM c_bpartner WHERE AD_Client_ID=1000000
  ) AS tab2 USING (row_number)
) AS uebersetzung
WHERE bp.value=uebersetzung.value
;

Repeat this with other columns than "name" (name2, address etc.). If you are afraid to expose the anonymizing algorithm here you may vary "md5(name)" (e.g. "md5('bla'||name)" gives a completely other order).

ADempiere Database

Copy German Translation for Document Types (Belegarten) to a new Client:

Version from needle58 for Oracle

-- 'name' fuer client fuer 'de_DE' aktualisieren auf den wert aus gw
UPDATE c_doctype_trl dtt
SET
  dtt.name = (
    SELECT dtt1.name FROM c_doctype_trl dtt1
    WHERE 
      dtt1.c_doctype_id = (
        SELECT dt1.c_doctype_id
        FROM c_doctype dt, c_doctype dt1
        WHERE dt1.c_doctype_id < 1000000
          AND dt1.c_doctype_id <> dt.c_doctype_id
          AND dt1.name = dt.name
          AND dt.c_doctype_id = dtt.c_doctype_id
      ) AND dtt1.ad_language='de_DE'
  ),
  dtt.printname = (
    SELECT dtt1.printname FROM c_doctype_trl dtt1
    WHERE dtt1.c_doctype_id = (
      SELECT dt1.c_doctype_id
      FROM c_doctype dt, c_doctype dt1
      WHERE dt1.c_doctype_id < 1000000
        AND dt1.c_doctype_id <> dt.c_doctype_id
        AND dt1.name = dt.name
        AND dt.c_doctype_id = dtt.c_doctype_id
    )
    AND dtt1.ad_language='de_DE'
  ),
  dtt.documentnote = (
    SELECT dtt1.documentnote FROM c_doctype_trl dtt1
    WHERE dtt1.c_doctype_id = (
      SELECT dt1.c_doctype_id
      FROM c_doctype dt, c_doctype dt1
      WHERE dt1.c_doctype_id < 1000000
        AND dt1.c_doctype_id <> dt.c_doctype_id
        AND dt1.name = dt.name
        AND dt.c_doctype_id = dtt.c_doctype_id
      )
      AND dtt1.ad_language='de_DE'
    )
  WHERE dtt.c_doctype_id >=1000000
  AND dtt.ad_language='de_DE'
; 

My (derived) version (PostgreSQL 8.4/ADempiere 3.6.1)

SET search_path = adempiere;

UPDATE c_doctype_trl dtt
SET
  name = coalesce((
    SELECT dtt1.name FROM c_doctype_trl dtt1
    WHERE 
      dtt1.c_doctype_id = (
        SELECT dt1.c_doctype_id
        FROM c_doctype dt, c_doctype dt1
        WHERE dt1.c_doctype_id < 1000000
          AND dt1.c_doctype_id <> dt.c_doctype_id
          AND dt1.name = dt.name
          AND dt.c_doctype_id = dtt.c_doctype_id
      ) AND dtt1.ad_language='de_DE'
  ),dtt.name),
  printname = coalesce((
    SELECT dtt1.printname FROM c_doctype_trl dtt1
    WHERE dtt1.c_doctype_id = (
      SELECT dt1.c_doctype_id
      FROM c_doctype dt, c_doctype dt1
      WHERE dt1.c_doctype_id < 1000000
        AND dt1.c_doctype_id <> dt.c_doctype_id
        AND dt1.name = dt.name
        AND dt.c_doctype_id = dtt.c_doctype_id
    )
    AND dtt1.ad_language='de_DE'
  ),dtt.printname),
  documentnote = coalesce((
    SELECT dtt1.documentnote FROM c_doctype_trl dtt1
    WHERE dtt1.c_doctype_id = (
      SELECT dt1.c_doctype_id
      FROM c_doctype dt, c_doctype dt1
      WHERE dt1.c_doctype_id < 1000000
        AND dt1.c_doctype_id <> dt.c_doctype_id
        AND dt1.name = dt.name
        AND dt.c_doctype_id = dtt.c_doctype_id
      )
      AND dtt1.ad_language='de_DE'
    ),dtt.documentnote)
  WHERE dtt.c_doctype_id >=1000000
  AND dtt.ad_language='de_DE'
; 


extract description and help from ADempiere database

Table information:

 SELECT tablename, name, description, help
 FROM AD_Table 
 WHERE
   tablename LIKE 'BAY_%';

Same with translation data:

 SELECT 
 AD_Table.tablename, AD_Table.name, AD_Table.description, AD_Table.help, AD_Table_TRL.name AS name_de 
 FROM AD_Table 
 LEFT JOIN ad_table_trl USING(ad_table_id) 
 WHERE 
   AD_Table_TRL.ad_language = 'de_DE' 
   AND tablename LIKE 'BAY_%'

Column information:

 SELECT AD_Table.tablename, AD_Table.name, AD_Table.description, AD_Table.help, AD_Column.name, AD_Column.description, AD_Column.help
 FROM AD_Table 
 LEFT JOIN AD_Column USING(AD_Table_ID) 
 WHERE 
   AD_Table.tablename LIKE 'BAY_%'
 


extract all values of a list reference (aka "enum values")

This example extracts all values (and names) of my own created list reference "BAY_ConditionLevel":

SELECT 
  ad_ref_list.value,
  ad_ref_list.name
FROM ad_reference LEFT JOIN ad_ref_list USING (ad_reference_id)
WHERE ad_reference.name='BAY_ConditionLevel';

extract data about your own company

You can use this e.g. for a report header or footer.

SELECT 
  /* Header */
  AD_Client.name,
  C_Location.address1,
  /* Logo */ 
  AD_Image.binarydata,
  /* Briefkopffenster */
  /* AD_Client.name, */
  /* C_Location.address1, */
  C_Location.address2,
  C_Location.postal,
  C_Location.city,
  /* Footer */
  AD_Org.Description,
  AD_OrgInfo.Phone,
  AD_OrgInfo.Fax,
  AD_OrgInfo.TaxID,
  AD_OrgInfo.RegisterEntry,
  C_Bank.Name AS BankName,
  C_Bank.RoutingNo,
  C_BAnkAccount.AccountNo,
  /* Benutzer */
  AD_User.Name AS UserName
FROM
  AD_PInstance
  LEFT JOIN AD_OrgInfo USING (AD_Org_ID, AD_Client_ID)
  LEFT JOIN AD_Org USING (AD_Org_ID, AD_Client_ID)
  LEFT JOIN AD_Client USING (AD_Client_ID)
  LEFT JOIN AD_ClientInfo USING(AD_Client_ID)
  LEFT JOIN AD_Image ON(AD_ClientInfo.logoreport_id = AD_Image.AD_Image_ID)
  LEFT JOIN C_Location USING(C_Location_ID)
  LEFT JOIN AD_User USING(AD_User_ID)
  LEFT JOIN C_BankAccount ON(C_BankAccount.AD_Org_ID=AD_OrgInfo.AD_Org_ID AND C_BAnkAccount.Description='Hauptkonto')
  LEFT JOIN C_Bank USING(C_Bank_ID)
WHERE
  AD_PInstance.AD_PInstance_ID=1000421
;

In JasperReports you should not use the hardcoded ID of "1000421" but extract it from the Parameters that ADempiere gives you. Create the Parameter AD_PINSTANCE_ID and use something like "1000421" as the default (for testing in iReport). When using from inside ADempiere it will get the right data for the calling user.

FreiBier Data Tables

read Statistics

Example from User:Srcfrge64:

 SELECT
   p.value as "Artikel-Nummer",
   p.name as "Artikelbezeichnung",
   p.volume,
   sum(us.stueck) as "Gekauft Gebinde",
   sum(us.umsatz) as "Umsatz EUR",
   sum(us.stueck * p.volume)/100 as "(hl)"
 FROM adempiere.bay_umsatzstatistik us, adempiere.bay_statistikperiode sp, adempiere.m_product p
 WHERE 
   c_bpartner_id = 1003095
   --and p.m_product_id = 1000333
   AND sp.bay_statistikperiode_id = us.bay_statistikperiode_id
   AND us.m_product_id = p.m_product_id
   AND sp.description IS NULL
   AND sp.enddate > '01.01.2011'
   AND sp.statistiktyp = 'MD'
 GROUP BY p.value, p.name, p.volume 
 ORDER BY p.value

(re)create view BAY_Leihgut_V

DROP VIEW BAY_Leihgut_V;

CREATE VIEW adempiere.BAY_Leihgut_V AS
WITH verschiedene AS (
  SELECT
    min(BAY_LeihgutZugang_ID) AS BAY_LeihgutZugang_ID,
    BAY_LeihgutArt_ID,
    serno,
    sum(quantity) AS quantity     /* noch nicht Datums-gesteuert */
  FROM BAY_LeihgutZugang
  GROUP BY BAY_LeihgutArt_ID,serno
)
SELECT
  verschiedene.BAY_LeihgutZugang_ID AS BAY_Leihgut_V_ID,
  BAY_LeihgutArt.AD_Client_ID,
  BAY_LeihgutArt.AD_Org_ID,
  BAY_LeihgutArt.created,
  BAY_LeihgutArt.createdBy,
  BAY_LeihgutArt.updated,
  BAY_LeihgutArt.updatedBy,
  CASE
    WHEN BAY_LeihgutArt.Description IS NOT NULL
    THEN BAY_LeihgutArt.Description
    ELSE BAY_LeihgutZugang.Description
  END AS Description,
  CASE
    WHEN BAY_LeihgutArt.Help IS NOT NULL
    THEN BAY_LeihgutArt.Help
    ELSE BAY_LeihgutZugang.Help
  END AS Help,
  BAY_LeihgutArt.isActive,
  BAY_LeihgutArt.M_Product_ID,
  BAY_LeihgutArt.Name ||
    CASE 
      WHEN verschiedene.serno IS NOT NULL
      THEN ' - ' || verschiedene.serno
      ELSE ''
    END AS Name,
  verschiedene.quantity,
  (
    SELECT max(dateDelivered) 
    FROM BAY_LeihgutWartung
    WHERE BAY_Leihgut_V_ID=verschiedene.BAY_LeihgutZugang_ID
  ) AS letzteWartung
FROM verschiedene
LEFT JOIN BAY_LeihgutArt USING(BAY_LeihgutArt_ID)
LEFT JOIN BAY_LeihgutZugang USING(BAY_LeihgutZugang_ID)
;

(re)create view BAY_Leihveranstaltung_Zeile_V

This is a helper view that adds some calculated fields to the table BAY_Leihveranstaltung_Zeile.

DROP VIEW BAY_Leihveranstaltung_Zeile_V;

CREATE VIEW adempiere.BAY_Leihveranstaltung_Zeile_V AS
SELECT
  (
    SELECT sum(zugaenge.quantity)
    FROM
      BAY_LeihgutZugang AS ich, 
      BAY_Leihgutart AS art, 
      BAY_LeihgutZugang AS zugaenge, 
      BAY_Leihveranstaltung AS event
    WHERE
      ich.BAY_LeihgutZugang_ID = BAY_Leihveranstaltung_Zeile.BAY_Leihgut_V_ID
      AND ich.BAY_Leihgutart_ID = art.BAY_Leihgutart_ID
      AND art.isActive='Y'
      AND art.BAY_Leihgutart_ID = zugaenge.BAY_Leihgutart_ID
      AND zugaenge.isActive='Y'
      AND BAY_Leihveranstaltung_Zeile.BAY_Leihveranstaltung_ID = event.BAY_Leihveranstaltung_ID
      AND zugaenge.dateReceived <= event.startDate
  ) AS Bestand,
  (
    SELECT sum(allzeilen.quantity)
    FROM
      BAY_Leihveranstaltung AS myevent, 
      BAY_Leihveranstaltung AS allevents, 
      BAY_Leihveranstaltung_Zeile AS allzeilen,
      BAY_LeihgutZugang AS zugang,
      BAY_LeihgutZugang AS myzugang
    WHERE
      BAY_Leihveranstaltung_Zeile.BAY_Leihveranstaltung_ID = myevent.BAY_Leihveranstaltung_ID
      AND allevents.startDate <= myevent.endDate
      AND allevents.endDate >= myevent.startDate
      AND allevents.isActive='Y'
      AND allzeilen.BAY_Leihveranstaltung_ID = allevents.BAY_Leihveranstaltung_ID
      AND allzeilen.BAY_Leihgut_V_ID = zugang.BAY_LeihgutZugang_ID
      AND BAY_Leihveranstaltung_Zeile.BAY_Leihgut_V_ID = myzugang.BAY_LeihgutZugang_ID
      AND zugang.BAY_Leihgutart_ID = myzugang.BAY_Leihgutart_ID
  ) AS Bestellt,
  (
    SELECT sum(allzeilen.quantity)
    FROM
      BAY_Leihveranstaltung AS myevent, 
      BAY_Leihveranstaltung AS allevents, 
      BAY_Leihveranstaltung_Zeile AS allzeilen
    WHERE
      BAY_Leihveranstaltung_Zeile.BAY_Leihveranstaltung_ID = myevent.BAY_Leihveranstaltung_ID
      AND allevents.startDate >= myevent.startDate
      AND allevents.endDate <= myevent.endDate
      AND allevents.isActive='Y'
      AND allzeilen.BAY_Leihveranstaltung_ID = allevents.BAY_Leihveranstaltung_ID
      AND allzeilen.BAY_Leihgut_V_ID = BAY_Leihveranstaltung_Zeile.BAY_Leihgut_V_ID
  ) AS EinzelnBestellt
  ,*
FROM BAY_Leihveranstaltung_Zeile

(re)create a view of the weeks of the next year (Kalenderwochen)

CREATE VIEW adempiere.BAY_Kalenderwoche_V AS
WITH montage AS (SELECT
  generate_series(
    current_date + interval '1 day' - interval '1 day' * EXTRACT(isodow FROM current_date) - interval '7 days',
    current_date + interval '1 day' - interval '1 day' * EXTRACT(isodow FROM current_date) + interval '1 year',
    interval '1 week'
  ) AS montag)
SELECT
  0 AS AD_Client_ID,
  0 AS AD_Org_ID,
  now() AS created,
  now() AS updated,
  0 AS createdBy,
  0 AS updatedBy,
  date_part('epoch', montag) AS BAY_Kalenderwoche_V_ID,
  date_part('week', montag) AS kw,
  lpad(date_part('week', montag)::text,2,'0') || '/' || substring(date_part('isoyear', montag)::text,3) 
    AS kwBezeichnung,
  montag AS start,
  montag + interval '6 days' AS ende,
  (
    SELECT count(*) 
    FROM BAY_LeihVeranstaltung
    WHERE (NOT startDate > montag + interval '7 days') AND (NOT endDate < montag)
  ) AS veranstaltungen
FROM
  montage
;

Tests for exclusive groups

Has a Product more than one Product group?

SELECT
  M_Product.M_Product_ID,
  M_Product.Value,
  count(*)
FROM M_Product
FULL JOIN (
  BAY_Artikelgruppe
  INNER JOIN BAY_Gruppe USING(BAY_Gruppe_ID)
  INNER JOIN BAY_Gruppenkategorie 
    ON(
      BAY_Gruppe.BAY_Gruppenkategorie_ID = BAY_Gruppenkategorie.BAY_Gruppenkategorie_ID
      AND BAY_Gruppenkategorie.Value='AZ'
    )
) USING(M_Product_ID)
WHERE
  M_Product.isActive='Y'
  AND M_Product.AD_Client_ID=1000000
GROUP BY
  M_Product.M_Product_ID,
  M_Product.Value
HAVING
  count(*)!=1

Has a product no product group?

SELECT
  *
FROM M_Product
FULL JOIN (
  BAY_Artikelgruppe
  INNER JOIN BAY_Gruppe USING(BAY_Gruppe_ID)
  INNER JOIN BAY_Gruppenkategorie 
    ON(
      BAY_Gruppe.BAY_Gruppenkategorie_ID = BAY_Gruppenkategorie.BAY_Gruppenkategorie_ID
      AND BAY_Gruppenkategorie.Value='AZ'
    )
) USING(M_Product_ID)
WHERE
  M_Product.isActive='Y'
  AND M_Product.AD_Client_ID=1000000
  AND BAY_Gruppe.BAY_Gruppe_ID IS NULL
Meine Werkzeuge
Namensräume

Varianten
Aktionen
Navigation
Werkzeuge