Post reply

Extracting several rows of information

  • January 31, 2014 at 6:07 am

    #303667

    Hi Guys

    I am very new to SQL and I am busy using microsoft access to learn the syntax.

    I am trying to write a query that selects multiple rows of a table and puts those into a single field.

    I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.

    "Starting Key Word"

    *row data to select*

    *row data to select*

    *row data to select*

    "Ending Key Word"

    Those 3 rows are then put into 1 field.

    I hope you will be able to assist.

    Thanks.

  • January 31, 2014 at 6:14 am

    #1685018

    please share sample data and expected output, and is this query for MSAccess or SQL Server?

  • January 31, 2014 at 6:27 am

    #1685029

    twin.devil (1/31/2014)

    please share sample data and expected output, and is this query for MSAccess or SQL Server?

    Hi sorry, this is an Access Query. All the data that I have is from an OpenVMS Sysuaf.lis file which is completely unorganised, as you can see below, there is no real structure but there are key words which identify what we need to extract.

    IDFullContent

    446Primary days: Mon Tue Wed Thu Fri

    447Secondary days: Sat Sun

    448No access restrictions

    449Expiration: (none) Pwdminimum: 8 Login Fails: 0

    450Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)

    451Last Login: (none) (interactive), (none) (non-interactive)

    452Maxjobs: 0 Fillm: 1024 Bytlm: 130000

    453Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0

    454Maxdetach: 0 BIOlm: 4096 JTquota: 8192

    455Prclm: 10 DIOlm: 4096 WSdef: 100000

    456Prio: 4 ASTlm: 250 WSquo: 200000

    457Queprio: 0 TQElm: 20 WSextent: 210000

    458CPU: (none) Enqlm: 2000 Pgflquo: 4000000

    459Authorized Privileges:

    460ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS

    461CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP

    462GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT

    463NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL

    464PRMMBX PSWAPM READALL SECURITY SETPRV SHARE

    465SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX

    466UPGRADE VOLPRO WORLD

    467Default Privileges:

    Sample Data:

    IDFullContent

    459Authorized Privileges:

    460ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS

    461CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP

    462GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT

    463NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL

    464PRMMBX PSWAPM READALL SECURITY SETPRV SHARE

    465SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX

    466UPGRADE VOLPRO WORLD

    467Default Privileges:

    Output Expected:

    "

    ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CMKRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,GROUP,GRPNAM,GRPPRV,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER…etcc"

    The delimiter can be anything.

  • January 31, 2014 at 6:39 am

    #1685034

    not sure how much help this would be.

    the CTE below is just so I have a table that represents the data you provided.

    only the select is the part you'd want to look at.

    i think this brackets the data you are asking for; it does identify the rows based on a beginning value and an ending value.

    so do you need the subsequent rows as a single row of data?

    ;WITH MyCTE([ID],[FullContent])

    AS

    (

    SELECT '446','Primary days: Mon Tue Wed Thu Fri' UNION ALL

    SELECT '447','Secondary days: Sat Sun' UNION ALL

    SELECT '448','No access restrictions' UNION ALL

    SELECT '449','Expiration: (none) Pwdminimum: 8 Login Fails: 0' UNION ALL

    SELECT '450','Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)' UNION ALL

    SELECT '451','Last Login: (none) (interactive), (none) (non-interactive)' UNION ALL

    SELECT '452','Maxjobs: 0 Fillm: 1024 Bytlm: 130000' UNION ALL

    SELECT '453','Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0' UNION ALL

    SELECT '454','Maxdetach: 0 BIOlm: 4096 JTquota: 8192' UNION ALL

    SELECT '455','Prclm: 10 DIOlm: 4096 WSdef: 100000' UNION ALL

    SELECT '456','Prio: 4 ASTlm: 250 WSquo: 200000' UNION ALL

    SELECT '457','Queprio: 0 TQElm: 20 WSextent: 210000' UNION ALL

    SELECT '458','CPU: (none) Enqlm: 2000 Pgflquo: 4000000' UNION ALL

    SELECT '459','Authorized Privileges:' UNION ALL

    SELECT '460','ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS' UNION ALL

    SELECT '461','CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP' UNION ALL

    SELECT '462','GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT' UNION ALL

    SELECT '463','NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL' UNION ALL

    SELECT '464','PRMMBX PSWAPM READALL SECURITY SETPRV SHARE' UNION ALL

    SELECT '465','SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX' UNION ALL

    SELECT '466','UPGRADE VOLPRO WORLD' UNION ALL

    SELECT '467','Default Privileges:'

    )

    SELECT * FROM MyCTE

    WHERE ID > (SELECT ID FROM MyCTE WHERE FullContent = 'Authorized Privileges:')

    AND ID < (SELECT ID FROM MyCTE WHERE FullContent = 'Default Privileges:')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 31, 2014 at 7:39 am

    #1685054

    Lowell (1/31/2014)

    not sure how much help this would be.

    the CTE below is just so I have a table that represents the data you provided.

    only the select is the part you'd want to look at.

    i think this brackets the data you are asking for; it does identify the rows based on a beginning value and an ending value.

    so do you need the subsequent rows as a single row of data?

    ;WITH MyCTE([ID],[FullContent])

    AS

    (

    SELECT '446','Primary days: Mon Tue Wed Thu Fri' UNION ALL

    SELECT '447','Secondary days: Sat Sun' UNION ALL

    SELECT '448','No access restrictions' UNION ALL

    SELECT '449','Expiration: (none) Pwdminimum: 8 Login Fails: 0' UNION ALL

    SELECT '450','Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)' UNION ALL

    SELECT '451','Last Login: (none) (interactive), (none) (non-interactive)' UNION ALL

    SELECT '452','Maxjobs: 0 Fillm: 1024 Bytlm: 130000' UNION ALL

    SELECT '453','Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0' UNION ALL

    SELECT '454','Maxdetach: 0 BIOlm: 4096 JTquota: 8192' UNION ALL

    SELECT '455','Prclm: 10 DIOlm: 4096 WSdef: 100000' UNION ALL

    SELECT '456','Prio: 4 ASTlm: 250 WSquo: 200000' UNION ALL

    SELECT '457','Queprio: 0 TQElm: 20 WSextent: 210000' UNION ALL

    SELECT '458','CPU: (none) Enqlm: 2000 Pgflquo: 4000000' UNION ALL

    SELECT '459','Authorized Privileges:' UNION ALL

    SELECT '460','ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS' UNION ALL

    SELECT '461','CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP' UNION ALL

    SELECT '462','GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT' UNION ALL

    SELECT '463','NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL' UNION ALL

    SELECT '464','PRMMBX PSWAPM READALL SECURITY SETPRV SHARE' UNION ALL

    SELECT '465','SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX' UNION ALL

    SELECT '466','UPGRADE VOLPRO WORLD' UNION ALL

    SELECT '467','Default Privileges:'

    )

    SELECT * FROM MyCTE

    WHERE ID > (SELECT ID FROM MyCTE WHERE FullContent = 'Authorized Privileges:')

    AND ID < (SELECT ID FROM MyCTE WHERE FullContent = 'Default Privileges:')

    Hi Lowell

    Thanks for the response and effort.

    I get an error saying "At most one record can be returned by this subquery" and then nothing happens.....

  • February 6, 2014 at 3:47 pm

    #1686845

    It's because either or both of the queries are returning more than one ID.

    SELECT ID FROM MyCTE WHERE FullContent = 'Authorized Privileges:'

    SELECT ID FROM MyCTE WHERE FullContent = 'Default Privileges:'

    Do you have, in your data more than one records with FullContent = 'Authorized Privileges:' or FullContent = 'Default Privileges:'?

    If yes, you must get only one ID from the above queries. Use distinct/max/min/ Top 1 as per your requirement.

  • February 6, 2014 at 9:32 pm

    #1686892

    Thanks Amit.

    Yes there are 1780 users in the data, so each would have their own privileges.

    Here is an extract of the file for 2 different users: >>>

    *the words highlighted in blue is what we are trying to extract

    Quote:

    Username: ALANCPS Owner: PENSIONS

    Account: PENSION UIC: [300,0] ([PENSIONS])

    CLI: DCL Tables: DCLTABLES

    Default: CPS_DEVICE:[CPS]

    LGICMD: LOGIN

    Flags: DisUser

    Primary days: Mon Tue Wed Thu Fri

    Secondary days: Sat Sun

    No access restrictions

    Expiration: (none) Pwdminimum: 8 Login Fails: 0

    Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)

    Last Login: (none) (interactive), (none) (non-interactive)

    Maxjobs: 0 Fillm: 1024 Bytlm: 130000

    Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0

    Maxdetach: 0 BIOlm: 4096 JTquota: 8192

    Prclm: 10 DIOlm: 4096 WSdef: 100000

    Prio: 4 ASTlm: 250 WSquo: 200000

    Queprio: 0 TQElm: 20 WSextent: 210000

    CPU: (none) Enqlm: 2000 Pgflquo: 4000000

    Authorized Privileges:

    ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS

    CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP

    GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT

    NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL

    PRMMBX PSWAPM READALL SECURITY SETPRV SHARE

    SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX

    UPGRADE VOLPRO WORLD

    Default Privileges:

    ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS

    CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP

    GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT

    NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL

    PRMMBX PSWAPM READALL SECURITY SETPRV SHARE

    SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX

    UPGRADE VOLPRO WORLD

    AND

    Quote:

    Username: AFFLNETC07 Owner: PENSIONS

    Account: PENSION UIC: [300,0] ([PENSIONS])

    CLI: DCL Tables: DCLTABLES

    Default: CPS_DEVICE:[CPS]

    LGICMD: LOGIN

    Flags: DisCtlY Restricted DisUser

    Primary days: Mon Tue Wed Thu Fri

    Secondary days: Sat Sun

    Primary 000000000011111111112222 Secondary 000000000011111111112222

    Day Hours 012345678901234567890123 Day Hours 012345678901234567890123

    Network: ----- No access ------ ----- No access ------

    Batch: ##### Full access ###### ##### Full access ######

    Local: ##### Full access ###### ##### Full access ######

    Dialup: ----- No access ------ ----- No access ------

    Remote: ##### Full access ###### ##### Full access ######

    Expiration: (none) Pwdminimum: 8 Login Fails: 0

    Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)

    Last Login: (none) (interactive), (none) (non-interactive)

    Maxjobs: 0 Fillm: 1024 Bytlm: 64000

    Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0

    Maxdetach: 0 BIOlm: 150 JTquota: 4096

    Prclm: 2 DIOlm: 150 WSdef: 2000

    Prio: 4 ASTlm: 250 WSquo: 2048

    Queprio: 0 TQElm: 20 WSextent: 16384

    CPU: (none) Enqlm: 2000 Pgflquo: 900000

    Authorized Privileges:

    NETMBX TMPMBX

    Default Privileges:

    NETMBX TMPMBX

    As you can see not all users have the same number of privileges.

    So basically this is my thought process: >>>

    There are 1780 users in the text file.

    For Authorised Privileges:

    -> Read every line until textline = "Authorised Privileges"

    -> Write every line after this to a single string file with a "," delimiter

    -> Stop writing until textline = "Default Privileges"

    Expected output:

    String 1: ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CMKRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,GROUP,GRPNAM,GRPPRV,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER,PFNMAP...etc...,WORLD

    String 2:

    NETMBX,TMPMBX

Viewing 7 posts - 1 through 6 (of 6 total)

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