How to shortening this sql code?

Question

How to shortening this sql code?

HerryMarkowitz

SSC Journeyman

Points: 90
More actions
July 10, 2015 at 1:08 am

#309799

Hi beautiful people,
Following code is okey.
SELECT
CODE,
NAME,
[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)
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
As you can realised the code above runs from INVENNO 101 to INVENNO 107.
So, the question is how to shortening code above...
Thanks in advance...
-------------------------------------------------------------------------------------
[font="Comic Sans MS"]I am vba expert not SQL. HerryMarkowitz[/font]
---------------------------------------------------------------------------------------

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

kyagi.jo SSCrazy Points: 2559 More actions · Answer 1

you should be able to left outer join to the table multiple times, which will make the code look cleaner. Something like:

SELECT

CODE,

NAME,

ISNULL(gn1.ONHAND,0) [FORUM AYDIN],

ISNULL(gn2.ONHAND,0) [BURSA CARREFOUR],

ISNULL(gn3.ONHAND,0) [FORUM BORNOVA],

ISNULL(gn4.ONHAND,0) [FORUM ÇAMLIK],

ISNULL(gn5.ONHAND,0) [ÇANKIRI YUNUS],

ISNULL(gn6.ONHAND,0) [ESPARK DÖNER],

ISNULL(gn7.ONHAND,0) [ESPARK]

FROM LG_015_ITEMS I

LEFT OUTER JOIN LV_015_01_GNTOTST GN1 ON GN.STOCKREF=I.LOGICALREF AND INVENNO = 101

LEFT OUTER JOIN LV_015_01_GNTOTST GN2 ON GN.STOCKREF=I.LOGICALREF AND INVENNO = 102

LEFT OUTER JOIN LV_015_01_GNTOTST GN3 ON GN.STOCKREF=I.LOGICALREF AND INVENNO = 103

LEFT OUTER JOIN LV_015_01_GNTOTST GN4 ON GN.STOCKREF=I.LOGICALREF AND INVENNO = 104

LEFT OUTER JOIN LV_015_01_GNTOTST GN5 ON GN.STOCKREF=I.LOGICALREF AND INVENNO = 105

LEFT OUTER JOIN LV_015_01_GNTOTST GN6 ON GN.STOCKREF=I.LOGICALREF AND INVENNO = 106

WHERE ACTIVE=0

GROUP BY CODE,NAME,I.LOGICALREF

ORDER BY CODE

Edit: noticed your "group by", not sure on your exact requirements, as you may get multiple records returned, depending on what your tables look like, did you just want the first entry? If not, you can add your extra columns to the group by statements

HerryMarkowitz SSC Journeyman Points: 90 More actions · Answer 2

Hi kyagi.jo,

Thanks for re-organized that code.

But my main question is about how to shorten that code.

I mean I dont want to enter every line from INVENNO 101 to INVENNO 107 into the code.

In other words I want a code like following;

for i=101 to 107

'do something

next i

Thanks in advance...

-------------------------------------------------------------------------------------
[font="Comic Sans MS"]I am vba expert not SQL. HerryMarkowitz[/font]
---------------------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

HerryMarkowitz (7/10/2015)
Hi kyagi.jo,
Thanks for re-organized that code.
But my main question is about how to shorten that code.
I mean I dont want to enter every line from INVENNO 101 to INVENNO 107 into the code.
In other words I want a code like following;
for i=101 to 107
'do something
next i
Thanks in advance...

This looks like a standard crosstab query, try these:

SELECT

i.CODE,

i.NAME,

[FORUM AYDIN]= SUM(CASE WHEN gn.INVENNO = 101 THEN gn.ONHAND ELSE 0 END),

[BURSA CARREFOUR]= SUM(CASE WHEN gn.INVENNO = 102 THEN gn.ONHAND ELSE 0 END),

