Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extracting several rows of information Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 6:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:51 PM
Points: 4, Visits: 6
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.
Post #1536739
Posted Friday, January 31, 2014 6:14 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:43 AM
Points: 635, Visits: 1,155
please share sample data and expected output, and is this query for MSAccess or SQL Server?
Post #1536743
Posted Friday, January 31, 2014 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:51 PM
Points: 4, Visits: 6
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.

ID FullContent
446 Primary days: Mon Tue Wed Thu Fri
447 Secondary days: Sat Sun
448 No access restrictions
449 Expiration: (none) Pwdminimum: 8 Login Fails: 0
450 Pwdlifetime: 30 00:00 Pwdchange: (pre-expired)
451 Last Login: (none) (interactive), (none) (non-interactive)
452 Maxjobs: 0 Fillm: 1024 Bytlm: 130000
453 Maxacctjobs: 0 Shrfillm: 0 Pbytlm: 0
454 Maxdetach: 0 BIOlm: 4096 JTquota: 8192
455 Prclm: 10 DIOlm: 4096 WSdef: 100000
456 Prio: 4 ASTlm: 250 WSquo: 200000
457 Queprio: 0 TQElm: 20 WSextent: 210000
458 CPU: (none) Enqlm: 2000 Pgflquo: 4000000
459 Authorized Privileges:
460 ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
461 CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
462 GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
463 NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
464 PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
465 SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
466 UPGRADE VOLPRO WORLD
467 Default Privileges:

Sample Data:

ID FullContent
459 Authorized Privileges:
460 ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
461 CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
462 GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
463 NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
464 PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
465 SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
466 UPGRADE VOLPRO WORLD
467 Default 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.
Post #1536754
Posted Friday, January 31, 2014 6:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 12,890, Visits: 31,849
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1536759
Posted Friday, January 31, 2014 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:51 PM
Points: 4, Visits: 6
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.....
Post #1536781
Posted Thursday, February 6, 2014 3:46 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:12 PM
Points: 827, Visits: 342
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.
Post #1538935
Posted Thursday, February 6, 2014 9:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:51 PM
Points: 4, Visits: 6
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
Post #1538990
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse