Manažerské rozhraní - funkce IQQUERY

[jana.krapkova]

Ve funkci IQQUERY musí být
- SELECT v uvozovkách
- odkaz na buňky a text SELECTu spojen pomocí &
- textové údaje uzavřeny v apostrofech


Příklad:

=IQQUERY("SELECT VychoziMnozstvi FROM TabKmenZbozi WHERE SkupZbo='"&B21&"' AND RegCis='"&C21&"'")

[jan.novotny]

Pokud je třeba do funkce IQQUERY vložit dlouhý SELECT, může se stát, že Excel při ukládání buňky hlásí "Vzorec je příliš dlouhý" a buňku nelze uložit.
Jednoduché řešení je sestavit požadovaný SELECT se všemi potřebnými tabulkami a atributy jako definovaný přehled v Heliosu (popř. VIEW na SQL serveru) a pomocí IQQUERY se dotazovat do tohoto přehledu (VIEW). Vzhledem k možnosti pojmenovat atributy přehledu (VIEW) vlastními aliasy, lze takto zkrátit délku výsledného SELECTu předaného do IQQUERY na minimum.

Druhou možností je přímo v Excelu rozložit do několika buněk potřebný SELECT a pomocí fce CONCATENATE vyskládat výsledek do fce IQQUERY. Toto ovšem znamená udržovat přímo v Excelu další list nebo oblast buňek pro sestavení požadovaných dotazů.

[jana.krapkova]

Může se také stát, že funkce v excelu odkazuje na buňky, jejichž výsledek je dlouhý řetězec.

Příklad:
v buňce A2 ... "=IQDENIK("Z";"H";"UCET=" & A3)"
v buňce A3 ... "=IQVYCET(B1:B200)"
v B2:B200 .... čísla účtů z účtové osnovy

Výsledek:
v buňce A3 je seznam účtů oddělený čárkou
v A2 je #HODNOTA!

V tomto případě excel nezahlásí chybu, rozšířená kontrola parametrů nezahlásí chybu, ale výsledek je chyba ze stejného důvodu jako v předchozím příspěvku - výsledný řetězec pro vyhodnocení buňky A2 je moc dlouhý!!!

Jediné osvědčené řešení v tomto případě je jít na věc jinudy např. použít IQQUERY

[jana.krapkova]

Pokud je v parametru funkce MR uvedeno více hodnot jedné řady, pak je možno výčet zapsat zkráceným zápisem. tj pomocí dvojtečky. Parametry zadávané odkazem na buňku nebo buňky, je nutno svázat pomocí operátoru & (sčítání řetězců).

Příklad:
funkce má vrátit zůstatek účtů začínajících 5 za zápisy s datem případu od 1.1.2007 do 31.1.2007
=IQDENIK("Z";"H";"UCET=5%";"DATP=1.1.2007:31.1.2007" )

pokud počáteční datum je v buňce A1 a konečné v buňce B1
=IQDENIK("Z";"H";"UCET=5%";"DATP=" &A1&":"&B1 )

pokud buňky s daty jsou na jiném listě, např.List2
=IQDENIK("Z";"H";"UCET=5%";"DATP=" &List2!A1&":"&List2!B1 )

[michal.stepanek]

Pokud by někoho zajímalo, proč je a není omezená délka argumentu "vlastní" funkce (VBA) - našel jsem tuhle diskusi http://www.vbforums.com/archive/index.php/t-252750.html. Pokud jsem správně pochopil, omezení 255 znaků platí, jenom pokud je použita excel funkce pro sestavení argumentu.

Já mám zvyk vytvářet si query jako vzor s "tagy" a ty pak nahrazovat hodnotami. Pokud používá někdo podobný způsob, lze originální IQQUERY obalit vlastní funkcí a obejít tak omezení délky argumentu.

Kód: Vybrat vše

Public Function IQQUERYWithReplace(ByVal rawQuery As String, _
    Optional ByVal co1 As String, Optional ByVal cim1 As String, _
    Optional ByVal co2 As String, Optional ByVal cim2 As String, _
    Optional ByVal co3 As String, Optional ByVal cim3 As String, _
    Optional ByVal co4 As String, Optional ByVal cim4 As String, _
    Optional ByVal co5 As String, Optional ByVal cim5 As String, _
    Optional ByVal co6 As String, Optional ByVal cim6 As String _
) As Variant
    Dim query As String

    query = Replace(Replace(Replace(rawQuery, co1, cim1), co2, cim2), co3, cim3)
    query = Replace(Replace(Replace(query, co4, cim4), co5, cim5), co6, cim6)
 
    IQQUERYWithReplace = Application.Run("IQQuery", query)
End Function

Množství argumentů této funkce lze samozřejmě libovolně rozšiřovat.

V Excelu pak například:

Kód: Vybrat vše

=IQQUERYWithREPLACE(DotazFaktury;"%Atribut%";E$4;"%OBDOBI%";OBDOBI;"%SKP%";$D7)

, kde DotazFaktury je

Kód: Vybrat vše

SELECT isNull(Sum(%Atribut%),0)
FROM TabPohybyZbozi TPZ
JOIN TabDokladyZbozi Dok ON Dok.ID=TPZ.IDDoklad
JOIN TabKmenZbozi Kmen ON Kmen.ID=(SELECT IDKmenZbozi FROM TabStavSkladu WHERE ID=TPZ.IDZboSklad)
WHERE Dok.IdObdobiStavu=%OBDOBI% AND Kmen.SKP in(%SKP%) AND(Dok.DruhPohybuZbo>=13)AND(Dok.DruhPohybuZbo<=14)