[FORUM BORNOVA]= SUM(CASE WHEN gn.INVENNO = 103 THEN gn.ONHAND ELSE 0 END),

[FORUM ÇAMLIK]= SUM(CASE WHEN gn.INVENNO = 104 THEN gn.ONHAND ELSE 0 END),

[ÇANKIRI YUNUS]= SUM(CASE WHEN gn.INVENNO = 105 THEN gn.ONHAND ELSE 0 END),

[ESPARK DÖNER]= SUM(CASE WHEN gn.INVENNO = 106 THEN gn.ONHAND ELSE 0 END),

[ESPARK]= SUM(CASE WHEN gn.INVENNO = 107 THEN gn.ONHAND ELSE 0 END)

FROM LG_015_ITEMS i

INNER JOIN LV_015_01_GNTOTST gn

ON gn.STOCKREF = i.LOGICALREF

WHERE ?.ACTIVE = 0 -- replace ? with correct table alias

GROUP BY i.CODE, i.NAME --, I.LOGICALREF

ORDER BY i.CODE

SELECT

i.CODE,

i.NAME,

i.LOGICALREF,

[FORUM AYDIN]= SUM(CASE WHEN gn.INVENNO = 101 THEN gn.ONHAND ELSE 0 END),

[BURSA CARREFOUR]= SUM(CASE WHEN gn.INVENNO = 102 THEN gn.ONHAND ELSE 0 END),

[FORUM BORNOVA]= SUM(CASE WHEN gn.INVENNO = 103 THEN gn.ONHAND ELSE 0 END),

[FORUM ÇAMLIK]= SUM(CASE WHEN gn.INVENNO = 104 THEN gn.ONHAND ELSE 0 END),

[ÇANKIRI YUNUS]= SUM(CASE WHEN gn.INVENNO = 105 THEN gn.ONHAND ELSE 0 END),

[ESPARK DÖNER]= SUM(CASE WHEN gn.INVENNO = 106 THEN gn.ONHAND ELSE 0 END),

[ESPARK]= SUM(CASE WHEN gn.INVENNO = 107 THEN gn.ONHAND ELSE 0 END)

FROM LG_015_ITEMS i

INNER JOIN LV_015_01_GNTOTST gn

ON gn.STOCKREF = i.LOGICALREF

WHERE ?.ACTIVE = 0 -- replace ? with correct table alias

GROUP BY i.CODE, i.NAME, i.LOGICALREF

ORDER BY i.CODE

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HerryMarkowitz SSC Journeyman Points: 90 More actions · Answer 4

Hi again,

Thanks for the codes.

But your codes are not what I want.

Maybe I am not able to explain my needs because I am very new with SQL coding.

Let me ask my question different way;

I want a code which includes only INVENNO 101 and INVENNO 107 lines.

I dont want a code which includes INVENNO 102, 103, 104, 105, 106 lines.

So, I need a code which will run from INVENNO 101 to INVENNO 107.

I need a loop code which will run from INVENNO 101 to INVENNO 107.

Sorry, if I am ridiculous...

-------------------------------------------------------------------------------------
[font="Comic Sans MS"]I am vba expert not SQL. HerryMarkowitz[/font]
---------------------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

HerryMarkowitz (7/10/2015)
Hi again,
Thanks for the codes.
But your codes are not what I want.
Maybe I am not able to explain my needs because I am very new with SQL coding.
Let me ask my question different way;
I want a code which includes only INVENNO 101 and INVENNO 107 lines.
I dont want a code which includes INVENNO 102, 103, 104, 105, 106 lines.
So, I need a code which will run from INVENNO 101 to INVENNO 107.
I need a loop code which will run from INVENNO 101 to INVENNO 107.
Sorry, if I am ridiculous...

You are contradicting yourself in this explanation, can you please clarify?

Have you executed the queries which have been posted?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

