July 10, 2015 at 7:42 am
HerryMarkowitz (7/10/2015)
Yes, that is exactly right. A loop is very inefficient.[/quote]
How percent inefficent?
I mean if I calculate both method how long they takes to complete?[/quote]
That depends on what you're doing and what set-based methods are available to perform the same work.
The first step is to make it work, so I would focus on answering Chris's question about the column name.
July 10, 2015 at 8:05 am
Ed Wagner (7/10/2015)
Yes, that is exactly right. A loop is very inefficient.HerryMarkowitz (7/10/2015)
How percent inefficent?I mean if I calculate both method how long they takes to complete?
Sorry, my friend, but if your originally posted SQL actually works, and works correctly, there's no way to avoid mention of each individual value in at least the SELECT statement, although you may be able to use a BETWEEN 101 AND 107 on a JOIN condition to that table, or in the WHERE clause, but eliminating the mention of individual values just isn't going to happen. There's no amount logic that can compensate. Even using a PIVOT query instead of a crosstab isn't going to eliminate that need. At some point, each resulting field is going to need to be mentioned along with it's corresponding numeric value. You don't need a subquery to derive each field in the SELECT, but you still need to reference each and every value no matter what you do. Please also recognize that SQL isn't a procedural language, it's a SET-BASED language, so you need to stop thinking procedurally and stop thinking about what to do to a row, but instead, what to do to a column. It's a signficant paradigm shift for many new developers, but the sooner you change how you think about it, the sooner that you'll get better at it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2015 at 8:22 am
All right,
Following is my original code.
What I want is shorten following code with loop code if it is possible and if it is efficient!
SELECT
CODE,
NAME,
[GENEL TOPLAM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=-1),0),
[FORUM AYDIN]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=101),0),
[BURSA CARREFOUR]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=102),0),
[FORUM BORNOVA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=103),0),
[FORUM ÇAMLIK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=104),0),
[ÇANKIRI YUNUS]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=105),0),
[ESPARK DÖNER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=106),0),
[ESPARK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=107),0),
[FORUM GAZÝANTEP]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=108),0),
[GEBZE CENTER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=109),0),
[FORUM KAYSERÝ]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=110),0),
[SERA KÜTAHYA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=111),0),
[KAYSERÝ PARK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=112),0),
[MARK ANTALYA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=113),0),
[FORUM MERSÝN]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=114),0),
[PALLADÝUM ANTAKYA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=115),0),
[SÜMERPARK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=116),0),
[FORUM TRABZON]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=117),0),
[TERRACÝYT]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=118),0),
[ZAFER PLAZA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=119),0),
[CEYLANPARK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=120),0),
[SEMBOL KOCAELÝ ÝSK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=121),0),
[ARMADA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=201),0),
[ANKUVA BURGER HAUSE]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=202),0),
[ANKAMALL DEVE]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=203),0),
[ANKUVA ÝSK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=204),0),
[ANATOLÝUM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=205),0),
[ANKUVA TADIM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=206),0),
[ANKAMALL TADIM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=207),0),
[BATIKENT CARREFOUR]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=208),0),
[ÝVEDÝK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=209),0),
[MEÞRUTÝYET]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=210),0),
[OPTÝMUM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=211),0),
[SAKARYA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=212),0),
[SÝNCAN]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=213),0),
[TAURUS ÝSK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=214),0),
[TAURUS TADIM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=215),0),
[ZÝYA GÖKALP]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=216),0),
[ZÝRVE KENT]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=217),0),
[ANKAMALL ÝSKENDER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=218),0),
[ANKAMALL DÖNER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=219),0),
[KENTPARK ÝSKENDER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=220),0),
[GORDÝON]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=221),0),
[NECATÝBEY ANK ÝSK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=222),0),
[B.PAÞA DÖNER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=301),0),
[FORUM B.PAÞA ÝSK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=302),0),
[BEYLÝDÜZÜ MÝGROS]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=303),0),
[CAPACÝTY]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=304),0),
[CAPÝTOL]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=307),0),
[CEVAHÝR]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=308),0),
[FORUM MARM.DÖNER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=309),0),
[FORUM MARMARA ÝSKENDER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=310),0),
[GALERÝA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=311),0),
[HÝSTORÝA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=312),0),
[ÝÇERENKÖY CARREFOUR]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=313),0),
[KADIKÖY RIHTIM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=314),0),
[MALTEPE CARF.]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=315),0),
[MARM.PARK DÖNER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=316),0),
[METROCÝTY]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=317),0),
[MARMARAPARK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=318),0),
[MALTEPE ÇARÞI]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=319),0),
[MARM.PARK TADIM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=320),0),
[NAUTÝLUS]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=321),0),
[OLÝVÝUM]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=322),0),
[NEOMARÝN PENDÝK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=323),0),
[SEFAKÖY ARMONÝPARK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=324),0),
[TRUMP TOWER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=325),0),
[ÜMR.MEYD.DÖNER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=327),0),
[VÝAPORT]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=328),0),
[ZEYTÝNBURNU]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=329),0),
[CANPARK ÝSK]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=330),0),
[CANPARK DÖNER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=331),0),
[KALE CENTER]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=332),0),
[MALL OF ÝSTANBUL]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=333),0),
[AKASYA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=334),0),
[AKBATI]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=335),0),
[BUYAKA]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=336),0),
[HDTD LTD.ÞTÝ.]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=401),0),
[PÝDEM A.Þ.]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=402),0),
[HD LTD.ÞTÝ.]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=403),0),
[MERKEZ]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=500),0),
[OSTÝM DEPO]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=501),0),
[ET ÝÞLEME]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=502),0),
[ÝSTANBUL OFÝS]=ISNULL((SELECT ONHAND FROM LV_015_01_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO=503),0)
FROM LG_015_ITEMS I
INNER JOIN LV_015_01_GNTOTST GN ON GN.STOCKREF=I.LOGICALREF
WHERE ACTIVE=0
GROUP BY CODE,NAME,I.LOGICALREF
ORDER BY CODE
-------------------------------------------------------------------------------------
[font="Comic Sans MS"]I am vba expert not SQL. HerryMarkowitz[/font]
---------------------------------------------------------------------------------------
July 10, 2015 at 12:05 pm
It's possible, to do this using dynamic sql.
The main problem is to know which are the possible values for INVENNO and what column name will they represent. If you can provide that information, we could help you get those 84 columns with less effort and in a way that will adapt to any changes.
Try to read the following article as well: http://www.sqlservercentral.com/articles/Crosstab/65048/
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply