Skrypt SQL do szukania pól własnych

[Ks. Robak 314]

Póki nie ma tej funkcjonalności w Sello trzeba jakoś sobie radzić.

Poniższy kod służy do uruchomienia w programie serwisowym Sello

Menu Baza->Zapytanie SQL

Pozwala znaleźć w których towarach użyliśmy pola własnego o danej nazwie,

lub w których towarach konkretne lub dowolne pole własne zawiera szukany ciąg znaków.

 

DECLARE @nazwa_pola_wlasnego varchar(100);
DECLARE @wartosc_pola_wlasnego varchar (8000);


-- Tu zmieniamy ---------------------------------------

-- dajemy '%' jako znak zastępujący dowolny ciąg

SET @nazwa_pola_wlasnego='nazwa_pola';
SET @wartosc_pola_wlasnego='%moja wartość%';

-------------------------------------------------------

select tw.it_Symbol as 'Symbol towaru', tw.it_Name as 'Nazwa towaru', pola_slownik.cat_Name as 'Nazwa pola wł.'

from it_CustomAttributes as pole
 LEFT JOIN it__Item as tw on pole.ia_ItemId=tw.it_Id
 LEFT JOIN sl_CustomAttributes as pola_slownik on pole.ia_AttributeId=pola_slownik.cat_Id
WHERE 
 pola_slownik.cat_Name LIKE @nazwa_pola_wlasnego AND
 pole.ia_AttributeValue LIKE @wartosc_pola_wlasnego

[PanBanBan Marcin 185]

Super

 

Robię masowe zmiany na polach własnych i tak się zastanawiałem czy do każdego towaru  dodałem potrzebne pola. Jak skończę to będę mógł sobie sprawdzić tym zapytaniem. Dzięki

 

Hmmm... A czy dałoby się zrobić tak żeby wyświetlić listę towarów, które nie zawierają dowolnego ze wskazanych pól, czy trzeba dla każdej nazwy pola robić to zapytanie oddzielnie?

 

Innymi słowy żeby nie wpisywać po kolei każdej nazwy pola własnego i wykonywać oddzielne zapytanie dla każdego pola. Tylko żeby można było od razu wpisać wiele nazw pól własnych i jeśli towar nie posiada któregoś z nich, to żeby się wylistował. A byłby super wypaśny bajer gdyby się wylistował z nazwą/nazwami brakujących pól własnych

[Ks. Robak 314]

 

Z pewnością jest to możliwe, choć podejrzewam, że zapytanie będzie dość obciążające bazę, bo będzie musiało uwzględnić wszystkie towary za jednym razem, aby sprawdzić w którym czegoś nie ma.

A z tego co pamiętam masz ich dziesiątki tysięcy 

 

Hmmm, pokombinuję jutro wieczorem.

[Ks. Robak 314]

Kod do szukania towarów, w których występują wszystkie z podanych pola własne (z ewentualnymi wartościami).

 

Można dodać dowolną liczbę pól za jednym razem, wyświetlą się tylko te towary, które mają je wszystkie.

 

Można zastosować dodatkowy filtr po nazwie lub symbolu towaru.

Znak % zastępuje dowolny ciąg znaków.

 

W teorii można też użyć tego znaku w nazwie pola własnego.

Ale uwaga - będzie to oznaczać, że każdy towar będzie musiał posiadać wszystkie pola własne spełniające wpisany wzór.

Np. gdy mamy w słowniku pola o nazwach ZdjecieA, ZdjecieB, ZdjecieC,

to gdy wpiszemy w nazwie szukanego pola własnego 'Zdjecie%', wówczas otrzymamy tylko te towary, w których są dodane wszystkie pola ZdjecieA, ZdjecieB, ZdjecieC, a nie towary zawierające dowolne z tych pól.

 

Jeśli chcemy wyszukać tylko obecność danego pola własnego, wówczas jako "wartosc_pola_wlasnego" wpisujemy '%'.

 

Przykład użycia:

