IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Difference bw Select 1 and Select '1'


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Sandeep D. Patil

New User


Joined: 29 Jun 2008
Posts: 23
Location: mumbai

PostPosted: Wed Mar 04, 2009 11:13 am
Reply with quote

Hi,
What is the diffrence between

SELECT 1
INTO :X
FROM TAB1


AND

SELECT '1'
INTO :X
FROM TAB1


Thanks for the anticipation
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Mar 04, 2009 11:36 am
Reply with quote

Hello,

Depending on how :X is defined, one or the other may be invalid and raise an sql error.
Back to top
View user's profile Send private message
Sandeep D. Patil

New User


Joined: 29 Jun 2008
Posts: 23
Location: mumbai

PostPosted: Wed Mar 04, 2009 11:40 am
Reply with quote

Hi,
Consider a case in which X in defined as numeric and in some as char then what would be the diffrence.?
Would it move 1 into X or whould it search for 1 cols in the TAB1

I am getting a bit doubt regarding this

Thanks for the reply
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Wed Mar 04, 2009 3:52 pm
Reply with quote

Consider this query :
Code:

EXEC-SQL
SELECT 1
INTO :X
FROM TAB1
WHERE NAME = 'SANDEEP'
END-EXEC

X is defined as s9(4) comp value zeroes.
Quote:
Would it move 1 into X or whould it search for 1 cols in the TAB1
So, when the Where clause criteria (in this case NAME = 'SANDEEP') qualifies , 1 would get moved to variable X.
Hope this helps,
WTF.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Wed Mar 04, 2009 5:04 pm
Reply with quote

Adding to the previous post ..
Quote:
Consider a case in which X in defined as numeric and in some as char then what would be the diffrence.?

If the Where criteria doesn't qualify then the numeric field will have zeroes and the char field will have spaces and if it does then both will have 1 with spaces or zeroes depending on the pic clause.
Quote:
SELECT 1
INTO :X
FROM TAB1

This would always fetch you 1 in the field X(except when the table is empty ), as there won't be a WHERE clause to qualify.

WTF
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Mar 04, 2009 8:19 pm
Reply with quote

That is merely an existance check. i.e. you are checking if the table contains the value specified in the where clause or not. As most of the programs I've seen in my shop handle the SQLCODE, I think it does not matter whether its 1 or '1' as long as it does not give an error.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 24, 2009 6:21 pm
Reply with quote

Succor wrote:
If the Where criteria doesn't qualify then the numeric field will have zeroes and the char field will have spaces ....


only if before execution of the sql,
the numeric field had zeroes or the x-field had spaces.

if the WHERE clause is not satisfied (or any negative sqlcode),
the host-variable is not populated with any value.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Wed Jun 24, 2009 7:18 pm
Reply with quote

Quote:
if the WHERE clause is not satisfied (or any negative sqlcode),
the host-variable is not populated with any value.

Are you really sure ...will all that experience you have..what will happen when there is -811.

WTF
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 24, 2009 7:20 pm
Reply with quote

Quote:
what will happen when there is -811

depends on the version of db2.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Wed Jun 24, 2009 7:37 pm
Reply with quote

Quote:
depends on the version of db2.

Having said that this doesn't hold true for the latest version i believe i.e. Db2 9.1.
Quote:
if the WHERE clause is not satisfied (or any negative sqlcode),
the host-variable is not populated with any value.

So, in your coming posts please mention the versions your comments refer to...if not the latest ones.This will save people from getting misdirected.

WTF.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 24, 2009 8:04 pm
Reply with quote

as of vsn 8 (an old version) db2 did not guarantee the population of host variables of sql returning an sqlcode of -811.

up to vsn 7 (an even older version), -811 would populate the host variable, but would not guarantee from which row in the resultset.

Quote:
mention the versions your comments refer to...if not the latest ones

unless the question is in version context, I will continue to frame my answers on latest technology.

but this discussion still does not erase the fact (even if you think all your bs does)
that you gave an incorrect answer,
which I corrected
so that people would not be 'misdirected'.

if the select was for 1/'1',
it would either populate with the 1/'1'
or not populate at all;
never with 0's/spaces.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Wed Jun 24, 2009 11:10 pm
Reply with quote

Apologies DBZ ...I couldn't reply to your post as i was in a meeting.
Quote:
unless the question is in version context, I will continue to frame my answers on latest technology.

What about VSN 9...the latest one ..or u think 8 is the latest one.Anyways
Quote:
if the select was for 1/'1',
it would either populate with the 1/'1'
or not populate at all;
never with 0's/spaces.

