Code400 -The Support Alternative - SQL http://www.code400.com/forum/ command line and embedded sql en Fri, 10 Sep 2010 03:48:12 GMT vBulletin 60 http://www.code400.com/forum/images/misc/rss.png Code400 -The Support Alternative - SQL http://www.code400.com/forum/ SQL Update Null Value http://www.code400.com/forum/showthread.php/9649-SQL-Update-Null-Value?goto=newpost Wed, 08 Sep 2010 19:45:58 GMT Can someone explain this to me?

PHP Code:
Update TOFILE A                      
 Set ( iGRP3, iGRP4 )                
   = ( Select coalesce( iGRP3, ' ' ),
              coalesce( iGRP4, ' ' ) 
         from FROMFILE B             
        Where A.iACTV  = B.iACTV     
          and A.iSTOR  = B.iSTOR     
          and A.iITEM  = B.iITEM  ) 
Gives me the message:

Null values not allowed in column or variable IGRP3.

I'm forcing the value to be either the actual value or *blank...

What gives??

:duh ]]> SQL FaStOnE http://www.code400.com/forum/showthread.php/9649-SQL-Update-Null-Value <![CDATA[my lastest and greatest "Stupid SQL Tricks"]]> http://www.code400.com/forum/showthread.php/9647-my-lastest-and-greatest-quot-Stupid-SQL-Tricks-quot?goto=newpost Wed, 08 Sep 2010 17:22:43 GMT is it not possible to use with in an insert?
Code:
Insert Into QTemp/FinTotals (                                     
  With t1 As (                                                   
  Select Cast(' ' As Char(1)) As Reg,                             
  Cast(' ' as Char(7)) As CustNo,Cast(' ' As Char(30)) As         
  CustName,Cast(Sum( Invoiced ) As Dec(15,2)) As Invoiced,       
  Cast(Sum( PayOff ) As Dec(15,2)) As PayOff, Cast(Sum(Payment) As
  Dec(15,2)) As Payment,Cast(Sum(Balance ) As Dec(15,2)) As     
  Balance                                                         
  From RO1050XL_1)                                               
  Select a.Reg,a.CustNo,a.CustName,a.Invoiced,a.PayOff,a.Payment, 
  Case When (a.Payment <> 0 And a.PayOff <> 0) Then               
  Cast((((a.Payment / a.PayOff) * 100))                           
  As Dec(15,2))                                                 
  Else 0 End As Percent,a.Balance From t1 a                       
);
if not...then why the hell not???? please save me...it's dark in here:duh ]]> SQL tomholden http://www.code400.com/forum/showthread.php/9647-my-lastest-and-greatest-quot-Stupid-SQL-Tricks-quot Adding new member in SQL table http://www.code400.com/forum/showthread.php/9580-Adding-new-member-in-SQL-table?goto=newpost Mon, 23 Aug 2010 07:13:06 GMT Hi Folks, There is SQl table , i need to add a new member in that table. For that purpose i am trying with ADDPFM but in this case an error occures. Is there any way to add new member in SQL table?? Pramendra Hi Folks,

There is SQl table , i need to add a new member in that table. For that purpose i am trying with ADDPFM but in this case an error occures.

Is there any way to add new member in SQL table??

Pramendra ]]> SQL pramendra http://www.code400.com/forum/showthread.php/9580-Adding-new-member-in-SQL-table CASE / IF in SQL http://www.code400.com/forum/showthread.php/9568-CASE-IF-in-SQL?goto=newpost Thu, 19 Aug 2010 12:13:24 GMT Hej Im new to query on as400. And i would be greatfull if you guys could help. Im trying to create a query where i want the sql to calculate for me. I have a field that determins if the coustomer pays 1/2/4 times a year. And another field that shows the price So i want it to calculate... Hej

Im new to query on as400.
And i would be greatfull if you guys could help.

Im trying to create a query where i want the sql to calculate for me.
I have a field that determins if the coustomer pays 1/2/4 times a year.
And another field that shows the price
So i want it to calculate something like
IF PPFOF (nummer of times the customer pays) = 2
Then PPPRM (price) * PPFOF

Can i do that? ]]> SQL SinCityLegend http://www.code400.com/forum/showthread.php/9568-CASE-IF-in-SQL Embeded SQL FETCH FIRST x ROWS http://www.code400.com/forum/showthread.php/9565-Embeded-SQL-FETCH-FIRST-x-ROWS?goto=newpost Wed, 18 Aug 2010 17:13:40 GMT Can we use a variable value for the FETCH FIRST x ROWS? Something like: PHP: --------- Select Name, Address, City, State, Zip, Phone From MyTable Where Zip = 85024 Can we use a variable value for the FETCH FIRST x ROWS?

Something like:

PHP Code:
Select Name, Address, City, State, Zip, Phone
From   MyTable
Where Zip = 85024
Fetch First :Num Rows Only; 
If not, how could this be accomplished?

(Sorry, working in COBOL and trying to help a colleague)

Thanks! ]]> SQL FaStOnE http://www.code400.com/forum/showthread.php/9565-Embeded-SQL-FETCH-FIRST-x-ROWS Determine library or schema creation http://www.code400.com/forum/showthread.php/9558-Determine-library-or-schema-creation?goto=newpost Tue, 17 Aug 2010 13:45:08 GMT Hi All, How can we determine from the library object that it is created via SQL or AS400 CRTLIB command? I have created library by two ways 1) SQL - Create Schema 2) AS400 - CRTLIB Now from the object MyLib how we can determine the difference whether it is created by SQL or AS400? Here I... Hi All,

