niedziela, 3 maja 2009

Formater SQL

Dziś znalazłem świetne narzędzie wspomagające pracę:
http://www.dpriver.com/pp/sqlformat.htm
To fomater SQL-a (różne dialekty) z możliwością konstruowania kodu dla
rożnych języków programowania.
Przykład działania poniżej:
SELECT DISTINCT o.offerID as offerID_OR_categoryID,0 as searchType,'offer'
as attributeID,'porady' as word FROM Offers o LEFT JOIN OfferTagAssign ota
ON ota.offerID = o.offerID LEFT JOIN Tags t ON t.tagID = ota.tagID LEFT
JOIN OfferPromoTypeAssign opta ON opta.offerID = o.offerID WHERE
o.beginDatetimeNOW() AND ((o.label LIKE 'porady' OR o.label LIKE 'porady %'
OR o.label LIKE '% porady %' OR o.label LIKE '% porady') OR (t.tag LIKE
'porady' OR t.tag LIKE 'porady %' OR t.tag LIKE '% porady %' OR t.tag LIKE
'% porady') OR (o.offeror LIKE 'porady' OR o.offeror LIKE 'porady %' OR
o.offeror LIKE '% porady %' OR o.offeror LIKE '% porady')) UNION SELECT
o.offerID,1,a.attributeID,'
porady' FROM Offers o LEFT JOIN OfferAttr oa ON
oa.offerID = o.offerID LEFT JOIN Attributes a ON a.attributeID =
oa.attributeID LEFT JOIN AttributeTagAssign ota ON ota.attributeID =
a.attributeID LEFT JOIN Tags t ON t.tagID = ota.tagID LEFT JOIN
OfferPromoTypeAssign opta ON opta.offerID = o.offerID WHERE
o.beginDatetimeNOW() AND ((oa.valueText LIKE 'porady') OR (oa.valueFloat
LIKE 'porady') OR (oa.valueDate LIKE 'porady') OR (oa.valueTime LIKE
'porady') OR (oa.valueInt LIKE 'porady')) 

Po przepuszczeniu zaś wygląda tak:
SELECT DISTINCT o.offerid AS offerid_or_categoryid,
0         AS searchtype,
'offer'   AS attributeid,
'porady'  AS word
FROM   offers o
LEFT JOIN offertagassign ota
ON ota.offerid = o.offerid
LEFT JOIN tags t
ON t.tagid = ota.tagid
LEFT JOIN offerpromotypeassign opta
ON opta.offerid = o.offerid
WHERE  o.Begindatetimenow()
AND ((o.label LIKE 'porady'
OR o.label LIKE 'porady %'
OR o.label LIKE '% porady %'
OR o.label LIKE '% porady')
OR (t.tag LIKE 'porady'
OR t.tag LIKE 'porady %'
OR t.tag LIKE '% porady %'
OR t.tag LIKE '% porady')
OR (o.offeror LIKE 'porady'
OR o.offeror LIKE 'porady %'
OR o.offeror LIKE '% porady %'
OR o.offeror LIKE '% porady'))
UNION 
SELECT o.offerid,
1,
a.attributeid,
'
porady'
FROM   offers o
LEFT JOIN offerattr oa
ON oa.offerid = o.offerid
LEFT JOIN attributes a
ON a.attributeid = oa.attributeid
LEFT JOIN attributetagassign ota
ON ota.attributeid = a.attributeid
LEFT JOIN tags t
ON t.tagid = ota.tagid
LEFT JOIN offerpromotypeassign opta
ON opta.offerid = o.offerid
WHERE  o.Begindatetimenow()
AND ((oa.valuetext LIKE 'porady')
OR (oa.valuefloat LIKE 'porady')
OR (oa.valuedate LIKE 'porady')
OR (oa.valuetime LIKE 'porady')
OR (oa.valueint LIKE 'porady'))

2 komentarze:

  1. http://www.sqlinform.com

    dobre jest to ze zamienia sqlke na stringa ( nie tylko oczywiscie ) ktorego mozna wrzucic odrazu w kod vba

    Tomek

    OdpowiedzUsuń
  2. A ja używam SQLReview i też sobie chwalę :)
    Pozdrawiam,
    MM

    OdpowiedzUsuń