My quote :
Quote:
If the Where criteria doesn't qualify then the numeric field will have zeroes and the char field will have spaces
And here it goes:
Code:
EXEC-SQL
SELECT '1'
INTO :X
FROM TAB1
WHERE NAME = 'SANDEEP'
END-EXEC

X is defined as PIC X(4), if the filter criteria matches/satisfies variable X will have 1 with three spaces else X will have four spaces.
MY Quote
Quote:
and if it does then both will have 1 with spaces or zeroes depending on the pic clause
You still dont understand or do you think the the host variable would shrink to PIC X(1).
There was no reason to pick a dormant post.In a process to pull someone down YOU end up becoming a bs(always).
You know what ...you are scared of the mirror ..which reveals your actual self...dont run from it ..accept it ..it will be easier for you..you make a mockery of your self infront of every person...exactly the same way you did last time...and more to follow.
WTF.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Jun 25, 2009 12:11 am
Reply with quote

Hello,

Succor wrote:
You still dont understand or do you think the the host variable would shrink to PIC X(1).
Suggest you run some kind of definitive test and post back your results.

succor wrote:
There was no reason to pick a dormant post.In a process to pull someone down
Why would you feel this would "pull someone down" icon_confused.gif

Suggest you focus on technical responses until such time as you (and we) believe it is proper for you to post such diatribes or provide guidance.

DBZ has answered a very large number of these questions with great accuracy and provided considerable help to very many of our users.
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Thu Jun 25, 2009 1:57 am
Reply with quote

Quote:
succor wrote:
There was no reason to pick a dormant post.In a process to pull someone down
Why would you feel this would "pull someone down"
Past Experiences with him.
Quote:
Suggest you run some kind of definitive test and post back your results.

The code
Code:

01 XTESC  PIC X(04) VALUE SPACES.
01 XTESO  PIC X(04) VALUE 'ABCD'.

DISPLAY 'INPUT HOST VARIABLE:'XTESC         
DISPLAY 'INPUT TEMP VARIABLE :'XTESO       
EXEC SQL                                   
SELECT '1' INTO :XTESC FROM                 
TABLE00 WHERE TABLE00_SERV_DTE = '20001018'
END-EXEC                                   
MOVE XTESC TO XTESO                         
DISPLAY 'OUTPUT HOST VARIABLE:'XTESC       
DISPLAY 'OUTPUT TEMP VARIABLE :'XTESO

SYSOUT
Code:

INPUT HOST VARIABLE:     
INPUT TEMP VARIABLE :ABCD
OUTPUT HOST VARIABLE:1   
OUTPUT TEMP VARIABLE :1

The value of the field XTESO get overridden by value 1 and followed by 3 spaces in XTESC.
Dick, I understand what you meant and i appreciate that.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 25, 2009 2:46 am
Reply with quote

Succor,

Quote:
X is defined as PIC X(4), if the filter criteria matches/satisfies variable X will have 1 with three spaces else X will have four spaces.

never contested that.

my issue with your post was if the sqlcode was +100 or <0.
Quote:
If the Where criteria doesn't qualify then the numeric field will have zeroes and the char field will have spaces


if it had been populated with 'abcd' before the sql,
it would still contain 'abcd' after the sql if the sqlcode was +100 or <0.

This is the same type of arguement we always have.
I point out something incorrect in your post and you have a tantrum.
Back to top
View user's profile Send private message
aishwarya_20

New User


Joined: 19 Nov 2008
Posts: 57
Location: pune

PostPosted: Thu Jun 25, 2009 12:22 pm
Reply with quote

SELECT 1 will select the first column value of the table and that value will be moved to host variable.

SELECT '1' will move 1 to the host variable.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 25, 2009 3:33 pm
Reply with quote

aishwarya_20

you should rethink and modify your answer
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Thu Jun 25, 2009 5:36 pm
Reply with quote

Quote:
if it had been populated with 'abcd' before the sql,
This is obvious,I reckon it was more of a process issue as I go by initializing the variables and never thought this could be a point of such a baseless discussion. And then you wrote :
Quote:
if the WHERE clause is not satisfied (or any negative sqlcode),
the host-variable is not populated with any value.
This was something which is not always true ...and it triggered another set of arguments.
Probably next time i'll PM you and ask that "is it the actual problem" or "am i looking at a different picture."
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Timestamp difference and its average ... DB2 11
No new posts SELECT from data change table DB2 5
No new posts Select two different counts from SQL... DB2 6
No new posts Difference when accessing dataset in ... JCL & VSAM 7
Search our Forums:

Back to Top