How can we determine from the library object that it is created via SQL or AS400 CRTLIB command?
I have created library by two ways
1) SQL - Create Schema
2) AS400 - CRTLIB

Now from the object MyLib how we can determine the difference whether it is created by SQL or AS400?
Here I can see only the text difference in both the objects,
If it is created by SQL then text will be 'created by SQL' & if it is created with CRTLIB then user text will come. Apart from this is there any difference to determine? ]]> SQL Nil http://www.code400.com/forum/showthread.php/9558-Determine-library-or-schema-creation Using AS to correlate a table http://www.code400.com/forum/showthread.php/9550-Using-AS-to-correlate-a-table?goto=newpost Mon, 16 Aug 2010 05:29:59 GMT Some days I hate SQL! The following statement works fine in interactive SQL: Code: --------- SELECT i1.odlbnm,i1.odobnm,i1.odobtp,i1.odobat,i1.odcdat, i3.odobnm,i3.odobtp,i3.odobat,i3.odcdat Some days I hate SQL!

The following statement works fine in interactive SQL:

Code:
SELECT         
i1.odlbnm,i1.odobnm,i1.odobtp,i1.odobat,i1.odcdat,                   
i3.odobnm,i3.odobtp,i3.odobat,i3.odcdat                             
FROM fmpscsrco AS i1 FULL OUTER JOIN fmpsctgto AS i3                 
ON i1.odlbnm=i3.odlbnm and i1.odobnm=i3.odobnm AND i1.odobtp=i3.odobtp
But when I precede it with

Code:
DECLARE c1 CURSOR FOR
...and embed it in some RPG I get a complaint about the AS not being expected.

Is this another feature that only works sometimes, depending on where you're using SQL? I guess I could just correlate the field names with the full file name and rue the day I chose such a long name!

Any tips appreciated. ]]> SQL zkarj http://www.code400.com/forum/showthread.php/9550-Using-AS-to-correlate-a-table quick question for counting unique order numbers http://www.code400.com/forum/showthread.php/9518-quick-question-for-counting-unique-order-numbers?goto=newpost Thu, 05 Aug 2010 19:11:53 GMT I have a file that contains order numbers. Some of them are repeated. I want to run a quick SQL to grab the count of unique order numbers in the file. I tried this, but it says I have a syntax error: Code: --------- SELECT COUNT(*) FROM (SELECT DISTINCT ORDER# FROM FILE1) ---------... I have a file that contains order numbers. Some of them are repeated. I want to run a quick SQL to grab the count of unique order numbers in the file. I tried this, but it says I have a syntax error:

Code:

SELECT COUNT(*) FROM (SELECT DISTINCT ORDER# FROM FILE1)
I know it's something simple, I just don't see it. ]]> SQL soup_dog http://www.code400.com/forum/showthread.php/9518-quick-question-for-counting-unique-order-numbers SET SCHEMA in SQL stored procedure http://www.code400.com/forum/showthread.php/9509-SET-SCHEMA-in-SQL-stored-procedure?goto=newpost Thu, 05 Aug 2010 05:23:59 GMT This is driving me absolutely batty. I've spent hours searching for a solution and not found one. It relates to the following procedure:

Code:
create procedure copy_user_ids                                   
(in lib char(10), fromuser char(10), touser char(10))           
language sql modifies sql data                                   
begin                                                           
  SET SCHEMA = lib;                                             
  insert into plusdf00                                           
  select touser, usevar, usval, usenvt, substr(SESSION_USER,1,10),
  year(now())*10000+month(now())*100+day(now()),                 
  hour(now())*10000+minute(now())*100+second(now())             
  from plusdf00                                                 
  where ususid = fromuser and usenvt = 'USER_IDS';               
end
The procedure creates just fine, but the SET SCHEMA statement appears to do absolutely nothing. No matter what I provide as a parameter value on the CALL statement, the file is never found unless it is somewhere in my library list.

The SQL Reference manual clearly shows the use of SET SCHEMA within a SQL-defined stored procedure, so as far as I can tell it should work.

I've tried all sorts of variations including specifying DYNDFTCOL, putting the variable as a qualifier and using *SYS versus *SQL naming conventions (which for the rest of the procedure content doesn't matter at all). Nothing changes the behaviour, though many times I made changes which stopped the procedure creating.

I am utterly stuck now. All I need to do - through whatever means possible - is allow for the library name to be specified on the CALL statement on an interactive SQL command line.

So much for my "this'll be a quick solution" approach to the problem. Oh, I know there are numerous ways to skin this cat, but I thought I should learn a new skill and create my first stored procedure seeing as I could easily express the solution as an SQL statement.

Any help appreciated. ]]> SQL zkarj http://www.code400.com/forum/showthread.php/9509-SET-SCHEMA-in-SQL-stored-procedure Need a Dynamic Insert Example http://www.code400.com/forum/showthread.php/9489-Need-a-Dynamic-Insert-Example?goto=newpost Thu, 29 Jul 2010 12:35:31 GMT Does anyone have one? Need to pass a Library and File name to a program and insert records into that table. Just curious if anyone has an example... Does anyone have one?

Need to pass a Library and File name to a program and insert records into that table. Just curious if anyone has an example... ]]> SQL FaStOnE http://www.code400.com/forum/showthread.php/9489-Need-a-Dynamic-Insert-Example