-- TĘ CZĘŚĆ KOPIUJEMY I WKLEJAMY JEDNĄ POD DRUGĄ TYLE RAZY ILE PÓL CHCEMY SPRAWDZIĆ --
SET @nazwa_pola_wlasnego='[b]Rodzaj[/b]';
SET @wartosc_pola_wlasnego='[b]Bluzka%[/b]';
INSERT INTO #pola_wlasne_temp SELECT cat_Id, @wartosc_pola_wlasnego FROM sl_CustomAttributes WHERE cat_Name LIKE @nazwa_pola_wlasnego;
-- KONIEC KOPIOWANIA --

-- TĘ CZĘŚĆ KOPIUJEMY I WKLEJAMY JEDNĄ POD DRUGĄ TYLE RAZY ILE PÓL CHCEMY SPRAWDZIĆ --
SET @nazwa_pola_wlasnego='[b]Marka[/b]';
SET @wartosc_pola_wlasnego='[b]Dior[/b]';
INSERT INTO #pola_wlasne_temp SELECT cat_Id, @wartosc_pola_wlasnego FROM sl_CustomAttributes WHERE cat_Name LIKE @nazwa_pola_wlasnego;
-- KONIEC KOPIOWANIA --

-- TĘ CZĘŚĆ KOPIUJEMY I WKLEJAMY JEDNĄ POD DRUGĄ TYLE RAZY ILE PÓL CHCEMY SPRAWDZIĆ --
SET @nazwa_pola_wlasnego='[b]Model na rok[/b]';
SET @wartosc_pola_wlasnego='[b]2010[/b]';
INSERT INTO #pola_wlasne_temp SELECT cat_Id, @wartosc_pola_wlasnego FROM sl_CustomAttributes WHERE cat_Name LIKE @nazwa_pola_wlasnego;
-- KONIEC KOPIOWANIA --

 

 

Sprawdźmy poprawność nazwy pola własnego - najlepiej kopiować ze słownika.

W przypadku błędnej nazwy - zapytanie nic nie zwróci.

 

 

DECLARE @pola_wlasne varchar (8000);
DECLARE @nazwa_pola_wlasnego varchar(100);
DECLARE @wartosc_pola_wlasnego varchar (8000);
DECLARE @nazwa_towaru nvarchar (100);
DECLARE @symbol_towaru nvarchar (20);

IF OBJECT_ID ('tempdb..#pola_wlasne_temp') IS NOT NULL
   DROP TABLE #pola_wlasne_temp;
CREATE TABLE #pola_wlasne_temp ( poleId int NOT NULL PRIMARY KEY, poleWartosc varchar(8000) NOT NULL);

------- TU ZMIENIAMY -------------------------------------------------

SET @nazwa_towaru='%';
SET @symbol_towaru='%';

-- TĘ CZĘŚĆ KOPIUJEMY I WKLEJAMY JEDNĄ POD DRUGĄ TYLE RAZY ILE PÓL CHCEMY SPRAWDZIĆ --
SET @nazwa_pola_wlasnego='Gwarancja';
SET @wartosc_pola_wlasnego='%';
INSERT INTO #pola_wlasne_temp SELECT cat_Id, @wartosc_pola_wlasnego FROM sl_CustomAttributes WHERE cat_Name LIKE @nazwa_pola_wlasnego;
-- KONIEC KOPIOWANIA --


------- KONIEC ZMIAN ------------------------------------------------


SELECT tw.it_Symbol as 'Symbol towaru', tw.it_Name as 'Nazwa towaru'
FROM it__Item as tw
 LEFT JOIN it_CustomAttributes as pole on pole.ia_ItemId=tw.it_Id
 LEFT JOIN sl_CustomAttributes as pola_slownik on pole.ia_AttributeId=pola_slownik.cat_Id
 LEFT JOIN #pola_wlasne_temp as szukane_pola_wl on poleId=pole.ia_AttributeId
