How to create a SQL view of the file

280 pts.
Tags:
AS/400
AS/400 SQL View
How to create a SQL VIEW of the file to present packed or binary values as characters? I know we can create an sql view using CREATE VIEW library/view1 AS SELECT * FROM library/filename.  But can anyone tell me how to present packed or binary values as characters using this? I used the command cast but it threw error keyword cast not expected. My As400 version is V4R2. I gave command CREATE VIEW palib/sijiq AS SELECT GGMID as GGMID, cast(GCMPNY as char(2)) as GCMPNY,GPCNTR as GPCNTR,GACC as GACC, GRP1 as GRP1, GRP2 as GRP2, GRP3 as GRP3,GMDESC as GMDESC, cast(GPD1 as char(8)) as GPD1,cast(GPD2 as char(8)) as GPD2 from bpcsfe2/ggm. It throws me "Keyword CAST not expected. Valid tokens: , FROM INTO." Sometimes getting error "Column list required for CREATE VIEW". Is there any thing else I need to do?

Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

create view palib/sijiq as select GGMID, <b>char(</b>GCMPNY<b>)</b> as GCMPNY, …

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • nuccio
    Hello, I am not an expert of SQL, but I use it to import files from As400 in Excel directly, in the case in which there is fields "Packed" in a string use the following method: Example Integer field (3,0): Integer (Substr (char (HEX (SUBSTR (STRING, 1, 2))), 1, 3)) AS Integer003 Example Decimal Field (5,2): (DEC (Substr (char (HEX (SUBSTR (STRING, 1, 3))), 1, 5))) /100 AS Decimal005002 To import data in excel it works, I hope to have been useful
    290 pointsBadges:
    report
  • vibgyor
    BiqKat, Thanks a lot for the suggestion.. It worked.
    280 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following