I would listen to Chris. The one think you certainly do not want here is a loop. The length of the query does not determine the execution speed. I can write short queries that are incredibly inefficient or some long ones that run very quickly. Don't be intimidated by the length of the query. Instead, focus on it working right and performing well. Making it pretty will also make it more maintainable.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

HerryMarkowitz SSC Journeyman Points: 90 More actions · Answer 7

I am so sorry but my English is not perfect.

Let me tell you what I understand;

A loop code is possible but not efficient.

Is that what you told?

-------------------------------------------------------------------------------------
[font="Comic Sans MS"]I am vba expert not SQL. HerryMarkowitz[/font]
---------------------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

HerryMarkowitz (7/10/2015)
I am so sorry but my English is not perfect.
Let me tell you what I understand;
A loop code is possible but not efficient.
Is that what you told?

The code I posted should be a logical equivalent to the code you posted. I can't tell the aggregation level which is why I posted two versions. Run both, one of them should return the results you are expecting.

However:

The explanation you gave later doesn't match your query and is ambiguous:

I want a code which includes only INVENNO 101 and INVENNO 107 lines.
I dont want a code which includes INVENNO 102, 103, 104, 105, 106 lines.
So, I need a code which will run from INVENNO 101 to INVENNO 107.
I need a loop code which will run from INVENNO 101 to INVENNO 107.

Do you want INVENNO 102, 103, 104, 105, 106 lines or not?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HerryMarkowitz SSC Journeyman Points: 90 More actions · Answer 9

I want INVENNO 101, 102, 103, 104, 105, 106, 107.

But I dont want to see 102, 103, 104, 105, 106 lines in the sql code.

I want a loop code running from 101 to 107.

-------------------------------------------------------------------------------------
[font="Comic Sans MS"]I am vba expert not SQL. HerryMarkowitz[/font]
---------------------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

HerryMarkowitz (7/10/2015)
I want INVENNO 101, 102, 103, 104, 105, 106, 107.
But I dont want to see 102, 103, 104, 105, 106 lines in the sql code.
I want a loop code running from 101 to 107.

OK I understand now.

Do you have a column which holds the values [FORUM AYDIN] etc. in the table LV_015_01_GNTOTST?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

HerryMarkowitz (7/10/2015)
I am so sorry but my English is not perfect.

No problem. Fortunately, SQL works in any language. 😉

HerryMarkowitz (7/10/2015)
A loop code is possible but not efficient.
Is that what you told?

Yes, that is exactly right. A loop is very inefficient.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

HerryMarkowitz SSC Journeyman Points: 90 More actions · Answer 12

ChrisM@Work (7/10/2015)
HerryMarkowitz (7/10/2015)
I want INVENNO 101, 102, 103, 104, 105, 106, 107.
But I dont want to see 102, 103, 104, 105, 106 lines in the sql code.
I want a loop code running from 101 to 107.
OK I understand now.
Do you have a column which holds the values [FORUM AYDIN] etc. in the table LV_015_01_GNTOTST?

Yes, it is.

-------------------------------------------------------------------------------------
[font="Comic Sans MS"]I am vba expert not SQL. HerryMarkowitz[/font]
---------------------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

HerryMarkowitz (7/10/2015)
ChrisM@Work (7/10/2015)
HerryMarkowitz (7/10/2015)
I want INVENNO 101, 102, 103, 104, 105, 106, 107.
But I dont want to see 102, 103, 104, 105, 106 lines in the sql code.
I want a loop code running from 101 to 107.
OK I understand now.
Do you have a column which holds the values [FORUM AYDIN] etc. in the table LV_015_01_GNTOTST?
Yes, it is.

You're going to make me guess what the column is called? 😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HerryMarkowitz SSC Journeyman Points: 90 More actions · Answer 14

[/quote]

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?

-------------------------------------------------------------------------------------
[font="Comic Sans MS"]I am vba expert not SQL. HerryMarkowitz[/font]
---------------------------------------------------------------------------------------