WHERE 
 szukane_pola_wl.poleId is not NULL
 AND pole.ia_AttributeValue LIKE szukane_pola_wl.poleWartosc
 AND tw.it_Name LIKE @nazwa_towaru
 AND tw.it_Symbol LIKE @symbol_towaru
 GROUP BY tw.it_Symbol,tw.it_Name
HAVING  COUNT(*)=(SELECT COUNT(*) FROM #pola_wlasne_temp )

 

 

PS. Teraz będę kombinował nad zapytaniem zwracającym towary, w których NIE MA podanych pól własnych.

[Ks. Robak 314]

 

Szukanie które towary nie posiadają dowolnego z podanych pól własnych.

 

Niestety nie umiem zrobić, aby wynik zapytania zwracał, które pola są w danym towarze brakujące.

 

Może ktoś będzie w stanie to zapytanie udoskonalić.

 

DECLARE @pola_wlasne varchar (8000);
DECLARE @nazwa_pola_wlasnego varchar(100);
DECLARE @wartosc_pola_wlasnego varchar (8000);
DECLARE @nazwa_towaru nvarchar (100);
DECLARE @symbol_towaru nvarchar (20);

IF OBJECT_ID ('tempdb..#pola_wlasne_temp') IS NOT NULL
    DROP TABLE #pola_wlasne_temp;
CREATE TABLE #pola_wlasne_temp ( poleId int NOT NULL PRIMARY KEY, poleWartosc varchar(8000) NOT NULL);

------- TU ZMIENIAMY -------------------------------------------------
SET @nazwa_towaru='%';
SET @symbol_towaru='%';

-- TO KOPIUJEMY TYLE RAZY ILE PÓL CHCEMY SPRAWDZIĆ --
SET @nazwa_pola_wlasnego='Moje pole';
SET @wartosc_pola_wlasnego='%';
INSERT INTO #pola_wlasne_temp SELECT cat_Id, @wartosc_pola_wlasnego FROM sl_CustomAttributes WHERE cat_Name=@nazwa_pola_wlasnego;
-- KONIEC KOPIOWANIA --



------- KONIEC ZMIAN ------------------------------------------------


SELECT tw1.it_Symbol as 'Symbol towaru', tw1.it_Name as 'Nazwa towaru'
    FROM #pola_wlasne_temp as szukane_pola_wl1
    LEFT JOIN it_CustomAttributes as pole1 on szukane_pola_wl1.poleId=pole1.ia_AttributeId
    RIGHT JOIN it__Item as tw1 on pole1.ia_ItemId=tw1.it_Id
    LEFT JOIN sl_CustomAttributes as pola_slownik1 on pole1.ia_AttributeId=pola_slownik1.cat_Id 
    WHERE pola_slownik1.cat_Name is NULL
    AND tw1.it_Name LIKE @nazwa_towaru
    AND tw1.it_Symbol LIKE @symbol_towaru

[PanBanBan Marcin 185]

I tak dużo pomoże to zapytanie Dziękuję za to co już zrobiłeś

 

Niedługo będę miał okazję sprawdzić zapytanie w akcji bo będę musiał przeszukać towary czy w każdym wstawiłem pola co miałem wstawić.

[birds22 3]

A czy nie wystarczy coś jak poniżej aby wyłapać towary bez pól własnych albo z polami pustymi?

DECLARE @nazwa_pola_wlasnego varchar(100);

-- Tu zmieniamy ---------------------------------------

-- dajemy '%' jako znak zastępujący dowolny ciąg

SET @nazwa_pola_wlasnego='Pierwsze';


SELECT tw.it_Symbol as 'Symbol towaru', tw.it_Name as 'Nazwa towaru'
FROM it__Item as tw
left join
(Select pola_slownik.cat_Name, pole.ia_ItemId,pole.ia_AttributeValue
 FROM it_CustomAttributes as pole 
 LEFT JOIN sl_CustomAttributes as pola_slownik on pole.ia_AttributeId=pola_slownik.cat_Id
 WHERE pola_slownik.cat_Name=@nazwa_pola_wlasnego) pola on pola.ia_ItemId=tw.it_Id
 where pola.ia_ItemId is null 
 --OR  pola.ia_AttributeValue like ''
 
--Jeżeli chcemy wyszukać te towary, które mają wybrane pole własne ale nic tam nie wpisaliśmy to 
 --usuwamy znaki komentarza przed słowem OR

 

Jeżeli chcemy uruchomić zestawienie w Subiekcie (bo będziemy mieli prosty wybór nazwy przeszukiwanego pola to:

SELECT tw.it_Symbol as 'Symbol towaru', tw.it_Name as 'Nazwa towaru'

FROM NAZWA_BAZY_SELLO..it__Item as tw

left join

(Select pola_slownik.cat_Name, pole.ia_ItemId,pole.ia_AttributeValue

  FROM NAZWA_BAZY_SELLO..it_CustomAttributes as pole

  LEFT JOIN NAZWA_BAZY_SELLO..sl_CustomAttributes as pola_slownik on pole.ia_AttributeId=pola_slownik.cat_Id

  WHERE pola_slownik.cat_ID in ({DB:select cat_Id, cat_Name from  NAZWA_BAZY_SELLO..sl_CustomAttributes:Pole własne})

) pola on pola.ia_ItemId=tw.it_Id

  where pola.ia_ItemId is null

  OR  pola.ia_AttributeValue like ''

 

Oczywiście w miejsce NAZWA_BAZY_SELLO wpisujemy nazwę swojej bazy Sello.

[Ks. Robak 314]

Integracja z Subiektem to ciekawy pomysł

 

Cały problem jest w szukaniu wielu pól naraz, stąd tak rozbudowany mój skrypt, a będzie jeszcze bardziej rozbudowany, bo może uda mi się zrobić za pomocą cursora aby były zwracane także nazwy brakujących pól.

 

Oczywiście jestem świadomy, że moje skrypty są pewnie dalekie od optymalizacji  ;D

[birds22 3]

W przypadku zestawień subiekta wystarczy zmienić na:

 

SELECT tw.it_Symbol as 'Symbol towaru', tw.it_Name as 'Nazwa towaru'
FROM NAZWA_BAZY_SELLO..it__Item as tw
left join
(Select pola_slownik.cat_Name, pole.ia_ItemId,pole.ia_AttributeValue
  FROM NAZWA_BAZY_SELLO..it_CustomAttributes as pole 
  LEFT JOIN NAZWA_BAZY_SELLO..sl_CustomAttributes as pola_slownik on pole.ia_AttributeId=pola_slownik.cat_Id
  WHERE pola_slownik.cat_ID in ({CHL_DB:select cat_Id, cat_Name from  NAZWA_BAZY_SELLO..sl_CustomAttributes:Pole własne})
) pola on pola.ia_ItemId=tw.it_Id
  where pola.ia_ItemId is null 
  OR  pola.ia_AttributeValue like ''

[birds22 3]

...

a będzie jeszcze bardziej rozbudowany, bo może uda mi się zrobić za pomocą cursora aby były zwracane także nazwy brakujących pól.

 

Tak na szybko. Nie mam takiej bazy z Sello aby to jakoś sensownie sprawdzić:

 

SELECT x.it_symbol Symbol,
       x.it_name,
       x.brak_pola [brakujace pola]
FROM   (SELECT it_symbol,
               it_name,
               Stuff((SELECT ', ' + cat_name
                      FROM   sl_customattributes sl
                             CROSS JOIN it__item tw
                             LEFT JOIN it_customattributes pola
                                    ON pola.ia_attributeid = sl.cat_id
                                       AND pola.ia_itemid = tw.it_id
                      WHERE  tw.it_id = it.it_id
                             AND pola.ia_attributevalue IS NULL
                      FOR xml path ('')), 1, 2, '') Brak_pola
        FROM   it__item it)x
WHERE  NOT x.brak_pola IS NULL