View previous topic :: View next topic
|
Author |
Message |
Sandeep D. Patil
New User
Joined: 29 Jun 2008 Posts: 23 Location: mumbai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Depending on how :X is defined, one or the other may be invalid and raise an sql error. |
|
Back to top |
|
|
Sandeep D. Patil
New User
Joined: 29 Jun 2008 Posts: 23 Location: mumbai
|
|
|
|
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 |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
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 |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
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 |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
what will happen when there is -811 |
depends on the version of db2. |
|
Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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"
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 |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
aishwarya_20
New User
Joined: 19 Nov 2008 Posts: 57 Location: pune
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
aishwarya_20
you should rethink and modify your answer |
|
Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
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 |
|
|
|