Consultation with grouping

Asked

Viewed 108 times

-3

Good morning gentlemen(s) I have the following problem: [Edited]

As can be seen in the image above, there are several fields with the same result (minus the "desric" column). What I need is that in addition to grouping the identical results, the desric column would look like this: "Domestic animals, noise";

Something like this:

|DESRIC|MEDPPIC|CONDFEPIC|USOIEPIC|PRZVEPIC|PERTEPIC|HIGEPIC|

|DOMESTIC ANIMALS|S|N|S|S|S|S|S|S|

|DOMESTIC ANIMALS, NOISE|S|S|S|S|S|S|S|S|

Sorry for the formatting, but I believe I can understand.

Follow my sql image [Edited]

----EDIT----

I ended up finding the command LIST(), which does exactly what I want, but I would still like to know from which version this function has been implemented.

2 answers

1

Good morning. You can make a process to return a list and put it in your sql. Follow the example below:

CREATE OR ALTER procedure GET_LISTA (
    SQL_LISTA varchar(1000))
returns (
    LISTA varchar(4000))
as
declare variable ADD_LISTA varchar(100);
begin
  FOR EXECUTE STATEMENT SQL_LISTA INTO :ADD_LISTA DO
  BEGIN
    IF( LISTA IS NOT NULL ) THEN
    BEGIN
        LISTA = LISTA || ',' || ADD_LISTA;
    END
    ELSE
    BEGIN
        LISTA = ADD_LISTA;
    END
  END
  suspend;
end

SELECT DISTINCT
A.MEDPPIC,A.CONDFEPIC,A.USOIEPIC,A.PRZVEPIC,A.PERTEPIC,A.HIGEPIC,
(
 SELECT * 
 FROM GET_LISTA('SELECT DISTINCT C.DESCRIC FROM ESAEPIC A INNER JOIN ESACER B ON B.IDEPIC=A.IDEPIC INNER JOIN ESARIC C ON C.CODRIC=B.RICCER 
 WHERE A.MEDPPIC='||A.MEDPPIC||' AND A.CONDFEPIC='||A.CONDFEPIC||' AND A.USOIEPIC='||A.USOIEPIC||' AND A.PRZVEPIC='||A.PRZVEPIC||' AND A.HIGEPIC='||A.HIGEPIC)
) AS DESCRIC
FROM ESAEPIC A
INNER JOIN ESACER B ON B.IDEPIC=A.IDEPIC
INNER JOIN ESARIC C ON C.CODRIC=B.RICCER
WHERE A.IDEPIC IN(SELECT IDEPIC FROM ESACER WHERE CLCCER = :CLCCER)

I just didn’t test the sql because I don’t have the database structure and the data, but in my database with my data it worked.

  • I’ll test it here and get back to you.

  • 1

    Friend, it worked for me here but I had to make some small adaptations. I thank you for your help. Tomorrow I will post here as answer the final version, so that if more people need.

  • The ideal is to migrate customers who are in the older version to new and use the "list". But in the worst case you can do it that way.

  • Sure the ideal is to update, but the problem is that these customers have very large database and any little problem of incompatibility generates a phenomenal headache.

1


Answering your question of when List was implemented:

  • Added in version 2.1
  • Highness in version 2.5

For more information you can see on firebird documentation

Smart to have helped

  • Is there a similar feature in version 1.5? I’ve searched everything and found nothing.

  • Firebird version 1.5 is practically a recompiled Interbase, you can search for something for Interbase (checking the version) and try it in Firebird.

  • I can’t help but advise you to migrate to the newer version of Firebird. Today I use version 2.5.2 and am migrating to 3. From 1.5 to 3.0 the changes are blatant. Consider this change as soon as possible.

  • On my machine (development) we used version 2.5.9 of Firebird, so locally it would work, but there are several clients each with tens of thousands of records that still use version 1.5.

  • In this case it is best not to use a >2.5 feature. Try to bypass using a precedent or bring the problem to be solved in the application. Unfortunately I don’t see much of a way out.

  • Exactly. I couldn’t find any example of sp that could help me. And so in my head, I have no idea what to do.

  • I will pass on the problem to DBA here that is very curious on the subject. If he has any ideas I report you.

  • Please do this. I really need help.

  • @Edenilson Rodrigo who is the DBA here made a solution and published as response. See if it helps you.

Show 4 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.